November 12, 2008 at 9:13 am
I need to identify inventory items that have not had a valid transaction within the last 12 months and list the part, description, storage bin number, on-hand quantity, unit cost, and extended cost. These items will be deemed as obsolete and dealt with accordingly.
I'm including code below to create and populate the following tables:
@Part
@Bin
@Trans
@Cost
Valid transactions can be identified by one of the following tranTypes in the Trans table:
'INS-SUB',
'MFG-STK',
'MFG-VEN'.
I'm looking to rewrite this query to be more efficient -- it runs very long in my live database which contains 3000+ inventory items and 43000+ transactions.
Thanks in advance.
Ahmet
--******************Create and populate table vars***************************************
DECLARE @Part TABLE
(
PartNum VARCHAR(50),
PartDescription TEXT
)
INSERT INTO @Part VALUES('A','Coil')
INSERT INTO @Part VALUES('B','Motor')
INSERT INTO @Part VALUES('C','Spring')
INSERT INTO @Part VALUES('D','Washer')
DECLARE @Bin TABLE
(
BinNum VARCHAR(10),
PartNum VARCHAR(50),
OnHandQty DECIMAL(16,2)
)
INSERT INTO @Bin VALUES('xxx','A',10)
INSERT INTO @Bin VALUES('yyy','A',2)
INSERT INTO @Bin VALUES('yyy','B',3)
INSERT INTO @Bin VALUES('xxx','C',0)
INSERT INTO @Bin VALUES('yyy','D',5)
DECLARE @Trans TABLE
(
TranDate DATETIME,
PartNum VARCHAR(50),
TranType VARCHAR(7)
)
INSERT INTO @Trans VALUES(GETDATE(),'A','INS-SUB')
INSERT INTO @Trans VALUES(GETDATE(),'B','bbb-ccc')
INSERT INTO @Trans VALUES(GETDATE(),'C','MFG-STK')
INSERT INTO @Trans VALUES(GETDATE(),'D','MFG-VEN')
INSERT INTO @Trans VALUES(GETDATE(),'A','xxx-yyy')
DECLARE @Cost TABLE
(
PartNum VARCHAR(50),
Cost DECIMAL(18,5)
)
INSERT INTO @Cost VALUES('A',5.5)
INSERT INTO @Cost VALUES('B',100)
INSERT INTO @Cost VALUES('C',3.25)
INSERT INTO @Cost VALUES('D',1)
--******************Get quantity and cost information on obsolete inventory**************
SELECT p.PartNum,
p.PartDescription,
b.BinNum,
b.OnHandQty,
c.Cost,
b.OnHandQty * c.Cost as ExtCost
FROM @Part p
INNER JOIN @Cost c ON p.PartNum=c.PartNum
INNER JOIN @Bin b ON p.PartNum=b.PartNum
WHERE b.OnhandQty>0
AND p.PartNum NOT IN
(SELECT DISTINCT PartNum
FROM @Trans
WHERE TranType IN ('INS-SUB', 'MFG-STK', 'MFG-VEN')
AND TranDate BETWEEN '2007-11-13' AND '2008-11-12'
)
November 12, 2008 at 9:42 am
I am assuming that in production you are running this against permanent tables, not table variables. If so, can you provide the DDL for the tables and the indexes defined on the tables?
November 12, 2008 at 9:48 am
A LEFT JOIN is often faster than a NOT IN...
[font="Courier New"]SELECT p.PartNum,
p.PartDescription,
b.BinNum,
b.OnHandQty,
c.Cost,
b.OnHandQty * c.Cost AS ExtCost
FROM @Part p
INNER JOIN @Cost c ON p.PartNum=c.PartNum
INNER JOIN @Bin b ON p.PartNum=b.PartNum
LEFT JOIN @Trans t ON t.PartNum = p.PartNum
AND t.TranType IN ('INS-SUB', 'MFG-STK', 'MFG-VEN')
AND t.TranDate BETWEEN '2007-11-13' AND '2008-11-12'
WHERE b.OnhandQty>0
AND t.PartNum IS NULL
[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 12, 2008 at 11:39 am
Can you post the execution plan please? Saved as a .sqlplan file, zipped and attached to your post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2008 at 1:31 pm
Chris -- The LEFT JOIN improved the execution time astronomically.. really, it went down from about 30 seconds to about 1 second. Thanks!
Lynn and Gail -- I'm having trouble getting the info you're asking for due to security limitations on my login. I'll work on that and post a reponse when I can.
Thank you all,
Ahmet
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply