query logic on frequent update

Question

Post reply

query logic on frequent update

kangarolf

Hall of Fame

Points: 3432
More actions
November 1, 2004 at 4:26 pm

#102256

Hi all,
Have a logic problem with a query that must run hourly on circa 10K rows.
Basic problem;
- Three warehouse distributors
- 10k products
- Linked across all three distributors by primary key
- Hourly updates of prices and stock for all three warehouse distributors
I need to either do a once an hour update to the main product table which inserts/updates the best price and its corresponding distributor, or construct a general query for returning products that always returns the cheapest warehouse at any one point.
Lots of freedom as to how the hourly updat einformation is stored.
Obvious problems that I've had;
- Cant use a min function and group on product PRIMARY KEY as also need the distributor to whom the minimum price belongs to
- Cant use temp tables for each and every product cos of resource issues!
- Could work out cheapest programmatically but again resource issues and very wasteful
Any ideas greatly received. If anyone wants more information please ask.
Thanks again
Rolf

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Rolf, it would help me to know a bit more about the current db schema. I'm guessing that you have something like:

Product(ID (PK), Description)

DistributorA(ProductID (PK), CurrentPrice)

DistributorB(as above)

DistributorC(as above)

with ProductID being a FK to Product.ID in each case?

If so, perhaps you could think about creating insert/update triggers on the distributor tables that update relevant fields in the Product table (eg Distributor and Price) if necessary? Your Product table is then always bang up to date.

Regards

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

kangarolf Hall of Fame Points: 3432 More actions · Answer 2

Thanks for the reply.

Currently the DB is still in the 'design' phase so lots of freedom as to what is stored where.

At the minute I have used two views, one is a union of the three distributor tables' productID, price and distributorID. The second view is a MIN function grouped by productID. I can then join this to the previous view on price and product ID to get the required info of distributorID, min price, and productID.

This all feels very ungrceful though and no less resource hungry than checking every price/prod row of each of the distributor tables in turn against the current best price in the main table (and updating where necessary).

Cheers

Rolf

Calvin Lawson SSChampion Points: 11030 More actions · Answer 3

Kanga,

I'm having a hard time understanding what you are trying to do. It seems like a simple update process that needs to happen once an hour, where it would be common sense to use an update/insert statement from your source to your destination. I know this is vague, but your description of the problem is vague as well. You'll have to describe either your schema in more detail (or if it's still in "design", briefly describe what you need to do in more detail).

cl

Signature is NULL

Phil Parkin SSC Guru Points: 246962 More actions · Answer 4

If you really have got full design freedom, then here is how I would create the tables:

1) Product (ID (PK), Description, {+ other product info fields})

2) Distributor (ID (PK), Description, {+ other distributor info fields))

3) ProductPricing (ProductID, DistributorID, Price)

The FKs should be self-evident.

The third table is required to accommodate the many-to-many relationship between products and distributors and has (ProductID, DistributorID) as a candidate key.

Regards

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

kangarolf Hall of Fame Points: 3432 More actions · Answer 5

Sorry for the vagueness of the question posted, it was the wrong time to post the question as lots of things were still 'floating'.

I have managed to get certain changes made to the way the data is provided so now I can use simple update statements and the many-to-many relationship as you have described above.

Thanks again for all the help.

Rolf