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.
> >
> >
> >
>
>
______________________________________________________________________
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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
