How do I fix the cardinality issue in the 4th & 5th query?
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.)
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.
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.
2014-01-22 02:11:00
You might be right. Hard to say without being able to see what you can see.
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