Is there a recommended size limit to an SQL Program ?

I wrote a program with 546 lines. When the code was run to line 406, it too
k
2 minutes and 9 seconds. When it ran all the way through, I stopped it afte
r
9 minutes. I ran just the section from line 406 to the end and it ran in 20
seconds.
For some reason, when all of the code is run together, the time is not the
sum of all of the individual sections. I tryed commits in various parts of
the code, but it had no effect. I then split the program to 4 sub programs
executed by a master program and the entire program ran in 41 seconds. Is
there a limit to the number of lines for one program and is there any other
solution other than splitting to subprograms ?What do you mean by a program? Are you referring to a stored procedure or a
separate application written in C# or some other development language?
Keith Kratochvil
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:1CD546BB-D5D3-4436-AB12-548F8E40E976@.microsoft.com...
>I wrote a program with 546 lines. When the code was run to line 406, it
>took
> 2 minutes and 9 seconds. When it ran all the way through, I stopped it
> after
> 9 minutes. I ran just the section from line 406 to the end and it ran in
> 20
> seconds.
> For some reason, when all of the code is run together, the time is not the
> sum of all of the individual sections. I tryed commits in various parts
> of
> the code, but it had no effect. I then split the program to 4 sub
> programs
> executed by a master program and the entire program ran in 41 seconds. Is
> there a limit to the number of lines for one program and is there any
> other
> solution other than splitting to subprograms ?
>|||Are you using a lot of temp tables in this program? Or datasets/arrays if
you are referring to a language like VB or C?
If the individual pieces use a lot of memory, and you are not properly
freeing up this memory when it is no longer needed, then things can slow
down considerably.
Per Keith's post, can you include more information, and possibly the actual
code, with notes as to where you split it into smaller pieces?
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:1CD546BB-D5D3-4436-AB12-548F8E40E976@.microsoft.com...
> I wrote a program with 546 lines. When the code was run to line 406, it
took
> 2 minutes and 9 seconds. When it ran all the way through, I stopped it
after
> 9 minutes. I ran just the section from line 406 to the end and it ran in
20
> seconds.
> For some reason, when all of the code is run together, the time is not the
> sum of all of the individual sections. I tryed commits in various parts
of
> the code, but it had no effect. I then split the program to 4 sub
programs
> executed by a master program and the entire program ran in 41 seconds. Is
> there a limit to the number of lines for one program and is there any
other
> solution other than splitting to subprograms ?
>|||Also, splitting the program up into smaller pieces requires some redefining
of inputs and outputs. Is it possible that some of your variables were
assigned incorrect values in the original code, which caused the performance
problems?
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OIfl4HFdGHA.4148@.TK2MSFTNGP05.phx.gbl...
> Are you using a lot of temp tables in this program? Or datasets/arrays if
> you are referring to a language like VB or C?
> If the individual pieces use a lot of memory, and you are not properly
> freeing up this memory when it is no longer needed, then things can slow
> down considerably.
> Per Keith's post, can you include more information, and possibly the
actual
> code, with notes as to where you split it into smaller pieces?
> "rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
> news:1CD546BB-D5D3-4436-AB12-548F8E40E976@.microsoft.com...
> took
> after
in
> 20
the
> of
> programs
Is
> other
>|||Jim, Keith,
Yes. I am using over 70 temp tables. I created a huge SQL Stored
procedure. It slows down considerably the further it runs, so I split this
SQL stored procedure into many stored procedures which I run from 1 stored
procedure with the following commands.
Exec @.iErr = u_spPCLD01
Exec @.iErr = u_spPCLD02
Exec @.iErr = u_spPCLD03
Exec @.iErr = u_spPCLD04
Exec @.iErr = u_spPCLD05
Exec @.iErr = u_spPCLD06, Etc.
I will use the drop table command afer each temp table is used. If this
does not work is there a transaction log which may need to be flushed and ca
n
such code be imbedded in the stored procedure ?
Exec
"Jim Underwood" wrote:

> Also, splitting the program up into smaller pieces requires some redefinin
g
> of inputs and outputs. Is it possible that some of your variables were
> assigned incorrect values in the original code, which caused the performan
ce
> problems?
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:OIfl4HFdGHA.4148@.TK2MSFTNGP05.phx.gbl...
> actual
> in
> the
> Is
>
>|||I'm assuming that you are passing the temp tables between procedures. If
this is the case, I would seriously consider changing the code so that it
uses real permenant tables. This will help by stopping the procedures
recompiling. Also the code will run alot faster as SQL does not need to
create the temp tables every time.
I had a stored procedure which used a single temp table. When I changed to
code to use a single table the procedure sped up by ten times! It really is
worth trying.
Regards
Colin Dawson
www.cjdawson.com
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:2106DC08-9AD7-45F2-8793-DDC9E545778D@.microsoft.com...
> Jim, Keith,
> Yes. I am using over 70 temp tables. I created a huge SQL Stored
> procedure. It slows down considerably the further it runs, so I split
> this
> SQL stored procedure into many stored procedures which I run from 1
> stored
> procedure with the following commands.
> Exec @.iErr = u_spPCLD01
> Exec @.iErr = u_spPCLD02
> Exec @.iErr = u_spPCLD03
> Exec @.iErr = u_spPCLD04
> Exec @.iErr = u_spPCLD05
> Exec @.iErr = u_spPCLD06, Etc.
> I will use the drop table command afer each temp table is used. If this
> does not work is there a transaction log which may need to be flushed and
> can
> such code be imbedded in the stored procedure ?
>
>
> Exec
> "Jim Underwood" wrote:
>