July 14, 2010 at 6:42 am
The query below is run within a dts package and gets data from the last 10 days. The product table has 170 million records. Can the query be optimized or re-written in a better way? Any advice would be appreciated.
SELECT
pl.TerminalID as [POS Code],
dbo.fn_ConvertToBusinessDate(pl.date) AS [Date Of Sale],
convert(char(5),pl.[Date],8) as [Time Of Sale],
pl.OrderLine AS OrderLineNo,
pl.Product AS [Entity Code],
pl.AccountID AS [XCID],
CASE WHEN pl.Portion = 1 THEN NULL
WHEN pl.Portion = 2 THEN 'H'
WHEN pl.Portion = 3 THEN 'D'
WHEN pl.Portion = 4 THEN 'O'
WHEN pl.Portion = 8 THEN 'T' END as [Product Type],
CASE WHEN ip.Ingredient = -1 THEN NULL
WHEN ip.Ingredient is NULL THEN NULL ELSE 'I' END as [Included],
CAST(pl.EmployeeID AS BigInt) AS SEC,
CASE WHEN pl.CorrectionMethod = -1 THEN NULL ELSE 'R' END AS [Reversal Flag],
CASE WHEN cm.[isWaste] = 1 THEN 'F' ELSE NULL END as [Fault Flag],
CASE WHEN pl.CorrectionMethod IS NULL THEN 0 ELSE pl.CorrectionMethod END AS [Reversal Mode Index],
pl.Quantity * pl.Ratio AS Quantity,
pl.Price/100.0 AS Income,
case when pl.Portion = 4 then t.OffSale else t.OnSale end as VatRate,
pl.Portion,
0 as PromotionID,
pl.[Order] AS PL_Order,
pl.[Transaction Number] AS PL_TxNo,
[Date] as SaleDateTimeAdj
FROM aztec.dbo.ProductLine AS pl
INNER JOIN aztec.dbo.ThemeEposDevice_Repl AS ted ON pl.TerminalID = ted.EPoSDeviceID
LEFT OUTER JOIN aztec.dbo.ImmediateParent AS ip ON pl.TransactionID = ip.TransactionID
LEFT OUTER JOIN aztec.dbo.Interface_CorrectionMethods cm on pl.CorrectionMethod = cm.CorrectionMethodID
LEFT OUTER JOIN
( SELECT p.EntityCode,
SUM(t.[On-Sale Rate]) AS OnSale,
SUM(t.[Off-Sale Rate]) AS OffSale
FROM aztec.dbo.ProductTaxRules p, aztec.dbo.TaxRules t
WHERE (p.TaxRule1 = t.[Index No] OR p.TaxRule2 = t.[Index No] OR p.TaxRule3 = t.[Index No] OR p.TaxRule4 = t.[Index No])
AND t.ExclusiveTax = 0
GROUP BY p.EntityCode ) t
ON pl.Product = t.EntityCode
where dbo.fn_ConvertToBusinessDate(pl.date) >= dateadd(day,-10,datediff(day,0,getdate()))
July 14, 2010 at 6:59 am
Comment out this section, it isn't referenced in the SELECT:
LEFT OUTER JOIN
( SELECT p.EntityCode,
SUM(t.[On-Sale Rate]) AS OnSale,
SUM(t.[Off-Sale Rate]) AS OffSale
FROM aztec.dbo.ProductTaxRules p, aztec.dbo.TaxRules t
WHERE (p.TaxRule1 = t.[Index No] OR p.TaxRule2 = t.[Index No] OR p.TaxRule3 = t.[Index No] OR p.TaxRule4 = t.[Index No])
AND t.ExclusiveTax = 0
GROUP BY p.EntityCode ) t
ON pl.Product = t.EntityCode
A UDF on an expression in a WHERE clause will almost always prevent the use of an index which might otherwise accelerate the query. Find out what dbo.fn_ConvertToBusinessDate(pl.date) does. You might have to replace the WHERE clause with a join to a table of dates.
In any case, modify the query like so:
WHERE pl.date >= dateadd(day,-10,datediff(day,0,getdate()))
and see how long it takes to run.
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
July 14, 2010 at 7:47 am
Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
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
July 14, 2010 at 2:07 pm
I'd use a different concept:
Instead of using the function dbo.fn_ConvertToBusinessDate(pl.date) I probably would use a calendar table and join the productLine table to it.
I would consider normalization of aztec.dbo.ProductTaxRules (preferably by populating a physical table).
And, finally, I would change the syntax for the derived table t to a statement using JOIN ON (just for the sake of readybility and consistency).
July 15, 2010 at 1:40 am
Hi Chris,
How do I create a date table? Here's what the function does below
CREATE FUNCTION [dbo].[fn_ConvertToBusinessDate] (@Date DATETIME) RETURNS DATETIME AS
BEGIN
DECLARE @RollOverTime DATETIME
SELECT @RollOverTime = [RollOverTime]
FROM [dbo].[ac_Estate]
SELECT @Date = CONVERT(VARCHAR(8), @Date-@RollOverTime, 112)
RETURN @Date
END
GO
July 15, 2010 at 1:51 am
Hi LutzM,
How do I create a calendar table?
July 15, 2010 at 1:55 am
Try this article
July 15, 2010 at 1:57 am
But , as Gail requested, DDL , execution plans etc are required to analyse the real problem.
July 19, 2010 at 4:03 am
Execution plan attached. Running SQL2000.
July 19, 2010 at 4:11 am
LadyG (7/15/2010)
Hi Chris,How do I create a date table? Here's what the function does below
CREATE FUNCTION [dbo].[fn_ConvertToBusinessDate] (@Date DATETIME) RETURNS DATETIME AS
BEGIN
DECLARE @RollOverTime DATETIME
SELECT @RollOverTime = [RollOverTime]
FROM [dbo].[ac_Estate]
SELECT @Date = CONVERT(VARCHAR(8), @Date-@RollOverTime, 112)
RETURN @Date
END
GO
This function subtracts a fixed datetime to the datetime passed in - which means that the same arithmetic could be applied to the other side of the join operator.
What do you get when you run
SELECT [RollOverTime] FROM [dbo].[ac_Estate] ?
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
July 19, 2010 at 4:28 am
1900-01-01 03:15:00.000
1900-01-01 03:15:00.000
July 19, 2010 at 4:45 am
1900-01-01 03:15:00.000
1900-01-01 03:15:00.000
July 19, 2010 at 5:06 am
Test and check that you get the same results, and compare the times:
-- existing version
WHERE dbo.fn_ConvertToBusinessDate(pl.date) >= dateadd(day,-10,datediff(day,0,getdate()))
-- proposed version
WHERE pl.date >= DATEADD(mi,195,dateadd(day,-10,datediff(day,0,getdate()))) -- 195 MINUTES = 03:15:00.000
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
July 20, 2010 at 1:19 am
Execution plan attached. Running SQL2000.
July 20, 2010 at 1:35 am
Clustered index scans probably due to the function use.
Did you try the code Chris suggested ?
Additionally can you clarify you index strategy ?
In the spreadsheet you put this...
ColumnsAttributesIndexes
TransactionIDbigintUnique clustered
datedatetimeUnique clustered
So what is your clustered index ?
(TransactionID,Date ) ?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply