Architecture question - design of incremental fetch of large read-only collections

stephen.fung posted on Thursday, October 05, 2006

Hi,

I'm designing a Windows Forms app on CSLA .NET, which may need to be accessed remotely with server side validation checking, so I'd like it to be mostly 3-tier.

An interesting design issue I'm having is that I'd like to display large collections of read-only data (a collection of summaries of up to 50,000 old orders) in a DataGridView, and incrementally show this data as it is loaded, so that after the first say 100 orders are loaded, the user can begin browsing while the rest of the data is being fetched.

A potential solution I'm considering is to bypass the application server and directly connect to the database from the client for this case (fetching large collections of read-only data) using a DataReader, but still going through the app server in other cases for write/update requests.  However, this halfway breaks the 3-tier abstraction and increases the risk of security issues.

Another potential idea is to have multiple command objects that request a small batch of 100 or so orders at a time through the app server.  However, this seems to increase complexity as the app server would either need many DB calls (like paging) or some DB cursor-like logic.  It also seems like an extra performance burden.

Does anybody have any experience or advice in implementing something like this?

Thanks,
-Stephen

ajj3085 replied on Thursday, October 05, 2006

Your list should manage this.  I'd create a method which fetches the next set of data.  You may be able to use the IReportTotalCount interface (not sure if that's the right name, just look at Csla.Core for interfaces).  I think there was some discussion of this before on this forum (which is why Rocky created that interface). 

Don't break n-tier by directly connecting to the database.

RockfordLhotka replied on Thursday, October 05, 2006

I agree - breaking n-tier for one object is just creating a problem you'll have to deal with forever...

There are numerous solutions to this problem - but arguably the best one is to use a virtual grid. Some grid controls directly support a "virtual" or "paged" mode.

To do this, you do need to make your collection itself support paging as well. As Andy points out, this is the reason for adding the IReportTotalRowCount interface - because paged web grids need to know the total count. But I'm guessing some of the Windows grids would need to know it as well.

stephen.fung replied on Thursday, October 05, 2006

Thanks for your responses, Andy and Rocky.  I didn't know about IReportTotalRowCount or grid virtual modes, and I will probably make the switch to CSLA 2.1 soon.

Could you please help me understand the tradeoffs a little better?  The main concern I have with doing this MS Access style UI with paging is performance.  The queries could potentially have non-trivial filters on them, and we'd normally like all the records to eventually be loaded in the background.  Loading this via many requests for data a page at a time seems like much more work for the database (even if we implement optimizations like temporary cache tables, etc.).  It would also mean a heavier load on the application server, which has to instantiate and serialize many objects.  A colleague is looking into some crude benchmarks for this style of approach.

On the other hand, what are the problems created by breaking n-tier, and how severe are they?

Some that I can see are:
- Security - the database will need to allow access from all client machines, not just the app server
- Deployment complexity - more open ports, access rights, etc. to configure
- More communication code - might be trickier to maintain, although it would be limited to this module

Thanks,
-Stephen

richardb replied on Friday, October 06, 2006

We are still on CSLA 1.x and have an Orders Search screen that in theory could bring back all the rows in the database, but we created a lightweight readonly collection and page the records, just bring back 1000 records at a time with an indicator if MoreRecords are available and what Page we are on.  We work out from the search criteria in the database stored procedure how many records we 've got, which page we are after and which 1000 records we need to send back.

It works well in our situation - SQL Server does the job, our server has fast disks and lots of memory, and the amount of data serialised up and down the wire is reduced and the client PC's get their data fast.

Looking forward to using CSLA 2.x .