blists wrote:
>
> I'm still using SQL Server 7.0. Not sure about the index question and I do
> not have the execution plan, and the query changes all the time based on
> what the user selected.
So can you show the execution plan for a fast query and the
execution plan for a slow query?
> Select * from users
> Where
> (
> (badminton = 1 and baseball = 1 )
> )
How many rows does this return?
> Select * from users
> Where
> (
> (badminton = 1 and baseball = 1 )
> )
> and
> (
> userid in (
> select o.userid
> from orders o join orderdetails od on (o.orderid = od.orderid)
> where realdate BETWEEN '2005-04-01' AND '2006-04-30'
> )
> )
How many records does this return? And if you run just the
subquery, how many records does it return and how long does it take?
> I think I must be causing something wrong to happen when I add all the
> parenthesis. I guess the execution plan must have way to many comparisons.
The problem shouldn't be the number of comparisons, the problem
is getting the joins to be executed in the right order. I don't
know the cardinality of your tables or the selectivity of your
predicates, but I am guessing you get a table or index scan on
the users table filtered by badminton and baseball, and then for
every row in that result the database checks whether there are
entries in the orders and orderdetail tables. What you want is
that first the orders and orderdetails tables are joined and
filtered and then mergejoined against the users table. That way
instead of running the subquery for every matching row in the
users table, you run and sort the subquery only once.
3 options in order of desirability and expected result:
- smarter database / join order hinting
- rewrite as a join like Ryan showed
- rewrite the IN to the equivalent EXISTS:
AND EXISTS (
SELECT 1
FROM orders o JOIN orderdetails od ON (o.orderid = od.orderid)
WHERE realdate BETWEEN '2005-04-01' AND '2006-04-30'
AND o.userid = users.userid
)
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237905
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
