VB.NET - Fast Databasing Problem

[cyberdaemon]

Hi all, ok I have this problem where I need to process 50,000+ records from three different tables on two different servers thus 150,000+ records. Now I have got this program to work, however, it runs two slow. I do the following:

Servers:
ServerA, Server B

Databases
ServerA.DB1, ServerB,DB2

Tables
DB1.TBL1, DB2.TBL1, DB2.TBL2

1) Get Row from DB1.TBL1, called CurRecord
2) Create a string from 4 records from said row, called RowID
3) find RowID in DB2.TBL1
4) Once Found get IDNumber from DB2.TBL1
5) Find IDNumber in DB2.TBL2
6) Once found get PageNumber
7) Comapre DB2.TBL2.PageNumber to DB1.TBL1.PageNumber, depending on result (i.e. = or <>) change a field in DB1.TBL1.CurRecord
Repeat until all records in DB1.TBL1 have been processed

Now I have tried, Datasets, Hashtables, only SQL, datareaders, and any combination of them to try and achieve the fastest processing time. Everyone on the MSDN forums keep telling me to use SQLBulkCopy, but that is not my issue, it is processing the information. Oh I have also tried placing a Primary Key on the tables, as well as threading. My fastest time is 1 hour, I need to get it as close to 10 minutes as possible. I am running out of ideas, hence my posting this thread. Thus my question, if you have any ideas on what I can do in VB.Net to increase processing speed of such a problem i would be most greatful. I am looking for ideas for me to research/ try You don't have to give and I am not asking for a complete answer just a nudge in the right direction is all. Thank you for you help in advance!

[Frinavale]

Hi all, ok I have this problem where I need to process 50,000+ records from three different tables on two different servers thus 150,000+ records. Now I have got this program to work, however, it runs two slow. I do the following:
...

Hi Cyberdaemon,

You have probably tried this already but...I'll ask anyways.
Have you thought of returning a fewer number of records to your application and just grabbing the next "batch" as they are requested instead of doing a huge return?

[Lokean]

Hi all, ok I have this problem where I need to process 50,000+ records from three different tables on two different servers thus 150,000+ records. Now I have got this program to work, however, it runs two slow. I do the following:

Servers:
ServerA, Server B

Databases
ServerA.DB1, ServerB,DB2

Tables
DB1.TBL1, DB2.TBL1, DB2.TBL2

1) Get Row from DB1.TBL1, called CurRecord
2) Create a string from 4 records from said row, called RowID
3) find RowID in DB2.TBL1
4) Once Found get IDNumber from DB2.TBL1
5) Find IDNumber in DB2.TBL2
6) Once found get PageNumber
7) Comapre DB2.TBL2.PageNumber to DB1.TBL1.PageNumber, depending on result (i.e. = or <>) change a field in DB1.TBL1.CurRecord
Repeat until all records in DB1.TBL1 have been processed

Now I have tried, Datasets, Hashtables, only SQL, datareaders, and any combination of them to try and achieve the fastest processing time. Everyone on the MSDN forums keep telling me to use SQLBulkCopy, but that is not my issue, it is processing the information. Oh I have also tried placing a Primary Key on the tables, as well as threading. My fastest time is 1 hour, I need to get it as close to 10 minutes as possible. I am running out of ideas, hence my posting this thread. Thus my question, if you have any ideas on what I can do in VB.Net to increase processing speed of such a problem i would be most greatful. I am looking for ideas for me to research/ try You don't have to give and I am not asking for a complete answer just a nudge in the right direction is all. Thank you for you help in advance!

Have you tried CLR integration?

[cyberdaemon]

First off thank you for your replies, they are much appriciated!

In Regards to Frinavale:

During my research I found that the datareader was the best in performance, as reading the data goes. And I was under the impression that the datareader.read() method already read in batches, I will look further into this. Maybe I was mistaken and have to explicitly do the read in batches.

In Regards to Lokean:

I am not familiar with CLR integration, I will look into this.

[cyberdaemon]

I did some research and found I was wrong about the .read method of the datareader. so how would I go about reading in batches? My idea would be to create a datatable and then read like 500 records into it, process those records then read the next 500.

Is this method of batching efficient? or is there a better way?

Would it be better to do my prev idea but read on one thread, process on another? I have tried threading before but it made my program slower, it was however my first attempt at threading so I might have messed it up.

In regards to the CLR Integreation, from what I read it must be done on the server. I do not have that ability, this program will most likely be running on a client PC, and if it was on a server not the server with the databases.

Thank you for your help, if any one has any other ideas for me to look at or answers to my questions it would be most helpful.