The second solution lead us to believe that a full text index was going to save the day. The TSQL query used an INDEX SEEK<\/span> to retrieve data; However, the results were not what was expected.<\/p>\n The third time is a charm. I suggest that we categorize the data during the load process. This can be done with a ETL tool or DML table TRIGGERS. I am going to option for the later since the amount of data we are talking about Drop and recreate the [WILD LIFE] database from scratch. We are going to add a column called [CATERGORY] to the table. It can be defined as an INT or a CHAR. When using a integer, store matches as powers of 2. The bitwise or (&) function can be used to retrive any combination of words you are categorizing. For larger sets of values, use a character array. X’ and O’s and be used to mark the existence of a word. The new table is shown below.<\/p>\n <\/P><\/p>\n A user defined FUNCTION<\/a> is going to be designed to take a [NAME] as an input and return a [CATEGORY] value as a result. The FUNCTION will parse the [NAME] looking for 4 key animals.<\/p>\n <\/P><\/p>\n A user defined TRIGGER<\/a> is going to be added to the [ANIMALS] table. When INSERTS or UPDATES happen to the table, the data will be categorized. Esentially, we are moving the text parsing from the QUERY execution to the DATA loading phase. This technique is only good if the data changes seldomly. Large record changes can make this choice worse than the first solution. Since animal names do not change that often, we have a good solution. <\/p>\n <\/P><\/p>\n Please run the rest of the full script<\/a> to load the table with data.<\/p>\n I am going to search for all animals that contain the word fly. We are going to look at how the query analyzer creates different cost based execution plans. <\/p>\n For a given plan, we are going to look at the time in milliseconds (SET STATISTICS TIME<\/a>) and the io counts (SET STATISTICS IO<\/a>). <\/p>\n To get a good test, we should write dirty pages to disk (CHECKPOINT<\/a>), drop clean buffers from memory (DBCC DROPCLEANBUFFERS<\/a>) and free the procedure cache of any query plans (DBCC FREEPROCCAHE<\/a>). These commands should be completed before executing the query.<\/p>\n The sql snippet below does just that.<\/p>\n <\/P><\/p>\n Here is the query to select all records that have the word ‘fly’ in the name.<\/p>\n
\nis relatively samll.<\/p>\n\r\n-- Create the animals table\r\nCREATE TABLE ANIMALS\r\n(\r\nID INT NOT NULL IDENTITY (1, 1),\r\nNAME VARCHAR(200) NOT NULL,\r\nCATEGORY INT DEFAULT (0)\r\n)\r\nGO\r\n\r\n-- Add index on name (clustered)\r\nCREATE CLUSTERED INDEX IDX_ANIMAL_CATEGORY ON DBO.ANIMALS(CATEGORY);\r\nGO\r\n<\/pre>\n
\r\n-- Create a function, map name -> code\r\nCREATE FUNCTION UFN_MY_CATEGORIES(@NAME VARCHAR(200))\r\nRETURNS INT AS\r\nBEGIN\r\n\r\n -- Default value\r\n DECLARE @RETVAL INT = 0;\r\n \r\n -- 2 POWER 0\r\n IF (LOWER(@NAME) LIKE '%fly%')\r\n SET @RETVAL = @RETVAL + 1;\r\n\r\n -- 2 POWER 1\r\n IF (LOWER(@NAME) LIKE '%pig%')\r\n SET @RETVAL = @RETVAL + 2;\r\n\r\n -- 2 POWER 2\r\n IF (LOWER(@NAME) LIKE '%bat%')\r\n SET @RETVAL = @RETVAL + 4;\r\n\r\n -- 2 POWER 3\r\n IF (LOWER(@NAME) LIKE '%dog%')\r\n SET @RETVAL = @RETVAL + 8;\r\n \r\n -- RETURN THE CODE\r\n RETURN(@RETVAL);\r\n \r\nEND;\r\n<\/pre>\n
\r\n-- Create a trigger to categorize name\r\nCREATE TRIGGER TRG_CATEGORIZE_ANIMALS ON ANIMALS\r\nAFTER INSERT, UPDATE AS\r\nBEGIN\r\n\r\n -- Detect inserts (1 .. n)\r\n IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) \r\n BEGIN \r\n -- Update records\r\n UPDATE ANIMALS\r\n SET CATEGORY = dbo.UFN_MY_CATEGORIES(I.NAME)\r\n FROM ANIMALS A JOIN INSERTED I ON A.ID = I.ID;\r\n \r\n -- Debugging \r\n PRINT 'INSERT DETECTED, CREATED CATEGORY';\r\n END; \r\n \r\n -- Detect deletes (1 .. n)\r\n IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted) \r\n BEGIN\r\n -- Debugging \r\n PRINT 'DELETE DETECTED, NOTHING TO DO';\r\n END;\r\n \r\n -- Detected updates (1 .. n)\r\n IF EXISTS (SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)\r\n BEGIN \r\n -- Update records\r\n UPDATE ANIMALS\r\n SET CATEGORY = dbo.UFN_MY_CATEGORIES(I.NAME)\r\n FROM ANIMALS A JOIN INSERTED I ON A.ID = I.ID;\r\n \r\n -- Debugging \r\n PRINT 'UPDATE DETECTED, ADJUST CATEGORY';\r\n END; \r\n \r\nEND\r\nGO\r\n<\/pre>\n
\r\n-- Show time & i\/o\r\nSET STATISTICS TIME ON\r\nSET STATISTICS IO ON\r\nGO\r\n\r\n-- Remove clean buffers & clear plan cache\r\nCHECKPOINT \r\nDBCC DROPCLEANBUFFERS \r\nDBCC FREEPROCCACHE\r\nGO\r\n<\/pre>\n
\r\n-- Select everything with word 'fly'\r\nSELECT * FROM dbo.ANIMALS WHERE NAME LIKE '%FLY%'\r\nGO\r\n<\/pre>\n<\/p>\n
![\"\"](\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/02\/categorized-results.jpg\" "\\"categorized-results\\"")
<\/a><\/p>\n![\"\"](\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/02\/categorized-query-plan.jpg\" "\\"categorized-query-plan\\"")
<\/a><\/p>\n