>
> Thanks Ben. I never thought to cache data in BL/DAL based on query.
For me
> it has always been the UI responsible for persisting relevant data, be
it
> WebForms Cache or local form variable. I was so used to doing it I
never
> even gave it a second thought.
[Ben Kloosterman]
Your cache works much better when all client use it.
>
> I agree that it makes a lot more sense to do it your way - at the end
of
> the day UI should not know where the data is coming from. Then again,
an
> optional flag could tell the DAL to force reload from db.
[Ben Kloosterman]
That is more of a business rule than a UI responsibility, Ie products
get updated 8 hours after entry = flush the cache every 8 hours.
>
> In our case we must implement Object Query - long story, business
> requerement. However, I see these two ideas complementing each other;
> there is no conflict.
[Ben Kloosterman]
You can use them as you can get some of the data from the cache . Often
O/R layers become much simpler.
>
> What strategy do you use to keep the cache in sync with the database,
> especially in the context of a Winforms App?
[Ben Kloosterman]
This is the most interesting bit :-) It really depends on how much you
want to scale - with caching your Mid Tiers are very fast and can often
handle hundreds or thousands of users - so often 1 server and a standby
is enough . On a Single Server what I do is this :
On a successful DB update , delete or insert, I update the cache. In
fact I even do this with multiple servers when there is 1 per geo
graphic location ( Sydney doesn't need to know Melbourne's updates) .
For multiple servers per location you have at least 3 strategies .
1. Put time stamps on your records and poll the tables looking for
changes . Data is out of synch by the time of the poll.
2. Send Cache updates to all other servers. This required the mid tier
to know about the other servers.
3. DB table Triggers
One thing to note if you are using a middle Tier as a web service you
have full access to the Web.Cache. If not you have to build your own
based on hashtable and some sort of ageing mechanism. The biggest issue
here is Web.Cache detects if the machine is under memory pressure and
starts trimming old objects. If you do not do this you have to make
sure the cache fits in memory , and what I do is I put small tables ( <
50,000 rows) like Callers in memory and often leave large ones uncashed
as request for these items are normally key based anyway - get me
transaction #000636363 ( clients can still cache the record) . Also for
large tables It is sometimes worthwhile just to cache todays information
ie transactions in the last 24 hours...
Remember though the goal is not performance ( as you are replacing
functions SQL server already does) , the cache insures that the business
layers has immediate access to frequent / key information , this allows
a good middle tier design which is simple and allows simple DB
interactions. This is possible with Datasets but they push you in the
wrong direction ( Datasets and caching are still a good solution for
small- medium projects ). Also the work is significant so it is not
really suited to smaller projects , but as a plus since you are
relieving the DB of a lot of work so it scales very well and often
allows a lower infrastructure investment.
Regards,
Ben
>
> Cheers,
> Roman.
>
>
> On Mon, 11 Oct 2004 17:16:18 +1000, Ben Kloosterman
> <[EMAIL PROTECTED]> wrote:
>
> >Not necessarily web apps but 3+ tier apps .... The cached tables
reside
> >on the mid Tier.
> >
> >IMHO The cost of implementing such an O/R layer would only be worth
it
> >on a large app and most of them are n tier .
> >
> >Ben
> >
> >> -----Original Message-----
> >> From: Unmoderated discussion of advanced .NET topics.
> >[mailto:ADVANCED-
> >> [EMAIL PROTECTED] On Behalf Of Roman Antchik
> >> Sent: Monday, 11 October 2004 10:02 AM
> >> To: [EMAIL PROTECTED]
> >> Subject: Re: [ADVANCED-DOTNET] Abstracting SQL
> >>
> >> Hi Ben,
> >>
> >> I've just realised what is going on here - you are talking about a
web
> >> app! So we have been talking cross-purposes for some time. Our
BL/DAL
> >code
> >> is primarily targeted at WinForms, so the notion of caching
> >transactional
> >> tables would not be of the same significance in our case. It does
make
> >a
> >> lot of sense for WebForms. Although you would need to keep the
cache
> >> synchronised with Db, how do you manage inserts/updates?
> >>
> >> Caching static tables works well in our case as well.
> >>
> >> As far as unions - that is a good idea. As long as the 'Add' method
> >checks
> >> for duplicates the concept would work quite well. Simplicity comes
at
> >the
> >> expense of fetching some extra rows from Db, of course, but in some
> >cases
> >> that's a small price to pay.
> >>
> >> However, I am not giving up yet on the Query Object pattern -
Frans'
> >post
> >> gave me a lot to think about.
> >>
> >> Cheers,
> >> Roman.
> >>
> >> On Fri, 8 Oct 2004 17:10:22 +1000, Ben Kloosterman
> >> <[EMAIL PROTECTED]> wrote:
> >>
> >> >Hi Roman,
> >> >
> >> >The problem here is Datasets , I used to be a great fan but they
> >create
> >> >poor designs and result in a tight DB Binding .. This is why you
get
> >> >Complex difficult to maintain code and SQl . Great for small quick
> >> >projects though.
> >> >
> >> >I now prefer Custom Collections and lazy initialisation , for <
> >100,000
> >> >Contacts you would keep all in memory ( Select * ) . For more than
> >this
> >> >I would have something like this ..
> >> >
> >> >ContacsCollection ContacsCollection.GetActiveWidgets ( string
> >> >widgetType , string widgetType2)
> >> >{
> >> > ContactsCollection contacts = new COntactsCollection();
> >> > // TODO check cache ..if (Cache["GetByWidgets"+ widgetType
]
> >!=
> >> >null... )
> >> >
> >Contacts.Add(DBClient.ContactsSchema.GetByWidgets(widgetType);
> >> >//uses data reader and simple select all contacts for that Widget
via
> >> >inner join
> >> >
> >Contacts.Add(DBClient.ContactsSchema.GetByWidgets(widgetType2);
> >> >
> >> >// if and and instead of a unit do it in code
> >> >
> >> > Return contacts;
> >> >}
> >> >
> >> >2 transactions and a union would add negligible time but would use
2
> >> >simple , reusable queries .. less code , less complexity ...More
> >> >flexible . In addition on larger systems such simple queries are
more
> >> >likely to be in cache and hence avoid a DB transaction altogether.
> >> >
> >> >If Attributes is a small table... you can store the whole table
in a
> >> >singleton ( note if it is used infrequently use a Select *) .
> >> >
> >> >ContacsCollection ContacsCollection.GetActiveWidgets ( string
> >> >widgetType , string widgetType2)
> >> >{
> >> > ContactsCollection contacts = new ContactsCollection();
> >> >
> >> > IList list AttributesCollection.GetByWidget(widgetType);
> >> >List.Add(AttributesCollection.GetByWidget(widgetType));
> >> >
> >> > foreach ( Attribute attribute in list )
> >> > contacts.Add(attribute.Contact)
> >> >// if and and instead of a unit do it in code
> >> >
> >> > Return contacts;
> >> >}
> >> >
> >> >Now in the case of both tables being huge it gets messy but the
idea
> >is
> >> >to avoid complicated SQL and use simple reusable components.
> >> >
> >> >The one exception to this is reports which will always be messy .
I
> >have
> >> >given up trying to get all reports working nicely and for some
just
> >use
> >> >painful SQL.
> >> >
> >> >Ben
> >> >
> >> >
> >> >
> >> >> -----Original Message-----
> >> >> From: Unmoderated discussion of advanced .NET topics.
> >> >[mailto:ADVANCED-
> >> >> [EMAIL PROTECTED] On Behalf Of Roman Antchik
> >> >> Sent: Friday, 8 October 2004 3:21 PM
> >> >> To: [EMAIL PROTECTED]
> >> >> Subject: Re: [ADVANCED-DOTNET] Abstracting SQL
> >> >>
> >> >> >> Don't forget you can also put complicated and important
> >> >> >> tables in memory
> >> >> >> on middle tiers this allows you to scale to many users and
> >> >> >> makes the DB
> >> >> >> performance irrelevant. These middle tiers also allows your
> >> >> >> OO design
> >> >> >> to capture business logic that requires sorting , joins ,
> >groupings
> >> >,
> >> >> >> exclusions etc and allows you to have much simpler SQL . (
ie
> >> >just
> >> >> >> simple selects and updates). In this case your SQL grammar
> >> >> >> is so simple
> >> >> >> it will be DB independent.
> >> >>
> >> >> Thanks Ben. Unfortunately that is not an option:
> >> >>
> >> >> 1. Sheer volume of data.
> >> >>
> >> >> 2. Straightforward O/R mapper is not hard to implement - this is
> >where
> >> >we
> >> >> stand now. It is the more complex stuff, like subqueries that
got
> >us
> >> >> stuck.
> >> >>
> >> >> Regardless, there is no support for subqueries in .NET framework
> >> >> DataTable / DataSet classes anyway.
> >> >>
> >> >> For example the only way to execute the query below - without
> >> >subqueries -
> >> >> would be to have two queries and a union.
> >> >>
> >> >> Extracting all contacts that either have Attribute 1 or
Attribute 2
> >> >> (assuming there are three tables - Contacts, Attributes &
> >> >> ContactAttributes):
> >> >>
> >> >> SELECT * FROM Contacts
> >> >> WHERE EXISTS
> >> >> (
> >> >> SELECT * FROM ContactAttributes
> >> >> WHERE
> >> >> Contact.Id = ContactAttributes.ContactId
> >> >> AND
> >> >> ContactAttributes.AttributeId = <Some Value>
> >> >> )
> >> >> OR EXISTS
> >> >> (
> >> >> SELECT * FROM ContactAttributes
> >> >> WHERE
> >> >> Contact.Id = ContactAttributes.ContactId
> >> >> AND
> >> >> ContactAttributes.AttributeId = <Some Other Value>
> >> >> )
> >> >>
> >> >>
> >> >> Thanks,
> >> >> Roman.
> >> >>
> >> >> ===================================
> >> >> This list is hosted by DevelopMentor(r) http://www.develop.com
> >> >> Some .NET courses you may be interested in:
> >> >>
> >> >> Essential .NET: building applications and components with CSharp
> >> >> August 30 - September 3, in Los Angeles
> >> >> http://www.develop.com/courses/edotnet
> >> >>
> >> >> View archives and manage your subscription(s) at
> >> >> http://discuss.develop.com
> >> >
> >> >===================================
> >> >This list is hosted by DevelopMentor(r) http://www.develop.com
> >> >Some .NET courses you may be interested in:
> >> >
> >> >Essential .NET: building applications and components with CSharp
> >> >August 30 - September 3, in Los Angeles
> >> >http://www.develop.com/courses/edotnet
> >> >
> >> >View archives and manage your subscription(s) at
> >> http://discuss.develop.com
> >>
> >> ===================================
> >> This list is hosted by DevelopMentor(r) http://www.develop.com
> >> Some .NET courses you may be interested in:
> >>
> >> Essential .NET: building applications and components with CSharp
> >> August 30 - September 3, in Los Angeles
> >> http://www.develop.com/courses/edotnet
> >>
> >> View archives and manage your subscription(s) at
> >> http://discuss.develop.com
> >
> >===================================
> >This list is hosted by DevelopMentor(r) http://www.develop.com
> >Some .NET courses you may be interested in:
> >
> >Essential .NET: building applications and components with CSharp
> >August 30 - September 3, in Los Angeles
> >http://www.develop.com/courses/edotnet
> >
> >View archives and manage your subscription(s) at
> http://discuss.develop.com
>
> ===================================
> This list is hosted by DevelopMentor(r) http://www.develop.com
> Some .NET courses you may be interested in:
>
> Essential .NET: building applications and components with CSharp
> August 30 - September 3, in Los Angeles
> http://www.develop.com/courses/edotnet
>
> View archives and manage your subscription(s) at
> http://discuss.develop.com
===================================
This list is hosted by DevelopMentor� http://www.develop.com
Some .NET courses you may be interested in:
Essential .NET: building applications and components with CSharp
August 30 - September 3, in Los Angeles
http://www.develop.com/courses/edotnet
View archives and manage your subscription(s) at http://discuss.develop.com
