One of my web pages populates a droplist with about 60k items pulled from SQL Server, and this operation takes upwards of 10 seconds to complete. Are there some tricks or optimizations I can try to improve performance? I'm using a SqlDataSource configured as a DataReader.
Thanks for any help.
Any operation that gets a large amount of data from the DB will take a while.
Populating a dropdown with that many items will also take some time as will transmission of the resulting html to the browser.
Not to mention that 60k items in a dropdown list is not very usable.
The optimisation is to change your application so it doesn't need 60k items in one go.
Perhaps a paged approach is more appropriate, where you only get a small subset of the data at a time and can display different parts of it.
You can consider an autocomplete as another option.
Check if you're doing a SELECT *
Change the call to the database to a Stored Procedure or View
Try dumping the result into the ASP.Net Cache and check if that's null before querying the database again.
60,000 items is going to be way too much both for performance and from the user's perspective. Try either cascading dropdown (if it makes sense) or an autocomplete.
I think putting 60k into a dropdown is not that useful, I would suggest switching to some sort of auto complete solution.
As others have stated, I think you should expend some energy pondering the necessity of retrieving 60k records in one shot. Can you not provide some sort of cascading functionality so that only a subset at a time is required? Or perhaps an auto-complete search function? There's got to be a better way. And just think, not only will you improve the database performance, but you'll likely enhance usability in the process!