![]() What if the query was 90%+ done when you cancelled it? How fast do you truly need the query to be? How often will you run an update like this? The only other change was to add a clustered index to the items table: CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.items (id) Īre you sure that you need it to be faster? You cancelled the query after 10 minutes but you don't really have a way to judge progress. (GBT.trigram1, GBT.trigram2, GBT.trigram3) AS MID WHERE ET1.trigram = Search implementation Returns Example ids matching all provided (non-null) trigrams Generate trigrams for the search string Trigram3 = MAX(CASE WHEN BT.rn = 3 THEN BT.trigram END) Trigram2 = MAX(CASE WHEN BT.rn = 2 THEN BT.trigram END), Trigram1 = MAX(CASE WHEN BT.rn = 1 THEN BT.trigram END), Always returns a row (NULLs if no trigrams found)ĬREATE FUNCTION dbo.Items_GetBestTrigrams varchar(255)) Most selective trigrams for a search string Selectivity of each trigram (performance optimization)ĬREATE OR ALTER VIEW dbo.ItemsTrigramCounts T.trigram COLLATE Latin1_General_BIN2 NOT LIKE '%%' ĬROSS APPLY dbo.GenerateTrigrams(E.body) AS GT Binary collation comparison so ranges work as expected Remove duplicates and ensure all three characters are alphanumeric SELECT TOP (CASE WHEN > 2 THEN - 2 ELSE 0 END) SELECT n = ROW_NUMBER() OVER (ORDER BY A.v) The modified trigram scripts from my article are below: CREATE FUNCTION dbo.GenerateTrigrams varchar(255)) ![]() The trigram structures built in around 2 seconds and the following update statement completed in 5 seconds on my mid-range laptop: UPDATE dbo.words WITH (TABLOCK) I loaded the words table with 7,669 unique words from the same text. I ran a quick test using the Complete Works of Shakespeare to populate the body column of the items table with 15,838 rows. The extra storage requirements, implementation complexity, and impact on update performance all weigh heavily against it. It may be suitable for your needs, but be aware:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |