again and again... query analyzer and wrong SQL statement plan... + outer join?

Hi,
again we have some SQL statement performance problems...
I've 2 table with 140 000 records each.
I join the 2 tables and the result is also 140 000, but the query analyzer
estimate 1000 rows only.
Its a simple join with 5 equals in the join
select * from tableA inner join table B on A.ID1 = B.ID2.....
all the stats for my tables are ok.
its not a so big problem, but when I want to estimate before an execution
and when the result is far different, its a big surprise for me!
Also, we have aproblem with a query which use 1 inner join and 3 left outer
join.
Each outer join is a sub-query which contain a group by:
select *from A inner join B on A.ID = B.ID
left outer join (Select ID1, ID2 from C group by ID1, ID2) CC
on A.ID1 = CC.ID1 and A.ID2 = CC.ID2
...
(the 2 others outer join use the same syntax)
This query takes more then 10 minutes, but if I precalculate each sub-query
in a temporary table and if I use these tables instead-of the sub-queries,
the result appear in only 19seconds!!!!!
how can I tell SQL Server to use the same way without creating myself the
temporary tables?
there is any table hint option to force SQL Server to treat the sub queries
has "physical tables".
I know its not the first time you have this type of questions... so thanks
for your time (again)
Jerome.create an Index for those 5 columns on each table.. But only those 5 columns
ll be used..
And let SQL estimate the proper Index for the execution
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:uX1Ih0htEHA.3604@.TK2MSFTNGP10.phx.gbl...
> Hi,
> again we have some SQL statement performance problems...
> I've 2 table with 140 000 records each.
> I join the 2 tables and the result is also 140 000, but the query analyzer
> estimate 1000 rows only.
> Its a simple join with 5 equals in the join
> select * from tableA inner join table B on A.ID1 = B.ID2.....
> all the stats for my tables are ok.
> its not a so big problem, but when I want to estimate before an execution
> and when the result is far different, its a big surprise for me!
> Also, we have aproblem with a query which use 1 inner join and 3 left
outer
> join.
> Each outer join is a sub-query which contain a group by:
> select *from A inner join B on A.ID = B.ID
> left outer join (Select ID1, ID2 from C group by ID1, ID2) CC
> on A.ID1 = CC.ID1 and A.ID2 = CC.ID2
> ...
> (the 2 others outer join use the same syntax)
> This query takes more then 10 minutes, but if I precalculate each
sub-query
> in a temporary table and if I use these tables instead-of the sub-queries,
> the result appear in only 19seconds!!!!!
> how can I tell SQL Server to use the same way without creating myself the
> temporary tables?
> there is any table hint option to force SQL Server to treat the sub
queries
> has "physical tables".
> I know its not the first time you have this type of questions... so thanks
> for your time (again)
> Jerome.
>