0

To score the price preference of our individual customers in, we run the below query.

Basically it is a weighted average of a product score. The score depends on the product group and the packaging type. The weight depends on the product type and the customer type.

SELECT SUBQRY.customerNo,
       SUM(WGT.weight * SUBQRY.avg_score) / SUM(WGT.weight) AS score
FROM
  (SELECT SALE.customerNo,
          SALE.ProductType,
          SALE.CustomerType,
          AVG(SCORE.score) AS avg_score
   FROM mySchema.Retail_sales AS SALE
   LEFT JOIN mySchema.product_scores AS SCORE 
        ON (SALE.ProductGroup = SCORE.ProductGroup AND 
          SALE.Packaging = SCORE.Packaging)
   GROUP BY SALE.customerNo,
            SALE.ProductType,
            SALE.CustomerType
   ) AS SUBQRY
LEFT JOIN mySchema.product_SOW AS WGT 
        ON (SUBQRY.CustomerType = WGT.CustomerType AND 
          SUBQRY.ProductType = WGT.ProductType)
GROUP BY SUBQRY.customerNo
ORDER BY SUBQRY.customerNo

We have two reatail chanes, one with 4 times as much sales and product Groups (16k vs 4k) as the other. How can I explain the larger one takes 300 times more time to execute and how can I cure that?

PS: We currently have no indexes on tables within mySchema. Creating an index on mySchema.product_scores.ProductGroup does hardly help.

  • Not all relationships are linear! Provide an EXPLAIN PLAN of your query on both servers. For an example of this phenomenon, (weight) vs. (damage to road surface) - this is a relationship of 1 - 1 * 10^5 - not what you might intuitively expect? – Vérace Jul 19 '16 at 15:43
0

You need to provide more information for us to be sure: the table definitions including all the indexes and other keys defined upon them, if any of the referenced objects are views rather then tables then their definitions too and those of their dependant objects, and the query plan (I'm assuming sybase can be asked to present you with that as easily as other DBMSs).

A super-linear growth in effort compared to data size usually means that the database is having to scan or partial scan one object for every row output from another. Another reason for unexpected time for lager data is that the data was previously small enough to all be processed in memory but with the new size the database is having to either read more from disc (this compounds very badly with having to scan something multiple times) or spool intermediate results out to disc or both.

We could hazard some guesses, but if you edit the question to include more info (query plan and table/index/key/view definitions) we can make much better suggestions with less guess work (and tell you have to interpret that information yourself if the same problem comes up elsewhere in future).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.