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/

good index?

Hi,
I have a table with
smallint,
smallint,
smalldatetime,
smallint
I made a clustered primary key containg the first 3 colomns.
Inserts are something like:
1,1,12:00,2
1,2,12:00,2
2,1,12:00,2
2,2,12:00,2
1,1,12:10,2
1,2,12:10,2
2,1,12:10,2
2,2,12:10,2
But a lot more rows of course. No updating or deleting.
I assume reindexing is not necessary because I select in the inserted order.
Am I right assuming this?
Thanks
FrankKeep these points in mind when considering using a clustered index;
* The physical ordering supports the range retrievals of important queries
* The cluster index key is used in the ORDER BY or GROUP BY clause
* The cluster index key is used in important joins relating multiple tables
* The cluster index columns are not changed regularly.
"Frank" wrote:
> Hi,
> I have a table with
> smallint,
> smallint,
> smalldatetime,
> smallint
> I made a clustered primary key containg the first 3 colomns.
> Inserts are something like:
> 1,1,12:00,2
> 1,2,12:00,2
> 2,1,12:00,2
> 2,2,12:00,2
> 1,1,12:10,2
> 1,2,12:10,2
> 2,1,12:10,2
> 2,2,12:10,2
> But a lot more rows of course. No updating or deleting.
> I assume reindexing is not necessary because I select in the inserted order.
> Am I right assuming this?
> Thanks
> Frank
>
>|||Check Scan Density of the index time to time using DBCC Showcontig.
If its less then 75-80% then either reindex it or defrag it using DBCC
Indexdefrag.
Thanks

Going after repeat offenders in SP

Issue is a batch processing SP that does multiple inserts (6 tables)
This statement contains a "repeat offending" NOT IN ( ) clause.
-- start code
SELECT
@.BatchID, MA.Merchant_Account_ID, 0, 0, @.Batch_Date, 0, 0
FROM Merchant_Account MA
WHERE Merchant_Account_ID NOT IN (SELECT El_Account_ID FROM
Operating_Account_Summary WHERE Transaction_Batch_ID=@.BatchID)
ORDER BY Merchant_Account_ID
Would it be better off to do that selection one time into a #T1 and
reference that all the needed times, finally dropping it at the end? Of
course I'd create it outside of the transaction and drop it likewise.
Table has 1/2 million rows now and growing @. 10,000 per w.
TIAIs it that the rows generated by this SELECT are to be inserted into
multiple tables? If so, it would make sense to populate a temp table and
then use it. I'm curious as to why these same rows have to be inserted into
multiple tables. I sit possible to use one table and reference it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"_Stephen" <srussell@.electracash.com> wrote in message
news:uIH3csTmGHA.492@.TK2MSFTNGP05.phx.gbl...
Issue is a batch processing SP that does multiple inserts (6 tables)
This statement contains a "repeat offending" NOT IN ( ) clause.
-- start code
SELECT
@.BatchID, MA.Merchant_Account_ID, 0, 0, @.Batch_Date, 0, 0
FROM Merchant_Account MA
WHERE Merchant_Account_ID NOT IN (SELECT El_Account_ID FROM
Operating_Account_Summary WHERE Transaction_Batch_ID=@.BatchID)
ORDER BY Merchant_Account_ID
Would it be better off to do that selection one time into a #T1 and
reference that all the needed times, finally dropping it at the end? Of
course I'd create it outside of the transaction and drop it likewise.
Table has 1/2 million rows now and growing @. 10,000 per w.
TIA|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e4con5TmGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Is it that the rows generated by this SELECT are to be inserted into
> multiple tables? If so, it would make sense to populate a temp table and
> then use it. I'm curious as to why these same rows have to be inserted
> into
> multiple tables. I sit possible to use one table and reference it?
Thanks for the reply.
It will insert different data table depending, but it's initial where is the
key not in (select key from other table)
So I will refactor this and other sp's that follow the same sense of
nonsense. Actually it's a refactor of an app that only needed to do this
once, but the new and better data layout needs the same call a few more
times.

global temporary tables

When does a global temp table get dropped? i have a stored proc that creates
a global temp table and inserts a record into it.say for eg. i run the store
d
proc first, the temp table gets created.if another user runs the stored proc
now,the temp table is already there,hence the stored proc just uses the same
table and inserts a record into it.now if i end my session,will the temp
table still be available to the other user?
Actually what iam experiencing is, the temp table gets dropped if i end my
session even though another person is still using it.
Thanks in advance.Hi,

>From the BOL:
Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing
them. The association between a task and a table is maintained only for
the life of a single Transact-SQL statement. This means that a global
temporary table is dropped at the completion of the last Transact-SQL
statement that was actively referencing the table when the creating
session ended.
HTH
Barry|||> Actually what iam experiencing is, the temp table gets dropped if i end my
> session even though another person is still using it.
Yes, that is correct. The life of a global temp table is the same as the
life of the session that started it. Which is one reason why they shouldn't
be used for sharing between concurrent users. If this is your intention,
use a real table!
A