Solved

Cache my Grid view when I do paging and sorting, but hit the database for a page load and a search action

Posted on 2009-07-16
4
336 Views
Last Modified: 2013-12-17
I have a very simple GridView working with Objectdatasource that is calling a Bussiness method that return a DataTable object. in my case I did a top 2000 records. using the built paging and sorting from the Grid View I hit the query each time I do any of the two operations.

I found out that the ObjectDatasource has a Cache property which works but because my page is search page. I want to store the results for each user in a Cache dependency key (this way each user who searches will have his own Cache, using the Session ID key).

Here are the rules I want the Cache to respect:

Page Load always hit the database.

Paging hit the cache if available

Sorting hit the cache if available

Clicking on the search button to get a new set of results  hit the database.

Can this be done? Did anyone did something like that ?

I could not find one sample online for something so basic and simple.

 

Personally I could not get this work, I had problems clearing the Cache.

A code sample would be great.

Thanks in advance.
0
Comment
Question by:TrafficTechDev
  • 2
4 Comments
 
LVL 39

Expert Comment

by:abel
ID: 24936198
> Can this be done? Did anyone did something like that ?
Basically, caching is a very complex matter (it seems easy at first, but then... when to invalidate, what to store, how much, how to notify that data has changed etc). Luckily, many people have already invented this for you. The excellent NHibernate ORM mapper contains full first-level and second-level caching, leaving you at the task at hand: making nice pages.= and writing business logic. Many other ORMs exist that have similar possibilities.

If you create this yourself, you'll hit one very important problem: suppose your rows are approx. 1000 bytes each (some dates, descriptions, memo, texts and ints quickly go so high). The entity object that you use will have about the same size (not really, but let's keep it simple). If you have a records set of 2000 records, that's 2MB altogether. With 500 users on your site that can surmount to 1000GB. This hits limits on most current installations of ASP.NET.

Solution? To-disk caching.

I wouldn't go that way. If your data is indeed about 2000 records, the overhead of simply getting them is not that much. You can keep them around during the request (I mean, prevent multiple SQL SELECT statements) and you should be quite fine.

If you want to do this the right way for the long run, a full blown ORM solution may seem a lot to invest in at first, but will save you all the time you'll need to write all your entities by hand, your caching by hand, your mapping by hand etc.

-- Abel --
0
 

Accepted Solution

by:
TrafficTechDev earned 0 total points
ID: 24938368
Hi, you may be right.
for now I found a way to do it, I have to monitor the server memory to make sure I don't kill it.
I know also that Microsoft is coming with a product called Velocity http://msdn.microsoft.com/en-us/data/cc655792.aspx that should handle large amouts of caching.

The objectdatasource calls a method in the Business layer there I am passing a Boolean value if I want to store the data set in cache or not.


[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select)]
        public static DataSet GetShipmentSearchTest(String UID,
                                                    Int64? SalespersonId,
                                                    Int64? ShipmentId,
                                                    String CustomerName,
                                                    Int16? ShipmentStatusId,
                                                    String ReferenceValue,
                                                    String CityName,
                                                    String ProvinceStateCode,
                                                    Int16? TaskTypeId,
                                                    String SupplierName,
                                                    DateTime? FromShipmentDate,
                                                    DateTime? ToShipmentDate,
                                                    Boolean ApplyGroupFilter,
                                                    Boolean IsAccountant,
                                                    Int64? CustomerId,
                                                    Boolean CanSeeProduce,
                                                    String CountryOrOfficeCode,
                                                    String UniquePageKey,
                                                    Boolean UseCache)
        {
            try
            {
                string CacheKey = string.Empty;
                CacheKey = "GetShipmentSearch" + UniquePageKey;
                DataSet ds = null;
                ShipmentWebService.ShipmentWS ws = new ShipmentWebService.ShipmentWS();
                if (UseCache)
                {
                    ds = (DataSet)CacheData.GetFromCache(CacheKey);
                }
                else
                {
                    CacheData.ClearFromCache(CacheKey);
                }
 
 
                    if (ds == null)
                    {
                        ds = ws.GetShipmentSearchTEST(UID,
                                                        SalespersonId,
                                                        ShipmentId,
                                                        CustomerName,
                                                        ShipmentStatusId,
                                                        ReferenceValue,
                                                        CityName,
                                                        ProvinceStateCode,
                                                        TaskTypeId,
                                                        SupplierName,
                                                        FromShipmentDate,
                                                        ToShipmentDate,
                                                        ApplyGroupFilter,
                                                        IsAccountant,
                                                        CustomerId,
                                                        CanSeeProduce,
                                                        CountryOrOfficeCode
                                                        );
                        CacheData.StoreInCache(CacheKey, ds);
                    }
                return ds;                
                                                    
 
            }
            catch (Exception)
            {
                throw;
            }
        }

Open in new window

0
 
LVL 39

Expert Comment

by:abel
ID: 24938492
Yes, Velocity is a good way to go (though I haven't used it myself). Using a full solution for these kind of things will solve many such problems.

As you may have found out, it is quite tedious to program yourself. To make it reliable, you can use objects that are serializable (most are by default) and that you simply pass the object (i.e,. not the typed object, just the Object object) to the cache.

Anyway, glad you got something going and working :)
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question