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.
>
>
>
______________________________________________________________________
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
