Help With Complex Query - Running Slow

russellhq · Mar 9, 2009

I'm usually not bad with queries, but this one is giving me a headache.

It's producing results but takes a while to do it.

It's like a cost comparison query, the following is an example:

I have the following stores:
Store A
Store B
Store C

And each store carries similar products:

Product A
Product B
Product C

And each product comes in different sizes:

Small
Medium
large

I have a table for each store with the following fields:

ID, StoreName, ProductName, SmallPrice, MediumPrice, LargePrice

What I want to do is design a query that tells me the cheapest price for each size of each product and what store is offering that price.

Hope that makes sense!

Oh, and one other thing, the same products have different names.

To get around this I came up with a master table with the fields:

ID, MasterProductName, StoreAProductNames, StoreBProductNames, StoreCProductNames

I then have to link the product in the stores table, to the corresponding field in the master table and use the ID field to compare against the other prodcuts.

DCrake · Mar 9, 2009

The first query you need is a group by product id Min(smallPrice)

Assuming small price is the field that holds the smallest price. Why have you got 3 prices for the same product? Are these selling prices? How do you calculate you stock value?

I assume that even though the different stores have similar sounding names for the same products they all refer to the same primary key.

Once you have the smallest price for each product then add you stores tabel to your query and join on product keys but display descriptions form each store along with the primary description.

gemma-the-husky · Mar 9, 2009

having this structure wont help

ID, StoreName, ProductName, SmallPrice, MediumPrice, LargePrice

instead a table

Code:

id, storename, productname, pricetype (ie 1 to 3 say), priceamount

ie up to 3 records for each one of yours would probably work a lot quicker, make the storage more efficient (no nulls) and make it future proof - (what if you get a fourth pack size?)

tables long and narrow, not short and wide

russellhq · Mar 9, 2009

Assuming small price is the field that holds the smallest price. Why have you got 3 prices for the same product? Are these selling prices? How do you calculate you stock value?

The "Small", "Medium" and "large" fields refer to the cost of the product at that size. E.g. Small Sized ProductA may cost $2 and a Medium Sized ProductA may cost $5

I assume that even though the different stores have similar sounding names for the same products they all refer to the same primary key.

Yes, I have a table with a primary key (auto number) and a field with a master list of product names. Each subsequent field corresponds with with the individual stores product name. So some stores may not carry a product in the master list and thus will have an empty field.

Thanks for the tip Gemma. I will re-do the table as you suggest. With the new table, how would i then find the store with the cheapest price for evey item?

gemma-the-husky · Mar 9, 2009

finding data related to a dmin or dmax isnt easy - i dont think you can do it in a single query anyway, but i will prbably be proved wrong

the trouble is if you do a totals, min query to say - find me the lowest price, by item, it tells you the price, but not the store

you then have to do another query to find the store related to the price you just retrieved.

russellhq · Mar 9, 2009

At the moment, I have all the values saved in separate csv files for each store.

Are you saying it would it be better then to save the values in one csv?

russellhq · Mar 10, 2009

Gemma, I forgot to ask. Were you suggesting that the table should include all stores or one table for each store?

the trouble is if you do a totals, min query to say - find me the lowest price, by item, it tells you the price, but not the store

you then have to do another query to find the store related to the price you just retrieved.

How would I do this in 2 queries?

jal · Mar 10, 2009

Queries usually run pretty fast - are you sure this is the source of the bottleneck?

russellhq · Mar 10, 2009

Hi Jal.

I have a union query that I think is the bottleneck. Do these normally run slower?

jal · Mar 10, 2009

Go ahead and paste the query so we can look at it. I can say that UNION runs slower than UNION ALL (in virtue of taking extra time to check for dups in the result set and eliminating them).

russellhq · Mar 10, 2009

The union looks a little like this;

Code:

SELECT [ProductID]
FROM [qry_StoreA]
UNION SELECT [ProductID]
FROM [qry_StoreB];
UNION SELECT [ProductID]
FROM [qry_StoreC];

This list all the products from all the stores, as some products are not available at all stores.

The qry_StoreA reads;

SELECT tbl_MasterProductList.ID AS ProductID, tbl_StoreA.ProductSize, tbl_StoreA.ProductPrice
FROM tbl_StoreA INNER JOIN tbl_MasterProductList ON tbl_StoreA.ProductName = tbl_MasterProductList.StoreA;

The query above joins the name of the product from StoreA with it's name in the StoreA field of the master product table, this then returns the Product ID. Each store has a Field in the master product table.

jal · Mar 10, 2009

SELECT tbl_MasterProductList.ID AS ProductID, tbl_StoreA.ProductSize, tbl_StoreA.ProductPrice
FROM tbl_StoreA INNER JOIN tbl_MasterProductList ON tbl_StoreA.ProductName = tbl_MasterProductList.StoreA;

Did you paste the query verbatim? Because the word in red looks like either a logic error or a forum typo. If it's a logic error, it might be part of the problem here ( worse yet perhaps you made the same error in all three queries) I would expect to have seen:

= tbl_MasterProductList.ProductName;

And how many records are we talking here? And how long is the query taking? Are you sure you need to use UNION? as I said, UNION ALL is faster, although it doesn't eliminate dups.

russellhq · Mar 10, 2009

jal,

That is not a typo. Field [StoreA] on table tbl_MasterProductList contains the product names for store A.

There are around 400 records and union takes about 3 seconds. I'm used to querys happening instantly. This is running on an Intel Quad core PC, so computer speed is not an issue.

Yes, I need UNION as I dont' want to list every product from every store, just all the distinct products.

jal · Mar 10, 2009

Are the joins column indexed? That might speed up this query, although it would slow down INSERT queries.

jal · Mar 10, 2009

I am wondering if there is a way to change the 3 joins into one join, and don't know if it will help. I didn't have time to finish writing this out, just trying to give you the general idea.

SELECT *
FROM
(
SELECT tbl_MasterProductList.StoreA as ProductName
UNION
SELECT tbl_MasterProductList.StoreB as ProductName
UNION
SELECT tbl_MasterProductList.StoreC as ProductName
) as MasterList
INNER JOIN
(
SELECT ProductName FROM STORE A
UNION
SELECT ProductName FROM STORE B
UNION
SELECT ProductName FROM STORE B
) as Stores
ON Stores.ProductName = MasterList.ProductName

Help With Complex Query - Running Slow

russellhq

New member

DCrake

Remembered

gemma-the-husky

Super Moderator

russellhq

New member

gemma-the-husky

Super Moderator

russellhq

New member

russellhq

New member

jal

Registered User.

russellhq

New member

jal

Registered User.

russellhq

New member

jal

Registered User.

russellhq

New member

jal

Registered User.

jal

Registered User.

Similar threads

Users who are viewing this thread