1

The query has around 40k rows taken generally from a cached query. For whatever reason the QoQ is just SLOW. I have tried to remove most of the logic (distinct, grouping etc) to no avail which leads me to believe something is wrong in the settings. Anybody have an idea about what is going on and how to speed this up?

subcats (Datasource=, Time=42979ms, Records=14)

            SELECT 
                DISTINCT(SNGP.subtyp1) AS cat,
                MIN(SNGP.sortposition) AS sortposition,
                MIN(taxonomy.web_url) AS url
            FROM
                SNGP,
                taxonomy
            WHERE
                SNGP.typ > ''
                AND UPPER(SNGP.typ) <> 'EMPTY'
                 AND UPPER(SNGP.DEPT) = 'SHOES' AND UPPER(SNGP.TYP) = 'FASHION' AND SNGP.SUBTYP1 <> 'EMPTY'
            GROUP BY SNGP.subtyp1
            ORDER BY SNGP.sortposition ASC
Magic Lasso
  • 1,504
  • 5
  • 22
  • 29
  • 1
    In order to debug I would begin breaking down your query to see if you can determine which part is taking the time. Start by removing the `GROUP BY` clause and re-run. Then remove the `ORDER BY` clause and re-run. Then start removing conditions from the `WHERE` clause one-by-one until you see a noticeable time difference. At least you will know what is causing the delay and then perhaps can come up with a better method of doing that part. – Miguel-F Jul 03 '13 at 15:53
  • 2
    Q of Q on large datasets are slow. That's just the way it is. Maybe there is another way for you to achieve your aim. – Dan Bracuk Jul 03 '13 at 16:05
  • He seems to be within the limits of QoQ. From the [docs](http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff9.html#WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff8): _A Query of Queries is ideal for tables of 5,000 to 50,000 rows, and is limited only by the memory of the ColdFusion host computer._ – Miguel-F Jul 03 '13 at 16:09
  • The site is hosted on a terrible server, memory may very well be the problem. – Magic Lasso Jul 03 '13 at 16:19
  • 1
    "The query has around 40k rows"... right... so this sort of data processing ought to be done back in the DB, right? Not in CF's memory using a pretty basic SQL-processing engine. Even if it worked really quickly you still oughtn't be doing this in CF, I reckon. – Adam Cameron Jul 03 '13 at 16:23
  • could you provide a sample row from the original query? i'm wondering if the amount of data in each row has anything to do with it. – Russ Jul 03 '13 at 21:45
  • This is a cross join with a distinct. Not a good idea. You will need to filter out more records in your where to create a join of some sort. Sometimes you need to be creative with multiple query of query. – J.T. Jul 04 '13 at 18:37

1 Answers1

0

  1. Do you have to do a QoQ; could your original query be amended to give you the data you need? Could you even cache all the possible QoQs you're doing, on a schedule?

  2. You're selecting from two tables (SNGP,taxonomy), but I don't see a join between them

  3. web_url sounds like a string, why are you doing a MIN() on it?

  4. In your WHERE clause move the most restrictive parts of that first. e.g. if typ > '' restricts the results to 1000 rows, but UPPER(SNGP.typ) <> 'EMPTY' would restrict it to just 100 rows, then you should put that first. This is general SQL advice, not sure how well it works with QoQ.

  5. 40k rows to then select just 14 results sounds like quite a data mismatch; is there any other way you'd be able to get the data more restricted before you try your QoQ.

duncan
  • 31,401
  • 13
  • 78
  • 99
  • I was trying to get a better result set but the issue is this was from a series of product filters. Unfortunately QoQ in Coldfusion doesnt have join functionality. MIN is there purely to avoid having to group by with using distinct. As for number 4, I had no idea WHERE worked that way, thats nifty information. Good suggestions but I think our overall database design is just archaic. It really just needs far more normalization so we can make better use of indexes. Thanks for the suggestions. – Magic Lasso Jul 03 '13 at 16:18
  • 1
    You *can* join in QoQ: SELECT cols FROM tbl1 T1, tbl2 T2 WHERE T1.PK = T2.FK – Adam Cameron Jul 03 '13 at 16:24
  • QoQ does support _some_ join functionality. From the [docs](http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html#WSc3ff6d0ea77859461172e0811cbec0e4fd-7fee): _Query of Queries supports joins through a WHERE clause._ _Query of Queries does not support joins through INNER JOIN or OUTER JOIN clauses._ _**Note:** Query of Queries supports joins between two tables only._ – Miguel-F Jul 03 '13 at 16:25