I am working on a business report winform app. It needs a ton of data from our Microsoft SQL Server, which it then processes and transforms into various output tables.

When I pull the data table using an SP, it is very big, about 300,000 rows with a big number of columns, but comes from the DB quickly enough. The rows can be processed one at a time. The client PC that is doing all the data crunching has about 512MB of RAM. The client PC hard drive is spinning constantly.

When I test a small dataset, the data is processed quickly, but with larger result sets, the processing time increases disproportionately because .NET seems to handle a large table inefficiently, even if it is iterating through its rows, constantly swapping between RAM and hard disk.

These are the options that I am considering. Any additional ideas or criticisms?

1. Save changes to the dataset intermittently, rather than after making them all, to keep the update statement small.

2. Rewrite the stored procedure to only return the "TOP @batchSize" rows, reapplying all the selection criteria and table joins every time to keep the result set small.

3. Run the original stored procedure once, put the results in a temp table on the database, and write a second stored procedure that consumes batches from the temp table to keep database CPU usage down.

4. Pull all the data onto the client, write it to an XML file, then consume the records from a stream, but I think this will really burn the client CPU.

How efficiently does .NET serialize and deserialize XML?

I appreciate any suggestions that anyone has (short of testing them all and reporting my results).