Deep Dive on new Search Features in Denali CTP1

Based on the white paper by Naveen Garg

Search Improvements FullText Search
•Revamped Codebase for Significant Performance and Scale Improvement
•New Property-scoped Search
•Customizable NEAR Search

Performance & Scale Goals
•Scale up to 350M documents
•Query magnitudes faster than 2008 release
•Worst-case Query response time < 3 sec
•At par or better than key DBMS players

Summary Of Code Improvements
•Faster Execution
–Numerous code and data layout improvements
–No blocking during high index update workloads
–Improved mixed relational query processing
–Optimize Top N by Rank
•10x: Select top 1K by score for keyword in 1M docs (250ms -> 28ms)
•Leverage CPU
–Cache for Operators and Core Algorithms
•Batch decompression and rank computation, virtual functions
–Vector CPU instructions (SSE*) for scalar computations
•Ranking, TOP N, and Stale Test as major benefiters
•Leverage multicore
–Parallel Query execution
–Parallel Master Merge

Performance & Scale Summary
•Index and Query tested on scale up to 350Million documents with < ~2 Sec Response
–~3X better w/o DML and ~9X better w DML throughput
–Scale easily with increasing number of connections
•TAP customers already reporting significant performance improvement on their workloads

Indexing Properties with Keywords
•Stored along with keywords but with additional Internal Property ID (s)

Property Scoped Search
•Load Office Filters (needed once per database instance)
–EXEC sp_fulltext_service ‘load_os_resources’,1;
–EXEC sp_fulltext_service ‘restart_all_fdhosts„;
•Create a property list
–CREATE SEARCH PROPERTY LIST p1;
•Add properties to be extracted
–ALTER SEARCH PROPERTY LIST [p1] ADD N’System.Author’ WITH
– (PROPERTY_SET_GUID = ‘f29f85e0-4ff9-1068-ab91-08002b27b3d9’,
– PROPERTY_INT_ID = 4, PROPERTY_DESCRIPTION = N’System.Author’);
•Create/Alter Fulltext index to specify property list to be extracted
–ALTER FULLTEXT INDEX ON fttable… SET SEARCH PROPERTY LIST = [p1];
•Query for properties
–SELECT * FROM fttable WHERE
– CONTAINS(PROPERTY(ftcol, ‘System.Author’), ‘fernlope’);

Customizable NEAR
•Search for documents with two words a distance apart
Old NEAR Usage
SELECT * FROM fttable WHERE CONTAINS(*, ‘test NEAR Space’)
New NEAR Usages
•Specify Distance
SELECT * FROM fttable WHERE CONTAINS(*, „NEAR((test, Space), 5,FALSE)’)
•Reduce Distance
SELECT * FROM fttable WHERE CONTAINS(*, „NEAR((test, Space), 2,FALSE)’)
•Mandate Order of words
SELECT * FROM fttable WHERE CONTAINS(*, „NEAR((test, Space), 5,TRUE)’)

Click here Download this PDF white paper by Naveen Garg