|
|
Posted by Gert-Jan Strik on 01/12/07 19:59
Does your real situation actually resemble this repro?
In the repro, it is the difference between a hash join and loop join.
The hash join is fast, the loop join is not. It is not suprising that
the loop join is not very fast, because there is quite a lot of
calculation needed for the CTE.
If this really is your problem, then you can probably add OPTION(HASH
JOIN) to eleviate the performance problems.
But if you are not actually joining two CTE's with deep recursion, then
we probably need a more real life example.
Gert-Jan
Emin wrote:
>
> 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
Navigation:
[Reply to this message]
|