June 6, 2013 at 9:50 am
I have this query:
UPDATEinvoice
SETactual_membernumber = members.mbrnbr,
vin = members.vin
FROM Network_Transaction invoice (nolock)
CROSS APPLY
(
SELECT clientindex, lname, fname, mbrnbr, vin FROM vw_ActiveMembers members (nolock)
WHERE members.clientindex = invoice.client_id
) AS members
INNER JOIN erm_claimsdata c (nolock) ON c.clientid = members.clientindex
WHERE invoice.transaction_type = 'I'
AND (@filename is null OR invoice.filename = @filename)
AND (@client_id is NULL OR client_id = @client_id)
AND invoice.deletedflag = 0
AND invoice.exportedflag = 0
AND invoice.last_eight_vin is not null
AND invoice.actual_membernumber is null
AND members.vin LIKE '%' + invoice.last_eight_vin
if I run it in SSMS it takes about 8 minutes, but when the same query is executed in a stored proc it takes 5 hours...sometimes longer before I kill the session. What could possibly cause this? This is on SQL Server 2005. Thanks
June 6, 2013 at 10:00 am
This is very likely to be parameter sniffing.
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
Your query itself also appears to have some of the performance issues discussed in the "catch-all" queries article.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
Last but not least, be VERY careful with those NOLOCK hints. They are very dangerous. As this looks like some sort of claims system you need to fully understand what that hint does and the nasty things it brings to the table.
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
_______________________________________________________________
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/
June 6, 2013 at 10:15 am
The dreaded IS NULL OR construct is the root issue here. Fix it per Sean's link. Those simply MUST NOT BE ALLOWED ON YOUR SYSTEM - EVER!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy