Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 604
  • Last Modified:

Sharepoint webpart data states

Greeings all experts,

If your grid takes 5 minutes to retreive records (Please don't ask why, I learned not to in my current position ;) )  then essentially you are regenerating that query each time someone chooses to do something on the page.

My goal was to prevent this.  I want to query the data once, store it in objects, then reference the objects each time I need the data.

I would like it to persist in memory until the functions are complete.

//gathers connection string information

// interface to connection string

//Here is where I want to display the data.

Currently I am thinking something like this

call the interface to get the database connection string and open a connection

//set a List<T> which will be static as I don't want to regenerate this
if (!Page.IsPostBack)
   LoadData(); //LoadData will open the database, and fill a datatable with data
_data = new DataLoad();
List<t> = _data.LoadObjects(_datatable) // After this is done, I don't want to bother with the connection, LoadData method, i don't even want to relaod this list when a user changes parameters.
//Here is where I am generating the grid for my data display.

Now the question.

when I call the LoadObjects(_dataTable).  The datatable is not always avalable.

Do I have the right Idea in developing this or is the requerying of the database necessary?
  • 2
  • 2
1 Solution
Ted BouskillSenior Software DeveloperCommented:
There are a few performance issues with a data grid.
- The time it takes to retrieve the data from the database
- The time it takes to render the data as HTML
- The time it takes to send the page to the client
- The time it takes to draw the grid in the browser.

It sounds like you are sure the data retrieval is the greatest penalty.  So, if the data changes infrequently I'd recommend you load the data into the HttpRuntime.Cache.

I has some interesting properties.  It's global and you can provide hints to the cache about when to drop it from memory if the server is short of RAM.  You can even add a SqlCacheDependency so that a query polled from the server can drop the cache when the data is no longer valid (if it changes infrequently)

You simply have to check the cache first before you use the object.  If it's not there (because the system had to clear the cache) you repopulate and reuse it.

However, I think you should also consider looking at Sharepoint's Output Caching.
rg20Author Commented:
Ok, point taken and will be explored as a possible solution.

BUT... (you knew there had to be one).

How can I store the database tables as ojbects so that they are accessable even during a reload?

Would claiming the variable as static take less memory resources than using cache?

Lets say for example (forgetting about memory issues for the moment) we had a webpart that defined the connection to the database.  On the first load of the page, the connetion exists through the interface.

So Now I have my connetion string

Now I have a grid, where a user can type in a number (for the sake of argument lets say a multiplier)

Now the values from the database are read in and the number is multiplied by 2.  The user changes the number to 3.  I don't want to have to reload the database to have to recalculate it, since I put it in objects.

I am worried also that the cache is going to have problems unloading when the site is left.  This is a leftover worry from the asp classic days where session varialbles did not always clear when the browser was closed and the OnSession_Close in the gloabal.asa could not be counted on.
Ted BouskillSenior Software DeveloperCommented:
OK, first off static objects can't be managed properly by the web application so they can't be serialized or released if memory is short.  The HttpRuntime.Cache is static variables that can be managed by the web application including releasing them if required.

Static variables will NOT be released until the site is restarted, with the HttpRuntime.Cache you can put on an expiration date or duration.  If you are worried about memory being left over static variables are worse.  You have 100% control of the cache far more precisely than session or static variables.

NOTE: Sharepoint stores sessions in the SQL database so it has a timed job that runs every minute to clear the old sessions.

The advantage with OutputCache is the rendered grid is cached, so you save time retrieving the data AND binding it to a data grid.

Based on your requirements I'd pull the data from the database using a SqlDataReader and store it in an ArrayList of custom objects that store each row.  The DataSet object or DataGrid's are large cumbersome objects that consume far more space that is required to store the data. Then I'd bind the ArrayList to a DataGrid as required and rendered it on the fly using your recalculation methods.  That way you minimize round trips to the database.

By the way, if you are using NLB with Sharepoint you have to be VERY careful about storing objects in memory.  The only way you can guarantee a user will hit the original server with their copy of the cache is by using Affinity.
rg20Author Commented:
I am still working on the issue of connections.  I got it to work, but it seems to work no matter which page I put the webpart.  Not good.


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now