How to decrease the search time in Sql Server

Question

I have created a windows form that uses SQL Server Database. The windows form contains a search grid which brings all the bank account information of a person. The search grid contains a special field "Number of Account" which displays the number of Accounts a person have associated with a bank.

There are more than 100,000 records in the table from where the data is fetched. I just wanted to know how should I decrease the response time or the search time while getting the data from the table in the search grid.

When I run the page it takes hell lot of time to get the records displayed in the search grid. Moreover, it does not get the data unless and until I provide a search criteria(To and from Date for searching)

Is their any possible way to decrease the search time so that the data should get displayed in the grid.

Answer 1

There are a few things that you can do:

1. Only fetch the minimum amount of data that you need for your results - this means only select the needed columns and limit the number of rows.
2. In addition to the above, consider using paging on the UI, so you can further limit the amount of data returned. There is no point in showing a user 100,000 rows.
3. If you hadn't done so already, add indexes to the table (though at 100,000 rows, things shouldn't be that slow anyway). I can't go into detail about how to do that.