SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Ottawa, Canada
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:

    Code SQL:
    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

    Code SQL:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Member phobucket's Avatar
    Join Date
    Jan 2011
    Location
    Chilladelphia
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    UNION still gets you the same underlying performance problem . . .

  6. #6
    SitePoint Member phobucket's Avatar
    Join Date
    Jan 2011
    Location
    Chilladelphia
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •