Gradual Performance Degradation

Hi
I am using vb6, ADO 2.8 and SQL Server 2000.
Each time I run a stored proc (updates and inserts) I find that I get a 20%
increase in execution time, both through VB6 and Query Analyzer. If I
Disconnect and reconnect before executing the performance remains the same.
Any ideas where I should start looking.
RegardsYou should start by looking at the procedure definitions, objects, keys,
indexes, etc. Not necessarily in that order. If you post DDL and sample data
,
we can provide better help.
ML
http://milambda.blogspot.com/|||Thank you for reply, what I a looking for is an understanding of why the sp
execution time is consistant if I connect and disconnect in a loop and
increases if I keep the same connection . Its like DoEvents in VB. I think I
need some sort of flush command to SQL Server. The sp I run is a "HUB" whic
h
calls other functions and sp's there are no cursors or temporary tables. Th
e
T-SQL is hundreds of lines and not authored by myself. I have constructed a
simplistic version of the sp, but that runs with consistant execution times.
Also this sp is the only activity on the server.
Thanks again for reply
Regards
Martin
"ML" wrote:

> You should start by looking at the procedure definitions, objects, keys,
> indexes, etc. Not necessarily in that order. If you post DDL and sample da
ta,
> we can provide better help.
>
> ML
> --
> http://milambda.blogspot.com/|||Try executing the stored procedure multiple times in query analyzer and see
if you get the same performace degradation.
I suspect that the problem is with your VB code rather than the database.
You might try posting on a VB newsgroup to see what the recomended way is of
making multiple calls to the database. I know I have rewritten how my
connections are opened and closed as many as a dozen times to tune a VB
program doing a single insert 8000 times. My current code is using a
component for DB access, which opens and closes the connection every time it
is called, so I can't say exactly how I handled the connections after my
tuning.
"MartinT" <MartinT@.discussions.microsoft.com> wrote in message
news:D5686754-EA29-4F4A-9F07-A0193F64473F@.microsoft.com...
> Hi
> I am using vb6, ADO 2.8 and SQL Server 2000.
> Each time I run a stored proc (updates and inserts) I find that I get a
20%
> increase in execution time, both through VB6 and Query Analyzer. If I
> Disconnect and reconnect before executing the performance remains the
same.
> Any ideas where I should start looking.
> Regards
>|||If your simplified version of the procedure does what you need, then do you
still need the old one? It might be a case of "parameter sniffing" (google
for the subject), or it may just as well be due to the complexity of the
procedure which over time takes more and more time to recompile. Hard to say
,
really, without seeing the code.
ML
http://milambda.blogspot.com/|||try WITH RECOMPILE option in your stored procedure and try executing it|||Been out of the office for couple of days .. I have run in QA with the same
degredation. Re-Start QA First time is fine then next x times gets slower. I
n
VB I have run with perfmon and only ever have one connection and the number
of times the connection is opened and closed are the number of time the code
requested the connections to be open / Closed.
Its a mystery.
Thanks for your reply
MartinT
"Jim Underwood" wrote:

> Try executing the stored procedure multiple times in query analyzer and se
e
> if you get the same performace degradation.
> I suspect that the problem is with your VB code rather than the database.
> You might try posting on a VB newsgroup to see what the recomended way is
of
> making multiple calls to the database. I know I have rewritten how my
> connections are opened and closed as many as a dozen times to tune a VB
> program doing a single insert 8000 times. My current code is using a
> component for DB access, which opens and closes the connection every time
it
> is called, so I can't say exactly how I handled the connections after my
> tuning.
>
> "MartinT" <MartinT@.discussions.microsoft.com> wrote in message
> news:D5686754-EA29-4F4A-9F07-A0193F64473F@.microsoft.com...
> 20%
> same.
>
>|||Hmmm interesting one ... I will have a go and let you know.
Regards
MartnT
"Omnibuzz" wrote:

> try WITH RECOMPILE option in your stored procedure and try executing it
>|||Thanks will have a look and let you know.
Regards
Martin
"ML" wrote:

> If your simplified version of the procedure does what you need, then do yo
u
> still need the old one? It might be a case of "parameter sniffing" (google
> for the subject), or it may just as well be due to the complexity of the
> procedure which over time takes more and more time to recompile. Hard to s
ay,
> really, without seeing the code.
>
> ML
> --
> http://milambda.blogspot.com/