wildcard query of optimization

Question

wildcard query of optimization

892717952

SSC Eights!

Points: 843
More actions
November 3, 2020 at 6:30 am

#3803770

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

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

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

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)

ScottPletcher SSC Guru Points: 101105 More actions · Answer 2

> 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

892717952 SSC Eights! Points: 843 More actions · Answer 3

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?

892717952 SSC Eights! Points: 843 More actions · Answer 4

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!