I have some rather complicated tSQL queries I need to join together so I can dump it to a CSV which is imported into a different database (MySQL). To that end, I have created a bunch of smaller table views that I join into a single view, which will give me my data in the order needed. But what I find odd is that while each of the smaller views take less than a second or two to execute, the consolidated view takes forever (it times out in SMS). I assume there's a problem with the way I've designed it, but I'm not sure what it is. As a non-tSQL person, I'm not even sure I'm joining the tables efficiently.Should I be writing my WHERE clause into each subquery (they are all identical) or should I use a single WHERE clause in my consolidated query?Here is a simplified version of my join scheme:## View1...SELECT join_field, a, b, c FROM table1 WHERE condition1 AND condition2 AND condition3 AND condition4## View2...SELECT join_field, d, e, f FROM table2 WHERE condition1 AND condition2 AND condition3 AND condition4## View3...SELECT join_field, g, h FROM table3 WHERE condition1 AND condition2 AND condition3 AND condition4## View4...SELECT join_field, i FROM table4 WHERE condition1 AND condition2 AND condition3 AND condition4## Main query...SELECT v1.a, v1.b, v1.c, v2.d, v2.e, v2.f, v3.g, v3.h, v4.iFROM view1 AS v1LEFT OUTER JOIN view2 AS v2 ON v1.join_field = v2.join_fieldLEFT OUTER JOIN view3 AS v3 ON v1.join_field = v3.join_fieldLEFT OUTER JOIN view4 AS v4 ON v1.join_field = v4.join_field
Can anyone give me some tips? Is my thinking way off here?(I can dump all the queries here if needed but it would be 200 lines long, formatted.)
Need help optimizing query/subqueries
Posted - 2009-09-24 : 18:38:25
