I'm building a website that shows various stats, each drawn from various large MySQL tables. It takes a few seconds for the query to run, retrieve the information, and my code to format that information into nice-looking tables. My original program simply loaded all the stats into one page, but as more users request more stats, the page loading time is getting longer and longer, so I decided to break the page up and only run the queries for the tables the user is looking for. I set up an Update Panel with several asp:Panel elements for groups of tables.
However, each time the user clicks on a button for a section, the queries for those stats run and the tables are generated. If a user goes to another section, then back to the first section, the queries for the first section are run a second time. This seems to be because, when the second section runs and the update panel is refreshed, the data for the first section is wiped out.
Is there any way to preserve the data generated across update panel refreshes, such that the queries and subroutines to build the tables are only ever run once? I would like to, for example, simply "hide" the first section when the user clicks on the second section, then show the first and hide the second when they click back on the first.
Pseudocode would be something like:
Sub SectionOneButtonClick
HideAllSections
If section1 is empty
generate section1
End if
ShowSection1
End Sub
Sub SectionTwoButtonClick
HideAllSections
If section2 is empty
generate section2
End if
ShowSection2
End Sub
Well, it turns out in most cases the server load to "persist" the data will cost you near as much as re-running the queries.
However, what I would consider is using temp tables. You could prefix the temp table name say with the user ID or some such.
So, when your query does all that munching and crunching? Send the summary results to a temp table. Then when they go back, you check for the temp table, and use that - it should be lighting fast. However, since web land is built around a state-less and disconnected model? Then you might consider creating the summary tables based on the user id, and thus not even use or create SQL temp tables (the issue will be how and when SQL server going to dump and dispose of those tables). So, either create some summary tables, and include a column with the user ID. You then to re-fresh or upon return check for rows in that temp reporting table, and thus pull those summary rows. Or as noted, create tables based on the current user ID, and then your re-queries against that data will cost next to nothing.
I suppose on session end event, you could consider code to delete the tables. However, session "end" does not reliably trigger.
Some date logic here could deal with this per-user issue of known when to create the temporary data tables. In fact, this could be another table with user ID and the last date when the data was generated.
So, I would not attempt to persist such data in memory, but use SQL server and its performance for this purpose. This also tends to reduce the size of viewstate for the browser depending on what kind of control you are using for the rendering of the data (such as ListView or GridView).
However, having stated all the above? If you are using update panels for each section? Then an update or change or filling of data in one update panel should NOT affect nor cause nor require a re-load of the other update panels.
Remember, a simple button placed in one update panel when clicked will trigger ALL update panels on that page. However, if you set the update panel mode with UpdateMode="Conditional".
At that point, you can hide, or show each panel in code behind, and you should not need to re-load or re-query each update panel section. They should be able to persist their data. I suppose it depends on what kind of controls you are using and rendering inside of each panel? Asp.net controls have automatic viewstate, and thus asp.net controls will retain their data - including GridView or ListView.
In the case of 3rd party controls, say like a cool looking Gauge control? Then you could persist the totals in hidden fields if you only need a few values, and then use the client-side JavaScript to re-load those values for your "own" viewstate system.