Combobox Dropdown Caching

Question

We have a VB.Net application that has in the region of about 80 unique comboboxes spread over 15 Windows. Currently when a Window is loaded, the Comboboxes retrieve their contents directly from the SQL database.

This is quite inefficient as the content of the comboboxes rarely changes, so it would make sense to retrieve all the combobox dropdown values once when the program is first run.

I've struggled to find working examples on the web of best practices in this regard. So to avoid me re inventing the wheel, does anyone our there have any words of wisdom or code snippets to point me in the right direction?

Answer 1

Caching is definitely the way to go, use a Hashtable or something like that. If you can merge all the queries into one SQL request that returns multiple tables and get everything in one call.

Answer 2

Could serialize the data to an xml file that you load on startup and fill in the combo boxes. Then you can add a table to your database with a single value, the last date you updated your database on, and if it's after the current date, run your database queries to fill in the comboboxes and then update the xml file.

Answer 3

This is very similar to what is reqd in the application I am developing. I have created a static class with static DataTables per combo box. When the app loads it populates all the static data tables.

Sample:

static public class GlobalDropdownData
    {
        static private DateTime Combo1Table;

        static private DataTable Combo1Table;

        static private DataTable Combo1Table;
 }

Vb.net would be something like:

Public Class GlobalDropDownData
    Shared Combo1DT As DataTable
    Shared Combo2DT As DataTable
    Shared Combo3DT As DataTable
End Class

Answer 4

Yes, you can load all the lookup data at application startup, in a single call returning multiple resultsets. A Winforms app that I inherited did just that, but the trade-off was that startup time was terribly slow, as the web service call that was being made to fetch the lookup data from the database to return all the cached resultsets took several seconds to execute. If this is a problem, you will want to launch the database/web service call asynchronously so more initialization work can happen as the database code is being executed. If you need to be sure that the data has been returned before the user performs a certain task, such as clicking on a button to display a screen with one or more of the comboboxes requiring the cached data, use of one or more Winforms Timers may help as well.

A further option to assist performance could be to perform the data access call in a separate thread, although in the project I was working on, I found the other options sufficient to reduce startup time from about 10 seconds to under 3 seconds without having to deal with the complexities of multithreading.