December 11, 2012 at 10:29 am
I need to find a faster way to return records from an order history table. The item numbers are all numeric. The digits of the item number have a specific meaning and they are always nine digits long.
The queries take a long time because the question is often something like, “Show all the sales for an item number that matches (or does not match) this pattern, ???66????“.
Currently, I am using the LIKE operator. E.g. “ ItemNo Like ‘___66____’” and even sometimes, “ ItemNo NOT LIKE ‘___66____’ “.
I was wondering if a Tally table (10 fields total, Item number and 9 other fields which would hold each digit in the Item number) would help. Continuing with the above example, e.g.
Select ItemNo, Sum(sales) as Sales from History H
Inner join TallyTable T
On H.ItemNo = T.ItemNo
Where T.Pos4 = 6 and T.Pos5 = 6
What other methods would speed up this type of query?
Thanks,
pat
December 11, 2012 at 11:37 am
mpdillon (12/11/2012)
I need to find a faster way to return records from an order history table. The item numbers are all numeric. The digits of the item number have a specific meaning and they are always nine digits long.The queries take a long time because the question is often something like, “Show all the sales for an item number that matches (or does not match) this pattern, ???66????“.
Currently, I am using the LIKE operator. E.g. “ ItemNo Like ‘___66____’” and even sometimes, “ ItemNo NOT LIKE ‘___66____’ “.
I was wondering if a Tally table (10 fields total, Item number and 9 other fields which would hold each digit in the Item number) would help. Continuing with the above example, e.g.
Select ItemNo, Sum(sales) as Sales from History H
Inner join TallyTable T
On H.ItemNo = T.ItemNo
Where T.Pos4 = 6 and T.Pos5 = 6
What other methods would speed up this type of query?
Thanks,
pat
The item numbers are all numeric but are really chars/varchars, or are they stored as integers?
looks like the position carries special meanings, so you've got more than one data element represented in a single field? why does position 4 & position 5 carry a special meaning? or is this just a search from "i remember the order had two sixes in it?
I would consider this approach: maybe add calculated , persisted columns that chop the ItemNo into 9 columns(ALTER TABLE History ADD Pos4 AS SUBSTRING(ItemNo,4,1) PERSISTED) then they could be indexed, an the queries modified to say WHERE Pos4 ='6'
Lowell
December 11, 2012 at 11:41 am
I'd be inclined to create an indexed view that breaks the data down for you, and then query that.
Alternatively, a set of computed columns in the table itself.
Computed columns version:
USE ProofOfConcept;
GO
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T;
CREATE TABLE #T
(ID INT IDENTITY
PRIMARY KEY,
ItemNumber CHAR(9) NOT NULL,
CONSTRAINT CK_ItemNumber_Digits CHECK (ItemNumber NOT LIKE '%[^0-9]%'),
ItemNumberD1 AS SUBSTRING(ItemNumber, 1, 1),
ItemNumberD2 AS SUBSTRING(ItemNumber, 2, 1),
ItemNumberD3 AS SUBSTRING(ItemNumber, 3, 1),
ItemNumberD4 AS SUBSTRING(ItemNumber, 4, 1),
ItemNumberD5 AS SUBSTRING(ItemNumber, 5, 1),
ItemNumberD6 AS SUBSTRING(ItemNumber, 6, 1),
ItemNumberD7 AS SUBSTRING(ItemNumber, 7, 1),
ItemNumberD8 AS SUBSTRING(ItemNumber, 8, 1),
ItemNumberD9 AS SUBSTRING(ItemNumber, 9, 1));
CREATE INDEX IDX_T_Breakdown ON #T (ItemNumberD1, ItemNumberD2, ItemNumberD3, ItemNumberD4, ItemNumberD5, ItemNumberD6, ItemNumberD7, ItemNumberD8, ItemNumberD9)
INCLUDE (ItemNumber);
You might end up needing a large number of indexes, at least 9, each with a different D# column as the leading edge, in order to get index seeks on this thing.
The indexed view version (which I like better), might look like:
USE ProofOfConcept;
GO
IF OBJECT_ID(N'dbo.TBreakdown') IS NOT NULL
DROP VIEW dbo.TBreakdown;
GO
IF OBJECT_ID(N'dbo.T') IS NOT NULL
DROP TABLE T;
GO
CREATE TABLE dbo.T
(ID INT IDENTITY
PRIMARY KEY,
ItemNumber CHAR(9) NOT NULL,
CONSTRAINT CK_ItemNumber_Digits CHECK (ItemNumber NOT LIKE '%[^0-9]%'));
GO
CREATE VIEW dbo.TBreakdown
WITH SCHEMABINDING
AS
SELECT ID,
SUBSTRING(T.ItemNumber, Number, 1) AS ItemNumberSub,
Number AS SubPosition
FROM dbo.T
INNER JOIN dbo.Numbers
ON Number BETWEEN 1 AND 9;
GO
CREATE UNIQUE CLUSTERED INDEX UCI_TBreakdown ON dbo.TBreakdown (ID, SubPosition);
GO
CREATE INDEX IDX_TBreakDown ON dbo.TBreakdown (ItemNumberSub, SubPosition)
INCLUDE (ID);
GO
INSERT INTO dbo.T
(ItemNumber)
VALUES ('123456789'),
('000120000');
SELECT *
FROM dbo.T;
SELECT *
FROM dbo.TBreakdown;
SELECT *
FROM dbo.T
WHERE ID IN (SELECT ID
FROM dbo.TBreakdown
WHERE SubPosition = 4
AND ItemNumberSub = '1')
AND ID IN (SELECT ID
FROM dbo.TBreakdown
WHERE SubPosition = 5
AND ItemNumberSub = '2');
You can modify the final query in a number of ways, to optimize for various results you might need. Given the right query, the results should be very, very fast. Since you'll have variations on which sub-positions you'll want to query, and whether to query for equality or inequality, dynamic SQL (paramaterized, of course) is probably your best bet for efficient queries.
(I have a Numbers table. Same as a Tally table. Just a different name.)
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 11, 2012 at 12:33 pm
Lowell,
Yes, the item numbers are char(15) variables. The item numbers follow the format XXX-XXX-XXXX. For simplicity, I dropped the “-“ and the 10th numeric digit because it is rarely present.
Unfortunately, the table is part of an ERP package. I can’t modify that table by adding columns and indexes to it.
There is nothing special about the 4th and 5th places. It could just as well be the 1st, 7th and 8th places. The query interface I designed allows the user to choose whatever combinations they feel they need.
GSquared,
The view idea intrigues me. I have never done that before. There are a few commands/syntax in your example that I do not understand and I will have to research them before I can go any further. E.g. “CONSTRAINT CK_ItemNumber_Digits CHECK (ItemNumber NOT LIKE '%[^0-9]%'),” and
"CREATE VIEW dbo.TBreakdown WITH SCHEMABINDING"
Thanks. I will work on this.
I will post my solution when I am finished. But I will not be back at this customer until next week. So this thread may go dormant for a while.
December 11, 2012 at 2:11 pm
The check constraint was simply to make it all numeric. It checks if there are any characters other than numbers, and rejects them. I based that on your original post, where you said they're always 9-digit numbers. Based on the later post, that constraint may not work. It's not too important to the whole thing, so just leave that out.
The "create view ... with schemabinding" bit creates a view, just like normal, but it binds the schema of the underlying objects. That simply means you can't do things like drop columns from the table unless you drop the view first. Necessary for indexed views, helpful for normal views. Simply a way to avoid odd errors in querying the view.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply