Hi Ryan,
The only thing is, what I posted is only part of the query, and by itself
it runs fine, but once I combine it with additional statements it gets
really slow. The query is dynamically generated based on "filters" created
by the user, so it was easy to build the SQL using sub queries in order to
join each chunk together into one big statement without worrying about the
dependencies between the selected filters and the resulting query. However,
I guess this is not going to fly.
I just don't know how I can keep adding onto this query to accommodate
additional clauses. For example, if the user wanted to add to this example
and also only select users who have purchased from specific suppliers how
would I combine the query you did with:
Select *
from users
Where userid in (select o.userid
from orders o join orderdetails od on (o.orderid =
od.orderid)
where productid in (select product_id
from products
where supplier in
(9,2,1) ) )
And I guess, I could work out how to do that if I knew it would only be
those two statements but the user could configure this filter to add more
clauses in different orders with different operators etc. And so, I need to
be able to BUILD the SQL dynamically, which I guess is where it gets
difficult.
Brookd
p.s Sorry for the double post....
At 12:59 PM 4/16/2006, you wrote:
>Forgive me, I didnt read the entire post, but what seems to be the
>problem is you are doing a subselect when an inner join will work just
>fine.
>
>You want all users who have orders right?
>
>instead of
>
>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'
> )
>
>Try
>
>Select *
>from users u
>inner
>join orders o
>ON o.userid = u.userid
>inner
>JOIN orderdetails od
>ON (o.orderid = od.orderid)
>AND realdate BETWEEN '2005-04-01' AND '2006-04-30'
>
>This does all logic in the join conditions and should work just fine.
>
>The other thing you need to think about is the indexes you have on
>these tables. Without the proper indexes this query would still run
>slow. But with the proper indexes this should run without any
>problem.
>
>On 4/16/06, blists <[EMAIL PROTECTED]> wrote:
> > 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:237872
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
