Spyros Spyros - 1 month ago 12
SQL Question

SQL Perfomance: Retrieve Many Data Once VS Retrieve Less Data Many Times

I am new in SQL so forgive me if my question seems childish.

Just a brief description of my application:


  1. I have a two table SQL database with table names: GUIelements, DataTable.

  2. The GUIelements table is a very small 50 line table

  3. The DataTable is a very big table with like 300000 entries

  4. I also have have a C# application in which I have a Dynamically generated GUI based on the data available in GUIelements.

  5. The C# application has a function which runs in a separate thread from the GUI, and the purpose of this function is to continuously (every 1 sec) monitor the GUIelements table to see if there is a change in the table. I do this by each time opening a connection, querying and closing the connection.



Everything works fine, the GUI updates as it should and I can also make queries in the Database from the main thread of the GUI. Everything seems perfect.

==============================

Now I would like to create another function which will be running again in another thread which will run every 10 minutes.
The purpose of this function will be to retrieve all the entries of the DataTable and once it retrieve them to a local list then do some low level processing like take the avg of the data.

My Questions is:

Is it better to retrieve from the sql database the whole DataTable once, store it in a temp list and do the processing:

USING()
{
- OpenConnection
- Query & Store data in a big 300000 elements list
- Close Connection
- Process list
}


Or it will be better to retrieve the data from the DataTable in packets, process each packet and then request for the next packet. The operation will look like this:

FOR LOOP 3 TIMES
USING()
{
- OpenConnection
- Query & Store data in a small 100000 elements list
- Close Connection
- Process list
}
END


Which approach will be better?

Is there any danger that some queries will fail because of multi-threading?

Please keep in mind the table and list sizes are not the actual, but I use these numbers as example.

Answer

If you have memory to store the whole data, then is better do it in one connection. To avoid the multiple network roundtrip. So the solution depend on your real size numbers.

But if you need the AVG, do it on the db not in your app.