It will be far more efficient to do the whole query in SQL rather than
creating 2 queries in CF. Let SQL do the work, it is very good at it. I
suspect that you IN clause is causing the Query optimizer to have problems
determining which index to use which is why you are seeing a huge difference
in run time between the query with or without the ZIP list. You need to
look at the plan for both queries and see which indexes it is using (the
latter may be forcing a table scan). You can force the use of the right
index as part of your query, just make sure you are using the right one.
Converting this to a Stored procedure will save in little time as it will
not have to compile the procedure every time it is run, but it may make the
IN clause hard to pass (though in General I always recommend using stored
procedures over pass through SQL.
I would also change the nested queries to joins. Maybe something like...
SELECT O.FileNo,
O.Order_ID,
O.Appraiser_ID,
O.HouseNo,
O.Street,
O.UnitNo,
O.City,
O.County,
O.State_ID,
O.Date_Ordered,
O.time_ordered,
O.Appt_Date,
O.Appt_Time,
O.AMorPM,
O.Lender_ID,
O.Zip,
S.State AS State,
L.Lender AS ClientID
FROM T_Orders O,
T_States S,
T_Lenders L
WHERE StatusType_ID = 3
AND Appt_Time < #DateAdd("h", -48,"#Now()#")#
AND O.StateID = S.StateID
AND O.Lender_ID = L.Lender_ID
AND O.Zip IN (#ziplist#)
Justin
> -----Original Message-----
> From: Sandy Clark [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 02, 2002 12:13 PM
> To: CF-Talk
> Subject: RE: Speeding up select queries
>
> 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.
> >
> >
> >
>
>
______________________________________________________________________
Get Your Own 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=coldfusionb
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
