By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,385 Members | 847 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,385 IT Pros & Developers. It's quick & easy.

Business / Data tiers - efficiency

P: n/a
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
--
Daisy The Cow
Nov 15 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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

"Daisy" <da***@nospam.oops> wrote in message
news:bs**********@linux01.dannytuppeny.com...
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
--
Daisy The Cow

Nov 15 '05 #2

P: n/a
"Daniel Pratt" <ko******************@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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.


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)... :-(
Nov 15 '05 #3

P: n/a
Hi Daisy,

"Daisy" <da***@nospam.oops> wrote in message
news:bs**********@linux01.dannytuppeny.com...
<snip>
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)... :-(


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
Nov 15 '05 #4

P: n/a
"Daniel Pratt" <ko******************@hotmail.com> wrote in message
news:u7**************@tk2msftngp13.phx.gbl...
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)... :-(


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)


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?
--
Daisy The Cow
Nov 15 '05 #5

P: n/a

"Daisy" <da***@nospam.oops> wrote in message
news:bs**********@linux01.dannytuppeny.com...
<snip>
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 more than one price, what would the product listing
look like?

Regards,
Dan
Nov 15 '05 #6

P: n/a
"Daniel Pratt" <ko******************@hotmail.com> wrote in message
news:eF**************@TK2MSFTNGP11.phx.gbl...
<snip>
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 more than one price, what would the product listing
look like?


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 The Cow
Nov 15 '05 #7

P: n/a

"Daisy" <da***@nospam.oops> wrote in message
news:bs**********@linux01.dannytuppeny.com...
"Daniel Pratt" <ko******************@hotmail.com> wrote in message <snip> 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.


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
Nov 15 '05 #8

P: n/a
"Daniel Pratt" <ko******************@hotmail.com> wrote in message
news:u4**************@TK2MSFTNGP11.phx.gbl...
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.


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 :-)
--
Daisy The Cow
Everyone complains about the weather... but nobody ever does anything about
it
Nov 15 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.