Thread: Re: select taking forever

Re: select taking forever

From

Greg Stark

Date:

29 August 2003, 10:34:11

> > explain select * from ChargeCodes where AccountID =
> > '{58C215AA-2C71-446F-88F3-BC2C0D23EF28}' and ChargeCodeID IN   (Select
> > ChargeCodeID from Products where ProductID in     (select ProductID from
> > OrderRules where WebUserRoleID in         (Select WebUserRoleID from
> > WebUsers where WebUserID = '{3CD5D4F5-448B-11D5-83DB-0001023EA2FA}')))

You could try transforming this one to a normal join:

SELECT ChargeCodes.*
  FROM WebUsers
  JOIN OrderRules  ON (WebUserRoleID)
  JOIN Products    ON (ProductID)
  JOIN ChargeCodes ON (ChargeCodeID)
 WHERE AccountID = '{58C215AA-2C71-446F-88F3-BC2C0D23EF28}'
   AND WebUserID = '{3CD5D4F5-448B-11D5-83DB-0001023EA2FA}'

This is not 100% equivalent to the IN form. Specifically it will behave
differently by returning all the combinations that satisfy the
WebUser/AccountID clauses. Depending on your data model that may or may not be
a problem.

--
greg