Did you ever see a change of a collation change drastically a performance of a query?

Question

Did you ever see a change of a collation change drastically a performance of a query?

FabricioLimaDBA

Mr or Mrs. 500

Points: 507
More actions
February 9, 2017 at 4:58 am

#318594

Hi Guys,
Did you ever see a change of a collation change drastically a performance of a query?
The results are amazing and I don't remember to read something with the performance results of a test like this.
I did one test: https://www.fabriciolima.net/blog/2017/02/08/improve-the-query-performance-with-like-string-changing-only-the-collation/
What do you think about SQL Collation be the default collation after thats results?
Thanks for your time.
Fabrício França Lima
MCITP – Database Administrator
https://www.fabriciolima.net/Blog

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

FabricioLimaDBA - Thursday, February 9, 2017 4:58 AM
Hi Guys,
Did you ever see a change of a collation change drastically a performance of a query?
The results are amazing and I don't remember to read something with the performance results of a test like this.
I did one test: https://www.fabriciolima.net/blog/2017/02/08/improve-the-query-performance-with-like-string-changing-only-the-collation/
What do you think about SQL Collation be the default collation after thats results?
Thanks for your time.

It's probably not as well known as it should be. Here's a reference on ssc.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

FabricioLimaDBA - Thursday, February 9, 2017 4:58 AM
Hi Guys,
Did you ever see a change of a collation change drastically a performance of a query?
The results are amazing and I don't remember to read something with the performance results of a test like this.
I did one test: https://www.fabriciolima.net/blog/2017/02/08/improve-the-query-performance-with-like-string-changing-only-the-collation/
What do you think about SQL Collation be the default collation after thats results?
Thanks for your time.

It's been known for a long time that collation can really affect performance for string comparisons but I like your article on the subject. I especially like the fact that you did NOT resort to a case sensitive/binary collation, which I previously thought was necessary for the kind of improvements that most folks seek. Thanks for posting the link to your good article. I, for one, have some new testing to do on a new instance. 😉 I want to make sure that the collation used in TempDB and (ugh!) SSRS don't cause any mismatched collation errors.

Let's just hope they never deprecate the collation you used as they have with several others. 😉 That's not a dig at you... that's a dig at Microsoft.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

p.s. As a bit of a sidebar, do notice that the server actually did more work (CPU) than with the original collation. All other info being absent, it does appear that duration was reduced only because parallelism occurred. It would be interesting to see if a binary collation would do any better using your same test data and conditions. My gut tells me "yes", it would make quite a difference because there would be no translations behind the scenes if a binary collation were used.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

FabricioLimaDBA Mr or Mrs. 500 Points: 507 More actions · Answer 4

Jeff Moden - Saturday, February 11, 2017 11:57 AM
p.s. As a bit of a sidebar, do notice that the server actually did more work (CPU) than with the original collation. All other info being absent, it does appear that duration was reduced only because parallelism occurred. It would be interesting to see if a binary collation would do any better using your same test data and conditions. My gut tells me "yes", it would make quite a difference because there would be no translations behind the scenes if a binary collation were used.

Hi Jeff.
No. The two plans are the same. Both use parallelism. The CPU used with the windows collations is bigger than sql collation. Try to test in a VM to see the results.
About binary collation, Yes, it is still more fast. Butbinary is CS_AS and I can't use in my case.

Thanks for the answer.

Fabrício França Lima
MCITP – Database Administrator
https://www.fabriciolima.net/Blog