Jochem,
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. Heres a better description of what I am doing:
I have a ecommerce store, and I am building an email system for the
administrator where they can build some filters to select the campaigns
recipients. I have 6 different filter types they can build and then they
can join them all together with AND/OR. So far, the only way I can get the
data I need is with subqueries and its really slow. I've been thinking how
I might use Queries of Queries to do this, but am not sure its possible.
The customer can build filters using different options like:
1. Where FIELD equals VALUE
This can be any field, a specific operator (eq,neq,gt,lt,in) and a value.
This one is pretty straightforward
2. Where customer has/has not purchased products from SUPPLIER
Customer selected from a list of suppliers
3. Where customer has/has not purchased within TIMEFRAME
Customer enters start date and end date
4. Where customers interest include INTERESTS
Customer selected from a list of interests
There are a couple of more aswell. Like where the customer has purchased
products that belong to a specific sport or category, purchase volume etc.
They can create each of these filters and then join them together with
AND/OR. So I am dynamically generating the SQL string for each filter and
wrapping each one in parenthesis and then joining each clause with the
AND/OR. For example, if the user created a "interests" filter and selected
a couple of interests, the query would be built like this:
Select * from users
Where
(
(badminton = 1 and baseball = 1 )
)
This executed fine, however, if they then added a "timeframe' filter, it
will kill the server and take forever. If I run either one by itself it is
fine - but together they take too long. Here is the second query:
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'
)
)
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.
And I know this is kind of a sloppy way to write this, but it greatly
simplified combining all of the different types of filters and combinations
into a query. This query does not need to be blazing fast and is only going
to be used to send email campaigns once and a while. But I need to find a
way to improve its efficiency while still being able to build the query
dynamically...
BrookD
At 01:18 PM 4/16/2006, you wrote:
>blists said:
> > 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
>
>You will need to EXPLAIN it for us :)
>
>
>Can you show us the execution plan for your query?
>
>
> > 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!
>
>Which SQL server are you using and is it capable of using multiple
>indexes in one scan?
>
>Jochem
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237873
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
