How to breakup/run a large SQL query?

Question

I have a table with large number of rows(~200 million) and I want to process these values in c#, after reading them from memory. Processing requires grouping entries by column values in a way that can't be done inside the sql server itself. Problem is that reading the whole data at once gives me a OutOfMemory exception, and takes a lot of time to execute even partially.

So I want to break my query into shorter pieces. One method is to obviously do an independent select and then use the where in clause. Another method that I have been suggested is to use sql cursors. I want to chose one of these methods(or another one if possible), especially with regards to the following points:

1. What would be the performance impact of the schemes on the server? Which would perform faster?
2. Can I safely parallelize the sql cursor queries? Would I get a performance benefit if I parallelize the first scheme(one with where in clause)?
3. How many objects can I specify in where in clause? Is it only limited by the size of the query string?

Any other suggestions are also welcome.

Edit1: I have been given different solutions, but I would still like to know the answers to my original questions(out of curiousity).

Answer 1

If you have to do the grouping logic in code, you can try to write the logic as a Managed Stored Procedure in sql server which can be used in the groping query.

Check out

• How to: Create and Run a SQL Server Stored Procedure by using Common Language Run-time Integration
• How to: Create and Run a SQL Server User-Defined Function by using Common Language Run-time Integration

This will allow you to group on the server before returning the dataset to your client.

[Edit - regarding your comments on using Dictionaries]

You can check out my project on Codeplex which has a disk persisting Dictionary<T,V>. This would prevent the out of memory exception. Would be interesting to see how it performs for your scenario. (If you are on a 32bit system, read the note on the intro page).

Answer 2

If you are using sql 2005 or higher you should check out sql based paging.

http://blogs.x2line.com/al/archive/2005/11/18/1323.aspx

It should work for what you are trying to do and is a better option than the two you listed.