I am running a VB.net console (batch) application that performs 50,000 sets
of reads, inserts, and updates to numerous SQL Server 2000 database tables.
Each individual set is mutually exclusive from the previous or subsequent
sets. I am monitoring the average processing time per set. When I start
the program, the average processing time is very small. However, the
average processing time slowly grows by about 67% by the end of the 50,000
sets. If I start another group of 50,000 right away, the processing time at
the beginning is again very fast. But, again, the performance degrades as
the program runs.
I've added more RAM to the server, increased the minimum buffer pool size,
and removed all of the constraints from the tables being inserted or
updated. None of these have eliminated nor reduced the degradation. I've
generated traces using Profiler, but have not been able to identify a stored
procedure that takes longer and longer to execute as the program progresses.
Can anyone suggest any other places to investigate?
Thanks,
Dan
Are you updating all 50K records as a single transaction? If so, you could be slowing down as the transaction log grows. Try breaking the updates into smaller logical batches (say 1000 inserts/updates at a time) and issue a commit. This may solve your
problem....
Brad Feaker
Database Administrator
2012年2月13日星期一
Batch performance degradation - SQL Server 2000
I am running a VB.net console (batch) application that performs 50,000 sets
of reads, inserts, and updates to numerous SQL Server 2000 database tables.
Each individual set is mutually exclusive from the previous or subsequent
sets. I am monitoring the average processing time per set. When I start
the program, the average processing time is very small. However, the
average processing time slowly grows by about 67% by the end of the 50,000
sets. If I start another group of 50,000 right away, the processing time at
the beginning is again very fast. But, again, the performance degrades as
the program runs.
I've added more RAM to the server, increased the minimum buffer pool size,
and removed all of the constraints from the tables being inserted or
updated. None of these have eliminated nor reduced the degradation. I've
generated traces using Profiler, but have not been able to identify a stored
procedure that takes longer and longer to execute as the program progresses.
Can anyone suggest any other places to investigate?
Thanks,
DanAre you updating all 50K records as a single transaction? If so, you could
be slowing down as the transaction log grows. Try breaking the updates into
smaller logical batches (say 1000 inserts/updates at a time) and issue a co
mmit. This may solve your
problem....
Brad Feaker
Database Administrator
of reads, inserts, and updates to numerous SQL Server 2000 database tables.
Each individual set is mutually exclusive from the previous or subsequent
sets. I am monitoring the average processing time per set. When I start
the program, the average processing time is very small. However, the
average processing time slowly grows by about 67% by the end of the 50,000
sets. If I start another group of 50,000 right away, the processing time at
the beginning is again very fast. But, again, the performance degrades as
the program runs.
I've added more RAM to the server, increased the minimum buffer pool size,
and removed all of the constraints from the tables being inserted or
updated. None of these have eliminated nor reduced the degradation. I've
generated traces using Profiler, but have not been able to identify a stored
procedure that takes longer and longer to execute as the program progresses.
Can anyone suggest any other places to investigate?
Thanks,
DanAre you updating all 50K records as a single transaction? If so, you could
be slowing down as the transaction log grows. Try breaking the updates into
smaller logical batches (say 1000 inserts/updates at a time) and issue a co
mmit. This may solve your
problem....
Brad Feaker
Database Administrator
Batch performance degradation - SQL Server 2000
I am running a VB.net console (batch) application that performs 50,000 sets
of reads, inserts, and updates to numerous SQL Server 2000 database tables.
Each individual set is mutually exclusive from the previous or subsequent
sets. I am monitoring the average processing time per set. When I start
the program, the average processing time is very small. However, the
average processing time slowly grows by about 67% by the end of the 50,000
sets. If I start another group of 50,000 right away, the processing time at
the beginning is again very fast. But, again, the performance degrades as
the program runs.
I've added more RAM to the server, increased the minimum buffer pool size,
and removed all of the constraints from the tables being inserted or
updated. None of these have eliminated nor reduced the degradation. I've
generated traces using Profiler, but have not been able to identify a stored
procedure that takes longer and longer to execute as the program progresses.
Can anyone suggest any other places to investigate?
Thanks,
DanAre you updating all 50K records as a single transaction? If so, you could be slowing down as the transaction log grows. Try breaking the updates into smaller logical batches (say 1000 inserts/updates at a time) and issue a commit. This may solve your problem...
Brad Feake
Database Administrator
of reads, inserts, and updates to numerous SQL Server 2000 database tables.
Each individual set is mutually exclusive from the previous or subsequent
sets. I am monitoring the average processing time per set. When I start
the program, the average processing time is very small. However, the
average processing time slowly grows by about 67% by the end of the 50,000
sets. If I start another group of 50,000 right away, the processing time at
the beginning is again very fast. But, again, the performance degrades as
the program runs.
I've added more RAM to the server, increased the minimum buffer pool size,
and removed all of the constraints from the tables being inserted or
updated. None of these have eliminated nor reduced the degradation. I've
generated traces using Profiler, but have not been able to identify a stored
procedure that takes longer and longer to execute as the program progresses.
Can anyone suggest any other places to investigate?
Thanks,
DanAre you updating all 50K records as a single transaction? If so, you could be slowing down as the transaction log grows. Try breaking the updates into smaller logical batches (say 1000 inserts/updates at a time) and issue a commit. This may solve your problem...
Brad Feake
Database Administrator
Batch Insert in SQL Server 2000 Database
I have a process that inserts data into a SQL Server 2000 database using a
VB script. Before inserting data into the database there are numerous checks
from data in the database that need to checked to make sure the data is
correct before inserting into the database. This batch process is very slow
inserting data into the database. Since the data is extracted from several
large table to checked.
Are there 3rd party utilities that could help me with this process or better
way to complete this process?
Thank You,Consider using staging tables, whereby you load these tables without the
constraints and then run your validation queries inside the DB. Insert only
the valid data.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:412A4856-B609-42F3-AAB0-F7EBE7F4B56E@.microsoft.com...
> I have a process that inserts data into a SQL Server 2000 database using a
> VB script. Before inserting data into the database there are numerous
> checks
> from data in the database that need to checked to make sure the data is
> correct before inserting into the database. This batch process is very
> slow
> inserting data into the database. Since the data is extracted from several
> large table to checked.
> Are there 3rd party utilities that could help me with this process or
> better
> way to complete this process?
> Thank You,
VB script. Before inserting data into the database there are numerous checks
from data in the database that need to checked to make sure the data is
correct before inserting into the database. This batch process is very slow
inserting data into the database. Since the data is extracted from several
large table to checked.
Are there 3rd party utilities that could help me with this process or better
way to complete this process?
Thank You,Consider using staging tables, whereby you load these tables without the
constraints and then run your validation queries inside the DB. Insert only
the valid data.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:412A4856-B609-42F3-AAB0-F7EBE7F4B56E@.microsoft.com...
> I have a process that inserts data into a SQL Server 2000 database using a
> VB script. Before inserting data into the database there are numerous
> checks
> from data in the database that need to checked to make sure the data is
> correct before inserting into the database. This batch process is very
> slow
> inserting data into the database. Since the data is extracted from several
> large table to checked.
> Are there 3rd party utilities that could help me with this process or
> better
> way to complete this process?
> Thank You,
Batch Insert in SQL Server 2000 Database
I have a process that inserts data into a SQL Server 2000 database using a
VB script. Before inserting data into the database there are numerous check
s
from data in the database that need to checked to make sure the data is
correct before inserting into the database. This batch process is very slow
inserting data into the database. Since the data is extracted from several
large table to checked.
Are there 3rd party utilities that could help me with this process or better
way to complete this process?
Thank You,Consider using staging tables, whereby you load these tables without the
constraints and then run your validation queries inside the DB. Insert only
the valid data.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:412A4856-B609-42F3-AAB0-F7EBE7F4B56E@.microsoft.com...
> I have a process that inserts data into a SQL Server 2000 database using a
> VB script. Before inserting data into the database there are numerous
> checks
> from data in the database that need to checked to make sure the data is
> correct before inserting into the database. This batch process is very
> slow
> inserting data into the database. Since the data is extracted from several
> large table to checked.
> Are there 3rd party utilities that could help me with this process or
> better
> way to complete this process?
> Thank You,
VB script. Before inserting data into the database there are numerous check
s
from data in the database that need to checked to make sure the data is
correct before inserting into the database. This batch process is very slow
inserting data into the database. Since the data is extracted from several
large table to checked.
Are there 3rd party utilities that could help me with this process or better
way to complete this process?
Thank You,Consider using staging tables, whereby you load these tables without the
constraints and then run your validation queries inside the DB. Insert only
the valid data.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:412A4856-B609-42F3-AAB0-F7EBE7F4B56E@.microsoft.com...
> I have a process that inserts data into a SQL Server 2000 database using a
> VB script. Before inserting data into the database there are numerous
> checks
> from data in the database that need to checked to make sure the data is
> correct before inserting into the database. This batch process is very
> slow
> inserting data into the database. Since the data is extracted from several
> large table to checked.
> Are there 3rd party utilities that could help me with this process or
> better
> way to complete this process?
> Thank You,
Batch Insert in SQL Server 2000 Database
I have a process that inserts data into a SQL Server 2000 database using a
VB script. Before inserting data into the database there are numerous checks
from data in the database that need to checked to make sure the data is
correct before inserting into the database. This batch process is very slow
inserting data into the database. Since the data is extracted from several
large table to checked.
Are there 3rd party utilities that could help me with this process or better
way to complete this process?
Thank You,
Consider using staging tables, whereby you load these tables without the
constraints and then run your validation queries inside the DB. Insert only
the valid data.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:412A4856-B609-42F3-AAB0-F7EBE7F4B56E@.microsoft.com...
> I have a process that inserts data into a SQL Server 2000 database using a
> VB script. Before inserting data into the database there are numerous
> checks
> from data in the database that need to checked to make sure the data is
> correct before inserting into the database. This batch process is very
> slow
> inserting data into the database. Since the data is extracted from several
> large table to checked.
> Are there 3rd party utilities that could help me with this process or
> better
> way to complete this process?
> Thank You,
VB script. Before inserting data into the database there are numerous checks
from data in the database that need to checked to make sure the data is
correct before inserting into the database. This batch process is very slow
inserting data into the database. Since the data is extracted from several
large table to checked.
Are there 3rd party utilities that could help me with this process or better
way to complete this process?
Thank You,
Consider using staging tables, whereby you load these tables without the
constraints and then run your validation queries inside the DB. Insert only
the valid data.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:412A4856-B609-42F3-AAB0-F7EBE7F4B56E@.microsoft.com...
> I have a process that inserts data into a SQL Server 2000 database using a
> VB script. Before inserting data into the database there are numerous
> checks
> from data in the database that need to checked to make sure the data is
> correct before inserting into the database. This batch process is very
> slow
> inserting data into the database. Since the data is extracted from several
> large table to checked.
> Are there 3rd party utilities that could help me with this process or
> better
> way to complete this process?
> Thank You,
订阅：
博文 (Atom)