|
|
Posted by Emin on 01/12/07 19:32
Dear Experts,
Thanks for the comments so far. To help describe the problem, I
constructed a stand alone example that illustrates the issue on MS SQL
Server 2005. If you run the following query, it will take a very long
time. But if you replace "FULL OUTER" with "INNER" or if you remove the
WHERE clause, the query runs fine. Any ideas why FULL OUTER JOIN blows
things up even though there are no NULLs in sight?
Thanks,
-Emin
----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.temp'))
DROP TABLE dbo.temp
;with TallyTableCTE(i)
as
(
select i = {ts '1970-01-01 00:00:00'}
union all
select i = i + 1 from TallyTableCTE where i < {ts '2007-01-01
00:00:00'}
)
,AnotherTallyTableCTE(j)
as
(
select j = {ts '1970-01-01 00:00:00'}
union all
select j = j + 1 from AnotherTallyTableCTE where j < {ts '2007-01-01
00:00:00'}
)
select *
into dbo.temp FROM
TallyTableCTE x
FULL OUTER JOIN --FULL OUTER JOIN with WHERE clause causes problem
--INNER JOIN --using INNER JOIN instead of FULL OUTER JOIN removes
problem
AnotherTallyTableCTE y
ON x.i = y.j
where x.i > {ts '1980-01-01 00:00:00'} --removing WHERE clause removes
problem
option (maxrecursion 30000)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.temp'))
DROP TABLE dbo.temp
--------------------------------
On Jan 12, 11:14 am, "Emin" <emin.shop...@gmail.com> 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]
|