September 18, 2009 at 9:35 am
I'm fairly new to SQL and I'm struggling with how the SQL engine puts together plans to run a query. I have written some code that I want to eventually put into a stored procedure but the lengthly runtime I think is unneccesary -- I'm hoping that its something in my code that will be obvious to more experienced programmers;-).
The problem seems to be around two table variables -- @selectevent and @timedata. If I take out these tables and all references to them the query runs in around 25 seconds returning 32,545 rows (that is going thru a year's worth of data and seems reasonable to me).
When including the two table variables, the query takes between 8 and 9 minutes to return the same 32,545 rows!! I've run the code to build @selectevent and @timedata by itself and it returns 2,700 rows in under 3 seconds. :w00t:
I'm lost and don't understand why such a difference!!
Any suggestions / help is greatly appreciated!!
btw: I've run pieces/parts thru the query analyzer -- all tables are indexed and the execution plans show that the indexes are being used. No table scans or anything major like that.
I'd include the execution plan but I'm not sure what part is relevant -- because of the way the query is constructed, the "Include Actual Execution Plan" breaks out several queries and I don't know what to include.
My Code is below:
use tfc;
DECLARE @BeginDate datetime, @EndDate datetime, @StartLineID int, @EndLineID int;
SET @BeginDate = dbo.fnBeginDatetime('6/1/08');
SET @EndDate = dbo.fnEndDatetime('6/30/09');
SET @StartLineID = 22;
SET @EndLineID = 26;
DECLARE @selectstock TABLE (StockID int UNIQUE, FlitchID int, StockGradeID int, ManufactureProductID int, ChargeOffReasonID int, MergedStockID int, SlicerLineID int, VeneerTypeID int, VeneerGroupID int, LogID int, LogSpeciesID int, Reserved bit, FlitchAllocationRate decimal(16,6))
INSERT INTO @selectstock (StockID, FlitchID, StockGradeID, ManufactureProductID, ChargeOffReasonID, MergedStockID, SlicerLineID, VeneerTypeID, VeneerGroupID, LogID, LogSpeciesID, Reserved, FlitchAllocationRate)
SELECTs.ID, s.FlitchID, s.StockGradeID, s.ManufactureProductID, s.ChargeOffReasonID, s.MergedStockID,
s.SlicerLineID, mp.VeneerTypeID,
dbo.fnStockTargetVeneerGroupIDByStockID(s.ID), fl.LogID, lg.SpeciesID, lt.Reserved,
dbo.fnFlitchAllocByStockID(s.ID)
FROMdbo.tStock s
INNER JOIN dbo.tChargeOffReason co ON co.ID = s.ChargeOffReasonID
INNER JOIN dbo.tFlitch fl WITH (INDEX(ndx_spStockReport)) ON fl.ID = s.FlitchID
INNER JOIN dbo.tManufactureProduct mp ON mp.ID = s.ManufactureProductID
INNER JOIN dbo.tSpecies sp ON sp.ID = mp.SpeciesID
INNER JOIN dbo.tLog lg WITH (INDEX(ndx_spStockReport)) ON lg.ID = fl.LogID
INNER JOIN dbo.tLot lt ON lt.ID = lg.LotID
INNER JOIN dbo.tLotType ltt ON ltt.ID = lt.LotTypeID
INNER JOIN dbo.tBuyer b ON b.ID = lt.BuyerID
WHERE (s.SlicedDate BETWEEN @BeginDate and @EndDate) AND (co.Production = 1 OR s.ChargeOffReasonID = 7) AND (s.SlicerLineID BETWEEN @StartLineID AND @EndLineID);
-- ==================================================
DECLARE @selectflitch TABLE (FlitchID int UNIQUE, LogSpeciesID int, Reserved bit)
INSERT INTO @selectflitch (FlitchID, LogSpeciesID, Reserved)
SELECT DISTINCT FlitchID, LogSpeciesID, Reserved
FROM@selectstock;
-- ==================================================
DECLARE @selectlog TABLE (LogID int UNIQUE)
INSERT INTO @selectlog (LogID)
SELECTDISTINCT LogID
FROM@selectstock;
-- ==================================================
DECLARE @selectevent TABLE(EventID int UNIQUE, SlicerLineID int, SlicedDate datetime, SlicedShift int, SlicerOperator varchar(50),SlicerGrossHours decimal(16,6), SlicerNetHours decimal(16,6),
SlicerDF decimal(16,6), DrierLineID int, DriedDate datetime, DriedShift int, DrierOperator varchar(50), DrierGrossHours decimal(16,6), DrierNetHours decimal(16,6), DrierGrossSqFt decimal(16,6), SpeciesID int, FractionalThickness float)
INSERT INTO @selectevent (EventID, SlicerLineID, SlicedDate, SlicedShift, SlicerOperator, SlicerGrossHours, SlicerNetHours, SlicerDF, DrierLineID, DriedDate, DriedShift, DrierOperator, DrierGrossHours, DrierNetHours, DrierGrossSqFt, SpeciesID, FractionalThickness)
SELECTe.ID,
CASE WHEN l.AreaID = 7 AND l.DivisionID = 5 THEN e.LineID ELSE 1 END AS SlicerLineID,
CASE WHEN l.AreaID = 7 AND l.DivisionID = 5 THEN e.ProductionDate ELSE '1/1/1975' END AS SlicedDate,
CASE WHEN l.AreaID = 7 AND l.DivisionID = 5 THEN e.ProductionShift ELSE 0 END AS SlicedShift,
CASE WHEN l.AreaID = 7 AND l.DivisionID = 5 THEN CASE WHEN LEFT(LTRIM(RTRIM(Operator)), 6) = 'HP3000' THEN RIGHT LTRIM(RTRIM(Operator)),(LEN(LTRIM(RTRIM(Operator))) - 7))
ELSE Operator END ELSE '' END AS SlicerOperator,
CASE WHEN l.AreaID = 7 AND l.DivisionID = 5 THEN ProductionHours + (ProductionMinutes/60) ELSE 0 END AS SlicerGrossHours,
CASE WHEN l.AreaID = 7 AND l.DivisionID = 5 THEN (ProductionHours + (ProductionMinutes/60)) - (DowntimeHours + (DowntimeMinutes/60)) ELSE 0 END AS SlicerNetHours,
CASE WHEN l.AreaID = 7 AND l.DivisionID = 5 THEN (SELECT SUM(dbo.fnStockFlitchDFByStockID(se.StockID)) FROM dbo.tStockEvent se WHERE se.EventID = e.ID) ELSE 0 END AS SlicerDF,
CASE WHEN l.AreaID = 8 AND l.DivisionID = 5 THEN e.LineID ELSE 1 END AS DrierLineID,
CASE WHEN l.AreaID = 8 AND l.DivisionID = 5 THEN e.ProductionDate ELSE '1/1/1975' END AS DriedDate,
CASE WHEN l.AreaID = 8 AND l.DivisionID = 5 THEN e.ProductionShift ELSE 0 END AS DriedShift,
CASE WHEN l.AreaID = 8 AND l.DivisionID = 5 THEN CASE WHEN LEFT(LTRIM(RTRIM(Operator)), 6) = 'HP3000' THEN RIGHT(LTRIM(RTRIM(Operator)),(LEN(LTRIM(RTRIM(Operator))) - 7)) ELSE Operator END ELSE '' END AS DrierOperator,
CASE WHEN l.AreaID = 8 AND l.DivisionID = 5 THEN ProductionHours + (ProductionMinutes/60) ELSE 0 END AS DrierGrossHours,
CASE WHEN l.AreaID = 8 AND l.DivisionID = 5 THEN (ProductionHours + (ProductionMinutes/60)) - (DowntimeHours + (DowntimeMinutes/60)) ELSE 0 END AS DrierNetHours,
CASE WHEN l.AreaID = 8 AND l.DivisionID = 5 THEN (SELECT SUM(st.GrossSqFt) FROM dbo.tStock st INNER JOIN dbo.tStockEvent se ON se.StockID = st.ID WHERE se.EventID = e.ID) ELSE 0 END AS DrierGrossSqFt,
e.SpeciesID, e.FractionalThickness
FROMdbo.tEvent e
INNER JOIN dbo.tLine l ON l.ID = e.LineID
WHERE((e.LineID BETWEEN @StartLineID AND @EndLineID) AND (e.ProductionDate BETWEEN @BeginDate AND @EndDate))
OR
((l.AreaID = 8 AND l.DivisionID = 5) AND (e.ProductionDate BETWEEN @BeginDate AND DATEADD(w,3,@EndDate))) ;
-- =======================================
DECLARE @timedata TABLE(StockID int UNIQUE, SlicerLineID int, SlicedDate datetime, SlicedShift int, SlicerOperator varchar(50),
SlicerGrossHours decimal(16,6), SlicerNetHours decimal(16,6), SlicerDF decimal(16,6),
SlicerGrossHoursPerDF decimal(16,6), SlicerNetHoursPerDF decimal(16,6),
DrierLineID int, DriedDate datetime, DriedShift int, DrierOperator varchar(50), DrierGrossHours decimal(16,6),
DrierNetHours decimal(16,6), DrierGrossSqFt decimal(16,6), DrierGrossHoursPerGrossSqFt decimal(16,6),
DrierNetHoursPerGrossSqFt decimal(16,6))
INSERT INTO @timedata
SELECT se.StockID,
MAX(e.SlicerLineID) as SlicerLineID, MAX(SlicedDate) AS SlicedDate, MAX(SlicedShift) AS SlicedShift, MAX(SlicerOperator) AS SlicerOperator,
SUM(SlicerGrossHours) AS SlicerGrossHours,
SUM(SlicerNetHours) AS SlicerNetHours, SUM(SlicerDF) AS SlicerDF,
CASE SUM(SlicerDF) WHEN 0 THEN 0 ELSE SUM(SlicerGrossHours) / SUM(SlicerDF) END AS SlicerGrossHoursPerDF,
CASE SUM(SlicerDF) WHEN 0 THEN 0 ELSE SUM(SlicerNetHours) / SUM(SlicerDF) END AS SlicerNetHoursPerDF,
MAX(DrierLineID) AS DrierLineID, MAX(DriedDate) AS DriedDate, MAX(DriedShift) AS DriedShift, MAX(DrierOperator) AS DrierOperator,
SUM(DrierGrossHours) AS DrierGrossHours, SUM(DrierNetHours) AS DrierNetHours,
SUM(DrierGrossSqFt) AS DrierGrossSqFt,
CASE SUM(DrierGrossSqFt) WHEN 0 THEN 0 ELSE SUM(DrierGrossHours) / SUM(DrierGrossSqFt) END AS DrierGrossHoursPerGrossSqFt,
CASE SUM(DrierGrossSqFt) WHEN 0 THEN 0 ELSE SUM(DrierNetHours) / SUM(DrierGrossSqFt) END AS DrierNetHoursPerGrossSqFt
FROM dbo.tStockEvent se
INNER JOIN @selectevent e ON e.EventID = se.EventID
GROUP BY se.StockID;
-- ==================================================
WITH stock AS
(
SELECT ss.StockID, s.StockNo, s.LengthFt, s.GrossSqFt, s.InventoryPrice,
CASE WHEN ss.VeneerGroupID = 3 THEN s.GrossSqFt WHEN ss.VeneerGroupID = 5 THEN s.GrossSqFt WHEN ss.ChargeOffReasonID = 7 THEN 0 ELSE s.NetSqFt END AS NetSqFt,
s.PricedDate, s.GradedDate, s.Estimate, s.SlicedDate, s.SlicerLineID, s.DriedDate, s.ManufacturingCost, s.Line,
CASE ss.ChargeOffReasonID WHEN 7 THEN 0 ELSE s.VeneerValue END as VeneerValue, s.ClippedDate,
s.ChargeOffDate, CASE ss.StockGradeID WHEN 1 THEN '' ELSE sg.Code END AS StockGrade,
mp.Code AS ProductCode, sp.Code AS Species, sp.Name AS SpeciesName, mp.HurdleValue,
dbo.fnVeneerChange(ss.VeneerTypeID, ss.VeneerGroupID) AS VenType,
mp.FractionalThickness, ss.VeneerGroupID, co.Code AS ChargeOffCode,
ss.ManufactureProductID, dbo.fnStockPriceCommentsByID(ss.StockID,'') AS StockPriceComments,
dbo.fnStockProductionCommentsByID(ss.StockID,'') AS StockProductionComments,
dbo.fnFlitchAllocByStockID(ss.StockID) as FlitchAllocationRate, ss.MergedStockID, ss.ChargeOffReasonID
FROM @selectstock ss
INNER JOIN dbo.tStock s ON s.ID = ss.StockID
INNER JOIN dbo.tStockGrade sg ON sg.ID = ss.StockGradeID
INNER JOIN dbo.tManufactureProduct mp ON mp.ID = ss.ManufactureProductID
INNER JOIN dbo.tSpecies sp ON sp.ID = mp.SpeciesID
INNER JOIN dbo.tChargeOffReason co ON co.ID = ss.ChargeOffReasonID
INNER JOIN dbo.tVeneerGroup vg ON vg.ID = ss.VeneerGroupID
)
, logs AS
(
SELECT l.ID AS LogID, l.SlugNo, lt.LotNo, CASE lt.FSCSourceID WHEN 1 THEN 2 ELSE lt.FSCSourceID END AS FSCSourceID,
fsc.Code AS FSCSource,
CASE l.PurchaseLogGradeID WHEN 1 THEN '' ELSE plg.Code END AS PGrade, l.PurchaseLogGradeID as PGradeID,
CASE l.ReceiveLogGradeID WHEN 1 THEN '' ELSE rlg.Code END AS RGrade, l.ReceiveLogGradeID as RGradeID,
RTrim(LTrim(l.VendorTag)) AS ProducerTag,
lt.PurchaseDate, b.BuyerNo, b.Name AS BuyerName, pv.VendorNo AS ProducerNo, pv.Name AS ProducerName, l.PurchaseCost,
l.ReceiveDF, ltt.Code AS LotType, l.PurchaseDiameter, l.PurchaseLength, l.ReceiveDiameter, l.ReceiveLength, l.ReceiveDate,
dbo.fnLogCommentsByID(sl.LogID,1,'') AS PurchaseComments,
dbo.fnLogCommentsByID(sl.LogID,2,'') AS ReceiveComments, lt.Reserved, l.SpeciesID,
l.ReceivePremium, lt.SpeciesID as LotSpeciesID
FROM @selectlog sl
INNER JOIN dbo.tLog l WITH (INDEX(ndx_spStockReport)) ON l.ID = sl.LogID
INNER JOIN dbo.tLogGrade plg ON plg.ID = l.PurchaseLogGradeID
INNER JOIN dbo.tLogGrade rlg ON rlg.ID = l.ReceiveLogGradeID
INNER JOIN dbo.tLot lt ON lt.ID = l.LotID
INNER JOIN dbo.tBuyer b ON b.ID = lt.BuyerID
INNER JOIN dbo.tVendor pv ON pv.ID = lt.ProducerVendorID
INNER JOIN dbo.tLotType ltt ON ltt.ID = lt.LotTypeID
INNER JOIN dbo.tFSCSource fsc ON fsc.ID = lt.FSCSourceID
WHERE (l.SoldDate IS NULL OR l.SoldDate = '1975-01-01 00:00:00.000')
)
, flitch AS
(
SELECT fl.ID AS FlitchID, fl.FlitchDate, fl.FlitchNo, fl.LogID, fl.SlicedDate,
CASE sf.Reserved WHEN 1 THEN ISNULL(spf.FlitchAvgDF, 0) ELSE fl.LogDF END as FlitchDF,
CASE sf.Reserved WHEN 1 THEN ISNULL(spf.FlitchAvgCost, 0) ELSE fl.LogCost END as FlitchCost,
ISNULL(f.Factor, 0) AS FSCProductFactor, fl.RejectDate, fl.RejectReasonID, s.Code AS Species, s.Name AS SpeciesName,
fl.RejectLineID, fl.ScheduleDetailID, fl.ChargeOffDate
FROM @selectflitch sf
INNER JOIN dbo.tFlitch fl WITH (INDEX(ndx_spStockReport)) ON fl.ID = sf.FlitchID
LEFT OUTER JOIN dbo.tSpeciesFactor spf ON spf.SpeciesID = sf.LogSpeciesID
INNER JOIN dbo.tSpecies s ON s.ID = sf.LogSpeciesID
LEFT OUTER JOIN (SELECT SpeciesID, Factor FROM dbo.tFSCProductFactor WHERE DivisionID = 5) f ON f.SpeciesID = sf.LogSpeciesID
)
SELECT sd.StockNo, sd.GrossSqFt, sd.NetSqFt, sd.InventoryPrice, sd.VeneerValue, sd.SlicedDate, sd.Species, sd.VenType,
sd.FractionalThickness, fd.FlitchDF * ss.FlitchAllocationRate AS FlitchDF, fd.FlitchCost * ss.FlitchAllocationRate as FlitchCost,
fd.FlitchNo
,CASE WHEN fd.FlitchDF <> 0 THEN (fd.FlitchCost / fd.FlitchDF) * 1000 ELSE 0 END AS LogCostMDF
,CASE WHEN (fd.FlitchDF * ss.FlitchAllocationRate) <> 0 THEN (sd.NetSqFt / (fd.FlitchDF * ss.FlitchAllocationRate)) ELSE 0 END AS NetYield
,(sd.VeneerValue - (fd.FlitchCost * ss.FlitchAllocationRate)) AS LogMargin
,CASE WHEN (fd.FlitchDF * ss.FlitchAllocationRate) <> 0 THEN ((sd.VeneerValue - (fd.FlitchCost * ss.FlitchAllocationRate)) / (fd.FlitchDF * ss.FlitchAllocationRate)) * 1000 ELSE 0 END AS LogMarginPerMDF
,l.Code AS SlicedLine
,ld.ProducerTag, ld.SlugNo, ld.LotType, ld.ReceiveLength, ld.ReceiveDiameter
-- Rows below are all related to @timedata
,td.SlicedShift
,(fd.FlitchDF * ss.FlitchAllocationRate) * ISNULL(td.SlicerGrossHoursPerDF, 0) AS GrossSlicedHours
,CASE ((fd.FlitchDF * ss.FlitchAllocationRate) * ISNULL(td.SlicerNetHoursPerDF, 0)) WHEN 0 THEN 0 ELSE (sd.VeneerValue - (fd.FlitchCost * ss.FlitchAllocationRate)) / ((fd.FlitchDF * ss.FlitchAllocationRate) * ISNULL(td.SlicerNetHoursPerDF, 0)) END AS LogMarginPerNetSlicedHour
,sd.GrossSqFt * ISNULL(td.DrierGrossHoursPerGrossSqFt, 0) AS DriedGrossHours
FROM @selectstock ss
INNER JOIN stock sd ON sd.StockID = ss.StockID
INNER JOIN flitch fd on fd.FlitchID = ss.FlitchID
INNER JOIN dbo.tLine l ON l.ID = ss.SlicerLineID
INNER JOIN logs ld ON ld.LogID = ss.LogID
LEFT OUTER JOIN @timedata td ON td.StockID = ss.StockID
September 18, 2009 at 12:24 pm
Table variables don't have statistics. That's probably the issue based on how you're using them in JOIN's. Try doing the same thing with temporary (#temp) tables and see if it makes a difference. Also, you've got functions in the select criteria. Do these do data access? That's a notorious bottleneck. You've got index hints in place. A lot of times these can cause performance bottlenecks rather than solve them. Finally, even if you replace the table variables with temporary tables, you may see slow performance. It's frequently (not always, you'll need to test, test, test) better to simply run a series of derived tables, select statements as tables, rather than loading data from one set of tables into temporary storage and then joining them together. You could try eliminating the temporary storage altogether.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 18, 2009 at 12:25 pm
Oh, and, in cases like this, all the execution plans are going to be relevant.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 18, 2009 at 12:33 pm
Thanks for the reply Grant.
I'll try the temp tables and see if that helps any. This version of the code is a variation of code in which I had originally used all CTEs and no Table variables. That code performed worse than this.
September 18, 2009 at 2:56 pm
Temp tables made a tremendous difference in this case. After changing all of the table variables to temp tables the query finished in around 27 seconds.
September 19, 2009 at 5:45 am
I wouldn't be surprised if you can tune it further. Nice to hear that it's working better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply