{"id":7342,"date":"2017-02-08T14:43:53","date_gmt":"2017-02-08T16:43:53","guid":{"rendered":"http:\/\/www.fabriciolima.net\/?p=7342"},"modified":"2017-02-08T15:12:29","modified_gmt":"2017-02-08T17:12:29","slug":"improve-the-query-performance-with-like-string-changing-only-the-collation","status":"publish","type":"post","link":"https:\/\/fabriciolima.net\/blog\/2017\/02\/08\/improve-the-query-performance-with-like-string-changing-only-the-collation\/","title":{"rendered":"Improve the performance of a query that uses ” like ‘%String%’ ” changing only the collation"},"content":{"rendered":"

Hi Folks,<\/p>\n

In this blog post, I will give you a very useful tip to improve the performance of a query that uses ” like ‘%String%’ “<\/em>.<\/p>\n

How often do you have to use a query like this?<\/p>\n

Select Columns,...\r\nfrom Table\r\nwhere Name like '%String%'<\/pre>\n
Reading the great book SQL Server 2012 Internals<\/em><\/a>, on page 230, we have:<\/p>\n
“Another case where the collation can make a huge difference is when SQL Server has to look at almost all characters in the strings. For instance, look at the following:<\/em><\/p>\n
SELECT COUNT(*) FROM tbl WHERE longcol like ‘%abc%’<\/em><\/p>\n
This might execute 10 time faster or more with a binary collation than a nonbinary Windows colation. Also, with varchar data, this executes up to seven or eight times faster with a SQL collation than with a Windows collation. If you have a varchar column, you can speed this up by forcing the collation as follows:<\/em><\/p>\n
SELECT COUNT(*) from tbl<\/em>
\n WHERE longcol COLLATE SQL_Latin1_Genneral_CP_CI_AS LIKE ‘%abc%’ “<\/em><\/p>\n
That’s a very important information. Let’s do some tests and see what’s happen.<\/p>\n
I will create a table with a varchar column and populate it with a lot of data.<\/p>\n
CREATE table dbo.Test_Collation_SQL (\r\nId_Table int identity(1,1) PRIMARY KEY,\r\nDt_Log datetime,\r\nDs_Name varchar(50)\r\n)\r\n\r\n-- Generate a lot of data. Can take a while...\r\nINSERT INTO dbo.Test_Collation_SQL\r\nselect getdate(), REPLICATE('A',50)\r\nGO 10\r\n\r\nINSERT INTO dbo.Test_Collation_SQL(Dt_Log,Ds_Name)\r\nSELECT Dt_Log,Ds_Name\r\nFROM Test_Collation_SQL\r\nGO 22<\/pre>\n
Now, I will insert two rows with my name to search later:<\/p>\n
insert into dbo.Teste_Collation_SQL\r\nselect getdate(), 'Fabricio Lima 1'\r\n\r\ninsert into dbo.Teste_Collation_SQL\r\nselect getdate(), '- Fabricio Lima 2'<\/pre>\n
Then I will create a index to try do help my query, just like everyone do:<\/p>\n
CREATE NONCLUSTERED INDEX SK01_Test_Collation_SQL ON Test_Collation_SQL(Ds_Name)  WITH(FILLFACTOR=90)<\/pre>\n
The size of the table is:<\/p>\n
\"\"<\/p>\n
Now I will enable this option to compare the results<\/p>\n
SET STATISTICS IO ON\r\nSET STATISTICS TIME ON<\/pre>\n
I also enable the Actual Execution Plan to see what’s SQL doing: CTRL+M<\/p>\n
My instance has this Windows Collation: Latin1_General_CI_AI<\/p>\n
My Database and Column also has the same Collation: Latin1_General_CI_AI<\/p>\n
Test 1: Using my default Windows Collation: Latin1_General_CI_AI <\/strong><\/h6>\n
SELECT COUNT(*)\r\nFROM Test_Collation_SQL\r\nWHERE Ds_Name LIKE '%Fabricio%'<\/pre>\n
The results \u00e9 two rows<\/strong> and this query took 45 secods<\/strong> to finish. It’s not cache problems because I’ve executed it three times.<\/p>\n
\"\"<\/p>\n
Reads<\/strong>
\nTable ‘Test_Collation_SQL’. Scan count 9, logical reads<\/strong> 373572<\/strong><\/p>\n
CPU Time<\/strong>
\nSQL Server Execution Times:
\nCPU time =<\/strong> 348922 ms<\/strong>,\u00a0 elapsed time = 45679 ms.<\/p>\n
<\/h6>\n
 Teste 2: Forcing a SQL Server collation and searching the same thing<\/strong><\/h6>\n
SELECT COUNT(*)\r\nFROM TreinamentoDBA..Teste_Collation_SQL\r\nWHERE Descri\u00e7\u00e3o COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%Fabricio%'<\/pre>\n
The results is two rows<\/strong> and this query took only 5 secods<\/strong>!!! Amazing!!! Unbelievable!!! It’s like magic!!<\/strong><\/p>\n
\"\"<\/p>\n
Reads<\/strong>
\nTable ‘Test_Collation_SQL’. Scan count 9, logical reads 374622<\/strong><\/p>\n
CPU Time<\/strong>
\nSQL Server Execution Times:
\nCPU time =<\/strong> 37265 ms<\/strong>,\u00a0 elapsed time = 5300 ms.<\/p>\n
The logical reads is almost the same, but we have a huge difference in the CPU Time.<\/p>\n
The two queries have the same execution Plan doing a Scan on the index SK01_Test_Collation_SQL and working on parallel.<\/p>\n
Results<\/strong><\/h6>\n
Windows Collation: 45 seconds and 348922 ms of CPU time<\/p>\n
SQL Collation: 5 seconds and\u00a0 37265 ms of CPU time<\/p>\n
Unbelievable!!!<\/strong><\/span><\/p>\n
Why does it happen?<\/p>\n
This article help us to understand:<\/p>\n
https:\/\/support.microsoft.com\/en-us\/help\/322112\/comparing-sql-collations-to-windows-collations<\/a><\/p>\n
“If you are storing and handling your data by using non-Unicode data types (char<\/span>, varchar<\/span>, text<\/span>), and you are using a SQL collation<\/strong>, string comparisons will be performed with a non-Unicode SQL sort order<\/strong>.<\/em><\/p>\n
If you are storing and handling your data by using non-Unicode data types (char<\/span>, varchar<\/span>, text<\/span>), and you are using a Windows collation<\/strong>, string comparisons will be performed with the Unicode sorting rules<\/strong>. This may cause<\/strong> certain operations that are unusually dependent on string sorting performance to take longer and to use more CPU<\/span> than a similar operation that is performed with a SQL collation.<\/strong><\/em>”<\/p>\n
I did another test with more data and the results are:<\/strong><\/p>\n
Windows Collation:<\/strong> 1 minute and 48 seconds<\/p>\n
SQL Collation:<\/strong> 12 seconds<\/p>\n
It’s like a magic! A huge difference!!
\n<\/span><\/p>\n
Enjoy and share with your friends…<\/p>\n
Thanks to my friend Edvaldo (Blog<\/a>) for the revision of the text.<\/p>\n
Fabr\u00edcio Fran\u00e7a Lima<\/p>\n
MCITP \u2013 Database Administrator<\/p>\n","protected":false},"excerpt":{"rendered":"
Hi Folks, In this blog post, I will give you a very useful tip to improve the performance of a query that uses ” like ‘%String%’ “. How often do you have to use a query like this? Select Columns,… from Table where Name like ‘%String%’ Reading the great book SQL Server 2012 Internals, on […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[33],"tags":[1459,475,1456,1453,1434,1458,1457,1450,1452,1451,1455,33,1454],"class_list":["post-7342","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-collate-sql_latin1_general_cp1_ci_ai","tag-collation","tag-collation-bin","tag-collation-performance","tag-collation-sql-server","tag-latin1_general","tag-latin1_general_ci_ai","tag-like","tag-performance-problem","tag-performance-tuning","tag-sql-collation","tag-sql-server","tag-windows-collation"],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/7342","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/comments?post=7342"}],"version-history":[{"count":0,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/7342\/revisions"}],"wp:attachment":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/media?parent=7342"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/categories?post=7342"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/tags?post=7342"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}