Tracy, When using 'IN' keep in mind that
SELECT blah FROM table WHERE ID IN (1,2,3) is basically the same as: SELECT blah FROM table WHERE ID = 1 OR ID = 2 OR ID = 3 If the ID list is really long, you could imagine how many 'OR' statements you are sticking into your query, and how slow it will make it run. I suspect restructuring your query to avoid the huge 'IN' list will speed your query up substantially. -Cameron -------------------- Cameron Childress elliptIQ Inc. p.770.460.1035.232 f.770.460.0963 -- http://www.neighborware.com America's Leading Community Network Software > -----Original Message----- > From: Tracy Bost [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 02, 2002 12:35 PM > To: CF-Talk > Subject: RE: Speeding up select queries > > > I've converted the dateadd function to sql as suggested. It looks like > it has taken about 17ms off the time by doing that. Thanks, as every ms c > ounts. > Also have used the query of query feature as Sandra suggested, and that r > eally > helped. I'll for sure be a query of query junkie now. > > Here's the initial query: > > getlateorders (Records=28, Time=43109ms) > > AND now the query using the query of query feature as suggested: > > getalllateorders (Records=32, Time=47ms) > > getlateorders (Records=31, Time=891ms) > > The new time for the query is just under a second. Now I'll just have to > re- > think the structing of using a long zip code list, which i suspect will b > e no > small project. > > Thanks All ! > > > > > > > > Quoting Craig Dudley <[EMAIL PROTECTED]>: > > > Maybe sorting the zip list might help. Although if it contains that > > many zip > > codes it might be worthwhile having a re-think. > > > > One more thing, the dateadd in your query, I'd convert that into SQL > > functions rather than CF, as follows. > > > > so.. > > > > Appt_Time < #DateAdd("h", -48,"#Now()#")# > > > > would become something like .. > > > > Appt_Time < dateadd(Hour,-48,GetDate()) > > > > It's always better to make SQL server do the work imho. > > > > > > -----Original Message----- > > From: Tracy Bost [mailto:[EMAIL PROTECTED]] > > Sent: 02 January 2002 16:55 > > 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. > > > > > > > > > > > > > > ______________________________________________________________________ Dedicated Windows 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=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
