Slow performance in SQL2005

[Tawfiq]

Hi,

We have shifted one of our data processing to a new instance of SQL
2005. For this a new DB was created in SQL2005 and it is not an
upgrade of SQL200 DB.

The data processing application is a VB6 application that runs a batch
process to insert about
4.5 million records everyday in this SQL 2005 database. I am using
ADO
(ver 2.6) Connection object to execute T-SQL insert statements to
insert the 4.5 mill. records.

This same application used to insert the data in the SQL 2000
database
and used to take about 2.5 hours to do so. After moving it to SQL
2005 the performance has really detriorated and it now takes more
than
6 hours.

Is there any configuration change that I need to do in SQL 2005?
Please help.

Note: There is also an instance of SQL 2000 running in the same
server.

TIA,
Tawfiq

[Tom van Stiphout]

Re: Slow performance in SQL2005

On Mon, 18 Feb 2008 19:14:11 -0800 (PST), Tawfiq
<tawfiq.choudhu ry@gmail.comwro te:

I get the impression you have VB6 code that does the equivalent of:
for i = 1 to 4.5 million
insert one record
next

Isn't there a better way?

A quick-and-dirty fix may be to continue to insert these records in
your SQL2000 instance, and then bulk-insert them into the new db. But
more than likely you can use SSIS to more efficiently import your
data.

-Tom.

>Hi,
>
>We have shifted one of our data processing to a new instance of SQL
>2005. For this a new DB was created in SQL2005 and it is not an
>upgrade of SQL200 DB.
>
>The data processing application is a VB6 application that runs a batch
>process to insert about
>4.5 million records everyday in this SQL 2005 database. I am using
>ADO
>(ver 2.6) Connection object to execute T-SQL insert statements to
>insert the 4.5 mill. records.
>
>This same application used to insert the data in the SQL 2000
>database
>and used to take about 2.5 hours to do so. After moving it to SQL
>2005 the performance has really detriorated and it now takes more
>than
>6 hours.
>
>Is there any configuration change that I need to do in SQL 2005?
>Please help.
>
>Note: There is also an instance of SQL 2000 running in the same
>server.
>
>TIA,
>Tawfiq

[Tawfiq]

Re: Slow performance in SQL2005

The application reads data from binary files and then inserts it into
the db.

I can improve perfomance by first writing the data first into text
files and then doing bulk insert. But why is there such a big
differnece in performce between SQL2000 vs SQL2005?

I guess some kind of configuration change is needed or maybe even I
have to rewrite the code in ADO.NET.

Anyone got any clue for such a drastic reduction in performance?

On Feb 19, 10:31 am, Tom van Stiphout <no.spam.tom7.. .@cox.netwrote:

On Mon, 18 Feb 2008 19:14:11 -0800 (PST), Tawfiq
>
<tawfiq.choudh. ..@gmail.comwro te:
>
I get the impression you have VB6 code that does the equivalent of:
for i = 1 to 4.5 million
  insert one record
next
>
Isn't there a better way?
>
A quick-and-dirty fix may be to continue to insert these records in
your SQL2000 instance, and then bulk-insert them into the new db. But
more than likely you can use SSIS to more efficiently import your
data.
>
-Tom.
>
>
>

Hi,

>

We have shifted one of our data processing to a new instance of SQL
2005. For this a new DB was created in SQL2005 and it is not an
upgrade of SQL200 DB.

>

The data processing application is a VB6 application that runs a batch
process to insert about
4.5 million records everyday in this SQL 2005 database. I am using
ADO
(ver 2.6) Connection object to execute T-SQL insert statements to
insert the 4.5 mill. records.

>

This same application used to insert the data in the SQL 2000
database
and used to take about 2.5 hours to do so. After moving it to SQL
2005 the performance has really detriorated and it now takes more
than
6 hours.

>

Is there any configuration change that I need to do in SQL 2005?
Please help.

>

Note: There is also an instance of SQL 2000 running in the same
server.

>

TIA,
Tawfiq- Hide quoted text -

>
- Show quoted text -

[Erland Sommarskog]

Re: Slow performance in SQL2005

Tawfiq (tawfiq.choudhu ry@gmail.com) writes:

The application reads data from binary files and then inserts it into
the db.
>
I can improve perfomance by first writing the data first into text
files and then doing bulk insert. But why is there such a big
differnece in performce between SQL2000 vs SQL2005?

Without further knowledge of your situation, it's hard to tell. But
if your code looks like Tom suspected, you loop and insert one row
at a time, it could be as simple as a network issue.

If there triggers on the table, they could have a role in it as well.

In any case, 2½ hours to load 4½ rows is overly long in my opinion.
Using bulk load seems like an excellent idea. Just make sure that
you run the bulk load with constraints in force.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx