Posted by schal on 01/12/07 17:05

Emin,
You gotta to be more detailed in the Q like wht data is in the tables.
For a query running slow, there could be n number of reasons. most
imortant one is having no indexes.

your query is running slower, cuz your innerQ is not effected by the
where condition, its outside the scope of the Full outer join, which is
obviously the most resourse intensive in the query.

try Rewriting the query like this:
SELECT *
FROM (
Select x.event_date
From #X x
FULL OUTER JOIN #Y y ON x.event_date = y.event_date
WHERE x.event_date >= '1980-01-01 00:00:00'
) innerQ

BTW, what is the purpose of writing the where condition like this:
WHERE ( innerQ.event_date >= {ts '1980-01-01 00:00:00'} )
Vs like this:
WHERE x.event_date >= '1980-01-01 00:00:00'

Does this give any specific advantage and what does it do?
Thanks, schal.

Emin wrote:
> Dear Experts,
>
> I have a fairly simple query in which adding a where clause slows
> things down by at least a factor of 100. The following is the slow
> version of the query
>
> -------------------------
> SELECT * FROM
> ( Select x.event_date From x FULL OUTER JOIN y
> ON x.event_date = y.event_date
> ) innerQ
> WHERE ( innerQ.event_date >= {ts '1980-01-01 00:00:00'} )
> ------------------------
>
> Removing the where clause makes the query run quickly. This seems
> extremely strange because it seems like SQL Server should simply be
> able to take the results of innerQ and discard anything with a date
> that doesn't match. If I instead split the query into two pieces where
> I create a temp table and put innerQ into that and then do the select *
> WHERE (...) from the temp table things work fine.
>
> Any thoughts on what SQL Server might be doing to make things slow and
> how I can fix it?
>
> Thanks,
> -Emin

[Back to original message]