If you are using CF5, why not do this in 2 queries. Query the database and then query the query.
<cfquery name="getalllateorders" datasource="#AppDSN#"> SELECT FileNo, Order_ID, Appraiser_ID, HouseNo, Street, UnitNo, City, Cou nty, State_ID, Date_Ordered,time_ordered,Appt_Date,Appt_Time,AMorPM, Lender_ID , Zip, (SELECT State FROM T_States WHERE State_ID = O.State_ID) AS State, (SEL ECT Client_ID FROM T_Lenders WHERE Lender_ID = O.Lender_ID) AS Client_ID FROM T_Orders O WHERE StatusType_ID = 3 AND Appt_Time < #DateAdd("h ", - 48,"#Now()#")# </cfquery> <cfquery name="getlateorders" dbtype="query"> Select * from getalllateorders Where Zip IN (#ziplist#) </cfquery> This way you are only selecting zips from a much smaller recordsource. Should be much faster. -----Original Message----- From: Tracy Bost [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:55 AM To: CF-Talk Subject: RE: Speeding up select queries This query takes about 47ms to execute, which I can live with that: <cfquery name="getlateorders" datasource="#AppDSN#"> SELECT FileNo, Order_ID, Appraiser_ID, HouseNo, Street, UnitNo, City, Cou nty, State_ID, Date_Ordered,time_ordered,Appt_Date,Appt_Time,AMorPM, Lender_ID , Zip, (SELECT State FROM T_States WHERE State_ID = O.State_ID) AS State, (SEL ECT Client_ID FROM T_Lenders WHERE Lender_ID = O.Lender_ID) AS Client_ID FROM T_Orders O WHERE StatusType_ID = 3 AND Appt_Time < #DateAdd("h ", - 48,"#Now()#")# </cfquery> BUT when I add this to the same query: AND Zip IN (#ziplist#) It takes anywhere from 45 to 50 seconds to execute. ziplist is a variable taken from a session.ziplist that contains the user's authorized zipcodes. It j ust started going slow, maybe there is a threshold after so many zipcodes are in the list it will slow things down tremendously ? Quoting Sandy Clark <[EMAIL PROTECTED]>: > General rules of thumb I use. > > Avoid Select * always specify your fields. > > Fastest is to select fields in the following datatype order, integers, > numeric, strings according to field size. Memo or Long text fields are > always last. The latter one is most important because all fields > selected > after a long text field will load as unbound fields and will slow the > sql > way down. > > -----Original Message----- > From: Tracy Bost [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 02, 2002 11:22 AM > To: CF-Talk > Subject: Speeding up select queries > > > What is the best approach to speed up "select" statements with a > sql2000 > database? The data is constantly being updated,deleted,inserted, throug h > out > the day with each user seeing data depending on his/her access level an d > assigned zip codes. I worry that using cachedwithin will not work in > this > senario, as using that will not allow to show data that has recently > changed. > ANY help, thoughts much appreciated. > > ----------------------------------------------------------------------- ----- > -- > Visit "The Most Powerful Tool on the Farm" at http://www.ifarm.com > Get the latest on Ag News, Market Reports, FREE email, and much more. > > > ______________________________________________________________________ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists