0

Here is the database schema:

[redacted]

I'll describe what I'm doing with the query below:

Innermost query: Select all the saleIds satisfying the WHERE conditions

Middle query: Select all the productIds that were a part of the saleId

Outermost query: SUM the products.cost and select the vendors.name.

And here is the SQL query I came up with:

SELECT vendors.name AS Company
, SUM(products.cost) AS Revenue
FROM
    products
    INNER JOIN sold_products
        ON (products.productId = sold_products.productId)
    INNER JOIN vendors
    ON (products.vendorId = vendors.vendorId)
            WHERE sold_products.productId IN (
                    SELECT sold_products.productId
                    FROM
                        sold_products
                    WHERE sold_products.saleId IN (
                            SELECT sales.saleId     
                            FROM
                                markets
                                INNER JOIN vendors
                                ON (markets.vendorId = vendors.vendorId)
                                INNER JOIN sales_campaign
                                ON (sales_campaign.marketId = markets.marketId)
                                INNER JOIN packet_headers
                                ON (sales_campaign.packetHeaderId = packet_headers.packetHeaderId)
                                INNER JOIN packet_details
                                ON (packet_details.packetHeaderId = packet_headers.packetHeaderId)
                                INNER JOIN sales
                                ON (sales.packetDetailsId = packet_details.packetDetailsId)
                            WHERE vendors.customerId=60
                            )
                    )
GROUP BY Company
ORDER BY Revenue DESC;

Any help in optimizing this?

Hrishikesh Choudhari
  • 11,617
  • 18
  • 61
  • 74

1 Answers1

1

Since you are just using inner joins you normally simplify the query to smth like this:

SELECT  ve.name     AS Company
,       SUM(pr.cost) AS Revenue
FROM    products        pr
,       sold_products   sp
,       vendors         ve
,       markets         ma
,       sales_campaign  sc
,       packet_headers  ph
,       packet_details  pd
,       sales           sa
Where pr.productId = sp.productId
And   pr.vendorId  = ve.vendorId
And   ve.vendorId  = ma.vendorId
And   sc.marketId = ma.marketId
And   sc.packetHeaderId = ph.packetHeaderId
And   pd.packetHeaderId = ph.packetHeaderId)
And   sa.packetDetailsId = pd.packetDetailsId
And   ve.customerId = 60
GROUP BY ve.Company
ORDER BY pr.Revenue DESC;

Please try if this works and if it is faster and let me know.

Karl Heinz Matchinger
  • 24
  • 1
  • Heiz Matchinger: This is exactly what I was doing before, but the result was a completely wrong. This query would return `SUM(products.cost)` of ALL the products, whether they are sold or not.. – Hrishikesh Choudhari Nov 11 '12 at 13:24
  • have you checked if it is really ALL products or if its just SUMs a product multiple times?! with the join to sold_products it can not SUM all products! Look at your query without the SUM => what is the result? – Karl Heinz Matchinger Nov 11 '12 at 13:29
  • Yes it really is a SUM of ALL the products by that Vendor; not the SUM of just the ones that were sold.. – Hrishikesh Choudhari Nov 11 '12 at 13:33
  • Try to simplify for finding the bug: SELECT ve.name AS Company , SUM(pr.cost) AS Revenue FROM products pr , sold_products sp , vendors ve Where pr.productId = sp.productId And pr.vendorId = ve.vendorId And ve.customerId = 60 GROUP BY ve.Company ORDER BY pr.Revenue DESC; – Karl Heinz Matchinger Nov 11 '12 at 13:42
  • Wow that works perfectly! And instantaneously too!! Thanks :) – Hrishikesh Choudhari Nov 11 '12 at 14:06
  • Is this type of `WHERE` a good replacement for `INNER JOINS` that I was using? – Hrishikesh Choudhari Nov 11 '12 at 14:08