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

From: Jéjé (willgart_at__A_hAotmail_A_.com)
Date: 10/19/04 

Date: Tue, 19 Oct 2004 16:51:14 -0400

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.

Relevant Pages

  • RE: Jet/Query editor destroys query...
    ... Oh, just to clarify, the auto-generated name for the sub-query was: ... Interestingly, my original SQL: ... within a double-quoted string literal. ... If I simply saved the query string (by directly assigning the string to the ...
    (microsoft.public.access.queries)
  • Re: Query with left outer join all of a sudden wont work?
    ... just doesn't work if it is a left or right outer join. ... rather than on the top most query calling it). ... SELECT * FROM [qryFarmer Contracts] ... can you post the whole SQL statement? ...
    (microsoft.public.access.queries)
  • Re: Need help to modify my query
    ... This is not very well-suited to SQL, ... Here's a query that will produce this report - it works ... then uses an outer join, since there may be different numbers ... left outer join S_usergroup as sug ...
    (microsoft.public.sqlserver.programming)
  • Re: Using q() to define a query
    ... I beleive most SQL parsers will ignore white space like that in an SQL query. ... FULL OUTER JOIN jsequipment AS eq ON jsjourneys.equipment = eq.id ... If those were really in the query, I can't imagine the database would run it, so I suppose they're an artifact of the combination of using qto quote my query and using Apache's logger to output it. ...
    (perl.dbi.users)
  • Re: Cartesian product & outer join
    ... An Ambiguous Outer Join basically means that your arrows are pointing the ... My suggestion to most query problems is to not try to do it in just one ... >> Post your SQL. ... >>> time with overtime time categories and multiplies by civilian overtime ...
    (microsoft.public.access.queries)
Quantcast