Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why the difference in query execution times? Expand / Collapse
Author
Message
Posted Thursday, June 6, 2013 9:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:55 AM
Points: 8, Visits: 37
I have this query:

UPDATE invoice
SET actual_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
Post #1460776
Posted Thursday, June 6, 2013 10:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
This is very likely to be parameter sniffing.

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

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/

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1460782
Posted Thursday, June 6, 2013 10:15 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:53 AM
Points: 4,350, Visits: 6,162
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 at GMail
Post #1460791
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse