RE: Structures No? Cached Queries Yes?
The purpose of my posting was a heads up to those folks who really know how to do this sort of thing. But what I have done is a valid indicator for my app. That is, I was attempting to hold all things constant except how the data was stored in/read from memory. In addition, consider the following: A 15% difference for this task is a difference between 207 and 180ms. I think it's fair to assume that taking out all but reads means both will be reduced by the same amount, say 50ms. Then we'd have 157 vs 130ms, OR, Structure reads are 21% slower.not just 15% slower. That's what should hit folks when then consider how I did the test... best, paul At 12:48 PM 8/31/00 -0400, you wrote: 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. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Structures No? Cached Queries Yes?
In my case it is fair, since I'm only interested in reads. Structures are widely touted as a way to store a DB in memory to speed things up. I was surprised to find results that suggested a cached query was faster. BTW, note that if this result is valid, it means stored procedures are less important for reads. best, paul At 12:48 PM 8/31/00 -0400, you wrote: 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? 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/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Structures No? Cached Queries Yes?
Interesting. FindNoCase had an effect on the Structure but not the Cached version. Structure still slower, 194/180 = 8% instead of 15%. best, paul At 11:42 AM 8/31/00 -0500, you wrote: 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. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Structures No? Cached Queries Yes?
This came up in the last two days. Check the archives. best, paul At 12:31 PM 8/31/00 -0500, you wrote: Additionally, a question: I've never had any problems with just sticking data in a structure, but without "creating" the structure using StructNew().. does anyone know what the implications of this are? -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Structures No? Cached Queries Yes?
These CFSETs are one-time, when App is first accessed. Their ms is not included in the timings I posted. best, paul At 12:31 PM 8/31/00 -0500, you wrote: ewww... I hate long blocks of cfset's :) Use cfscript instead when you have all those cfsets.. that'll speed things up some more. 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 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Structures No? Cached Queries Yes?
Try CompareNoCase. ;) -Original Message- From: paul smith [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 31, 2000 2:40 PM To: [EMAIL PROTECTED] Cc: Andy Peterson Subject: Re: Structures No? Cached Queries Yes? Interesting. FindNoCase had an effect on the Structure but not the Cached version. Structure still slower, 194/180 = 8% instead of 15%. best, paul At 11:42 AM 8/31/00 -0500, you wrote: 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. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Structures No? Cached Queries Yes?
Brief test. I doubted, so I tried. At first I was a little misled... then realized I wasn't testing properly. ;) Don't try doing this with a loops... loops throw it off. And be sure to reset the ticks between each test. CFSET x = 1 times: 1 cfset: 0 milliseconds 10 cfsets: 0 milliseconds 100 cfsets: 0 milliseconds 500 cfsets: 0 milliseconds 1000 cfsets: 10 milliseconds 5000 cfsets: 50 milliseconds CFSCRIPT x = 1 times: 1 set: 0 milliseconds 10 sets: 0 milliseconds 100 sets: 0 milliseconds 500 sets: 0 milliseconds 1000 sets: 0 milliseconds 5000 sets: 20 milliseconds Conclusion: CFSCRIPT is faster. Some of us knew it all along. ;) Ed -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Structures No? Cached Queries Yes?
OK, gonna jump in on this one now. I don't use cached queries. I use scoped queries. Essentially the same thing, but I like managing my own caching rather than allowing the server to determine what gets cached (based on the rotating list of "last used"). Just a preference. But on the queries vs. structures discussion you're comparing apples and oranges. If you have data that you're going to loop over and output, use a query. That's what they're designed for. But if you're going to call individual pieces of data (particularly organizationally nested pieces of data) than use a structure, much quicker to point to exactly what you want than to loop to find it. Example, a structure that holds school information: schools.schoolname.class.teacher = "[teachername]" In my code (where I know the school and class): #schools[schoolname][class]["teacher"]# will output teacher name. I can pull this information out of a resident structure much quicker than running a query, or even looping through a query to find the information I want. This may not be the best example, but it may help to look at some of the queries you're running to get complex information and see where a nested structure (queried for and built once) saved into application scope (then copied to request scope with cflock for usage) might be a better alternative. I *love* structures and I'm open to any questions on using them efficiently. Sharon P.S., cfscript rules for working with structures, whether it's faster or not. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Structures No? Cached Queries Yes?
Thanks for jumping in, Sharon. Good suggestions. In just a few minutes mulling over your ideas, I've come up with some possibilities. My app is a Yellow Pages. So one query I have been running is to list all Headings that start with "A" for example (Academies, Access Control Systems, Accessories-Fashion, and so forth...) So your approach would be to load all Headings that begin with A into a structure, all those that begin with B into another structure, and so forth. Right? This eliminates a call to the DB. Another would be to load all Listings in one city, say New York into a structure, all Listings in Newark into another structure, and so forth. Is this the idea? Do you have any specific suggestions for Yellow Pages? best, paul At 09:18 PM 9/1/00 -0400, you wrote: OK, gonna jump in on this one now. I don't use cached queries. I use scoped queries. Essentially the same thing, but I like managing my own caching rather than allowing the server to determine what gets cached (based on the rotating list of "last used"). Just a preference. But on the queries vs. structures discussion you're comparing apples and oranges. If you have data that you're going to loop over and output, use a query. That's what they're designed for. But if you're going to call individual pieces of data (particularly organizationally nested pieces of data) than use a structure, much quicker to point to exactly what you want than to loop to find it. Example, a structure that holds school information: schools.schoolname.class.teacher = "[teachername]" In my code (where I know the school and class): #schools[schoolname][class]["teacher"]# will output teacher name. I can pull this information out of a resident structure much quicker than running a query, or even looping through a query to find the information I want. This may not be the best example, but it may help to look at some of the queries you're running to get complex information and see where a nested structure (queried for and built once) saved into application scope (then copied to request scope with cflock for usage) might be a better alternative. I *love* structures and I'm open to any questions on using them efficiently. Sharon P.S., cfscript rules for working with structures, whether it's faster or not. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Structures No? Cached Queries Yes?
It would depend a lot on how you're using the information now, that is, how people are accessing it. Structures (particularly nested structures) are great for drill down information. But if you're doing a lot of looping, then queries might be faster. What might work is the nested structure like: APPLICATION.newark["Access Control Systems"] = an ordered array of listings (You can use "non-standard" key names provided you build and access the structure using array notation. I.e., APPLICATION.newark.Access Control Systems would not work.) You *can* loop over structures, either as a collection, or by pulling the keys with a StructKeyList(). I've done some sites where I kept a good portion of the "common" information in large hierarchical structures that I used all over the site. Sometimes I looped over the structure, sometimes I pulled detailed information from several layers into the structure. I liked having one place for all the information. But if I didn't need the information in a structure, I'm just as happy saving a query or two to APPLICATION scope. If you have more detailed information about what you're trying to accomplish, I can give you some more ideas. Sharon At 06:47 PM 9/1/2000 -0700, paul smith wrote: Thanks for jumping in, Sharon. Good suggestions. In just a few minutes mulling over your ideas, I've come up with some possibilities. My app is a Yellow Pages. So one query I have been running is to list all Headings that start with "A" for example (Academies, Access Control Systems, Accessories-Fashion, and so forth...) So your approach would be to load all Headings that begin with A into a structure, all those that begin with B into another structure, and so forth. Right? This eliminates a call to the DB. Another would be to load all Listings in one city, say New York into a structure, all Listings in Newark into another structure, and so forth. Is this the idea? Do you have any specific suggestions for Yellow Pages? best, paul At 09:18 PM 9/1/00 -0400, you wrote: OK, gonna jump in on this one now. I don't use cached queries. I use scoped queries. Essentially the same thing, but I like managing my own caching rather than allowing the server to determine what gets cached (based on the rotating list of "last used"). Just a preference. But on the queries vs. structures discussion you're comparing apples and oranges. If you have data that you're going to loop over and output, use a query. That's what they're designed for. But if you're going to call individual pieces of data (particularly organizationally nested pieces of data) than use a structure, much quicker to point to exactly what you want than to loop to find it. Example, a structure that holds school information: schools.schoolname.class.teacher = "[teachername]" In my code (where I know the school and class): #schools[schoolname][class]["teacher"]# will output teacher name. I can pull this information out of a resident structure much quicker than running a query, or even looping through a query to find the information I want. This may not be the best example, but it may help to look at some of the queries you're running to get complex information and see where a nested structure (queried for and built once) saved into application scope (then copied to request scope with cflock for usage) might be a better alternative. I *love* structures and I'm open to any questions on using them efficiently. Sharon P.S., cfscript rules for working with structures, whether it's faster or not. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Structures No? Cached Queries Yes?
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/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf _talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
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# TDFONT FACE="Arial" SIZE=-2CFIF 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"]#/ACFELSEA HREF="/HL/LL/index.cfm/HD.#Class_ID#/AL.#attributes.alpha#"#APPLICATION.YPS["#Class_ID#"]["Class_Name"]#/Anbsp;/CFIF/FONT/TD CFIF CURRENTROW MOD COLUMNCOUNT EQ 0 /TR CFIF CURRENTROW NEQ MAXROWS TR BGCOLOR="#IIf(CurrentRow Mod 6, DE('ff'), 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# TDFONT FACE="Arial" SIZE=-2CFIF PAGES EQ "C"A HREF="/HL/SHL/index.cfm/HD.#Class_ID#/AL.#attributes.alpha#"#Class_Name#/Anbsp; CFELSEA HREF="/HL/LL/index.cfm/HD.#Class_ID#/AL.#attributes.alpha#"#Class_Name#/Anbsp;/CFIF/FONT/TD CFIF CURRENTROW MOD COLUMNCOUNT EQ 0 /TR CFIF CURRENTROW NEQ MAXROWS TR BGCOLOR="#IIf(CurrentRow Mod 6, DE('ff'), 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/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf _talk or send a message to [EMAIL PROTECTED] with
RE: Structures No? Cached Queries Yes?
I'm a little confused as to what you are doing here. Can you paste a short code snippet? I keep queries in structures in memory all the time and access is pretty much instantaneous, so I'm guessing that's not what you are doing? -Rick -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/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Structures No? Cached Queries Yes?
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# TDFONT FACE="Arial" SIZE=-2CFIF 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"]#/ACFELSEA HREF="/HL/LL/index.cfm/HD.#Class_ID#/AL.#attributes.alpha#"#APPLICATION.YPS ["#Class_ID#"]["Class_Name"]#/Anbsp;/CFIF/FONT/TD CFIF CURRENTROW MOD COLUMNCOUNT EQ 0 /TR CFIF CURRENTROW NEQ MAXROWS TR BGCOLOR="#IIf(CurrentRow Mod 6, DE('ff'), 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# TDFONT FACE="Arial" SIZE=-2CFIF PAGES EQ "C"A HREF="/HL/SHL/index.cfm/HD.#Class_ID#/AL.#attributes.alpha#"#Class_Name#/A nbsp; CFELSEA 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('ff'), 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
RE: Structures No? Cached Queries Yes?
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# TDFONT FACE="Arial" SIZE=-2CFIF 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"]#/ACFELSEA HREF="/HL/LL/index.cfm/HD.#Class_ID#/AL.#attributes.alpha#"#APPLICATION.YPS ["#Class_ID#"]["Class_Name"]#/Anbsp;/CFIF/FONT/TD CFIF CURRENTROW MOD COLUMNCOUNT EQ 0 /TR CFIF CURRENTROW NEQ MAXROWS TR BGCOLOR="#IIf(CurrentRow Mod 6, DE('ff'), 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# TDFONT FACE="Arial" SIZE=-2CFIF PAGES EQ "C"A HREF="/HL/SHL/index.cfm/HD.#Class_ID#/AL.#attributes.alpha#"#Class_Name#/A nbsp; CFELSEA 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 C
RE: Structures No? Cached Queries Yes?
Thanks! In an entirely different Structure/Cached Query application comparison from the one where I earlier today posted the code here, I'm seeing Structures are still 15% slower. Dave Watts opined here that he didn't believe there really was a limit to how many queries CF would cache, the CF Administrator/Settings Statement that: "The maximum number of cached queries allowed at any given time is 100." notwithstanding. (I notice CF4.0.1 accepted my limit of but not 1. Is there a message here?) So IF the CF limit on Cached Queries is not limiting to your application, and IF Cached Queries are faster than Structures, then it's starting to look like Structure NYET, Cached SI. NO? I guess it's possible in a very active site for Cached Queries to require more ram then Structures. This consideration might require monitoring to establish the appropriate timeout for the Cached Queries. Anyone? best, paul At 11:18 AM 8/31/00 -0500, you wrote: Cool work Paul. This is valuable stuff to most of us. I am interested in keeping posted on your findings. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Structures No? Cached Queries Yes?
ewww... I hate long blocks of cfset's :) Use cfscript instead when you have all those cfsets.. that'll speed things up some more. Additionally, a question: I've never had any problems with just sticking data in a structure, but without "creating" the structure using StructNew().. does anyone know what the implications of this are? -- Billy Cravens [EMAIL PROTECTED] paul smith wrote: I got the difference down to 15% by eliminating CFSETs in the Structure version. (Structure still slower.) 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 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Structures No? Cached Queries Yes?
Thanks! In an entirely different Structure/Cached Query application comparison from the one where I earlier today posted the code here, I'm seeing Structures are still 15% slower. Without doing tests, I believe this is going to be the case. Structures are more efficient for storing and managing data, but aren't necessarily the fastest for looping or outputting that data because query result sets are simple grids which are very easy for an application server to run through. Dave Watts opined here that he didn't believe there really was a limit to how many queries CF would cache, the CF Administrator/Settings Statement that: "The maximum number of cached queries allowed at any given time is 100." notwithstanding. (I notice CF4.0.1 accepted my limit of but not 1. Is there a message here?) I posed this question directly to Jeremy Allaire a few weeks ago and he said that there was not a limit on the number of cached queries. The 100 is misleading. He said as long as you have RAM, you can cache queries, although as this number gets larger and larger, your server may slow slightly as it tries to manage all this information. So just exercise good judgment and don't try to cache everything, just because you can. Cache it if it benefits the application. So IF the CF limit on Cached Queries is not limiting to your application, and IF Cached Queries are faster than Structures, then it's starting to look like Structure NYET, Cached SI. NO? I would tend to agree although I really don't do a lot with structures. So someone else that's a structure fan might have evidence to the contrary. Running through queries is a very simple looping operation. Parsing a structure would inherently take at least a little more effort. Whether that's noticible would probably depend on the structure model itself and the server executing the code. I guess it's possible in a very active site for Cached Queries to require more ram then Structures. This consideration might require monitoring to establish the appropriate timeout for the Cached Queries. Again, as long as you have the RAM, you're using the queries for a beneficial purpose, and you have them expire at a reasonable time, you shouldn't have "memory overload" and your application and server should stay lean and efficient. That's my two cents. Anybody else want to jump in?? --Doug -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Structures No? Cached Queries Yes?
Billy, Actually benchmark testing shows that it takes longer to set vars when using cfscript compared to when just using cfset. cfscript is only faster on the Development side of things. As for the second question, look at this thread... http://www.mail-archive.com/fusebox@houseoffusion.com/msg01168.html HTH, Robert Hinojosa WebDeveloper 512.912.3775 [EMAIL PROTECTED] www.cirrus.com -Original Message- From: Billy Cravens [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 31, 2000 12:31 PM To: [EMAIL PROTECTED] Subject: Re: Structures No? Cached Queries Yes? ewww... I hate long blocks of cfset's :) Use cfscript instead when you have all those cfsets.. that'll speed things up some more. Additionally, a question: I've never had any problems with just sticking data in a structure, but without "creating" the structure using StructNew().. does anyone know what the implications of this are? -- Billy Cravens [EMAIL PROTECTED] paul smith wrote: I got the difference down to 15% by eliminating CFSETs in the Structure version. (Structure still slower.) 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 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Structures No? Cached Queries Yes?
Structure is more faster than query for example if you want to create a shopping cart, all information is saved in memory and you can add, delete, update structures to change items and it is much faster than saving items in database. Cache query may be faster only for display. 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/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf _talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. --- --- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Structures No? Cached Queries Yes?
No. I was referring to eliminating CFSETs at the output stage to set dummy variables and using variables with Structure syntax instead. That is, eliminating steps like: CFSET ClassID = APPLICATION.YPS["#Class_ID#"] changed the Structure method to be only 15% slower instead of 26% slower. best, paul At 12:48 PM 8/31/00 -0400, you wrote: You mentioned eliminating CFSET's is that the actually initalization of the structures? -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.