wildcard query of optimization

  • There is an Itemcode field with char(15) in a table named ItemInfo, it has 200 million pieces of records, now there are 3 kinds of query on itemcode field. status field includes values from 0 to 6 , and grade field includes values from 0 to 3, belows the common query, how to optimize the query from the table design to create index and statisctis ? thanks a lot!

    1. it includes the specified characters at any position in the itemcode.

    SELECT Itemcode,SiteID FROM ItemInfo WHERE [Status] = 0 AND CHARINDEX('C8',[ Itemcode]) > 0 AND [grade] != 0

    2. it includes the specified characters at the end of the itemcode, the folloiwng example shows it 3 fixed characters,

    SELECT Itemcode,SiteID FROM ItemInfo WHERE [Status] = 0 AND RIGHT([ Itemcode], 3) = 'CB1' AND [grade] != 0

    3. the 1st , 5th and 6th character is the one we want to search.

    SELECT Itemcode,SiteID FROM ItemInfo WHERE [Status] = 0 AND [ Itemcode] LIKE '1___A8_________' AND [grade] != 0

  • Lordy... some designer needs a serious pork chop lesson on the first rule of the First Normal Form. <facepalm>.

    The only way you're going to be able to fix this for performance is to split the one column into two in the table itself.  If the designers won't allow that, then add two persisted computed columns that do NOT use any form of scalar function (system or otherwise) in their formulas.

    Hmmmm... another possibility might be to make an indexed view to "sister" this table.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • > how to optimize the query from the table design to create index and statisctis ? <<

    As Jeff said, you can't, not with the data in its current form.

    You should separate that one column into as many columns as are needed so that every distinct piece of data is in a separate column.

    You can use a computed column to combine the columns so that the table (view) still has the same column name in it for doing SELECTs.  For INSERTs and UPDATEs, you'd have to use a trigger or change the current app code that does the INSERTs and/or UPDATEs, which, yeah, is a pain.

     

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher wrote:

    > how to optimize the query from the table design to create index and statisctis ? <<

    As Jeff said, you can't, not with the data in its current form.

    You should separate that one column into as many columns as are needed so that every distinct piece of data is in a separate column.

    You can use a computed column to combine the columns so that the table (view) still has the same column name in it for doing SELECTs.  For INSERTs and UPDATEs, you'd have to use a trigger or change the current app code that does the INSERTs and/or UPDATEs, which, yeah, is a pain.

    Thank you ScottPletcher!

    the itemcode in my table has 15 characters , so do you mean I need to separate this column into 15 columns and use this 15 columns for searching criteria?

     

  • Jeff Moden wrote:

    Lordy... some designer needs a serious pork chop lesson on the first rule of the First Normal Form. <facepalm>.

    The only way you're going to be able to fix this for performance is to split the one column into two in the table itself.  If the designers won't allow that, then add two persisted computed columns that do NOT use any form of scalar function (system or otherwise) in their formulas.

    Hmmmm... another possibility might be to make an indexed view to "sister" this table.

    Thank you Jeff Moden! if  as you said mention that I need to split the column into two in the table? sorry to bother you , could you please guide me how to split into two ?  many thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply