-1

ORDER BY slows my query to a crawl.

While digging through StackOverflow trying to fix this, I've found several references to wrapping one's query with 'SELECT *'. For some unfathomable reason, this ALSO slows my query to a crawl.

I don't understand how wrapping my query this way should have any effect. Shouldn't SELECT * FROM (QUERY) be identical to QUERY?

This is my query:

SELECT W.NDB_No, Seq, Gm_Wgt*Nutr_Val/100
    FROM WEIGHT AS W,
    (SELECT NDB_No, Nutr_No FROM FOOD_DES, NUT ORDER BY nutrEnum) AS A
    LEFT JOIN 
    NUT_DATA AS B 
    ON A.NDB_No = B.NDB_No AND A.Nutr_No = B.Nutr_No;

It takes 0.8 seconds. Wrapping this query with SELECT * FROM (...) AS X slows the query down tremendously. What is going on here? Also, any help for getting ORDER BY to work would be greatly appreciated (probably a related problem). Please see SQL Fiddle here.

Community
  • 1
  • 1
ishmael
  • 1,796
  • 3
  • 18
  • 19
  • 1
    There's no `ORDER BY` in this query, is there? – tadman Sep 05 '13 at 15:36
  • 6
    as always - check the EXPLAIN PLAN – Randy Sep 05 '13 at 15:37
  • @tadman: No, not yet there isn't, but that's where I'm going with this. For now, I'm baffled by why wrapping the query with SELECT * changes the speed at all. I mean, it's the same query! – ishmael Sep 05 '13 at 15:40
  • Why do you want to wrap your query that way? You should only do that if you have no other choice. It will always be slower as it runs the original query and then runs the wrapper over it. Please, add your table with sample data and your expected output too. – Mosty Mostacho Sep 05 '13 at 15:41
  • @Randy: Thanks, I'm currently trying to interpret the results from EXPLAIN. I'll post them. – ishmael Sep 05 '13 at 15:43
  • @MostyMostacho: I'm just madly trying everything I can think of to get ORDER BY to work. The idea for wrapping with SELECT * came from the post I referenced. – ishmael Sep 05 '13 at 15:46
  • How are you joining the subquery? `FROM FOOD_DES, NUT` without a WHERE or JOIN means you're going to get a result set that is the product of both tables. – Andy Lester Sep 05 '13 at 15:46
  • So, you aren't trying to improve that query but rather trying to order a query that you haven't actually provided. First, provide that query. Then tell us how you need that data ordered and what error you're getting. There is no need to go for an EXPLAIN PLAN but rather for an EXPLAIN QUESTION :) – Mosty Mostacho Sep 05 '13 at 15:49
  • @MostyMostacho: The end goal is to add: ORDER BY NDB_No, Seq. There is no error, it's just excruciatingly slow. – ishmael Sep 05 '13 at 15:55
  • 1
    This really smells like bad query design which results in bad query performance. I bet this is a pretty simple query but without sample data and expected output it is impossible to understand what you're trying to do with that query. If you also provide a [fiddle](http://sqlfiddle.com) I bet this will be solved in a matter of minutes – Mosty Mostacho Sep 05 '13 at 16:03
  • 1
    Remove the inner ORDER BY. And consider whether a CROSS JOIN is really what you want. – Strawberry Sep 05 '13 at 16:12
  • Which tables (in the subquery) do NDB_No and Nutr_No come from? – Strawberry Sep 05 '13 at 16:15
  • @MostyMostacho: Ok, I've added some sample data to Fiddle. See link in my post. – ishmael Sep 05 '13 at 16:55
  • @Strawberry: I can't remove the inner ORDER BY because that ensures that my data is ordered correctly. Also, I think I'm using LEFT JOIN, not CROSS JOIN, unless I'm missing something. Also, please take a look at the Fiddle sample database I linked to. – ishmael Sep 05 '13 at 17:12
  • "FROM FOOD_DES, NUT " -- this is a CROSS JOIN – Strawberry Sep 05 '13 at 17:19
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/36885/discussion-between-ishmael-and-strawberry) – ishmael Sep 05 '13 at 17:24

1 Answers1

0

This seems like the same result albeit in a slightly different order (because the ordering requirements are under-specified) http://sqlfiddle.com/#!2/69972/7

Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • Thanks, Strawberry. This seems promising, but it's still very slow when I add ORDER BY. Maybe some index is missing? – ishmael Sep 05 '13 at 17:42
  • Nut_data is missing a PK and weight appears to have 4 times as many keys as it needs - the PK should suffice. Apart from that, we still have the CROSS JOIN, which can't be great for performance. – Strawberry Sep 05 '13 at 17:48