how to retrieve bulk data from sql without slowing down the server/program

Question

hi

i have a program wherein it will retrieve data from sql based on a specific date range.. the problem is when the date range is set to a year or greater than that then the loading of data is so slow that sometimes the program will be not responding. Is there a way avoid this?

Answer 1

You can load the data in a background thread using the BackgroundWorker component.

It will still take time, but the program won't be frozen.

Alternatively, you can modify your program to load less data.
For example, you can move the logic that uses the data to a sproc on the server.

Answer 2

there are 2 parts of question

Do you want to make your UI responsive?

or

Do you want to make loading of data faster?

if 1st is the case than use BackgroundWorker Class toload your data on secondary thread and if 2 case is there than use sampling of data and load data in chunks

Answer 3

Another option is to prevent the user selecting a date range that is so large.

This may appear restrictive but usually when the user is presented with 10,000 separate records, they realise they need to make their query more specific. The time taken to retrieve the large data set is just a waste of server, network and the user's time.