My suggestion would be to create a table in your SQL Server that contains
the list of zip codes that a user can see. Each record would contain the
user's ID and 1 zip code they can see. Then you can either join this table
in, or user it in the "in" clause.
So your select statement would be something like the following 2 selects:
<cfquery name="getlateorders" datasource="#AppDSN#">
SELECT FileNo,
Order_ID,
Appraiser_ID,
HouseNo,
Street,
UnitNo,
City,
County,
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,
(SELECT Client_ID FROM T_Lenders WHERE Lender_ID = O.Lender_ID) AS
Client_ID
FROM T_Orders O
INNER JOIN user_zip Z
ON O.zip = Z.zip
WHERE StatusType_ID = 3
AND Appt_Time < #DateAdd("h", -48,"#Now()#")#
AND Z.user_id = #my_user_id#
</cfquery>
or option 2:
<cfquery name="getlateorders" datasource="#AppDSN#">
SELECT FileNo,
Order_ID,
Appraiser_ID,
HouseNo,
Street,
UnitNo,
City,
County,
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,
(SELECT 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()#")#
AND O.zip IN (SELECT zip FROM user_zip WHERE Z.user_id =
#my_user_id#)
</cfquery>
______________________________________________________
Bill Grover
Supervisor IS Department Phone: 301.424.3300 x396
EU Services, Inc. FAX: 301.424.3561
649 North Horners Lane E-Mail: mailto:[EMAIL PROTECTED]
Rockville, MD 20850-1299 WWW: www.euservices.com
______________________________________________________
> -----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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
