Vb.Net/SQL slowdown

27 Nov 2007 8:17 PM

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!

27 Nov 2007 8:58 PM

Arthur Dent

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

Show quoteHide quote

"Tom wilson" <yeahright@nospam.com> wrote in message
news:8kuok3hedmjemkc4g3mef1s64vtcn49e5v@4ax.com...
> 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!
>

27 Nov 2007 9:25 PM

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.

On Tue, 27 Nov 2007 15:58:29 -0500, "Arthur Dent"
<hitchhikersguideto-n***@yahoo.com> wrote:

Show quoteHide quote

>Is it not possible to simply do a SQL INSERT INTO/SELECT FROM query?
>That would be the fastest way.
>
>
>"Tom wilson" <yeahright@nospam.com> wrote in message
>news:8kuok3hedmjemkc4g3mef1s64vtcn49e5v@4ax.com...
>> 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!
>>

27 Nov 2007 10:39 PM

Spam Catcher

Tom wilson <yeahright@nospam.com> wrote in

news:4i2pk31kaipvkvi58g1eo1mdrrdl5nhvr3@4ax.com:

> 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?

27 Nov 2007 9:10 PM

cfps.Christian

On Nov 27, 2:17 pm, Tom wilson wrote:
Show quoteHide quote

> 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.

27 Nov 2007 9:42 PM

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

Show quoteHide quote

"Tom wilson" wrote:

> 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!
>
>

27 Nov 2007 11:58 PM

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

Show quoteHide quote

"Tom wilson" <yeahright@nospam.com> wrote in message
news:8kuok3hedmjemkc4g3mef1s64vtcn49e5v@4ax.com...
> 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!
>

28 Nov 2007 5:43 AM

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