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]

Re: Vb.Net/SQL slowdown

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


"Tom wilson" <yeahright@nosp am.comwrote in message
news:8kuok3hedm jemkc4g3mef1s64 vtcn49e5v@4ax.c om...

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

[cfps.Christian]

Re: Vb.Net/SQL slowdown

On Nov 27, 2:17 pm, 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!

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

[Tom wilson]

Re: Vb.Net/SQL slowdown

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"
<hitchhikersgui deto-news@yahoo.comw rote:

>Is it not possible to simply do a SQL INSERT INTO/SELECT FROM query?
>That would be the fastest way.
>
>
>"Tom wilson" <yeahright@nosp am.comwrote in message
>news:8kuok3hed mjemkc4g3mef1s6 4vtcn49e5v@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!
>>

[=?Utf-8?B?S2VycnkgTW9vcm1hbg==?=]

RE: Vb.Net/SQL slowdown

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


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

[Spam Catcher]

Re: Vb.Net/SQL slowdown

Tom wilson <yeahright@nosp am.comwrote in
news:4i2pk31kai pvkvi58g1eo1mdr rdl5nhvr3@4ax.c om:

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]

Re: Vb.Net/SQL slowdown

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

"Tom wilson" <yeahright@nosp am.comwrote in message
news:8kuok3hedm jemkc4g3mef1s64 vtcn49e5v@4ax.c om...

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

[Cor Ligthert[MVP]]

Re: Vb.Net/SQL slowdown

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