Speeding up a "Super search" 1

[gbaughma]

I have written a little web page that searches through our accounting database.

It takes about 7 seconds to search through a few hundred thousand records, and the reason is because it is using multiple OR's with LIKE's.

So, for example, the query looks similar to this:

SELECT * FROM AccountingDatabase WHERE BillToName LIKE '%@Search%' OR ShipToName LIKE '%@Search%' OR BillToZip = @Search OR ShipToZip LIKE '%@search%' OR CustomerPhone = @Search OR BillToCity LIKE '%@Search%' OR OrderNumber = @Search

... and so on.

So, what would be the "correct" way to do this? I thought about concatenating another field with all of the fields that I am doing LIKE searches on, so I can do a LIKE search just on that one field...

Not all of the criteria are LIKE's... for example, and order number would be an exact match.

This is proving to be a really useful tool, allowing us to type the criteria into one box, and have it search a whole bunch of fields. However, it's time to optimize it for speed.

Thoughts? Comments? Thank you in advance!


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[SQLBill]

The killer is when your search uses a wildcard at the beginning, such as '%@search%'. When you do that, the query cannot use an index and has to go through each row one-by-one. So in your example, first the query has to go through each row to find a match for '%@search%' in column BillToName, then it has to go through each row for the column ShipToName, then ShipToZip, then BillToCity.

If you can eliminate the leading wildcard it will speed up your query.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

One option, if you can narrow down the amount of data being searched, it will speed up your query. For example, let's say you are only looking at a specific date range. You would first do a search for that date range, put the records into a temporary table and then do the search against the temporary table.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gbaughma]

Unfortunately, that's not easily done.

For example, the Bill To and Ship To names are on a single field; for example "James Smith"

.. the sales person will usually try "Jim Smith", then just "Smith".

So the indexes don't work if I lead with a % in a LIKE...
What about full-text indexing on those fields? Would that help?


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

Do you allow searches on all columns, or is it limited to the columns you show in your original post.

The reason I ask is because you may get better performance if you create indexes for each column you allow a search on.

Create Index idx_AccountingDatabase_BillToName On AccountingDatabase(BillToName) Include (PrimaryKeyColumn)

Create Index idx_AccountingDatabase_ShipToName On AccountingDatabase(ShipToName) Include (PrimaryKeyColumn)

etc....

then....

SELECT AccountingDatabase.* 
FROM   AccountingDatabase 
       Inner Join (
         Select PrimaryKeyColumn
         From   Accounting
         WHERE  BillToName LIKE '%@Search%'

         Union

         Select PrimaryKeyColumn
         From   Accounting
         WHERE  ShipToName LIKE '%@Search%'

         Union

         Select PrimaryKeyColumn
         From   Accounting
         WHERE  BillToZip = @Search

         etc....
      ) As A
      On AccountingDatabase.PrimaryKeyColumn = A.PrimaryKeyColumn

I'm not saying this will be better, but it may be worth a try.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

Yes, thank you George. I have added indexes to all of the columns that I am searching on. Sounds like Bill was saying however that the index gets negated when my LIKE starts with a wildcard...



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

I should also mention that having a ton of indexes like this will likely slow down your inserts, updates, and deletes. So... try this to see if it's any faster. If not, drop those indexes. If it is, then also check the performance of inserts, updates, and deletes.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Bill is right. Your like searches will not be able to use an index to SEEK the data, so it will need to scan it instead. However, scanning an index with little bits of data may be faster than scanning a big ole table.

I can say for sure that scanning an index is faster than scanning a table, but in this case, you'll need to scan multiple indexes, which may actually cause the query to run slower. A lot of this depends on the table structure.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

How about trying your own idea?

Simply add a computed field named "Search", in it's formula specify BillToName+ShipToName etc and then only search via Search LIKE '%Search%'

Set the field to be persisted, otherwise this will always be computed on the fly. You might have another side effect, eg if concatenating 'abc'+'def' you will find this via a search for 'cde', because of the search in the concatenated text. To prevent that, put the formula as BillToName+'|+ShipToName+'|'+...

Fulltextindexing is also helpful, but has other pros than performance. Reading about FREETEXT will help you to get a picture:

http://msdn.microsoft.com/en-us/library/ms176078.aspx

This enables you to specify a list of fields and a search term (or phrase) and will also find inflectional forms or synonyms.

You might be better off performancewise in creating the calculated field and move the search only there.

Bye, Olaf.

 

[SQLScholar]

What i have done in the past is to build the SQL query on the fly so that it only does the likes on fields that are populated from the front end search.

So basically if someone types in the order number why do you have the rest of the likes - they are just slowing down the query.

So build up a string of your SQL first build the simple select without a where. Then add on whatever elements of the where clause are required for that specific search.

If you arent doing this already - it should help.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gbaughma]

So what about my concept of building a search table, or field... one that cats the fields that I'm doing a "LIKE" on, into one field?

This way, it would only have to do one pass with the LIKE statement against that one field... does that sound like a viable solution?



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

This way, it would only have to do one pass with the LIKE statement against that one field... does that sound like a viable solution?

I suppose it's worth a try.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lameid]

Something wortht trying if you are concatenating the columns together would be an indexed view...

http://strangenut.com/blogs/dacrowl...indexed-view-in-sql-server-2005-and-2008.aspx

Although the overhead performance of writes and spacewise may make this a pass.