{"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":"","jetpack_post_was_ever_published":false},"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"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":7307,"url":"https:\/\/fabriciolima.net\/blog\/2017\/02\/06\/video-melhorando-a-performance-de-uma-consulta-com-like-string-alterando-a-collation\/","url_meta":{"origin":7342,"position":0},"title":"V\u00eddeo: Melhorando a performance de uma consulta com like ‘%String%’ alterando a Collation","author":"Fabr\u00edcio Lima","date":"6 de fevereiro de 2017","format":false,"excerpt":"Fala Pessoal, Nesse v\u00eddeo de hoje vou\u00a0dar uma dica de tuning que\u00a0s\u00f3 aprendi agora em 2017. Uma coisa simples que\u00a0pode melhorar drasticamente uma consulta como essa abaixo: Select\u00a0Colunas,XXX FROM Tabela WHERE Coluna like '%String%' \u00c9 s\u00e9rio... Parece m\u00e1gica... Assista... Teste... Compartilhe... Veja no v\u00eddeo abaixo: https:\/\/www.youtube.com\/watch?v=cyteAND34I0 Segue os scripts utilizados\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/img.youtube.com\/vi\/cyteAND34I0\/0.jpg?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1104,"url":"https:\/\/fabriciolima.net\/blog\/2012\/01\/10\/casos-do-dia-a-dia-erro-de-collation-em-uma-consulta\/","url_meta":{"origin":7342,"position":1},"title":"Casos do Dia a Dia – Erro de Collation em uma Consulta","author":"Fabr\u00edcio Lima","date":"10 de janeiro de 2012","format":false,"excerpt":"Fala Pessoal, O foco desse post n\u00e3o \u00e9 explicar o que \u00e9 uma collation, mas sim contar uma experi\u00eancia de duas situa\u00e7\u00f5es que j\u00e1 passei. Quando instalamos o SQL Server ele possui uma collation default chamada SQL_Latin1_General_CP1_CI_AS. Eu particularmente, utilizo na maioria das vezes a collation SQL_Latin1_General_CP1_CI_AI. Agora, imaginem que\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2012\/01\/ErroCollation1.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2012\/01\/ErroCollation1.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2012\/01\/ErroCollation1.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2012\/01\/ErroCollation1.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":19589,"url":"https:\/\/fabriciolima.net\/blog\/2024\/06\/05\/azure-sql-copilot-03-sera-que-ele-consegue-analisar-uma-query-e-sugerir-melhorias\/","url_meta":{"origin":7342,"position":2},"title":"Azure SQL Copilot (#03) – Ser\u00e1 que ele consegue analisar uma query e sugerir melhorias?","author":"Fabr\u00edcio Lima","date":"5 de junho de 2024","format":false,"excerpt":"Fala Pessoal, Seguimos aqui nos testes e feedbacks do SQL Copilot para o time de produto. Dessa vez vou mostrar se ele consegue analisar uma query, que provavelmente ser\u00e1 uma das coisas que os clientes mais devem usar nele, principalmente quem n\u00e3o tem um DBA interno. Criei uma tabela de\u2026","rel":"","context":"Em "Copilot"","block_context":{"text":"Copilot","link":"https:\/\/fabriciolima.net\/blog\/category\/copilot\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2024\/06\/post-301-300x199.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":19561,"url":"https:\/\/fabriciolima.net\/blog\/2024\/05\/31\/azure-sql-copilot-02-qual-query-o-copilot-usa-para-validar-a-performance-do-bd\/","url_meta":{"origin":7342,"position":3},"title":"Azure SQL Copilot (#02) – Qual query o Copilot usa para validar a performance do BD?","author":"Fabr\u00edcio Lima","date":"31 de maio de 2024","format":false,"excerpt":"Fala Pessoal, Seguindo nos testes e posts sobre Copilot para SQL no Azure, hoje vou mostrar a query que ele usa para realizar a valida\u00e7\u00e3o de performance do ambiente. Ao perguntar ao Copilot como voc\u00ea pode melhorar a performance do ambiente, assim como no post anterior, ele responde como est\u00e1\u2026","rel":"","context":"Em "Copilot"","block_context":{"text":"Copilot","link":"https:\/\/fabriciolima.net\/blog\/category\/copilot\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2024\/05\/mensagem-validacao-copilot-300x172.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":9693,"url":"https:\/\/fabriciolima.net\/blog\/2018\/11\/30\/managed-instance-12-configuracao-da-collation-da-instancia\/","url_meta":{"origin":7342,"position":4},"title":"Managed Instance (#12) \u2013 Configura\u00e7\u00e3o da Collation da inst\u00e2ncia","author":"Fabr\u00edcio Lima","date":"30 de novembro de 2018","format":false,"excerpt":"Fala Pessoal, Esse \u00e9 mais um post da s\u00e9rie sobre o Azure SQL Database Managed Instance. Caso ainda n\u00e3o tenha visto, seguem os posts anteriores: Azure SQL DB Managed Instance \u2013 Introdu\u00e7\u00e3o Managed Instance \u2013 Criando minha primeira inst\u00e2ncia Managed Instance \u2013 Como se conectar direto do SSMS utilizando uma\u2026","rel":"","context":"Em "Azure SQL Database"","block_context":{"text":"Azure SQL Database","link":"https:\/\/fabriciolima.net\/blog\/category\/azure-sql-database\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2018\/11\/Managedinstance_Collation_1.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2018\/11\/Managedinstance_Collation_1.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2018\/11\/Managedinstance_Collation_1.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2018\/11\/Managedinstance_Collation_1.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":920,"url":"https:\/\/fabriciolima.net\/blog\/2011\/07\/02\/routine-to-update-database-statistics\/","url_meta":{"origin":7342,"position":5},"title":"Routine to Update Database Statistics","author":"Fabr\u00edcio Lima","date":"2 de julho de 2011","format":false,"excerpt":"Hi Folks, First off all, sorry for my English. I hope you understand me. We always heard that the database statistics must be constantly updated to the Query Optimizer generate the best execution plan for our querys. For this, we should enable the 'Auto Update Statistics' option in our databases.\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"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}]}}