Re: Structures No? Cached Queries Yes?

Andy Peterson Thu, 31 Aug 2000 09:57:04 -0700
Hi,

As an aside, I'd be curious to know how FindNoCase would affect processing
time if you replaced "eq" and "is" with it in both examples, particularly
whether it sped up the evaluation of the structure value more so than the
query output value.

-Andrew
----- Original Message -----
From: paul smith <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, August 31, 2000 11:03 AM
Subject: RE: Structures No? Cached Queries Yes?


> I got the difference down to 15% by eliminating CFSETs
> in the Structure version.  (Structure still slower.)
>
> Structure set a la the following:
>
> <!--- Set Up Structure for YellowPage Headings --->
> <CFQUERY NAME="GetYPs" DATASOURCE="#DSN#" USERNAME="#user#"
PASSWORD="#pass#">
> SELECT Class_ID, Class_Name, Pages, NewPages
> FROM YellowPages (nolock)
> </CFQUERY>
>
> <CFSET STTEMP = STRUCTNEW()> <!--- temporary structure --->
> <CFSET APPLICATION.YPS = STRUCTNEW()> <!--- final structure for
Headings --->
> <CFLOOP QUERY="GetYPs">
> <CFSET STTEMP["Class_ID"] = "#Class_ID#">
> <CFSET STTEMP["Class_Name"] = "#Class_Name#">
> <CFSET STTEMP["Pages"] = "#Pages#">
> <CFSET STTEMP["NewPages"] = "#NewPages#">
> <CFSET APPLICATION.YPS[STTEMP["Class_ID"]] = STRUCTCOPY(STTEMP)>
> <CFSET TMPVAR = STRUCTCLEAR(STTEMP)>
> </CFLOOP>
>
> Query for Structure IDs as follows:
>
> <cfquery name="GetHeadingsFirstLetter" datasource="#DSN#"
username="#user#"
> password="#pass#" blockfactor="100"
cachedwithin="#CreateTimeSpan(0,12,0,0)#">
> SELECT Class_ID
> FROM YellowPages (nolock)
> WHERE Class_Name LIKE '#attributes.alpha#%'
> ORDER BY Class_Name
> </cfquery>
>
> Timing measured displaying the following on a 8 successive pages 99
> Headings each (Structure Version):
>
> <CFSET COLUMNCOUNT = 3>
> <DIV ALIGN="center"><TABLE WIDTH="600" BORDER="0" CELLPADDING="1"
> CELLSPACING="2"><TR BGCOLOR="FFCC66">
>     <CFOUTPUT QUERY="GetHeadingsFirstLetter" STARTROW="#attributes.start#"
> MAXROWS=#MAXROWS#>
>        <TD><FONT FACE="Arial" SIZE=-2><CFIF
> APPLICATION.YPS["#Class_ID#"]["Pages"] IS "C"><A
>
HREF="/HL/SHL/index.cfm/HD.#Class_ID#/AL.#attributes.alpha#">#APPLICATION.YP
S["#Class_ID#"]["Class_Name"]#</A><CFELSE><A
>
HREF="/HL/LL/index.cfm/HD.#Class_ID#/AL.#attributes.alpha#">#APPLICATION.YPS
["#Class_ID#"]["Class_Name"]#</A>&nbsp;</CFIF></FONT></TD>
>        <CFIF CURRENTROW MOD COLUMNCOUNT EQ 0>
>           </TR>
>           <CFIF CURRENTROW NEQ MAXROWS>
>              <TR BGCOLOR="#IIf(CurrentRow Mod 6, DE('ffffff'),
DE('ffcc66'))#">
>           </CFIF>
>        <CFELSEIF CURRENTROW EQ MAXROWS>
>           <CFLOOP FROM="#Evaluate(columncount-(currentrow MOD
> columncount))#" TO="1" STEP="-1" INDEX="i">
>              <TD></TD>
>           </CFLOOP>
>           </TR>
>        </CFIF>
>     </CFOUTPUT>
> </TABLE></DIV>
>
> Query for Cached Query as follows:
>
> <cfquery name="GetHeadingsFirstLetter" datasource="#DSN#"
username="#user#"
> password="#pass#" cachedwithin="#CreateTimeSpan(0,12,0,0)#">
> SELECT Class_ID,Class_Name,Pages
> FROM YellowPages (nolock)
> WHERE Class_Name LIKE '#attributes.alpha#%'
> ORDER BY Class_Name
> </cfquery>
>
> Timing measured displaying the following on 8 successive pages 99 Headings
> each (Cached Query Version):
>
> <CFSET COLUMNCOUNT = 3>
> <DIV ALIGN="center"><TABLE WIDTH="600" BORDER="0" CELLPADDING="1"
> CELLSPACING="2"><TR BGCOLOR="ffcc66">
> <CFOUTPUT QUERY="GetHeadingsFirstLetter" STARTROW="#attributes.start#"
> MAXROWS=#MAXROWS#>
>     <TD><FONT FACE="Arial" SIZE=-2><CFIF PAGES EQ "C"><A
>
HREF="/HL/SHL/index.cfm/HD.#Class_ID#/AL.#attributes.alpha#">#Class_Name#</A
>&nbsp;
> <CFELSE><A
>
HREF="/HL/LL/index.cfm/HD.#Class_ID#/AL.#attributes.alpha#">#Class_Name#</A>
&nbsp;</CFIF></FONT></TD>
>        <CFIF CURRENTROW MOD COLUMNCOUNT EQ 0>
>           </TR>
>           <CFIF CURRENTROW NEQ MAXROWS>
>              <TR BGCOLOR="#IIf(CurrentRow Mod 6, DE('ffffff'),
DE('ffcc66'))#">
>           </CFIF>
>        <CFELSEIF CURRENTROW EQ MAXROWS>
>           <CFLOOP FROM="#Evaluate(columncount-(currentrow MOD
> columncount))#" TO="1" STEP="-1" INDEX="i">
>              <TD></TD>
>           </CFLOOP>
>           </TR>
>        </CFIF>
>     </CFOUTPUT>
> </TABLE></DIV>
>
> best,  paul
>
>
> At 09:38 AM 8/31/00 -0400, you wrote:
> >I'd be interested in knowing what the code and data structure in your
tests
> >looked like...  We are using structs a good deal in some apps, and I had
> >been assuming it would be faster than a query.
> >
> >-Cameron
> >
> >--------------------
> >Cameron Childress
> >McRae Communications
> >p. 770-460-7277 x.232
> >f. 770-460-0963
> >
> > > -----Original Message-----
> > > From: paul smith [mailto:[EMAIL PROTECTED]]
> > > Sent: Thursday, August 31, 2000 9:06 AM
> > > To: [EMAIL PROTECTED]
> > > Subject: Structures No? Cached Queries Yes?
> > >
> > >
> > > I see my earlier post was obscure to opaque.
> > >
> > > For the same results:
> > >
> > > Get data from a cached query: Average = 180ms
> > > Get data from a database stored in a Structure: Average = 227ms
> > >
> > > Structure takes 26% longer than a cached query to get the same
> > > data.
> > >
> > > This kind of makes sense, since with a Structure I have to run a
> > > query anyway to get the list of KEYS, which I use to loop over
> > > the Structure to pull only two more columns of data out of the
> > > Structure.
> > >
> > > More tests later...
> > >
> > > best, paul
> > >
> > >
> > >
> > > Paul Smith
> > > Web/Database Droid
> > > A: SupportNet, Inc, 3871 Piedmont Ave, Oakland, CA 94611
> > > P: (510) 763-2358
> > > C: (510) 205-6755
> > > F: (510) 763-2370
> > > E: [EMAIL PROTECTED]
> > > W: http://www.support.net
> > >
> > > ------------------------------------------------------------------
> > > ------------
> > > Archives: http://www.mail-archive.com/[email protected]/
> > > To Unsubscribe visit
> > > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf
> >_talk or send a message to [EMAIL PROTECTED] with
> >'unsubscribe' in the body.
> >
>
>---------------------------------------------------------------------------
---
> >Archives: http://www.mail-archive.com/[email protected]/
> >To Unsubscribe visit
> >http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
or
> >send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> >the body.
>
> --------------------------------------------------------------------------
----
> Archives: http://www.mail-archive.com/[email protected]/
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
>
>


------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Structures No? Cached Queries Yes?

Reply via email to
