Why is a simple WHERE clause so slow?
- From: "Emin" <emin.shopper@xxxxxxxxx>
- Date: 12 Jan 2007 08:14:52 -0800
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
.
- Follow-Ups:
- Re: Why is a simple WHERE clause so slow?
- From: othellomy
- Re: Why is a simple WHERE clause so slow?
- From: Emin
- Re: Why is a simple WHERE clause so slow?
- From: Gert-Jan Strik
- Re: Why is a simple WHERE clause so slow?
- From: schal
- Re: Why is a simple WHERE clause so slow?
- From: Alex Kuznetsov
- Re: Why is a simple WHERE clause so slow?
- Prev by Date: Re: How can I modify a report in Crystal Report 7
- Next by Date: Re: Why is a simple WHERE clause so slow?
- Previous by thread: 2005 Copy database objects from one server to another
- Next by thread: Re: Why is a simple WHERE clause so slow?
- Index(es):