RE: Structures No? Cached Queries Yes?

paul smith Thu, 31 Aug 2000 09:09:03 -0700
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.YPS["#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.
RE: Structures No? Cached Queries Yes?

Reply via email to
