Hi geeks,
i'm working on a big web project that still keeps evolving, therefor it uses "plain old" sqlcommands, rob connery's subsonic and linq to sql. most queries were written with sqlcommands back then, but new data access methods use linq to sql.
we need to cache our data, because the web site is slowing down more and more. because of many users with many different roles and different languages i would not like to use output/fragment caching.
instead i would like to use data caching our business objects.
currently we are not using any caching on a couple of our classes/objects at all. hence there are a lot of round trips to the database - even for a single request - for the same data.
the pro of this is we don't have any stale data. even when updating something in the DB at the beginning of the request we get up-to-date data if we ask for it later on in the same request.
a few classes/objects we are already caching by using data caching with cache keys that consists of the name of the class + the id + the culture-name.
the sql server 2005 notification has a lot of limitations as you may probably know: we can not use it because many of the sql queries in the project are using asterisk (*) or joins and so on. (it would be near impossible to pull apart all the joins and using all the column names instead of asterisks)
the sqlcachedependency's polling mechanism is also not an option because we can't afford any stale data.
maybe this is an additional requirement and should not be mentioned here but to make things more understandable:
there are many composite objects in our code that are containing other objects as properties, too. if any of the data gets updated of such an (containing) object in the database, the composite object should be "recreated"/re-fetch the updated data so it is not becomming stale.
some of them are tree objects that contain children and parent properties to traverse the tree.
is there any alternative to sql server 2005's sqlcachedependency's notification mechanism without its limitation? any alternative to avoid stale data at all while caching?
do you have any recommendations? would be the only way to get this working to pull apart the sql joins and separate them in separate classes/objects?
many many thanks, tobi