How do I fix the cardinality issue in the 4th & 5th query?

John Couch 2014-01-21 23:42:41

The 5th and 6th query was an attempt to separate the 3rd query into more usable results that would make better use of the indexes on the tables.

Aaron Bertrand 2014-01-21 23:50:25
I assume statistics are up to date? And since you are dealing with date parameters (which likely shift over time), have you tested the query with OPTION (RECOMPILE)? (Though I confess I don't see that the compiled values of those parameters are exposed in the plan.)
SQLkiwi 2014-01-22 00:09:43
Yes, do try adding OPTION (RECOMPILE) to the problematic statements. When running ad-hoc SQL (outside a procedure), SQL Server does not sniff the values of variables for cardinality estimation. Adding the hint changes that, so estimates will be based on the specific values at the time.
John M Couch 2014-01-22 00:23:28
I tried that and I still have a pretty big data skew. Looks like the temp table is taking a scan vs. a seek.
SQLkiwi 2014-01-22 02:11:00
You might be right. Hard to say without being able to see what you can see.
John M Couch 2014-01-21 23:56:45
Hi Aaron. Yes, I just actually updated statistics and usage on the database. Here is the code I was testing with outside of the procedure.

 declare    @customer_number int = 860658066,    
            @search_limit int = 0,    
            @my_quote char(10) = '0',    
            @login_profile_id int = 2684667,    
            @soldtonumber  int = 10043536
 
 declare  @li_CustomerNumber int = @customer_number
         ,@li_SearchLimit int = @search_limit
         ,@lc_MyQuote char(10)
         ,@li_LoginProfileID int = @login_profile_id
         ,@li_SoldToNumber int = @soldtonumber
         ,@lvc_SoldToNumber varchar(10) = replicate('0',(10 - len(@soldtonumber))) + cast(@soldtonumber as varchar(10)) 
         ,@ldt_StartDate datetime = dateadd(mm, -300, getdate()) 
         ,@ldt_EndDate datetime = getdate()
         ,@lsdt_StartDate datetime = dateadd(mm, -300, getdate())
         ,@lsdt_EndDate datetime = getdate()         
         ,@lc_SearchMyQuote char(1) 
         ,@li_SalesAreaID int
         ,@ldt_ExpirationDate datetime = null   
         ,@lvc_SalesOrg varchar(4)
 
     SET nocount on
 
    SET @lc_SearchMyQuote = 
      CASE WHEN @li_SearchLimit = 1 
           THEN 'Q' 
           ELSE '' 
       END 
 
    SET @lc_MyQuote = replicate('0',(10 - len(@my_quote))) + cast(@my_quote as varchar(10))
 
        set @lvc_SalesOrg  = ( select distinct(sales_org) from rept_client_sales_area b with (nolock) 
            inner JOIN rept_client_experience_to_soldto p with (nolock) 
                on p.sales_area_id = b.sales_area_id and b.partner_number = p.partner_number 
                    inner JOIN rept_sales_area_master c with (nolock) on c.sales_area_id = b.sales_area_id
                        where p.client_experience_id =@li_CustomerNumber) 
 
      SELECT @li_SalesAreaID = sales_area_id    
      FROM web_reporting.dbo.rept_client_sales_area csa with (nolock)     
      WHERE csa.partner_number = @li_SoldToNumber
         AND is_deleted = 0  
 
 
      SELECT distinct top 5 
        ob.customer_soldto,    
        ob.sales_document_number,    
        ob.customer_internal_reference,    
        ob.customer_contact,    
        cast(ob.order_create_date as datetime) as order_create_date,     
        cm.name_1,    
        CASE WHEN ((rtrim(ltrim(isnull(cm.blocked_reason,''))) = '90') 
              AND (cm.is_deleted = 0))
              or ((rtrim(ltrim(isnull(csa.blocked_reason,''))) = '90') 
              AND (csa.is_deleted = 0)) then 1
             WHEN ((rtrim(ltrim(isnull(cm.blocked_reason,''))) = '01') 
              AND (cm.is_deleted = 1))     
              or ((rtrim(ltrim(isnull(csa.blocked_reason,''))) = '01') 
              AND (csa.is_deleted = 1)) then 2
             WHEN ((rtrim(ltrim(isnull(cm.blocked_reason,''))) not in('01', '90')) 
              AND (cm.is_deleted = 0))  
              or ((rtrim(ltrim(isnull(csa.blocked_reason,''))) not in ('01' ,'90')) 
              AND (csa.is_deleted = 0)) then 0
          END,  
         ob.nweb, 
         @ldt_ExpirationDate   
      FROM web_reporting.dbo.wr_order_buffer ob with (nolock) 
           INNER JOIN web_reporting.dbo.rept_client_master cm with (nolock) on cm.partner_number = @li_SoldToNumber    
           INNER JOIN web_reporting.dbo.rept_client_sales_area csa with (nolock) on csa.sales_area_id = @li_SalesAreaID    
             AND csa.partner_number = @li_SoldToNumber   
      WHERE ob.order_create_date between @lsdt_StartDate AND @lsdt_EndDate   
        AND ob.sales_document_type = 'ZQT'                 
        AND ob.nweb != 'X' 
        AND ob.customer_soldto = @lvc_SoldtoNumber    
      ORDER BY order_create_date desc ,ob.sales_document_number desc 
 
create table #ltbl_OrderHeader (customer_soldto varchar(10)
                                ,sales_document_number varchar(10)
                                ,sales_document_type varchar(4)
                                ,customer_internal_reference varchar(12)
                                ,order_create_date datetime
                                ,sales_area_id int
                                ,quote_valid_to_date datetime
                                CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber
                                PRIMARY KEY CLUSTERED (sales_document_number, sales_document_type)
                                WITH (IGNORE_DUP_KEY = OFF)
)
 
--create clustered index cidx__ltbl_OrderHeader__sales_document_type__sales_document_number on #ltbl_OrderHeader  (sales_document_number, sales_document_type)
 
          SELECT distinct 
        oh.customer_soldto,
        oh.sales_document_number, 
        oh.customer_internal_reference, 
        oh.order_create_date,
        od.ZCONT_ID,    
        @li_SalesAreaID,  
        oh.quote_valid_to_date 
        FROM GDW_WEB.dbo.bi_order_Header oh WITH (nolock) 
        INNER JOIN GDW_WEB.dbo.bi_order_detail od with (nolock) on oh.sales_document_number = od.sales_document_number       
               AND oh.sales_document_type = od.sales_document_type    
         WHERE oh.order_create_date between @ldt_StartDate AND @ldt_EndDate    
           AND oh.customer_soldto = @lvc_SoldToNumber 
           AND oh.sales_document_type = 'ZQT'    
           AND oh.nweb != 'X' -- ADDED CRD 3168, Revision 0016  
           and oh.sales_organization = @lvc_SalesOrg
 
insert into #ltbl_OrderHeader                             
        SELECT oh.customer_soldto,
                oh.sales_document_number, 
                oh.sales_document_type,
                oh.customer_internal_reference, 
                oh.order_create_date,
                @li_SalesAreaID,  
                oh.quote_valid_to_date 
        FROM GDW_WEB.dbo.bi_order_Header oh WITH (nolock)
         WHERE oh.order_create_date between @ldt_StartDate AND @ldt_EndDate    
           AND oh.customer_soldto = @lvc_SoldToNumber 
           AND oh.sales_document_type = 'ZQT'    
           AND oh.nweb != 'X'
           and oh.sales_organization = @lvc_SalesOrg
 
        SELECT distinct
        oh.customer_soldto,
        oh.sales_document_number, 
        oh.customer_internal_reference, 
        oh.order_create_date,
        od.ZCONT_ID,    
        @li_SalesAreaID,  
        oh.quote_valid_to_date 
        FROM #ltbl_OrderHeader oh INNER JOIN GDW_WEB.dbo.bi_order_detail od with (nolock, INDEX(ix__bi_order_detail_sales_doc_type__po_number__contact_email__contact_name)) 
                                                                  on oh.sales_document_number = od.sales_document_number       
                                                                 AND oh.sales_document_type = od.sales_document_type 
                                                                 --AND od.sales_document_type = 'ZQT'
 
 
 
drop table #ltbl_OrderHeader