Dynamic Select Lists

[DWFENNEL]

I have three select lists that are being populated when a page is loaded, but the load time is a little slow. I was wondering, would it be quicker to build the lists by calling SQL each time the page is loaded, or store the results in an Array or Vector and build the lists from that? I understand server-side scripting vs. client-side. I am also trying to avoid creating views or load tables that are updated nightly. The tables are pretty big (millions of records potentially). Thanks in advance.

[jwenting]

if they're updated infrequently, it's best to cache the results for a period known to be shorter than that update interval.
Analyse how long you can live with the old information after new information becomes available.
That will tell you the longest period you should cache the data.

And don't use a Vector, they're notoriously slow as well as being effectively deprecated, retained for backwards compatibility reasons only.

Use something like ehcache to cache the data, and set the cache timeout to a fixed interval (rather than a period after the last request) of maybe 5-10 minutes.
You might even have a dedicated process running that at regular intervals updates the cache in the background, so http requests never notice the time needed for the data retrieval.
But whether you want that depends on the actual time penalty. If it's only a few seconds, it's not worth it, users will just think the site's a bit more busy than usual when it happens (and they'll be effectively right, the server is busy waiting for the database).
If you're talking minutes, definitely do it out of process (but in that case you may want to seriously look at your data retrieval as well, there's likely something extremely inefficient in there that can be seriously improved, even a few database indices and stored procedures can do wonders).

[DWFENNEL]

I went ahead and used views to call the data. The sql was pretty straight forward, but there will be potentially millions of rows.