August 22, 2011 at 8:05 am
Hi Experts,
I was given a stored procedure by a developer to tune it. I used io and time statistics to drill down to a command that takes much of the load. Here is that select query:
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
JOIN dbo.Carrier b ON b.CarrierID = a.CarrierID
JOIN dbo.Shipto c ON c.ShipToID = a.ShipToID
JOIN dbo.Address d ON d.AddressID = c.AddressID
JOIN dbo.Customer e ON e.CustomerID = c.CustomerID
Even after adding indexes on the join fields, I cannot make this query run fast enough. I am adding table scripts in the attachment. Please give me your opinion.
Thanks
Chandan
August 22, 2011 at 8:19 am
There's not much to tune here.
Index are there to <mostly> support filtering or order operations. Neither of which you ar doing here.
I can take a quick look and the actual execution plan if you post it but I don't think I can help much.
August 22, 2011 at 8:21 am
Yeah, I'd need to see the execution plan.
The fact that there is no WHERE clause is concerning. Queries that don't filter the data frequently lead to scans and there's little you can do about that. If they're trying to read everything, then everything will get touched and processed. Processing everything, can't be made faster.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2011 at 10:51 am
Actually I should have been more precise and accurate here. The above query is actually a view and in my procedure, there are some filter operations in where clause. I was more concerned because view was taking a lot of time to display but as both of you stated correctly, I should have looked it more carefully.
So now the question if, in my procedure this view is used with some where clause filters, so should i look at creating the index on the view itself or underlying tables(depending upon which columns are used?
I believe I should create an index on view itself rather than going to base tables but I use a 2008 R2 standard edition so not sure if I can create one on a view. I tried creating an index on the view and it gave me an error' cannotc create a view because the view is not schema binded.'
Thank you Ninja and Grant for your thoughts. Please help me with your advice on the above.
Regards
Chandan
August 22, 2011 at 11:03 am
On the base table(s).
An indexed views materialize the results of the view and must be kept up to data every time you do dml on the base table(s).
And it's a PITA to build usually (dozens of limitations).
August 22, 2011 at 11:20 am
Indexed views are really only good if you have reasonably unvolatile data. If your data is being updated constantly then the indexed view is going to hurt more than it helps by a long shot. So, I'd recommend indexing the tables appropriately.
Also, based on your description I have to ask, are you joining this view to other views or other tables? If so, you're introducing other performance issues since you'll be making it much more difficult for simplification to take place within the query optimizer. Meaning, if SQL Server can, it will ignore tables in the view, but this is part of the optimization process. If you have views joining views, the query will be overly complex and the optimizer won't be able to simplify and eliminate tables.
All sorts of stuff to think about.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2011 at 12:00 pm
Grant Fritchey (8/22/2011)
Indexed views are really only good if you have reasonably unvolatile data. If your data is being updated constantly then the indexed view is going to hurt more than it helps by a long shot. So, I'd recommend indexing the tables appropriately.Also, based on your description I have to ask, are you joining this view to other views or other tables? If so, you're introducing other performance issues since you'll be making it much more difficult for simplification to take place within the query optimizer. Meaning, if SQL Server can, it will ignore tables in the view, but this is part of the optimization process. If you have views joining views, the query will be overly complex and the optimizer won't be able to simplify and eliminate tables.
All sorts of stuff to think about.
Sir,
I am attaching the query plan for the entire procedure itself. It runs for 3 min and displays 20 rows. Profiler column 'Reads' displays 10000000+ value.
Please suggest what to tune here. I may be going wrong in tuning the tables which might not be required here.Please guide me.
Thanks
Chandan
August 22, 2011 at 12:34 pm
Start by removing the scalar function. Does it run any faster/less reads? If so, try to rewrite the scalar function to a inline table valued function.
August 22, 2011 at 1:10 pm
Nils Gustav Stråbø (8/22/2011)
Start by removing the scalar function. Does it run any faster/less reads? If so, try to rewrite the scalar function to a inline table valued function.
Which scalar function are you talking about here? The operator showing Table valued function(XML Reader with xpath filter)?
August 22, 2011 at 1:44 pm
dbo.fn_GetDispatchStatus
August 22, 2011 at 1:56 pm
Nils Gustav Stråbø (8/22/2011)
dbo.fn_GetDispatchStatus
Did it look costly to you? I mean we can write something better here but can we find some way to reduce so much of execution time.
thanks
August 22, 2011 at 3:03 pm
It does appear that your function is the major bottleneck. Scalar functions are notorious for being huge hits on performance. There is a great thread here talking about the difference between TVF and scalar functions. Also read the link in the post from the OP. Did you try removing the function from the query as suggested to see what kind of performance gains you might find?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 22, 2011 at 3:25 pm
I'd say that it's the XML that's killing you. Passing XML into a query is fine, but you should load it into temp tables as soon as possible. Constantly querying the XML is extremely costly and, there's nothing you can do to speed it up. Immediately moving the XML out into a temp table means you can put indexes on it and get some statistics generated and the optimizer will having something to work with.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 23, 2011 at 4:57 am
chandan_jha18 (8/22/2011)
Nils Gustav Stråbø (8/22/2011)
Start by removing the scalar function. Does it run any faster/less reads? If so, try to rewrite the scalar function to a inline table valued function.Which scalar function are you talking about here? The operator showing Table valued function(XML Reader with xpath filter)?
I just opened the sqlplan, and my first though was: OMG 🙂
A huge "one query fits all" with lots of ANDs and ORs.
The scalar function is still an issue as it is executed once per row over 86000 rows, but the WHERE also causes a lot of unnecessary index scans and key lookups due to the AND and OR mess.
I would try two things: change the scalar function to a iTVF, and rewrite the final query into a dynamic SQL which gets executed with sp_executesql. sp_executesql has it's downsides though; breaking of ownership chain and potential sql incjection if you don't use parameters.
Anyone else who has any thoughts on my suggestion? Feel free to butcher it if you want to 😛
August 23, 2011 at 6:01 am
Nils Gustav Stråbø (8/23/2011)
chandan_jha18 (8/22/2011)
Nils Gustav Stråbø (8/22/2011)
Start by removing the scalar function. Does it run any faster/less reads? If so, try to rewrite the scalar function to a inline table valued function.Which scalar function are you talking about here? The operator showing Table valued function(XML Reader with xpath filter)?
I just opened the sqlplan, and my first though was: OMG 🙂
A huge "one query fits all" with lots of ANDs and ORs.
The scalar function is still an issue as it is executed once per row over 86000 rows, but the WHERE also causes a lot of unnecessary index scans and key lookups due to the AND and OR mess.
I would try two things: change the scalar function to a iTVF, and rewrite the final query into a dynamic SQL which gets executed with sp_executesql. sp_executesql has it's downsides though; breaking of ownership chain and potential sql incjection if you don't use parameters.
Anyone else who has any thoughts on my suggestion? Feel free to butcher it if you want to 😛
You are right. there a lot of joins plus the whole world of 'and' and 'or'. BTW, if you are talking about the function: dbo.fn_GetDispatchStatus, then i should tell you that it is very efficient. But I need to know how you determined that it is executed 86k+ times?
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply