Need to reduce Query Time

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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/