Bad performance of a query
-
It's executed one per row. Hence 86K times.
The reason it "becomes" slow is that 86K times 0.01 sec is still 8.6 seconds.
-
Does the function query other tables? Then it doesn't matter how efficient the query inside it is when it has to execute 86000 times.
Look in the execution plan you posted and look for the "Compute Scalar" operator that executes the function. The "in-line" contains 86000 rows.
Edit: Ninja beat me
Edit 2: Here's a screen shot of the plan where the scalar function is executed
-
thank you both. I am trying to figure out the compute scalar operator among so many operators, so meanwhile I am attaching the schema for the procedure which I never posted.
Thanks
Chandan
-
I found the operator from the plan which you talked about. As per your remark, this function is getting executed 85k+ times. Not sure, how to avoid this.
Can you please guide me.
Chandan
-
Its probably in the view vw_D1OrderHeader.
-
Nils Gustav Stråbø (8/23/2011)
Its probably in the view vw_D1OrderHeader.Yes. you were faster enough to leave me behind in editing my post. It is indeed the part of the view. What do you see as a quick cure to the problem. I think I should somehow get rid of the function in the view. the view is based on 5 costly tables each having million plus rows.
-
5M rows is not much. Should run pretty fast if you can avoid that function...
-
I don't know how the function looks like, but my suggestion is to create a new inline table variable function and then use CROSS APPLY/OUTER APPLY (depending on whether each passed parameter returns a row or not, similar to inner outer join) to call it in the view.
-
The view itself is nasty:
SELECT
b.CarrierCode,b.Descr as CarrierDescr, a.Comment, a.InternalCmt, RTRIM(e.CustName) as CustName, e.CustNum, a.DeliveryDate, Year(a.DeliveryDate) as DeliveryYear, a.InvDate,
a.InvoiceAmt, a.OrderID, a.OrderStatus, a.Po as PONumber, a.ReleaseNum,
c.ShipTo, c.ShipToName, d.Line1 as ShipToLine1, d.Line2 as ShipToLine2, d.AddrName as ShipToAddress, d.City as ShipToCity, d.Country as ShipToCountry, d.State as ShipToState,
d.ZipCode as ShipToZipCode,
dbo.fn_GetDispatchStatus(a.OrderID,a.Dispatched) as DispatchStatus,
a.Time1 as WindowStart, a.Time2 as WindowEnd, a.GUID as OrderGuid, c.CustomerID, b.VendorID,
a.DeliveryTicketNo,a.Dispatched, 0 as RecordCount
FROM dbo.Orders a (NOLOCK)
JOIN dbo.Carrier b (NOLOCK) ON b.CarrierID = a.CarrierID
JOIN dbo.Shipto c (NOLOCK) ON c.ShipToID = a.ShipToID
JOIN dbo.Address d (NOLOCK) ON d.AddressID = c.AddressID
JOIN dbo.Customer e (NOLOCK) ON e.CustomerID = c.CustomerID
It is using function as one of the select columns and it means it will get called so many times in the view itself.
Any clue how to avoid the function here?
Thanks for your help.
-
Here is an example on how inline functions outperforms a scalar function. the example is quite silly, but it illustrates the differences, and how scalar and inline functions are used.
If you look at the query plan for the inline example, you'll see that there is no call to the function itself. It is "merged" into the calling SQL.
In the example using the scalar function you will se that the function is called 100000 times, and if you run Profiler (inlcude SP:Starting) while the query is running you are in for a shock.
use tempdbgo
create table dbo.Orders(OrderId int identity primary key, OrderStatus tinyint not null)
go
-- Insert 100000 orders
Insert into Orders(OrderStatus)
select top 100000 t.c
from sys.columns a,sys.columns b
cross apply(select cast(newid() as binary(16)) & 1)t(c)
go
-- create a scalar function that reads order status
create function dbo.GetOrderStatus_scalar(@OrderId int)
returns tinyint
as
begin
declare @status tinyint
select @status=OrderStatus from dbo.Orders where OrderId=@OrderId
return @status
end
go
-- create an inline table valued function that reads order status
create function dbo.GetOrderStatus_inline(@OrderId int)
returns table
as
return(select OrderStatus from dbo.Orders where OrderId=@OrderId)
go
-- Execute a query using the scalar function. On my pc this takes 25 seconds, and performs 1.750.0000 reads and 27.000 writes!! YIKES!!
-- you have to use Profiler to see the actual reads and writes.
select OrderId,dbo.GetOrderStatus_scalar(OrderId) as OrderStatus
from dbo.Orders
-- And the execute a similar query using the inline function. This takes less than a second, with 698 logical reads, 0 writes!!!!
select o.OrderId,s.OrderStatus
from dbo.Orders o
cross apply dbo.GetOrderStatus_inline(o.OrderId) s
-
Same train of thoughts but with triggers. I wrote this 4 years ago but it still apply to this case => http://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/
-
Thanks to both of you. I will learn with the examples you provided and try to implement an inline function.
Meanwhi8le, the function was being used to display 'dispatchstatus'. To test the stuff, i got rid of this column so this function was never called in my procedure. I created a dummy procedure having everything as same as original but avoided using the column which uses function( did not use the function in view definition).
It gave me 20 seconds benefit but it still runs for 4 min 🙁
Table 'Address'. Scan count 0, logical reads 516864, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customer'. Scan count 0, logical reads 12826152, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Shipto'. Scan count 0, logical reads 19323072, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Carrier'. Scan count 0, logical reads 14207632, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#7E8FF20D'. Scan count 2, logical reads 3551908, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 2, logical reads 10893249, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
I am posting the new execution plan here. I know some tables are being scanned because in where clause we have something like '%value% but still i think it is doing something which can be avoided.
-
I'm not certain this will help in your case, but I noticed that you have a variable named @ipOrderGuid which is declared as a varchar(max)
You might try changing the type of this variable to be the same type as the orderGuid column in Orders.
Another thing I'd recommend is refactoring the
(SELECT count(*) FROM @ipOrderStatusCodes WHERE...)=0block in the header insert so that you pre-calculate the counts. I don't think this will improve your performance significantly, but it will simplify your plan a lot, making it much easier to zero in on your core problem...
You're already precalculating the count for all status codes, do the same for each of those.
After you make that change, please repost your plan.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.' -
weitzera (8/23/2011)
I'm not certain this will help in your case, but I noticed that you have a variable named @ipOrderGuid which is declared as a varchar(max)You might try changing the type of this variable to be the same type as the orderGuid column in Orders.
Another thing I'd recommend is refactoring the
(SELECT count(*) FROM @ipOrderStatusCodes WHERE...)=0block in the header insert so that you pre-calculate the counts. I don't think this will improve your performance significantly, but it will simplify your plan a lot, making it much easier to zero in on your core problem...
You're already precalculating the count for all status codes, do the same for each of those.
After you make that change, please repost your plan.
I have changed the data type of variables on the same lines as the view(or base tables).It can surely help us. But i did not understand your point where you talked about 'refactoring' . Can you please explain it a bit.
Regards
Chandan
-
On or around line 287 you have a bunch of subqueries selecting counts from the @ipOrderStatusCodes with no outer references (meaning the subquery does not reference anything in the main query, so it will be the same value for every row.)
I think you should change these lines like this:
((SELECT count(*) FROM @ipOrderStatusCodes WHERE StatusCode='O' AND SubCode='Declined')=0 OR (a.OrderStatus = 'O' AND DispatchStatus='Declined')) AND ...becomes:
(@ipOrderStatusCodeCountODeclined=0 OR (a.OrderStatus = 'O' AND DispatchStatus='Declined')) AND ...On line 193 where you calculate the total order status count, you can precalculate each of these values:
DECLARE @ipOrderStatusCodeCountODeclined INT;SELECT @ipOrderStatusCodeCountODeclined=count(*) FROM @ipOrderStatusCodes WHERE StatusCode='O' AND SubCode='Declined';
and so on...
Again, I don't think this is affecting your performance, but it is making it harder to read the plan.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply