asked
Improving Query Execution
[link text][1][link text][2] Hi! I have a few queries that I really need to improve the performance on because they take too long to return the data. How could I improve the following query? DECLARE @FromDate Datetime; DECLARE @ToDate Datetime; DECLARE @cID Int; set @FromDate = '1/1/2015'; set @ToDate = '4/30/2015'; set @cID = '50113'; Select s.shipped_date AS ShipDate ,s.invoice_id AS InvoiceID ,s.invoiced_date AS InvoiceDate ,s.packlist_id AS PackListID ,
c.id AS CustID ,c.name AS CustName ,
sr.id AS SalesRepID ,sr.name AS SalesRepName ,CLD.User_5 AS DSPromiseDate ,col.promise_date AS LinePromise ,co.promise_date AS OrderPromise ,co.create_date AS OrderDate ,
co.id AS OrderID ,col.line_no AS OrderLineNo ,ShippedQty = CASE WHEN sld.line_no is not NULL THEN sld.shipped_qty ELSE sl.shipped_qty END ,sl.unit_price AS UnitPrice ,total_amt_shipped = case WHEN sld.line_no is not NULL THEN sl.unit_price * sld.shipped_qty WHEN col.total_amt_shipped = rl.amount THEN col.total_amt_shipped ELSE sl.unit_price * sl.shipped_qty End ,total_amt_invoiced = CASE WHEN s.invoice_id is not null and rl.amount is not null THEN rl.amount END, case WHEN sld.line_no is not NULL THEN sl.unit_price * sld.shipped_qty WHEN s.invoice_id is not null and rl.amount is null THEN sl.unit_price * sl.shipped_qty WHEN s.invoice_id is null THEN sl.unit_price * sl.shipped_qty Else rl.amount End ,col.part_id AS PartID ,PurFab = CASE WHEN p.Purchased = 'Y' THEN 'P' WHEN p.Fabricated = 'Y' THEN 'F' ELSE '' END ,p.user_1 AS LicenseCode ,p.commodity_code As CommodityCode ,ReasonCode = CASE WHEN CLD.User_2 is not NULL THEN CLD.User_2 WHEN COL.User_4 is not NULL THEN COL.User_4 ELSE '' END ,p.planner_user_id AS PlannerID ,CompareDate = CASE WHEN ISDATE(CLD.Desired_Ship_Date) = 1 THEN CLD.Desired_Ship_Date WHEN ISDATE(CLD.User_5) = 1 THEN CLD.User_5 WHEN ISDATE(COL.promise_date) = 1 THEN COL.promise_date WHEN ISDATE(CO.promise_date) = 1 THEN CO.promise_date END ,dbo.ParsePart(
p.id,3) AS PartCondition ,ShipStatus = 'Shipped' ,co.CURRENCY_ID ,co.CUSTOMER_PO_REF FROM DATABASE.dbo.shipper s LEFT JOIN DATABASE.dbo.shipper_line sl ON s.packlist_id = sl.packlist_id LEFT JOIN DATABASE.dbo.cust_order_line col ON sl.cust_order_id = col.cust_order_id AND sl.cust_order_line_no = col.line_no LEFT JOIN DATABASE.dbo.receivable_line rl ON s.invoice_id = rl.invoice_id AND sl.cust_order_id = rl.cust_order_id AND sl.cust_order_line_no = rl.cust_order_line_no LEFT JOIN ONTIC.dbo.customer_order co ON
co.id = col.cust_order_id LEFT JOIN ONTIC.dbo.customer c on co.customer_id =
c.id LEFT JOIN DATABASE.dbo.sales_rep sr ON co.salesrep_id =
sr.id LEFT JOIN DATABASE.dbo.Shipper_line_del sld ON sl.PackList_ID = sld.Packlist_ID AND sl.cust_order_id = sld.cust_order_id AND sl.cust_order_line_no = sld.cust_order_line_no LEFT JOIN DATABASE.dbo.Cust_Line_DEL cld ON cld.cust_order_id = sld.cust_order_id AND cld.cust_order_line_no = sld.cust_order_line_no AND cld.del_sched_line_no = sld.cust_del_line_no LEFT JOIN DATABASE.dbo.part p ON col.part_id =
p.id LEFT JOIN DATABASE.dbo.account a ON
a.id = col.GL_REVENUE_ACCT_ID WHERE COL.promise_date BETWEEN @FromDate AND @ToDate and
c.id = @cID [1]: /storage/temp/2369-blexecutionplan.sqlplan [2]: /storage/temp/2367-queryexecutionplan.sqlplan
Yeah, none of the classic code smells are present, so I'd need to see the execution plan to see how the optimizer is dealing with your structure. One vague suggestion, instead of local variables, try this with parameters to see what happens to the execution plan and execution time. Parameter sniffing is a good thing.
Are those all tables, or are there some views in the FROM clause? But, yeah, the execution plan would be helpful.
I think you missed it. There's nothing there except a local variable assignment.
The query is complex enough that the optimizer timed out rather than finish. You can see this in the SELECT operator properties of the execution plan. In general, to "fix" this, you have to reduce the complexity of the plan. It's an estimated plan, so I can't tell for sure how accurate your statistics are. That's something you can check for yourself. It thinks its returning 86.3028 rows. How accurate is that? First real issue I see is that you have a CONVERT_IMPLICIT on the Customer.X_CUSTOMER_1 index: CONVERT_IMPLICIT(int,[ONTIC].[dbo].[CUSTOMER].[ID] as [c].[ID],0)=[@cID] That's causing an index scan. Get the right data type there. You also have multiple key lookups on Customer, customer_order and shipper_line. You either need to get the clustered index to handle those calls, or possibly add the OUTPUT columns from the lookups to the nonclustered indexes using the INCLUDE operator. For customer, that will work well. It's only looking up the NAME column. It's retrieving five columns from customer_order, so it might not work well. It's only two columns for shipper_line, so that will work as INCLUDE pretty well. Really, most of the plan looks pretty good. You don't have anything major out of wack. The timeout is concerning, but the basic plan looks good. Fix the data type. Possibly fix the lookups. Assuming the statistics are accurate, there's not much else you can do from there.
MSDN is the best place for that kind of information:
https://msdn.microsoft.com/en-us/library/ms188783(v=sql.90).aspx
17 People are following this question.
