What is the fastest way to retrieve data from SQL Server?

Question

_{Tuesday, April 24, 2007 5:56 PM}

Hi all,

New to this data access and storage forum. Just want to know what is the fastest way to retrieve a large amount of recordsets (2000 to 5000 rows) from MS SQL 2005 server to a windows.forms.treeview control. This is a intranet enviroment and I am using vs2005/c#.

Thanks.

All replies (5)

_{Tuesday, April 24, 2007 8:23 PM ✅Answered}

I just wanted to concur with the above.  We push most of our queries down to the database level in the form of stored procedures, but we are able to quickly retrieve extraordinarily large datasets with simple select statements contained within those stored procs.

I would say for 5000 records you're not looking at much overhead at all.  That's a relatively small database by most standards.  If you would like to optimize things further for potential future growth, consider how you are indexing the database.  Make sure you have indexes on columns which you believe will be frequently searched.  Just keep in mind that while ubiquitous indexing speeds up searching, it has an inverse effect on updating and inserting (since all of the indexes also need to be maintained during those statements).

If you're running an OLTP (Online Transaction Processing) database that *also* has a requirement for frequent searching, consider implementing a second database that is heavily indexed for searching (we call this our "archive" database).  You can transactionally replicate data from the OLTP database to your Archive database, but point all search functionality towards your Archive (which is heavily indexed).  This gives you the best of both worlds, since you can achieve speedy updates/inserts on your OLTP database while also achieving speedy searches against your Archive database.  The only real downside to this is the latency with which records are transactionally replicated from your OLTP database to your Archive.  But in practice this is negligible.  Our latency is measured in miliseconds, to put it in perspective.

Additionally, make sure that you aren't doing something like "SELECT * FROM MyTable, ORDER BY MySortingColumn".

Never "SELECT *" if you can avoid it.  Specify the individual fields that you know you need instead.  This will increase your performance.  Additionally, my organization tries to never sort datasets on the database side of the house.  We usually try to do that in managed code where possible (due to the OLTP nature of our DB).

_{Friday, April 27, 2007 7:12 PM ✅Answered}

DataReader will be the fastest for building the tree view. DataSet and DataAdapter will be more useful if you intend to use your GUI to make updates to the database. For example, you can fill a DataSet and then bind it to the treeview. For 5000 records you will probably notice a slight delay but it shouldn't be too bad.

_{Tuesday, April 24, 2007 6:48 PM}

An ordinary SELECT statment is quite fast--you can put it in a stored procedure if you need a bit more speed, but otherwise you shouldn't have much work on the database side.  Your challenge will be building the treeview quickly...

-Ryan / Kardax

_{Tuesday, April 24, 2007 9:36 PM}

Thanks Ryan and MujDBA, I think this is the best answer from server side.

Now about the client side, what should I use between datareader, dataset and dataadapter? Or something else which will give me the best performance to build the treeview.

This will be forward only and readonly. No new row or update will be written back into database. (Why? Maybe I should think this twice...) 

_{Tuesday, April 24, 2007 10:18 PM}

Hi,

Also, try populating the TreeView in a seperate thread or a WorkerThread so that your UI will be responsive even though that your treeview is still loading. Just indicate to the user that the Treeview is still being loaded....

cheers,

Paul June A. Domag