A vb.net server application

Have you considered an SQL store procedure and a SQL recurrent job to do the dirty work?

I mean, use SQL server to find what is your looking for,
leave this on a summary table and use your app to get only the results.

if it remains too slow, may be you need a second server with replicate data, if you slow it, is less critical than the production one.

Saludos

Hi Oshri,

Retrieving a million rows using a SQL Server stored procedure is one problem in itself; also, what is it you want to do with the records ?

Are the server application and the database going to be located on the same physical machine ? If so then you are placing a heavy load on this machine during the retrieval AND processing of the data in .NET. If not then you will have to consider network overheads - transporting a million rows from the database server to the .NET application server could take some time, depending on your network performance.

I would usually attempt to solve a problem like this using as much of the DB engine's power as possible; that means using stored procedures. If you're doing some sort of calculation that can be handled in T-SQL (and possibly with cursors) then do this at stored procedure level; if not, do it in .NET. Obvisouly the former is going to load the database server much more than the latter, if they're separate machines.

Please let me know what it is you're intending to do inside this "loop" - then I can help more.

Gareth