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

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/