May 15, 2014 at 7:15 am
Hi everyone
I've a problem with a SP, running slow, even though there is a return statement at the beginning of the SP. (I put it there for testing purposes)
for example:
alter procedure dbo._DO_something
(@myId char(36),
@matrixIdint,
@ret_statvarchar(4000) output)
as begin
set nocount on
set xact_abort on
set transaction isolation level read uncommitted
set @ret_stat = 'blah blah'
return 1
...
some 300 lines of code including try catch
...
return 0
end
is it recompile that might be bugging me?
Due to unsufficient rights I cannot use profiler or some DM view to check out if recompile takes place.
May 15, 2014 at 7:41 am
marko.celarc (5/15/2014)
Hi everyoneI've a problem with a SP, running slow, even though there is a return statement at the beginning of the SP. (I put it there for testing purposes)
for example:
alter procedure dbo._DO_something
(@myId char(36),
@matrixIdint,
@ret_statvarchar(4000) output)
as begin
set nocount on
set xact_abort on
set transaction isolation level read uncommitted
set @ret_stat = 'blah blah'
return 1
...
some 300 lines of code including try catch
...
return 0
end
is it recompile that might be bugging me?
Due to unsufficient rights I cannot use profiler or some DM view to check out if recompile takes place.
It should be nearly instant. The only thing that might slow it down would be if the plan is gone from the cache and it needs to be recompiled. But the recompile shouldn't take very long.
As a side note...do you know all the caveats with read uncommitted? Do you know that you can and will get missing and/or duplicate data at random times? That is the same thing as adding the NOLOCK to every table inside your procedure.
_______________________________________________________________
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/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply