All geek questions in one place

How to split / run a large SQL query?

I have a table with a lot of rows (~ 200 million), and I want to process these values ​​in C # after reading them from memory. Processing requires grouping records by column values ​​in a way that cannot be performed inside the SQL server itself. The problem is that reading all the data at once gives me an OutOfMemory exception and takes a long time to complete even partially.

So, I want to break my request into shorter fragments. One method is to obviously make an independent choice and then use the where in clause. Another method I suggested is using sql cursors. I want to choose one of these methods (or, if possible, another), especially regarding the following points:

  • What will be the influence of the schemes on the server? What will work faster?
  • Is it possible to safely parallelize sql cursor queries? Do I get performance benefits if I parallelize the first circuit (the one with which in the section)?
  • How many objects can be specified in the where in section? Is it limited only by the size of the query string?

Any other suggestions are also welcome.

Edit1: They gave me different solutions, but I still would like to know the answers to my original questions (out of curiosity).

+4
source share
2 answers

If you need to execute grouping logic in code, you can try to write the logic as a managed stored procedure in an SQL server that can be used in a search query.

Departure

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

[Edit - your comments on the use of dictionaries]

You can check out my project on Codeplex , which stores the Dictionary<T,V> disk. This will prevent memory deletion. It would be interesting to see how this works for your scenario. (If you are on a 32-bit system, read the note on the entry page).

+1
source

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

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

It should work on what you are trying to do, and is a better option than the ones you indicated.

+1
source

Source: https://habr.com/ru/post/1316131/

More articles:


All Articles