Searching SQL Server

[omalleytrading]

Are there any SQL Server experts on the board?

I've got a problem with a complex search for an e-commerce site I manage. When someone types a keyword or phrase into the search field, I need to be able to search several fields across 2 or 3 tables. Even with good indexing the query can be very resource intensive when the WHERE clause is like:

WHERE t.field1 LIKE '%keyword%'
OR t.field2 LIKE '%keyword%'
OR u.field1 LIKE '%keyword%'

...you get the idea.

Unfortunately, I can't use the built in FULL TEXT INDEXING service of SQL Server because many of the fields contain things like product codes with dashes, periods, slashes, etc. FTI chokes on those, which prevents you from doing something useful like searching by product code.

This must be a common problem in the e-commerce industry. Anyone have other ideas?

Thanks in advance!

[qball]

Quote:

Are there any SQL Server experts on the board?

I've developed on SQLServer, but your question is more just SQL.

Quote:

This must be a common problem in the e-commerce industry. Anyone have other ideas?

[Creosote]

I use SQL Server extensively at work, and I kind of understand what you mean.

There are numerous factors that probably play a role in your performance issue. One is the fact that if you are paying for SQL space, the server itself may not be up to snuff compared with what you would like.

A second would be the design issue of your database. While not as well-rounded, you might want to implement a keyword table. If the site receives enough hits, you might take a survey behind the seens and save common phrases. With those common phrases, and maybe some similar ones, populate a keyword table and use that to return results from your main table. If the keyword is not found, then query your tables.

Also, you could implement an option for the user--I've seen some sites where the users checks 'starts with', 'exact match', etc.
With that, you could more optimize your query instead of always using 2 wildcards in your LIKE clause.

Since you are querying more than one table, I assume that each table has logical factors in common. Maybe ask the user to break it down for you as well. If one table was Cars and one was Trucks, give the user and option to pick which category, then send off his query for windsheiled wiper blades.

The fewer joins you have in your query should help speed things up for you.

[omalleytrading]

Thanks for the response guys.

qball - the UNION is an interesting idea. I'd always thought they were among the most expensive types of queries, but perhaps I was wrong. I'm not sure I can use them in my case due to the way the data is distributed (and what I need returned), but I'll certainly give it some thought.

Creosote - you're indexing idea is something I've been thinking about lately. If I could index all of the product pages up front, then just search the index table, it might be much faster than trying to search 2, 3 or 4 joined tables.

There are several commercial packages on the market that will do such a thing, but none that I've found that will give me the flexibility to display the results the way I want them. Now I'm hoping to find some source code out there on the web that I can use as a starting point for my own system.

It's a bit frustrating. The Full Text features of SQL Server are so close to being able to do everything I need. They just fall a bit short.

[qball]

Quote:

I'd always thought they were among the most expensive types of queries, but perhaps I was wrong.

UNION as expensive as the SELECTs they union.

a SELECT with multiple tbl JOINs on provisional WHERE clause, can be defeated with UNION., maybe.

No type of indexing, given your original WHERE clause will help much. Not indexing problem.

Quote:

Problem more for anyone who wants to search multiple tables or fields simultaneously in WHERE clause.

[omalleytrading]

qball - This is a sample query

SELECT p.id, p.code, p.name, p.thumbnail, p.mfrpartnum, p.price, m.name AS mfr, '' AS pricing, '' AS type
FROM CSICproducts p INNER JOIN
CUApricing pr ON p.id = pr.id LEFT OUTER JOIN
CSICmanufacturers m ON p.store = m.store AND p.manufacturer = m.id
WHERE (p.store = 8)
AND (p.active = 'Y')
AND (pr.type = '')
AND ((p.code LIKE '%elmo%'
OR p.name LIKE '%elmo%'
OR p.[desc] LIKE '%elmo%'
OR m.name LIKE '%elmo%'
OR p.mfrpartnum LIKE '%elmo%' ) )
ORDER BY p.name ASC

And the Execution plan is attached. I don't quite understand it all that well. I've made sure there are no table scans. Beyond that, I'm not sure what to look for.

[Creosote]

Have you tried convertinig into a sproc?

Something like:

CREATE PROCEDURE p_QueryProducts
@Criteria NVARCHAR(50)
AS

SELECT p.id, p.code, p.name, p.thumbnail, p.mfrpartnum, p.price, m.name AS mfr, '' AS pricing, '' AS type 
FROM CSICproducts p INNER JOIN 
CUApricing pr ON p.id = pr.id LEFT OUTER JOIN 
CSICmanufacturers m ON p.store = m.store AND p.manufacturer = m.id 
WHERE (p.store = 8) 
AND (p.active = 'Y') 
AND (pr.type = '') 
AND ((p.code LIKE '%' + @Criteria + '%' 
OR p.name LIKE '%' + @Criteria + '%' 
OR p.[desc] LIKE '%' + @Criteria + '%'  
OR m.name LIKE '%' + @Criteria + '%'  
OR p.mfrpartnum LIKE '%' + @Criteria + '%'  ) ) 
ORDER BY p.name ASC

[qball]

omalleytrading,

great reply.

Quote:

Have you tried convertinig into a sproc?

If SQL slow, storedproc, little help. Creating view, may help, but if SQL slow...

you can do whatever, but lets break down.

You have 3 tbls:

"FROM CSICproducts=p
CUApricing=pr
CSICmanufacturers=m"

prod without price or manuf, doesn't make much sense.

what do you want the query to return? exactly?
what do you want the user to search on?

show me all products, if price exists, if manuf, exists that have name LIKE %qball%?

you want the user to find prods, but how?

[omalleytrading]

First, THANK YOU to both of you for your generous help -- I really do appreciate it.

Quote:

Have you tried convertinig into a sproc?

Not really. The example I gave is quite tame. If the user enters in something like "Sony Internal DVD", then the query gets to be HUGE...

AND ((p.code LIKE '%Sony%'
OR p.code LIKE '%Internal%'
OR p.code LIKE '%DVD%'
OR p.name LIKE '%Sony%'
OR p.name LIKE '%Internal%'
OR p.name LIKE '%DVD%'

... and so on.

I would have to dynamically create the SQL in the Stored Procedure, which defeats a lot of the performace gains.

Quote:

prod without price or manuf, doesn't make much sense.

[Creosote]

Quote:

If SQL slow, storedproc, little help. Creating view, may help, but if SQL slow...

I seriously doubt a view would help if a sproc wouldn't. I think it would be slower than a sproc.

If you're querying each word as separate criteria, why not query each separately. Return results from all in the output, looking like one query. Three queries for three words. Might not help a lot, but it might.