October 21, 2010 at 9:29 am
This is a general/conceptual question (with practical applications for me) for a problem that seems to keep cropping up, on more than one database, and now critically for my business.
For illustrative purposes, I have 4 tables in a query, tables A, B, & C are the "main" tables and table L is a "lookup" table.
I cannot change the table structures, they are proprietary.
The problem is, when I script a statement along the lines of
select
<Columns from A, B & C>,
L1.Value,
L2.Value,
L3.Value,
L4.Value,
... etc ...
from
<A, B & C inner joined>
left join L L1
on L1.Code1 = A.Code1
left join L L2
on L2.Code3 = A.Code3
left join L L3
on L3.Code7 = B.Code7
left join L L4
on L4.Code12 = C.Code12
... etc ...
where
<various criteria on A, B & C>
it runs a lot slower than running the likes of
select
<Columns from A, B & C>,
A.Code1,
A.Code3,
B.Code7,
C.Code12
... etc ...
into #t
from
<A, B & C inner joined>
where
<various criteria on A, B & C>
GO
select
T.*,
L1.Value,
L2.Value,
L3.Value,
L4.Value,
... etc ...
from
#t T
left join L L1
on L1.Code1 = T.Code1
left join L L2
on L2.Code3 = T.Code3
left join L L3
on L3.Code7 = T.Code7
left join L L4
on L4.Code12 = T.Code12
... etc ...
The issue is I cannot use temporary tables etc - it needs to be a single statement.
I can't seem to simulate the effect of evaluating the joins on A,B & C first, and then applying the left join lookups, in a single statement.
I have tried:
(a) sticking the WHERE criteria after the FROM as inner join ON search conditions and adding OPTION (FORCE ORDER)
(b) changing each left join L into a subquery in the main select statement instead of a left join
(c) playing with join hints (too numerous to mention all of them)
None of the above made it faster some (especially (c) not surprisingly) made matters worse.
The only thing I haven't tried yet is to run each query seperately for each L and bolt them together as a (distinct) UNION, mostly because it's ugly as hell and feels like the "wrong" answer.
Any thoughts very much appreciated.
October 21, 2010 at 2:17 pm
there is no doubt that breaking up a larger query into two smaller queries via a temp table can make a big improvement. You say you cannot use temp tables... have you tried creating a CTE (common table expression) out of the first 3 inner joined tables first then using that in the subsequent query?
One other option is to create a virtual table out of the first three then join the results back from that. I have no idea whether either would give you better performance but you could try it.
The probability of survival is inversely proportional to the angle of arrival.
October 22, 2010 at 2:02 am
sturner (10/21/2010)
there is no doubt that breaking up a larger query into two smaller queries via a temp table can make a big improvement. You say you cannot use temp tables... have you tried creating a CTE (common table expression) out of the first 3 inner joined tables first then using that in the subsequent query?One other option is to create a virtual table out of the first three then join the results back from that. I have no idea whether either would give you better performance but you could try it.
sorry yes, I meant to add "(d) chuck the A + B + C chunk into a CTE and join the L's on that". The optimiser simply applies the same execution plan as if I hadn't.
In any case, one of the reasons it has to be a single statement is in some cases these are MS CRM Filtered reports and the Pre-filtering engine can't handle stored proc's or (as far as I can tell so far) CTEs; even complex single-statement queries (including unions) you have to jump through a few hoops to persuade it to take.
I'll try the Virtual table (did you mean derived table?) idea, but I suspect the optimiser will unravel it.
Cheers for the ideas.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply