It probably will not optimize

My experience suggests that complex queries without group bys can be optimized. But as soon as you add a group by, that level of nesting becomes "opaque" to the query analyzer which then does the whole group by only to filter later.

The workaround that I've used for that is to stick inner inner queries in that create a condition that greatly reduces the size of that subselect. As you might imagine, this makes queries rather heinous with lots of duplicated information, so when I do this I typically also switch over to writing code to generate the SQL.

In fact that's usually how I handle complex queries. It is a lot easier to read code that produces SQL, and it is far easier to edit. With the query that I wrote above I might write something like this:

\nmy $formularyid_by_drugid_sql = qq{\n  SELECT MIN(formularyid) AS formularyid\n  FROM formulary\n  GROUP BY drugid\n};\n\nmy $full_formulary_by_drugid_sql = qq{\n  SELECT f.*\n  FROM\n    formulary f\n    INNER JOIN (}\n      . indent($formularyid_by_drugid_sql)\n      . qq{    ) top\n    ON f.formularyid = top.formularyid\n};\n\nmy $drug_and_formulary_isql = qq{\n  SELECT *\n  FROM\n    drug d\n    LEFT OUTER JOIN (}\n      . indent($full_formulary_by_drugid_sql)\n      . qq{    ) f\n    ON d.drugid = f.drugid\n};\n

And now I find the structure of the query somewht easier to interpret.

BTW this wasn't the best example of the technique. It makes more of a difference when you have multiple subqueries and/or interesting WHERE conditions on some subqueries.

Cheers,
Ben

SELECT *\nFROM\n drug d\n LEFT OUTER JOIN formulary f ON (f.drugid = d.drugid)\nWHERE\n ((f.formularyid IS NULL) OR\n (f.formularyid = (\n SELECT MIN(forumularyid)\n FROM formulary x\n WHERE (x.drugid = d.drugid))))

Post #258,163 by ben_tilly 6/8/06 1:27:27 PM Reply	It probably will not optimize My experience suggests that complex queries without group bys can be optimized. But as soon as you add a group by, that level of nesting becomes "opaque" to the query analyzer which then does the whole group by only to filter later. The workaround that I've used for that is to stick inner inner queries in that create a condition that greatly reduces the size of that subselect. As you might imagine, this makes queries rather heinous with lots of duplicated information, so when I do this I typically also switch over to writing code to generate the SQL. In fact that's usually how I handle complex queries. It is a lot easier to read code that produces SQL, and it is far easier to edit. With the query that I wrote above I might write something like this: \nmy $formularyid_by_drugid_sql = qq{\n SELECT MIN(formularyid) AS formularyid\n FROM formulary\n GROUP BY drugid\n};\n\nmy $full_formulary_by_drugid_sql = qq{\n SELECT f.\n FROM\n formulary f\n INNER JOIN (}\n . indent($formularyid_by_drugid_sql)\n . qq{ ) top\n ON f.formularyid = top.formularyid\n};\n\nmy $drug_and_formulary_isql = qq{\n SELECT \n FROM\n drug d\n LEFT OUTER JOIN (}\n . indent($full_formulary_by_drugid_sql)\n . qq{ ) f\n ON d.drugid = f.drugid\n};\n And now I find the structure of the query somewht easier to interpret. BTW this wasn't the best example of the technique. It makes more of a difference when you have multiple subqueries and/or interesting WHERE conditions on some subqueries. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #258,171 by ChrisR 6/8/06 2:00:51 PM Reply	Yep, took forever to run. But I did end up figuring out a SS solution using the min function (in non-SQL-standard sort of fashion). SELECT *\nFROM\n drug d\n LEFT OUTER JOIN formulary f ON (f.drugid = d.drugid)\nWHERE\n ((f.formularyid IS NULL) OR\n (f.formularyid = (\n SELECT MIN(forumularyid)\n FROM formulary x\n WHERE (x.drugid = d.drugid))))
Post #258,187 by drewk 6/8/06 2:40:14 PM Reply	Nothing non-standard about that In fact, that's the solution I'd like to think I'd have come up with if I'd worked on it long enough. === Purveyor of Doc Hope's [link\|http://DocHope.com\|fresh-baked dog biscuits and pet treats]. [link\|http://DocHope.com\|http://DocHope.com]

Welcome to IWETHEY!