RE: Structures No? Cached Queries Yes?

Jeremy Allen Thu, 31 Aug 2000 10:05:26 -0700
You mentioned eliminating CFSET's
is that the actually initalization
of the structures?

its not going to be very fair to do a cached
query just pulling data versus

setting and pulling the data.. im
assuming that is what you mean by
eliminating cfsets in the structure
version.

Also I notice from the structure version you use
<CFIF
APPLICATION.YPS["#Class_ID#"]["Pages"] IS "C">

and then in the Query version you use

<CFIF PAGES EQ "C">

Also this is known to be a less effecient method
of String comparison in CF.

So.. the code used was different?

For testing and benchmarking I would want to
measure the raw performance of nothing but
query reads on a page and nothing but
structure reads on a page, and then
do a presentation of the data.

All of the extraneous data and processing
done is not needed when benchmarking.

And since Cached queries are read only
is it even really fair to compare the two
given how easy it is to set and work with
a structure and the somewhat more limiting
nature of cached queries?

Also was this a 'clean' test machine?

Just curios.... :)

Jeremy Allen
[EMAIL PROTECTED]



-----Original Message-----
From: paul smith [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 31, 2000 12:04 PM
To: [EMAIL PROTECTED]
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
