THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

How to make text searching go faster

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at: http://blogs.lobsterpot.com.au/2016/03/08/how-to-make-text-searching-go-faster/

Published Tuesday, March 8, 2016 2:39 PM by Rob Farley

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Hi Rob

It is great post, but question is not answered :-))) Waiting for your investigation.

March 7, 2016 11:56 PM
 

Rob Farley said:

You mean the question about why a Hash is 10% (ish) faster in a binary collation?

It's because with a non-binary collation, it needs to make a call to GetSortKey(), so that it can hash a value which is equal for all the things that sort to the same point in that collation. Like how in a CI collation, H and h need to end up in the same hash bucket. In a binary collation, it doesn't have to call GetSortKey(), because things are only equal when they have exactly the same binary value.

I worked this out by asking someone who had access to a debugger which they regularly hook SQL Server into. He's a good friend, and happens to live only a few time zones away from me. (Thanks Paul!)

March 8, 2016 12:19 AM
 

Uri Dimant said:

I am aware of this Rob.

Using binary collation as you pointed out may return incorrect data (not all data found)

March 9, 2016 1:56 AM
 

Rob Farley said:

Yes. If you need to find "Uri" and "URI" and "uri", then you can't use a binary collation. But if it's always stored as "URI", then you can.

March 9, 2016 2:06 AM
 

Saeid Hasani said:

There are many situation that we can use this method such as codes, barcodes, national security codes etc.

http://social.technet.microsoft.com/wiki/contents/articles/31238.t-sql-improve-the-performance-for-like-wildcard-by-changing-the-collation.aspx

March 25, 2016 7:54 PM
 

Rob Farley said:

Yes, Saeid. Lots of situations. Nice article - I wasn't aware of it. I presented this as one of my T-SQL Tips at TechEd AU back in 2008, but it wasn't new then either. Interesting that you refer to Brent's article on Sargability - he refers to one of mine in his, and was in the audience for my SQLBits session on it in 2010, which you can see at http://bit.ly/Sargability

March 25, 2016 8:16 PM
 

Saeid Hasani said:

Thanks Rob for your fast response. By referring to that article , I wanted to confirm your solution. I'm not against Uri's idea. He is a bright person in SQL world. But, this solution has its opponents (like few comments on my article). So, I was become happy when I read your great post. I didn't consider Brent's article references. I downloaded your SQLBits session and I saw it . It is an awesome session. Wish I saw it few years ago! :-) Thank you so much!

March 25, 2016 9:03 PM
 

Rob Farley said:

No worries, and thanks for supporting it.

March 25, 2016 9:12 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement