|
|
Posted by JayCallas on 11/03/05 16:20
This is more a theoretical question so I do not have any DDL (working)
to post.
Let's say that I have a query which needs to be filtered for specific
accounts while also needing several joins to retrieve additional data.
Is it better to so one big SELECT / JOIN / WHERE statement? As in
SELECT * FROM T1
JOIN T2 ON T2.[Col1] = T1.[Col1]
JOIN T3 ON T3.[Col2] = T1.[Col2] AND T3.[Col3] = T1.[Col3]
and so on...
WHERE T1.[Account] IN ('123', '456', '789')
OR is it better to do an inner SELECT / WHERE and pass that to a SELECT
/ JOIN? As in
SELECT * FROM
(
SELECT * FROM T1
WHERE T1.[Account] IN ('123', '456', '789')
) IT
JOIN T2 ON T2.[Col1] = IT.[Col1]
JOIN T3 ON T3.[Col2] = IT.[Col2] AND T3.[Col3] = IT.[Col3]
and so on...
First glance logic says that the inner select is the way to go since
the joins would have less rows to work with, as opposed to join
everything and THEN pulling out what is not needed. But the query
planner sometimes seems to have a mind of its own... Does it know that
rows will be pulled so it does that first? If I follow the same
"structure" with many different queries does in us the same logic all
the time or do I need to try the same thing for each and check it?
How does this apply to situations where there is a UNION involved? Do I
do the union and then apply WHERE and JOIN to filter out rows and get
additional data, respectively, or do I filter out rows inside the union
and take the combined set and do the JOINS?
SELECT * FROM
(
SELECT T1.[Col1], T1.[Col2] FROM T1
UNION ALL
SELECT T2.[Col1], T2.[Col2] FROM T2
) CT
JOIN T2 ON T2.[Col1] = CT.[Col1]
JOIN T3 ON T3.[Col2] = CT.[Col2] AND T3.[Col3] = CT.[Col3]
and so on...
WHERE CT.[Account] IN ('123', '456', '789')
versus
SELECT * FROM
(
SELECT T1.[Col1], T1.[Col2] FROM T1 WHERE T1.[Account] IN ('123',
'456', '789')
UNION ALL
SELECT T2.[Col1], T2.[Col2] FROM T2 WHERE T2.[Account] IN ('123',
'456', '789')
) CT
JOIN T2 ON T2.[Col1] = CT.[Col1]
JOIN T3 ON T3.[Col2] = CT.[Col2] AND T3.[Col3] = CT.[Col3]
and so on...
Navigation:
[Reply to this message]
|