0

I need to read about 200k database rows from an Oracle database to be processed into an in-memory data-structure. I don't want to do one gigantic select - does .NET provide built-in functionality for 'paging' such a large result set or do I need to figure out my own approach?

I don't know what this technique is called in order to search for specific topics so my Googling is throwing up very little, and I'm not sure if this is really a .NET or a SQL question.

I had considered I could use a modulus on my id field e.g pseudo:

for(i = 0 to 50)
  select * from table where mod(id,50) = i

But I imagine this is perhaps a solved problem on the API (or SQL) side. The data being read is static so performing multiple queries is safe if that's the better option.

Mr. Boy
  • 57,008
  • 88
  • 294
  • 540
  • 2
    Are you using some form of ORM? If so then there's probably an almost trivially easy way to do it. If not, then 'pagination' is the correct term, check out [this](https://stackoverflow.com/q/241622/9363973) excellent Q&A for implementing paging directly in the SQL – MindSwipe Apr 12 '22 at 12:29
  • 1
    Does this answer your question? [How to skip/offset rows in Oracle database?](https://stackoverflow.com/questions/40997680/how-to-skip-offset-rows-in-oracle-database) – Crowcoder Apr 12 '22 at 12:30
  • 200k rows is not necessarily too many; I’d at least try just reading them in one go. – sellotape Apr 12 '22 at 12:46
  • @MindSwipe no ORM, bare SQL. I'm not an Oracle guy, so I forgot it had `rownum` (I mainly use MSSQL). I see you link mentioned has both options so is probably what I was looking for. – Mr. Boy Apr 12 '22 at 12:47
  • @sellotape fair point but it was 20k rows a few years back and could easily grow further. – Mr. Boy Apr 12 '22 at 12:48
  • What is the problem that you are trying to solve here? 200,000 rows at, say, 200 bytes per row is ~38 MB of data across the network which shouldn't be a huge issue. Are you trying to split up the data so you can have multiple threads consuming it simultaneously? Just running 50 selects in a loop that each fetch a few thousand rows is going to be less efficient than running a single select that fetches a couple hundred thousand rows. – Justin Cave Apr 12 '22 at 13:54
  • @JustinCave well a complex select might be way over 200 bytes per row if it is joining several tables. And what when my DB grows to 1mil rows. We're going off-topic though. – Mr. Boy Apr 12 '22 at 17:15
  • I disagree that it is off-topic. The bigger and hairier the SQL statement, the more expensive it will be to break it up to run 50 statements in a loop. You're forcing the database to do a lot of extra work for no gain. If your actual intention is to break the query up so that you can run smaller statements in separate threads, that's at least a plausible way to improve the process. Assuming that the .NET code loading the data is the bottleneck. But it may be faster and easier to just let the database run the actual query in parallel within the database. – Justin Cave Apr 12 '22 at 17:22

0 Answers0