0

I am learning C# and trying to figure out more about collections. I hired a tutor a few days ago and he was telling me the virtues of Linq for iterating through large data results, and so I was planning on using that, but now am wondering if I understood incorrectly.

I am querying Mysql and will have up to 200,000 results which will need to be formatted and posted in groups of 500 to an HTTP server as JSON. Should I be making a single query and then using Linq to paginate the results, or should I be breaking the SELECT up into groups of 500?

My understanding is that the data flow is:

Mysql
   => MysqlDataAdapter
      => Fill DataSet
         => Use Linq to paginate

Or 

Mysql
   => MysqlDataAdapter (Limit 500)
      => Fill DataSet

Just trying to figure out which way makes more sense from a performance perspective. It will be querying localhost.

Alan
  • 2,046
  • 2
  • 20
  • 43
  • 1
    If you want 500 results then I wouldn't request all 200,000+ (and likely increasing) from the database. Your database query should allow you to select a page number and page size. – Scott Hannen Jan 12 '18 at 16:50
  • *performance perspective* is a broad term (think CPU/Memory/IO among other things) and without a goal (I need to be able to run this 1000 times in a second) answers can at best be an opinion that still are unlikely to fit your case. – rene Jan 12 '18 at 16:59
  • Thanks for the comment Rene, you are right that I didn't mention how / when it would run. The first time it runs it will process all 200,000 results in groups of 500. After that it will do only changed items every 5 minutes. Trying to see how this could be incorporated into my generic query class: https://stackoverflow.com/a/6185236/3025534 – Alan Jan 12 '18 at 17:09
  • 1
    Another option is to use the ability of the Provider objects to load a number of rows at a time. Given what is being done with them its probably not as useful since you'd have to maintain some sort of offset (maybe a RowFilter would mitigate this) . But `DataAdapter.Fill(int First, int Count, DataTable[])` allows us to load data in "pages" of a given size – Ňɏssa Pøngjǣrdenlarp Jan 12 '18 at 18:04

1 Answers1

0

Here you can find some comparisons that can help you datareader-vs-dataset-vs-linq

Raphael
  • 11
  • 1
  • 5