Okay, my SQL Server is at 100% and this query is taking like 40 seconds or
something ridiculous like that and its not even full size yet. I need some
help!
Without explaining how this entire thing works, let me tell you that its a
dynamically generated query, where the user creates the clause and adds the
AND/OR operator. My query works when run by itself like this
Select *
from users
where 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'
)
This query gets all users who have orders in the orders table between two
dates. This runs fine. Now, since the end user is building these queries,
there might be another part to this query. Like this
Select *
from users
Where
(
(badminton = 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'
)
)
This query takes forever to execute, and the user can still add many more
statements to this! The reason this query "looks" strange is because I am
building each section of the query seperatly (and me thinks this might be
what is causing the query to take soe long), so the first part is this:
(
(badminton = 1 )
)
and the second part is
(
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'
)
)
Because each chunk is self contained, I can easily build a large query and
throw the user defined AND/OR between each chunk to arrive at the final
query. But I guess there must be something inherently wrong with using all
this parenthesis eh?
If any SQL Gurus have some advise about what the heck I am causing to
happen on the SQL Server I would love to know. I must be forcing it to do
way more work than is nesscary. But I don't know how to avoid it because I
am building these queries dynamically. Thanks for the help!
Brookd
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237869
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=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
