I'm not familiar with the query analysis capabilities of sql2000, but you could look to see how that query might be behaving with respect to the database and get some suggestions for optimization that way, with any too ls available. Perhaps you could try indexing that table on the zip code (if it's not already indexed), or create a combined index, and see if that helps. If you're not the DBA for the database, have a chat with that per son about it.
Regards, Joel Parramore > -----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, C ou > 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, (S EL > 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 variab le > 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 a re > 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 a re > > 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, thro ug > 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
