Querying Multilple fields for the same string (MS SQL)

I have a program, that needs to search for a value within any of 24 fields of the same type. They are all string fields and the query looks like this:


SELECT  distinct Product.sys_pro_id FROM Product 
INNER JOIN Barcode ON Product.sys_pro_id = Barcode.sys_id 
LEFT OUTER JOIN Product_fin ON Product.sys_pro_id = Product_fin.sys_pro_id 
LEFT OUTER JOIN Owner ON Product.sys_who_id = Owner.sys_who_id 
LEFT OUTER JOIN Where_sql ON Product.sys_whr_id = Where_sql.sys_whr_id 
LEFT OUTER JOIN Supplier ON Product.sys_sup_id = Supplier.sys_sup_id 
LEFT OUTER JOIN Model ON Product.sys_mod_id = Model.sys_mod_id 
LEFT OUTER JOIN Manuf ON Product.sys_mfr_id = Manuf.sys_mfr_id 
LEFT OUTER JOIN Dep_rules ON Product.sys_dep_id = Dep_rules.sys_dep_id 
WHERE (Barcode.type = 'P')  
AND ((Product.sys_pro_id LIKE '%100%') 
OR (Barcode.barcode LIKE '%100%') 
OR (Product.old_asset LIKE '%100%') 
OR (Product.desc_short LIKE '%100%') 
OR (Product.desc_full LIKE '%100%') 
OR (Product.ser_num LIKE '%100%') 
OR (Product.comments LIKE '%100%') 
OR (Product.class LIKE '%100%') 
OR (Product.status LIKE '%100%') 
OR (Product.coll_code LIKE '%100%') 
OR (Dep_rules.dep_code LIKE '%100%') 
OR (Product.contract LIKE '%100%') 
OR (Product.po LIKE '%100%') 
OR (Product.time_stamp LIKE '%100%') 
OR (Manuf.name_full LIKE '%100%') 
OR (Model.name_full LIKE '%100%') 
OR (Supplier.name_full LIKE '%100%') 
OR (Supplier.short_code LIKE '%100%') 
OR (Where_sql.name_full LIKE '%100%') 
OR (Owner.name_full LIKE '%100%') 
OR (Owner.name_first LIKE '%100%') 
OR (Product_fin.Type1 LIKE '%100%') 
OR (Product_fin.Type2 LIKE '%100%') 
OR (Product_fin.Type3 LIKE '%100%') 
OR (Product_fin.Type4 LIKE '%100%') 
OR (Product_fin.Type5 LIKE '%100%') 
OR (Product_fin.Type6 LIKE '%100%')) 
ORDER BY product.sys_pro_id DESC 

Is there a simpler way to get the same result. I was thinking of something alone the lines of


SELECT  distinct Product.sys_pro_id FROM Product 
INNER JOIN Barcode ON Product.sys_pro_id = Barcode.sys_id 
LEFT OUTER JOIN Product_fin ON Product.sys_pro_id = Product_fin.sys_pro_id 
LEFT OUTER JOIN Owner ON Product.sys_who_id = Owner.sys_who_id 
LEFT OUTER JOIN Where_sql ON Product.sys_whr_id = Where_sql.sys_whr_id 
LEFT OUTER JOIN Supplier ON Product.sys_sup_id = Supplier.sys_sup_id 
LEFT OUTER JOIN Model ON Product.sys_mod_id = Model.sys_mod_id 
LEFT OUTER JOIN Manuf ON Product.sys_mfr_id = Manuf.sys_mfr_id 
LEFT OUTER JOIN Dep_rules ON Product.sys_dep_id = Dep_rules.sys_dep_id 
WHERE (Barcode.type = 'P')  
AND (Barcode.barcode or Product.sys_pro_id or Product.old_asset or Product.desc_short or Product.desc_full or Product.ser_num or Product.comments or Product.class or  Product.status or Product.coll_code or Dep_rules.dep_code or Product.contract or Product.po or Product.time_stamp or Manuf.name_full or Model.name_full or Supplier.name_full or Supplier.short_code or Where_sql.name_full or Owner.name_full or Owner.name_first or Product_fin.Type1 or Product_fin.Type2 or Product_fin.Type3 or Product_fin.Type4 or Product_fin.Type5 or Product_fin.Type6 like '%100%' )
ORDER BY product.sys_pro_id DESC 

Also are there any tips for speeding this query up, or is it always going to be relatively slow with so many likes?

Thanks, Colin

unfortunately the syntax must remain as you first posted it, each condition specifying the column name and the LIKE predicate

(note you don’t need parentheses around them)

and there is no way to speed it up – every one of those conditions will slow it down, as it requires a complete table scan because of the leading wildcard in the LIKE string

If you try and do this in straight SQL, you are SOL as r937 points out. But what you can do is use full-text indexing to handle the search. How to accomplish this in a pretty transparent way is:

a) create a table for your indexing. Needs to at least have the ID column (presumably sys_pro_id) and one VARCHAR(MAX) to hold the indexable data.
b) Add UPDATE, INSERT and DELETE TRIGGERS to populate the indexable data, which should be every single one of those columns you want to search in.
c) Use CONTAINS() to find a list of IDs that applies.

You could push some other things – like the Barcode.type – in there if you wanted. You are looking to create a flat search structure that can get you to a list of IDs that you can use to go back and grab the appropriate records.

This could also be accomplished with UNION statements which could move some of the LEFT OUTER JOINS into separate subqueries and may be more easily parsed.

Also, when you are searching multiple fields for the same value, you could concatenate all of the fields together in the individual union query or subquery and then search the concatenated field.

UNION still gets you the same underlying performance problem . . .

Thanks for the tip. I was not sure of the speed impact for many LOJ vs UNIONS. Regardless, your approach will be better for speed. Just posting an alternative query structure that might be easier to read in case the OP is not able to create additional tables and triggers.

Gotcha. FWIW, I think the joins are not the issue here, but rather the non-sargable LIKE ‘%100%’ statements that killed you. Now, no one can have any real idea without a copy of the execution plan.