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
