December 1, 2008 at 3:10 am
Hi,
I have a stored procedure which contains more than 1000 lines of code.
It has following kind of structure:
IF (some condition)
BEGIN
--Do something
END
ELSE IF(some condition)
BEGIN
--Do something
END
ELSE IF(some condition)
BEGIN
--Do something
END
......
......
.......
And so on.
When I try to execute this stored procedure, it is taking nearly 17-18 seconds first time to give the output. Subsequent execution of the same stored procedure takes hardly 1 second.
After trying indexing, statement optimizations finally I commented rest all ELSE IF statements and kept only 1 'IF' statement. Now, when I executed this stored procedure, it is giving the same output within less than 1 second.
So can the lenghtier stored procedure cause the longer execution time? Mentioned stored procedure is very much bulky and have large no. of comments specified in it.
December 1, 2008 at 4:24 am
On the first execution, the statement has to be complied and optimised. The optimiser will optimise all of the queries in the proc, regardless of whether or not they can be reached with the current parameters. If there's contention for the proc cache (which I've seen on 2000, not 2005) that compile can take a while. It shouldn't be 15 seconds though.
You can test. Run the proc from management studio with Statistics Time on. You'll see an entry for parse and compile time.
It's usually a good idea to spit that kind of proc up, no so much for compile time, but because of the optimise all on first execution, you can get some really bad plans for some branches.
If (someCondition)
exec Subproc1
if (someotherCondition)
exec Subproc2
....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2008 at 12:56 pm
I would do all the "IF...THEN" on the "Client" and call the appropriate proc on the server directly.
* Noel
December 3, 2008 at 7:16 am
Also the data may not have been cached in memory on first execution so you had to do physical reads, which are slower than logical reads. While the data was in memory for the second execution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 3, 2008 at 8:18 am
The first time excution of any stored procedure will take more time than subsequent runs. It has to compile the script, decide on execution plan, pull the data pages,etc.
Once all these are set, from next time, the same Stored Procedure will just use the previous execution plan and through the desired result.
But for the better processing time and managing objects, keeping the stored procedure small is a better idea. It will also be very helpful in debugging.
Regards
Atul
December 3, 2008 at 9:26 am
Splitting it into multiple sub-procs is generally a good idea, in terms of execution plans for each one. I've gotten very nice performance improvements by doing exactly that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 3, 2008 at 12:43 pm
yeh i am also thinking that you must split procedure , because reason is that you have so many if condition in to that. so there is rare chance to reuse of right execution plan... believe me split that and it will definately improve performance
Raj Acharya
July 8, 2010 at 5:07 am
I prefer a solution below.
declare @proc_name sysname
if (someCondition)
set @proc_name = 'Subproc1'
if (someotherCondition)
set @proc_name = 'Subproc2'
...
exec @proc_name
July 8, 2010 at 5:20 am
Please note: Two year-old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2010 at 5:31 am
Silly me. I looked at "Last login" date. :blush: Eh, what can I say. Sorry.
July 8, 2010 at 5:53 am
Matjaz Justin (7/8/2010)
Silly me. I looked at "Last login" date. :blush: Eh, what can I say. Sorry.
No worries. Just put that there so that anyone who sees the thread and goes to help is aware that the problem is likely long-time fixed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply