There are lots of different ways to speed things up. Heh.
First off, make sure your tables all have indexes on the columns you will be sorting / joining. That alone can speed things up a GREAT deal. Second, you could grab everything from users, and use "query of queries" to run any futher queries. At this point the results are in memory, and that's faster than a trip to the DB. Third, there are tools built right into the database for showing you what EXACTLY is going on with a query. Depends on the DB, but you can do a DESCRIBE SELECT blah.blah FROM (I think) and it will spill the beans. There are other things too I bet but that's it off the top-o-da-head. :D 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:237978 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
