Vb.Net/SQL slowdown

[Tom wilson]

Hi!

I have a massive SQL 2000 database that needs records extracted into
another database. so I write a VB.Net (2005) program that simply
queries the records I need, populates a new record in the new database
and so on. There's no data transformation, it's a direct record to
record copy.

So I run this thing and numbers are flying by at a rate of about 30
records per second. Within an hour, it gradually slows down to one
record every 30 seconds and there's hundreds of thousands of records
left.

Why is it doing this? It starts off great and slows down more and
more as time goes on. There's nothing complicated about it. I had
this problem under VB6 as well.

Any ideas because this is completely non-functional.

Thanks!

 

[Arthur Dent]

Is it not possible to simply do a SQL INSERT INTO/SELECT FROM query?
That would be the fastest way.

 

[cfps.Christian]

Hi!

I have a massive SQL 2000 database that needs records extracted into
another database. so I write a VB.Net (2005) program that simply
queries the records I need, populates a new record in the new database
and so on. There's no data transformation, it's a direct record to
record copy.

So I run this thing and numbers are flying by at a rate of about 30
records per second. Within an hour, it gradually slows down to one
record every 30 seconds and there's hundreds of thousands of records
left.

Why is it doing this? It starts off great and slows down more and
more as time goes on. There's nothing complicated about it. I had
this problem under VB6 as well.

Any ideas because this is completely non-functional.

Thanks!

You could always just write a SQL DTS service. Much faster.

 

[Tom wilson]

Perhaps but this application needs to do this on its own. The
database gets so large that certain data needs to be extracted by the
user via an automated selection process. But the thing is, VB.Net
seems entirely incapable of reading and writing a large amount of
records. The language is a dead end it seems. I don't understand why
it won't read/write database records without dragging into a complete
stall.

Thanks for the reply.

 

[Kerry Moorman]

Tom,

Does the table being written to have a bunch of indexes that need to be
constantly updated by the DBMS as you add rows?

Or a bunch of constraints that must be checked as rows are added?

Kerry Moorman

 

[Guest]

Perhaps but this application needs to do this on its own. The
database gets so large that certain data needs to be extracted by the
user via an automated selection process. But the thing is, VB.Net
seems entirely incapable of reading and writing a large amount of
records. The language is a dead end it seems. I don't understand why
it won't read/write database records without dragging into a complete
stall.

What about SSIS or DTS?

Do you have samples of the queries your running? Maybe your transaction log
is blowing up... or you're leaking memory?

 

[Trevor Benedict]

Are u using a Transaction?

Have you thought about using a linked server and executing the insert via
the backend instead of having to pulling the data to the client and then
sending it back to another server. Typically servers are placed closer to
each other w/ a faster network. Moving the load to the server would be the
best option. If you would be interested in trying the linked server
approach, reply to the group.

30 records/second is poor performance.

Regards,

Trevor Benedict
MCSD

 

[Cor Ligthert[MVP]]

Hi Tom,

Do you let the user type them in or do you have any kind of code to do this
in an automatic way. In the first case maybe do the users become tired.

Just kidding, however your question is like this answer in my opinion.

While everybody gives you good advices, are the giving solutions where there
are probably hundred other ones and is it just gambling.

I guess that you use some code to do this (should be less than 20 lines)
therefore show that part to us, then we can maybe real help you.

Cor