I'm not sure how to approach this problem. I require a big chunk of data records from the SQL server. This chunk is based on variables, so I don't know before what records I need. I need to do a large series of calculations and each calculation requires one (or more) records from this chunk of data. Again: I do not know which records are required.
A. Load this data into the application memory all at once
- This creates a single connection to the DB, loads ALL required data by a query command (and a forward only DataReader) and then doesn't bother the SQL server anymore.
- The datafetch seems to be slow, since it's reading hundred of thousands of lines into memory
B. Whenever the calculation needs data, retrieve it from the database
- This would open and close a connection to the SQL db multiple times per second.
- The initial datafetch is reduced to only a few miliseconds, but creates a massive load on the SQL server during calculation.