November 21, 2016 at 6:36 am
I have a stored procedure that updates a few 1000 records in a table.
When I run the SP from within the Studio Manager it takes less than a second to run.
When I do an EXEC of that same SP with same parameters from within my dotnet code, it takes about 4 seconds.
What can cause this difference?
If required I post my data sample, but it's quite complicated and since no output is being delivered from SP, I suppose it is irrelevant...
November 21, 2016 at 8:02 am
Run both through SQL Profiler and compare.
Things to look out for : Differences in connection settings, parameter sniffing, parameter conversion issues. Also maybe you could post your .net code and we can see if there's anything going on there.
November 21, 2016 at 8:07 am
marc.corbeel (11/21/2016)
I have a stored procedure that updates a few 1000 records in a table.When I run the SP from within the Studio Manager it takes less than a second to run.
When I do an EXEC of that same SP with same parameters from within my dotnet code, it takes about 4 seconds.
What can cause this difference?
If required I post my data sample, but it's quite complicated and since no output is being delivered from SP, I suppose it is irrelevant...
Any chance this being a case of MSDTC transaction escalation?
😎
November 21, 2016 at 8:17 am
This is the code that runs the SP.
parameter glbSQL_connection is the SQL connection
and SQL is the SP = 'EXEC stp_InventoryItemTypes_UpdatePathNamesOfChildren 1'
Dim oSQLcommand As SqlCommand = New SqlCommand(SQL, glbSQL_connection)
Try
oSQLcommand.ExecuteScalar()
Catch ex As Exception
End Try
November 21, 2016 at 8:41 am
After a while, execution from dotnet worked fast as well.
I haven't experienced this before...
Sorry to bother you all, my issue is solved (don't know the reason though)...
November 21, 2016 at 8:50 am
I doubt this is "solved", it has only gone away for the moment. It will come back again. This has the classic signs of parameter sniffing gone bad.
Check out Gail's article on the topic which explains the issue and how to solve for good.
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
_______________________________________________________________
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/
November 21, 2016 at 9:20 am
Thanks for this advice...
Learning every day!
November 21, 2016 at 10:11 am
DouglasH (11/21/2016)
Run both through SQL Profiler and compare.Things to look out for : Differences in connection settings, parameter sniffing, parameter conversion issues. Also maybe you could post your .net code and we can see if there's anything going on there.
A frequent problem to look for when comparing these is the setting of arithabort. You can also see this setting in the
sys.dm_exec_sessions dynamic management view. Management Studio defaults to arithabort = 1 (or ON) and .Net defaults to 0 (or OFF)
You can change what the connection default is at instance level:
https://msdn.microsoft.com/en-us/library/ms180124.aspx
Microsoft themselves even say don't use arithabort OFF
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply