Business / Data tiers - efficiency

  • Thread starter Thread starter Daisy
  • Start date Start date
D

Daisy

Hi Folks,

Got a load of business objects - Product, Price, Offer, FAQ, Description...

Product contains collections of the others (eg. Product.Offers is a
collection of Offer objects)

Similarly, in the database, Price, Offer, Faq, Description all have Product
foreign keys (and there can be multiple of all except Price (it's in its own
table for other reasons)).

I want to pull back a list of all products that contain "Blue" and have a
price between £10 and £20, and given the result set could be 2000 records, I
want to populate everything fron one query.

Is it possible to have my SP return a DataSet with relationships (like ADOs
DataShaping) for this? If not, what's the quickest way to do it, without
hitting the database 2000 times to populate Prices on Products as they're
accessed?

Thanks
 
Hi Daisy,

Is it possible that you could delay pulling back the detail for each
product until absolutely necessary? For example, if this is a scenario where
the user is looking at a list of products and then choosing one to
view/edit, you could pull back a single table with only the fields from the
product that will appear in the list. Then when the user chooses to
view/edit one you can go get the product and detail for a single product via
the primary key.

Regards,
Dan
 
Daniel Pratt said:
Is it possible that you could delay pulling back the detail for each
product until absolutely necessary? For example, if this is a scenario where
the user is looking at a list of products and then choosing one to
view/edit, you could pull back a single table with only the fields from the
product that will appear in the list. Then when the user chooses to
view/edit one you can go get the product and detail for a single product via
the primary key.
Click to expand...

On the listing page, I'd like to display the price (and therefore probably
any offer too) :o(
Currently, the business object fetches data as it's requested (if it's not
already been fetched once), so this results in lots of hits to the database
as it outputs each row.

I'd had thoughts about passing a list of objects to a method that pulls back
all prices in one go, and then populates them from one query, though passing
2,000 keys from products into a store procedure isn't easy either...

This seems to be something we do a lot of in ASP (without proper objects
;)), I can't understand how it can be so hard if so many people are doing it
:-\

I've also thought about redundant data (cache prices into product table) to
reduce what I'm pulling back to a flat list, but then enters the nightmare
of maintaining this data (database triggers vs doing it in C# - curerntly
we've got one trigger that caches a tree of 5,000 categories into a flat
table, but the trigger takes over a minute to run, this grinding things to a
halt at critical times)... :-(
 
Hi Daisy,

I've also thought about redundant data (cache prices into product table) to
reduce what I'm pulling back to a flat list, but then enters the nightmare
of maintaining this data (database triggers vs doing it in C# - curerntly
we've got one trigger that caches a tree of 5,000 categories into a flat
table, but the trigger takes over a minute to run, this grinding things to a
halt at critical times)... :-(
Click to expand...

Unless I'm missing something, there's no need to cache. Use a join in
the query that returns the listing data. Something like:

select
Products.ProductID,
Products.Name,
Products.Description,
...
Prices.Amount as ProductPrice
from Products
inner join Prices
on (Products.ProductID = Prices.ProductID)

Regards,
Dan
 
Daniel Pratt said:
to

Unless I'm missing something, there's no need to cache. Use a join in
the query that returns the listing data. Something like:

select
Products.ProductID,
Products.Name,
Products.Description,
...
Prices.Amount as ProductPrice
from Products
inner join Prices
on (Products.ProductID = Prices.ProductID)
Click to expand...

If a product has multiple offers (buy 2 get 1 free + 10% discount) this
would result in multiple product records that would have to be sorted out at
the other end. ADO had DataShaping to stop this, but I'm not sure if this
goes well with .NET, since it used RecordSets?
 
If a product has multiple offers (buy 2 get 1 free + 10% discount) this
would result in multiple product records that would have to be sorted out at
the other end. ADO had DataShaping to stop this, but I'm not sure if this
goes well with .NET, since it used RecordSets?
Click to expand...

If a product has more than one price, what would the product listing
look like?

Regards,
Dan
 
Daniel Pratt said:
out

If a product has more than one price, what would the product listing
look like?
Click to expand...

A product only has one price, but may have multiple offers. How it's
displayed on the final page isn't too important, what's important is that I
have a product object that has a price object attached, and a collection of
offers, so I can do something like:

Response.Write(prod.Name);
Response.Write(prod.Price.IncVat);
foreach (Offer o in prod.Offers)
{
Response.Write(o.TextDetails);
}

My problem is getting this kind of hierarchy from the database (product,
price and offer tables - price and offer have product_fkeys) into these
objects. DataTables are flat, and I don't know if it's possibly (or how) to
have a stored procedure return a DataSet of multiple Tables rather than a
single Table.
 
Daisy said:
"Daniel Pratt" <[email protected]> wrote in message
Click to expand...
A product only has one price, but may have multiple offers. How it's
displayed on the final page isn't too important, what's important is that I
have a product object that has a price object attached, and a collection of
offers, so I can do something like:

Response.Write(prod.Name);
Response.Write(prod.Price.IncVat);
foreach (Offer o in prod.Offers)
{
Response.Write(o.TextDetails);
}

My problem is getting this kind of hierarchy from the database (product,
price and offer tables - price and offer have product_fkeys) into these
objects. DataTables are flat, and I don't know if it's possibly (or how) to
have a stored procedure return a DataSet of multiple Tables rather than a
single Table.
Click to expand...

I obviously don't know much about your app, but...If I were displaying a
large list of products to the user, I would not use Product objects or
multi-table DataSets. I would use a DataReader or single-table DataSet that
would contain exactly the information that would be in the list and nothing
more. Now that I've disclaimed that much...

Supposing you have a strongly typed DataSet that contains tables for
Products, Prices, Offers, ect and the relationships between them. You
populate the Products with a query that looks like this:

select * from Products where ProductName like '%blue%'

You could populate the Offers table with a query that looks like this:

select Offers.* from Offers
inner join Products on (Offers.ProductID = Products.ProductID)
where Products.ProductName like '%blue%'

And so on. The point is, you can run one statement per table, instead of
several statements per product.

Now, if the select command that you use with the data adapter returns
multiple resultsets (i.e. there are more than one select statements
seperated with ';'), then the data adapter will (I believe) put each
resultset in its own table in the DataSet. For example, if the select
command has two select statements, the DataSet will contain Table1 (the
first resultset) and Table2 (the second resultset). I think it may also be
possible to choose something other than Table1, Table2, Table(n) via the
table mappings. All this is what I remember from a while ago and I haven't
verified it.

Regards,
Dan
 
Daniel Pratt said:
Supposing you have a strongly typed DataSet that contains tables for
Products, Prices, Offers, ect and the relationships between them. You
populate the Products with a query that looks like this:

select * from Products where ProductName like '%blue%'

You could populate the Offers table with a query that looks like this:

select Offers.* from Offers
inner join Products on (Offers.ProductID = Products.ProductID)
where Products.ProductName like '%blue%'

And so on. The point is, you can run one statement per table, instead of
several statements per product.
Click to expand...

Aha! That's what I was looking for. I was struggling to return multiple
tables into a dataset, and didn't think of using seperate queries to fill
tables, and join them together afterwards!

I'll investigate... Thanks :-)
 
Back
Top