Reputation: 17
My problem is below query takes 38 seconds to complete, I need to reduce this time as much as I can. When I look at Execution plan : %54 cost spend on Dim_Customers Index Scanning. Any suggestion would be appreciated. Thanks
DECLARE @SalesPersonCode NVARCHAR(4)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @SalesPersonCode = 'AC';
SET @StartDate = '03/01/2012';
SET @endDate = '03/31/2012';
SELECT AA_FactSalesOrderDetails.Salesperson
, Dim_SalesOrganisation.[Salesperson name]
, AA_FactSalesOrderDetails.[Order Date]
, Dim_Customers.[Customer number]
, Dim_Customers.[Customer name]
, Dim_Customers.[Area/state]
, Dim_Customers.country
, Dim_Customers.[Customer stop] AS [Customer Block]
, AA_FactSalesOrderDetails.[Customer order stop] AS [Co Stop]
, AA_FactSalesOrderDetails.[First delivery date Header]
, AA_FactSalesOrderDetails.[Last delivery date Header]
, Dim_Customers.[User-defined field 6 - customer]
, Dim_Customers.[Customer group name]
, AA_FactSalesOrderDetails.[Contact Method]
, AA_FactSalesOrderDetails.[Customer order number]
, AA_FactSalesOrderDetails.[Price Level]
, AA_FactSalesOrderDetails.[Item number]
, Dim_Items.[Product group description] AS [Item name]
, AA_FactSalesOrderDetails.[Ordered quantity - basic U/M] AS [Quantity Ordered]
, AA_FactSalesOrderDetails.[Ordered quantity - basic U/M] * AA_FactSalesOrderDetails.[Net price] AS [Order Line Total ]
FROM AA_FactSalesOrderDetails
LEFT JOIN
Dim_SalesOrganisation
ON
AA_FactSalesOrderDetails.Salesperson = Dim_SalesOrganisation.Salesperson
LEFT JOIN
Dim_Customers
ON
AA_FactSalesOrderDetails.Dim_Customers_dKey = Dim_Customers.Dim_Customers_dKey
LEFT JOIN
Dim_Items
ON
AA_FactSalesOrderDetails.[Item number] = Dim_Items.[Item number]
LEFT JOIN
Dim_CustomerOrderTypes
ON
AA_FactSalesOrderDetails.[Customer order type] = Dim_CustomerOrderTypes.[Customer order type]
WHERE AA_FactSalesOrderDetails.[Order Date]
BETWEEN
dbo.fnc_M3_sql_datetime_to_M3_date(@StartDate) /* !!!Procedural Approach!!! */
AND
dbo.fnc_M3_sql_datetime_to_M3_date(@EndDate) /* !!!Procedural Approach!!! */
AND
AA_FactSalesOrderDetails.Salesperson = @SalesPersonCode
Upvotes: 0
Views: 1981
Reputation: 280644
I am willing to bet money that this version runs faster than 35 seconds.
Now, there still may be other optimizations possible (such as creating or improving indexes, which we can't know without seeing the plan), but I think I've cleaned up several issues in your query that should assist performance.
EDIT a few edits since apparently the user is running against 2000 even though the question is tagged 2008...
-- make sure you don't have an implicit conversion between varchar and nvarchar
DECLARE
@SalesPersonCode NVARCHAR(4),
@StartDate DATETIME,
@EndDate DATETIME;
SELECT
@SalesPersonCode = N'AC', -- nvarchar needs N prefix!
-- get rid of the function call, I am guessing it just removes time
-- in which case, use the DATE data type instead.
@StartDate = '20120301',
@EndDate = '20120331';
-- since a salesperson can only have one code, and you are only pulling the name into the
-- SELECT list (it will be the same for every row), use a constant and eliminate the join.
DECLARE @SalesPersonName NVARCHAR(255);
SELECT @SalesPersonName = SalesPerson_Name
FROM dbo.Dim_SalesOrganisation
WHERE SalesPerson = @SalesPersonCode;
-- I've added table aliases which make the query MUCH, MUCH easier to read
SELECT f.Salesperson
, Salesperson_name = @SalesPersonName
, f.[Order Date]
, c.[Customer number]
, c.[Customer name]
, c.[Area/state]
, c.country
, c.[Customer stop] AS [Customer Block]
, f.[Customer order stop] AS [Co Stop]
, f.[First delivery date Header]
, f.[Last delivery date Header]
, c.[User-defined field 6 - customer]
, c.[Customer group name]
, f.[Contact Method]
, f.[Customer order number]
, f.[Price Level]
, f.[Item number]
, i.[Product group description] AS [Item name]
, f.[Ordered quantity - basic U/M] AS [Quantity Ordered]
, f.[Ordered quantity - basic U/M] * f.[Net price] AS [Order Line Total ]
-- I've also added schema prefix. See below *
FROM
dbo.AA_FactSalesOrderDetails AS f
-- I've removed the join to Dim_SalesOrganisation as per above
LEFT OUTER JOIN dbo.Dim_Customers AS c
ON f.c_dKey = c.Dim_Customers_dKey
LEFT OUTER JOIN dbo.Dim_Items AS i
ON f.[Item number] = i.[Item number]
-- I've removed the join to Dim_CustomerOrderTypes since it is never used
WHERE
-- in case [Order Date] is DATETIME and includes time information. See below **
f.[Order Date] >= @StartDate
AND f.[Order Date] < DATEADD(DAY, 1, @EndDate)
-- still need to restrict it to the stated salesperson
AND f.SalesPerson = @SalesPersonCode;
*
Bad habits to kick : avoiding the schema prefix
**
Bad habits to kick : mis-handling date / range queries
Upvotes: 0
Reputation: 6712
Although @hatchet is right in avoiding using functions on WHERE
clause, I guess this is not the problem in this case, because it is used over scalar values (one could only be sure with the actual query plan).
Definitely, you can remove the reference to the table Dim_CustomerOrderTypes
, that is not filtering nor returning any data. And I believe this query should improve performance using the following indexes:
-- to seek on [Salesperson] and scan on [Order Date]
CREATE CLUSTERED INDEX IDXC ON AA_FactSalesOrderDetails([Salesperson], [Order Date]);
-- to seek on key
CREATE CLUSTERED INDEX IDXC ON Dim_Customers([Dim_Customers_dKey]);
-- to seek only this index instead of reading from table
CREATE INDEX IDX0 ON Dim_SalesOrganisation([Salesperson], [Salesperson name]);
-- to seek only this index instead of reading from table
CREATE INDEX IDX0 ON Dim_Items ([Item number], [Product group description])
I hope these suggestions help you.
Upvotes: 0
Reputation: 16257
Since the fnc_M3_sql_datetime_to_M3_date takes a value that is constant throughout the execution of the query, move those two calls (the one with startDate and the one with endDate to the top of your query and assign the returned values to declared variables. Then reference those declared variables below instead of calling the function within the where clause. That may help. Functions sometimes inhibit the formulation of a good query plan.
This talks a little about it Why do SQL Server Scalar-valued functions get slower? and this too http://strictlysql.blogspot.com/2010/06/scalar-functions-on-where-clause.html
declare @m3StartDate Numeric(8,0)
Set @m3StartDate = fnc_M3_sql_datetime_to_M3_date(@StartDate)
declare @m3EndDate Numeric(8,0)
Set @m3EndDate = fnc_M3_sql_datetime_to_M3_date(@EndDate)
...
WHERE AA_FactSalesOrderDetails.[Order Date]
BETWEEN @m3StartDate AND @m3EndDate
AND
AA_FactSalesOrderDetails.Salesperson = @SalesPersonCode
The type of the two @m3-- vars should be exactly the same as AA_FactSalesOrderDetails.[Order Date].
I would also examine the definition of the key on Dim_Customers that is getting the scan instead of a seek, and ensure Dim_Customers is indexed in a way that helps you if it isn't already. http://blog.sqlauthority.com/2009/08/24/sql-server-index-seek-vs-index-scan-diffefence-and-usage-a-simple-note/
Upvotes: 2