bad execution plan ???

Question

bad execution plan ???

virgilrucsandescu

SSCrazy

Points: 2457
More actions
August 20, 2007 at 12:35 pm

#153759

hi everybody!
I have a query like:
SELECT some_fields FROM t1
LEFT OUTER JOIN t2
on t1.f1=t2.f1 and t1.f2=t2.f2
LEFT OUTER JOIN t3
on t1.f1=t3.f1 and t1.f2=t3.f2
...............
LEFT OUTER JOIN t10
on t1.f1=tn.f1 and t1.f2=t10.f2
WHERE t1.ClientID = value
(field1 in any table = ClientID)
Some fields = means a few fields from table t1 and one -two fields form t2-t10
t1-t5 = are tables having ~630 rows (t1=628 rows)
t6-t10 = are non-indexed views having ~630 rows (t1=628 rows)
The query returns 628 rows (number of rows in table t1)
Size of a row is ~ 1.5 KB/row
The execution time of the query is 1 min 20 s.
But :
4 hash matches (t1&t2, t1&t3, t1&t4, t1&t5) take 80% of the time (4*20%=80%)
5 merge joins take the rest of the 20%
(funny - the tables have the right indexes, thing I can't say about the views - they don't have any - I can't use SCEMABINDING...)
All tables involved are pretty big - having in total ~ 100.000 rows - I suppose SQL does first all the joins,
and at the end filters everything based by "WHERE t1.ClientID = value" clause ...
(this is the only explanation I can find for the huge timings for hash matches...), so it sounds like a bad execution plan.
Is there any way to filter first all the tables and after make all the joins ???
I tried:
SELECT some_fields FROM t1
LEFT OUTER JOIN t2
on t1.ClientID = value AND t1.f1=t2.f1 and t1.f2=t2.f2
LEFT OUTER JOIN t3
on t1.f1=t3.f1 and t1.f2=t3.f2
...............
LEFT OUTER JOIN t10
on t1.f1=tn.f1 and t1.f2=t10.f2
but I got the exact same timings ...
Note = Im using SQL Server 2000 Standard Edition SP3a
Q2 The problem described at http://support.microsoft.com/kb/818335/EN-US/ could be the reason for this slowliness ???
Thanks everybody for any help!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

100,000 rows is tiny, and if the desired indexes are indeed all there, it's going to be hard to guess at the problem without more detail. Initially, the query itself (and is this ad hoc, or a stored proc?), as well as the code for the view would give us a starting point.

As for that link, unless your view has a correlated subquery (not unheard of, but not that common in most places I've been), I don't think it's applicable.

Lowell SSC Guru Points: 323518 More actions · Answer 2

OK based on your post,i think this query has 10 left outer joins altogether;

I think I saw Jeff Moden mention that left outer joins require a table scan, right?

that would slow it down some...can you check if you can change any of the joins to inner joins instead?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Since all tables (t2..tn) join on the same columns, try to rewrite them as an UNION instead.

SELECT <Col List Here>

FROM t1

LEFT JOIN (

SELECT f1, f2 FROM t2 UNION ALL

SELECT f1, f2 FROM t3 UNION ALL

SELECT f1, f2 FROM tn

&nbsp AS t ON t.f1 = t1.f1 AND t.f2 = t1.f2

WHERE t1.ClientID = @Value

N 56°04'39.16"
E 12°55'05.25"

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Tables t2-t10 should have clustered index on first column f1 and second column f2.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Dollars to donuts the views are aggragate views that either return to many columns or might even have views of views or UDF's.

Also, any of these tables have a primary key? As Serqiy suggests, is there clustered index on the tables? If so, what is it?

And I gotta ask, does SELECT somefields have any * in it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

noeld SSC Guru Points: 96590 More actions · Answer 6

Been there before ...

views of views of views of .... --- Usually a very BAD Idea!

Just my $0.02

* Noel

virgilrucsandescu SSCrazy Points: 2457 More actions · Answer 7

the query is an ad-hoc query, not a stored procedure.

same_fields is something like t1.f1, t2.f2, t3.f3, ..., t10.f10

(it doesn't contain * ... 🙂 and UNION solution is not working too)

The tables t1..t5 contain all clustered indexes based on the f1,f2

The views don't contain calls to other views.

Because the database is in a continuous changing, SCHEMABINDING cannot be used, so I can't use indexed views - but the performance deterioration is not because the views (that part takes only 20% of the total time), something is wrong with the table joining ... And I am sure all the tables have the right indexes at their places ....

I haven't knew that LEFT OUTER JOIN's force a table scan , this could really be the cause of all this issue ... Could you give me please a link for a more detailed explanation on this topic ? Please ...

Is it OK to post here the full code of query / views and the execution plan ? If so, I will do it tomorrow (they are in fact quite big, the blueprint I posted is just a very short version ..)