ADO.NET DATAVIEWS VERSUS SQL ENGINE

Hi All,

Any suggestions / views / help on below question would be welcomed.

I am building an asp.net 2.0 application with sql 2005 express as back end. My back end has 3 major tables which are:

tblArticles - saves basic info on articles posted by user (like articleid, title, short desc, rating, views, etc)

tblCategories - saves various categories and their hierarchies (id, parented, name, etc)

tblArticleCategories - saves info on which articles fall in which categories (like articleid, categoryid)

as of now, i am caching all rows from the first 2 tables, but i am in a bit of doubt for caching the third table (tblArticleCategories), although data in this table wont change very often and also this table will just have 2 columns and not many rows as well and this is a good target for caching,

but the reason I am in a bit of doubt to cache this table is, when my website visitor clicks on any category link in the category tree view, I need to use an inner join across all these 3 tables to locate and return all articles found in that particular category.

But I can do the same thing without hitting the database as I already have 2 of the required 3 tables in my cache, I can simply add the third table to my cache and then using the dataview objects rowfilter property on these 3 cached tables, I can very well get the appropriate results.

But I wonder which of the 2 methods would you prefer and suggest, I mean do you feel that just to save hits against the database, I am going to far and doing a lot of crap using the dataview (which might not be as efficient as sql engine) or you feel that the inefficiency of the dataview will still win compared to the cost of hitting the database for this

Thanks in advance, bye take care

Raj Chaudhari, Mumbai, India (MCAD.NET)

www.xtremebiz.biz

i would cache smaller objects that are the result of a more specific query.

let the db do its joins and return from the db only articles for a specific category.
cache this data.

when other categories are selected, hit the db, and cache that data too as a seperate object.

the result is that you dont need to perform rowfiltering on every page hit and you will only have articles in cache for categories that have been selected

|||If you are using SQL Express or SQL Server 2005 as the back end, it sounds like you are doing a lot of manual work that you really don't need to. Just use the SQLDatasource object, put your query in the select query text property, set the caching to true, and set the cache dependancy property to the appropriate value "tblArticles,tblCategories,tblArticleCategories" for the query that access all 3 tables. If you have performance problems after that, address them at the time. Heck, remember prior to ASP.NET 2.0 *most* applications did no caching of database queries at all and ran just fine.