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
342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

705 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