performance degradation after several inserts

We have a VB6 app that uses an MSDE SP3 database. We use a process with
transactions to add data to the database. As several similar packets of data
are added we've noticed a degradation in the speed of loading the
information. We've also noticed that if at the end of the insert of several
packets of data we get a 30 second upload time per packet, then shut down
MSDE, start it up again and then insert in an almost identical packet of
data, it will run twice to 3 times faster.
We tried turning autoshrink and auclose off but that didn't seem to help.
Thanks,
Terry
hi Terry,
tgrignon wrote:
> We have a VB6 app that uses an MSDE SP3 database. We use a process
> with transactions to add data to the database. As several similar
> packets of data are added we've noticed a degradation in the speed of
> loading the information. We've also noticed that if at the end of
> the insert of several packets of data we get a 30 second upload time
> per packet, then shut down MSDE, start it up again and then insert in
> an almost identical packet of data, it will run twice to 3 times
> faster.
> We tried turning autoshrink and auclose off but that didn't seem to
> help.
it can depend on the transaction... if you load a big transaction, you will
see a degradation as the transaction size increses... you can perhaps
separate the load into separated transactions..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks for the reply Andrea!
The loads are separated into different transactions. If I use a monitor I
can see that memory use continues to grow with each load so it seems like
there is a memory leak somewhere.
We are planning to try:
DBCC FREEPROCCACHE
&
DBCC DROPCLEANBUFFERS
in between loads with our next build. Do you have any other suggestions?
multo grazie,
Terry
"Andrea Montanari" wrote:

> hi Terry,
> it can depend on the transaction... if you load a big transaction, you will
> see a degradation as the transaction size increses... you can perhaps
> separate the load into separated transactions..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
|||hi Terry,
tgrignon wrote:
> Thanks for the reply Andrea!
> The loads are separated into different transactions. If I use a
> monitor I can see that memory use continues to grow with each load so
> it seems like there is a memory leak somewhere.
not a memory leak (or at least...) ... just the standard SQL Server
behavior... plese see http://tinyurl.com/bvpg9 for some details on SQL
Server memory management...

> We are planning to try:
> DBCC FREEPROCCACHE
> &
> DBCC DROPCLEANBUFFERS
> in between loads with our next build. Do you have any other
> suggestions?
do not know I I like that
those to DBCC commands will actually clear the procedure cache... that
usually includes penalty executing statements due to recompilations of
stored procedures, plans, more I/O activity due to refetching of database
pages...
and more, a nasty security requirements for them... they require high
privileges...
http://msdn.microsoft.com/library/de..._dbcc_7q5l.asp
http://msdn.microsoft.com/library/de..._dbcc_512h.asp
that's to say sysadmins and serveradmins membership...
if I could eventually not speed up operations via separating the job into
multiple transactions, I'd think I'd stay with the time degradation...

> multo grazie,
prego, you are welcome
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I see what you mean about the permissions... but we tried
DBCC FREEPROCCACHE
in between loads and it works perfectly... the separate transaction loads
are back down to a manageable load time. I wonder if a stored procedure is
being left "on" somewhere in the code...
Any other ideas?
Thanks,
Terry
"Andrea Montanari" wrote:

> hi Terry,
> tgrignon wrote:
> not a memory leak (or at least...) ... just the standard SQL Server
> behavior... plese see http://tinyurl.com/bvpg9 for some details on SQL
> Server memory management...
>
> do not know I I like that
> those to DBCC commands will actually clear the procedure cache... that
> usually includes penalty executing statements due to recompilations of
> stored procedures, plans, more I/O activity due to refetching of database
> pages...
> and more, a nasty security requirements for them... they require high
> privileges...
> http://msdn.microsoft.com/library/de..._dbcc_7q5l.asp
> http://msdn.microsoft.com/library/de..._dbcc_512h.asp
> that's to say sysadmins and serveradmins membership...
> if I could eventually not speed up operations via separating the job into
> multiple transactions, I'd think I'd stay with the time degradation...
> prego, you are welcome
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi,
tgrignon wrote:
> I see what you mean about the permissions... but we tried
> DBCC FREEPROCCACHE
> in between loads and it works perfectly... the separate transaction
> loads are back down to a manageable load time. I wonder if a stored
> procedure is being left "on" somewhere in the code...
no idea... you have to control

> Any other ideas?
nope..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply