ObjectDataSource and Efficient 3-tier Design

[Jami Bradley]

I'm in need of some design suggestions!

We have a fairly large DB with thousands of users accessing data
throughout the day. I have been given the task of restructuring a
core part of the web application. It will be a design example for the
rest of the site and I really want to get it right the first time!

Basically we need to present a lot of data to the user. On a single
page, we summarize about 5000 rows (grouped so they don't look at more
than about 200 at a time). I'm struggling with a good 3-tier design
and the performance of accessing so much data.

In the main table, call it A, we have about 10 FKs per row, and we
need data from about 7 other tables using those FKs. Table A has a
lot of columns (50 or so). Of the 5000 rows in A, it probably joins
to 4500 rows in B, which also has 50 or so columns. These two tables
are most of the data we need to present. Table A is updated
frequently, Table B is not.

So, how would you structure the DAL and BLL objects to be efficient?
I would really like to cache the data, but I'm worried that I would
run out of memory on the web servers. I would also prefer to keep the
DAL close to the structure of the DB, instead of cooking the results
with several joins. But, if that doesn't make sense, I'd love to hear
suggestions.

How would you efficiently load these objects? I don't want it to be
too chatty creating the objects, so it seems like I need to
instantiate whole blocks of objects.

Thanks!

Jami

 

[sloan]

The quickest way to get the data from the db, would be to use an IDataReader
in a DAL object.
The DAL object can return this IDataReader to the Biz layer.
You can loop on the IDataReader, and create mini objects, and then add them
to an <List> generic collection.


Is your data "read only", ...

And ....
5000 rows in A, it probably joins

to 4500 rows in B,

for every row A, do the B rows always go back to 1 distinct A, or can a B be
associated with more than one A?
If yes, then ...you'll have different options vs if no.


Also see:
6/5/2006
Custom Objects and Tiered Development II // 2.0
http://sholliday.spaces.live.com/blog/


...

Are your summary rows something the database can do? (SUM AVG, etc)
or do you need to load the data.


...

Here are some hints:

With asp.net 2.0, You can cache a search result. I usually cached a strong
dataset here.
The gridview binds to a ObjectDataSource. You can populate the
ObjectDataSource with the cached copy of the data.

For stressed out web servers, look at the WeakReference object.


Post some more info about your need.........

 

[Jami Bradley]

The quickest way to get the data from the db, would be to use an IDataReader
in a DAL object.
The DAL object can return this IDataReader to the Biz layer.
You can loop on the IDataReader, and create mini objects, and then add them
to an <List> generic collection.


Is your data "read only", ...

A is read/write (although with 20M rows and 5000 updates a day, it is
mostly read only!)

B is almost read/only (350K rows, probably 20 updates/day).

The data in A is user specific, the data in B is shared among many
users. I would like to keep the data in B shared at the application
level if I can fit it!

We have quite a few 'constant' tables that would be purely read only.

And ....
5000 rows in A, it probably joins

for every row A, do the B rows always go back to 1 distinct A, or can a B be
associated with more than one A?
If yes, then ...you'll have different options vs if no.

A always references exactly one B (FK is in A, non-null)

Also see:
6/5/2006
Custom Objects and Tiered Development II // 2.0
http://sholliday.spaces.live.com/blog/


..

Are your summary rows something the database can do? (SUM AVG, etc)
or do you need to load the data.

I think the summary can and should be done by the DB (count and group
by). I've toyed with the idea of preloading all 5000 rows for
performance, but I think it will be better to be lazy to avoid
unnecessary loads.

..

Here are some hints:

With asp.net 2.0, You can cache a search result. I usually cached a strong
dataset here.
The gridview binds to a ObjectDataSource. You can populate the
ObjectDataSource with the cached copy of the data.

For stressed out web servers, look at the WeakReference object.

Our web servers are underutilized at the moment. When we start
caching more of the data, though, weak references might be an
interesting option.

Right now, our DB is stressed because of the repeated queries for
data. That I hope to resolve with the web server caching of the data.

Post some more info about your need.........

I've been reading most of the day and I'm putting together a prototype
now. The basic model is similar to the PetShop DAL/BLL/Model:

Simple wrapper classes for the data
DAL to create those wrappers
BLL will proxy the data for now and provide methods to insert/update
the data properly (we have extensive business logic).

The data is presented at three levels:
Summary info (I'll make a specific DAL/BLL for this)
'Top level information' This is the common information presented for
a row of data from A (including all of it's subordinate tables)
'Detailed information' This is shown when the user explicitly expands
a single row.

The user would typically show the summary, expand one to show approx.
200 rows, then show details of a few of the 200. This routine would
be repeated over their session, usually within the same summary.

Right now, I'm leaning towards segmenting the data and lazy loading
the detailed portion. Several important parts of the summary
information are kept in table B, so I may have to preload the B rows
even to get the top level information. It is tempting to just stick
it in the DAL for A, but I think that will be a problem later on when
I develop the BLL for B.

Jami