RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
Here's a follow up question. Imagine a situation where you pass a list of IDs to a query. You know that the query will only return, at most, the same # of rows as IDs. Taking the same kind of query, where the amount of bytes returned per row divided into the buffers size would tell us to use 100 for blockfactor, would you still use 100 if the # of IDs was 100? If I knew beforehand that there would always be fewer than 100 rows returned, I'd set BLOCKFACTOR to the maximum number of rows that might be returned. If I didn't know, I would just set it to 100 and be done with it. I probably wouldn't know, in most cases, and would just set it to 100. I don't think that wasting space within the 32Kb buffer is very significant; the bigger concern is avoiding the case where you specify too many rows to fit within 32Kb. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
Here's a follow up question. Imagine a situation where you pass a list of IDs to a query. You know that the query will only return, at most, the same # of rows as IDs. Taking the same kind of query, where the amount of bytes returned per row divided into the buffers size would tell us to use 100 for blockfactor, would you still use 100 if the # of IDs was 100? === Raymond Camden, Principal Spectra Compliance Engineer for Allaire Email : [EMAIL PROTECTED] ICQ UIN : 3679482 "My ally is the Force, and a powerful ally it is." - Yoda -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 23, 2000 7:14 PM To: CF-Talk Cc: '[EMAIL PROTECTED]' Subject: RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS How large are buffers set to? I often use BLOCKFACTOR=100 in a query where I: SELECT ID FROM foo WHERE bar Is this setting a large buffer? The size of the buffer will depend on the maximum size of a returned row. Given that you're using a field called "ID", which is probably an integer, your maximum row size will be 4 bytes. One hundred rows of 4 bytes each will take 400 bytes, which will be a small fraction of the maximum buffer size of 32,768 bytes. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
How large are buffers set to? I often use BLOCKFACTOR=100 in a query where I: SELECT ID FROM foo WHERE bar Is this setting a large buffer? The size of the buffer will depend on the maximum size of a returned row. Given that you're using a field called "ID", which is probably an integer, your maximum row size will be 4 bytes. One hundred rows of 4 bytes each will take 400 bytes, which will be a small fraction of the maximum buffer size of 32,768 bytes. In summary, don't worry about it! Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
CF will not generate an error if the database does not support block factoring, it's far worse than that. Actually, I think that there was a problem if you tried to use it with a Sybase or Informix native datasource - one of these (I forget which) would cause CF to throw an error. In any case, I'd generally regard an error as more serious than slower performance. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
On 11/21/00, Ben Forta penned: CF will not generate an error if the database does not support block factoring, it's far worse than that. CF has no way to poll the database to see what it supports, so if you specify a number to high it'll try that, if that fails it'll try a lesser number, and then a lesser one, all the way down to 1. And by that time you've lost the entire performance gain, and probably introduced new lag time. According to the article by Mr. Van Horn, any Oracle or ODBC datasource supports blockfactor. Is that not true? Also, setting a block factor too high when it is not needed will hurt performance because allocating and freeing those larger buffers takes time (and takes memory away from where it could be more efficiently used). Well, how can you know when it's needed? Obviously, when doing a search, you may return 1,000 records, or you may return 1. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
According to the article by Mr. Van Horn, any Oracle or ODBC datasource supports blockfactor. Is that not true? I don't know for sure. Also, setting a block factor too high when it is not needed will hurt performance because allocating and freeing those larger buffers takes time (and takes memory away from where it could be more efficiently used). Well, how can you know when it's needed? Obviously, when doing a search, you may return 1,000 records, or you may return 1. You won't be able to exactly determine the optimal value for each use. You'll have to estimate. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
On 11/21/00, J.Milks penned: BLOCKFACTOR Optional. Specifies the maximum number of rows to fetch at a time from the server. The range is 1 (default) to 100. This parameter applies to ORACLE native database drivers and to ODBC drivers. Certain ODBC drivers may dynamically reduce the block factor at runtime. This is from the April 2000 edition of CFDJ. Article; In Defense of MS Access, By Bruce Van Horn: First, add the Blockfactor="100" attribute to all your CFQUERY tags. This alone will dramatically increase the speed of your queries. Without this attributes, when you run a query, ODBC hands the retrieved records back to the CF server one at a time. By adding this attribute, ODBC will keep the records and then hand them off to CF in blocks of 100 at a time, which is much faster. He goes onto to give a benchmark result that shows the time for a query with blockfactor="100" running at 97.19 ms as opposed to 420 ms without. My understanding of this is that it determines the number of rows to fetch, regardless of size. Am I wrong? All rows matching your search terms will be fetched during any query. Blockfactor tell how many to hand from ODBC to ColdFusion at a time. So with blockfactor="100", a query returning 800 records will result in CF and ODBC "talking" to each other 8 times, as opposed to 800 times. Also, are you able to answer my other question about if or not the MAXROWS would take precedence and if a stored procedure was used, what effect BLOCKFACTOR and MAXROWS have if the SP itself was limiting rows. I would think blockfactor would have the same results with SP. Maxrows is purely on the CF side of things. All rows from the query are returned with or without SP, but only the number you set as maxrows are output. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
I started this thread, and its evolution has lead me to believe that in fact I may not understand the implementation of the BLOCKFACTOR attribute. The following is from the 4.5 Studio help: BLOCKFACTOR Optional. Specifies the maximum number of rows to fetch at a time from the server. The range is 1 (default) to 100. This parameter applies to ORACLE native database drivers and to ODBC drivers. Certain ODBC drivers may dynamically reduce the block factor at runtime. My understanding of this is that it determines the number of rows to fetch, regardless of size. Am I wrong? Also, are you able to answer my other question about if or not the MAXROWS would take precedence and if a stored procedure was used, what effect BLOCKFACTOR and MAXROWS have if the SP itself was limiting rows. The BLOCKFACTOR and MAXROWS attributes are completely unrelated. Neither will affect the other. BLOCKFACTOR is used to control how many rows are transferred from the database to the database client at one time. Let's say you execute a query, and it returns one hundred rows. By default, the value for BLOCKFACTOR is 1, and the database (DB) will have a conversation with the database client (CL) like this: CL: Here's my SQL query. Get to work! ... DB: I've got your recordset right here. CL. OK. Give me the first record. DB. Here it is. Got it? CL. Yes. Give me the second record. ... and so on up to the hundredth record, at which point CF will be able to use the recordset in a CFOUTPUT or whatever. Increasing the BLOCKFACTOR will allow the database to return more than one record at a time to the client, which generally improves performance. The total number of records that can be transferred at once is limited by the available buffer size, which is 32Kb. So, to figure the appropriate BLOCKFACTOR, you find the maximum length of a single row from your recordset, then divide that into 32Kb. The use of BLOCKFACTOR doesn't affect how many records are returned from the database, it only affects how they'll be passed from the database to CF. MAXROWS, on the other hand, determines how many records CF will accept from the database; if you only want to show ten records, you'd set MAXROWS to 10. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
This is from the April 2000 edition of CFDJ. Article; In Defense of MS Access, By Bruce Van Horn: First, add the Blockfactor="100" attribute to all your CFQUERY tags. This alone will dramatically increase the speed of your queries. Without this attributes, when you run a query, ODBC hands the retrieved records back to the CF server one at a time. By adding this attribute, ODBC will keep the records and then hand them off to CF in blocks of 100 at a time, which is much faster. I don't think you'd always want to simply set BLOCKFACTOR to 100. If you set the BLOCKFACTOR too large, the database driver will lower it - and I'm not sure exactly how it figures out what to lower it to. It might simply lower it back to the default value of 1, which won't serve you well. Allaire recommends that you divide your maximum record length into 32Kb; I suspect they recommend this because you might not get optimal performance otherwise. I could be wrong about this, though. I don't know enough about how things work at that relatively low level to be sure one way or the other. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
Is BLOCKFACTOR=10 the same as a SQL Select top 10 *? Note: Top X only works in MS SQL for sure. Is BLOCKFACTOR faster? Thanks Mark W. Breneman -Cold Fusion Developer -Network Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com 608.270.9770 -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 21, 2000 2:13 PM To: CF-Talk Cc: [EMAIL PROTECTED] Subject: RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS I started this thread, and its evolution has lead me to believe that in fact I may not understand the implementation of the BLOCKFACTOR attribute. The following is from the 4.5 Studio help: BLOCKFACTOR Optional. Specifies the maximum number of rows to fetch at a time from the server. The range is 1 (default) to 100. This parameter applies to ORACLE native database drivers and to ODBC drivers. Certain ODBC drivers may dynamically reduce the block factor at runtime. My understanding of this is that it determines the number of rows to fetch, regardless of size. Am I wrong? Also, are you able to answer my other question about if or not the MAXROWS would take precedence and if a stored procedure was used, what effect BLOCKFACTOR and MAXROWS have if the SP itself was limiting rows. The BLOCKFACTOR and MAXROWS attributes are completely unrelated. Neither will affect the other. BLOCKFACTOR is used to control how many rows are transferred from the database to the database client at one time. Let's say you execute a query, and it returns one hundred rows. By default, the value for BLOCKFACTOR is 1, and the database (DB) will have a conversation with the database client (CL) like this: CL: Here's my SQL query. Get to work! ... DB: I've got your recordset right here. CL. OK. Give me the first record. DB. Here it is. Got it? CL. Yes. Give me the second record. ... and so on up to the hundredth record, at which point CF will be able to use the recordset in a CFOUTPUT or whatever. Increasing the BLOCKFACTOR will allow the database to return more than one record at a time to the client, which generally improves performance. The total number of records that can be transferred at once is limited by the available buffer size, which is 32Kb. So, to figure the appropriate BLOCKFACTOR, you find the maximum length of a single row from your recordset, then divide that into 32Kb. The use of BLOCKFACTOR doesn't affect how many records are returned from the database, it only affects how they'll be passed from the database to CF. MAXROWS, on the other hand, determines how many records CF will accept from the database; if you only want to show ten records, you'd set MAXROWS to 10. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
On 11/21/00, Dave Watts penned: I don't think you'd always want to simply set BLOCKFACTOR to 100. If you set the BLOCKFACTOR too large, the database driver will lower it - and I'm not sure exactly how it figures out what to lower it to. It might simply lower it back to the default value of 1, which won't serve you well. I wouldn't think it would lower it to 1. Allaire recommends that you divide your maximum record length into 32Kb; I suspect they recommend this because you might not get optimal performance otherwise. I could be wrong about this, though. I don't know enough about how things work at that relatively low level to be sure one way or the other. OK, how do you figure out the max record length? A byte for every character in every field in the record? Or only the fields that are actually included in the query? So, if all the fields have 1024 characters max, it would be 32 / 1 for a blockfactor of 32? -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
And for the most efficiency, why not just always use BLOCKFACTOR=100 ? Why is the default the most inefficient choice? Is there any advantage in uses a lower number? At 03:12 PM 11/21/00 -0500, Dave Watts wrote: I started this thread, and its evolution has lead me to believe that in fact I may not understand the implementation of the BLOCKFACTOR attribute. The following is from the 4.5 Studio help: BLOCKFACTOR Optional. Specifies the maximum number of rows to fetch at a time from the server. The range is 1 (default) to 100. This parameter applies to ORACLE native database drivers and to ODBC drivers. Certain ODBC drivers may dynamically reduce the block factor at runtime. My understanding of this is that it determines the number of rows to fetch, regardless of size. Am I wrong? Also, are you able to answer my other question about if or not the MAXROWS would take precedence and if a stored procedure was used, what effect BLOCKFACTOR and MAXROWS have if the SP itself was limiting rows. The BLOCKFACTOR and MAXROWS attributes are completely unrelated. Neither will affect the other. BLOCKFACTOR is used to control how many rows are transferred from the database to the database client at one time. Let's say you execute a query, and it returns one hundred rows. By default, the value for BLOCKFACTOR is 1, and the database (DB) will have a conversation with the database client (CL) like this: CL: Here's my SQL query. Get to work! ... DB: I've got your recordset right here. CL. OK. Give me the first record. DB. Here it is. Got it? CL. Yes. Give me the second record. ... and so on up to the hundredth record, at which point CF will be able to use the recordset in a CFOUTPUT or whatever. Increasing the BLOCKFACTOR will allow the database to return more than one record at a time to the client, which generally improves performance. The total number of records that can be transferred at once is limited by the available buffer size, which is 32Kb. So, to figure the appropriate BLOCKFACTOR, you find the maximum length of a single row from your recordset, then divide that into 32Kb. The use of BLOCKFACTOR doesn't affect how many records are returned from the database, it only affects how they'll be passed from the database to CF. MAXROWS, on the other hand, determines how many records CF will accept from the database; if you only want to show ten records, you'd set MAXROWS to 10. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists --- Peter Theobald, Chief Technology Officer LiquidStreaming http://www.liquidstreaming.com [EMAIL PROTECTED] Phone 1.212.545.1232 x204 Fax 1.212.545.0938 To put this contact information into your Palm device, click here: http://www.coola.com/cgi-bin/addinfo.cgi?pid=15803rid=972879910type=A ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
Is BLOCKFACTOR=10 the same as a SQL Select top 10 *? No, it's not. It has absolutely no effect on how many records are returned from the database to CF. It only affects how they're returned. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
Not the same thing at all. TOP 10 tells the database to only give you the first 10. BLOCKFACTOR=10 tells the database driver to give you all of the results of the query, but transfer them internally 10 at a time. It does not affect the results of the query. Just the speed and efficiency of the transfer of the data. At 02:50 PM 11/21/00 -0600, Mark W. Breneman wrote: Is BLOCKFACTOR=10 the same as a SQL Select top 10 *? Note: Top X only works in MS SQL for sure. Is BLOCKFACTOR faster? Thanks Mark W. Breneman -Cold Fusion Developer -Network Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com 608.270.9770 -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 21, 2000 2:13 PM To: CF-Talk Cc: [EMAIL PROTECTED] Subject: RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS I started this thread, and its evolution has lead me to believe that in fact I may not understand the implementation of the BLOCKFACTOR attribute. The following is from the 4.5 Studio help: BLOCKFACTOR Optional. Specifies the maximum number of rows to fetch at a time from the server. The range is 1 (default) to 100. This parameter applies to ORACLE native database drivers and to ODBC drivers. Certain ODBC drivers may dynamically reduce the block factor at runtime. My understanding of this is that it determines the number of rows to fetch, regardless of size. Am I wrong? Also, are you able to answer my other question about if or not the MAXROWS would take precedence and if a stored procedure was used, what effect BLOCKFACTOR and MAXROWS have if the SP itself was limiting rows. The BLOCKFACTOR and MAXROWS attributes are completely unrelated. Neither will affect the other. BLOCKFACTOR is used to control how many rows are transferred from the database to the database client at one time. Let's say you execute a query, and it returns one hundred rows. By default, the value for BLOCKFACTOR is 1, and the database (DB) will have a conversation with the database client (CL) like this: CL: Here's my SQL query. Get to work! ... DB: I've got your recordset right here. CL. OK. Give me the first record. DB. Here it is. Got it? CL. Yes. Give me the second record. ... and so on up to the hundredth record, at which point CF will be able to use the recordset in a CFOUTPUT or whatever. Increasing the BLOCKFACTOR will allow the database to return more than one record at a time to the client, which generally improves performance. The total number of records that can be transferred at once is limited by the available buffer size, which is 32Kb. So, to figure the appropriate BLOCKFACTOR, you find the maximum length of a single row from your recordset, then divide that into 32Kb. The use of BLOCKFACTOR doesn't affect how many records are returned from the database, it only affects how they'll be passed from the database to CF. MAXROWS, on the other hand, determines how many records CF will accept from the database; if you only want to show ten records, you'd set MAXROWS to 10. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists --- Peter Theobald, Chief Technology Officer LiquidStreaming http://www.liquidstreaming.com [EMAIL PROTECTED] Phone 1.212.545.1232 x204 Fax 1.212.545.0938 To put this contact information into your Palm device, click here: http://www.coola.com/cgi-bin/addinfo.cgi?pid=15803rid=972879910type=A ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
I don't think you'd always want to simply set BLOCKFACTOR to 100. If you set the BLOCKFACTOR too large, the database driver will lower it - and I'm not sure exactly how it figures out what to lower it to. It might simply lower it back to the default value of 1, which won't serve you well. I wouldn't think it would lower it to 1. That's because you're using common sense. Cut that out! Unfortunately, as you're well aware, things don't always work out optimally, in the world of software or anywhere else. The fact is, I don't know what happens if you specify a value that would work out to being too large for the buffer. However, if Allaire makes such a point out of recommending that you figure out the appropriate rowcount, that indicates to me that it's probably worth our while to do this. Allaire recommends that you divide your maximum record length into 32Kb; I suspect they recommend this because you might not get optimal performance otherwise. I could be wrong about this, though. I don't know enough about how things work at that relatively low level to be sure one way or the other. OK, how do you figure out the max record length? A byte for every character in every field in the record? Or only the fields that are actually included in the query? So, if all the fields have 1024 characters max, it would be 32 / 1 for a blockfactor of 32? You're interested in the maximum length of a single row returned by your query. So, let's say you have a table with this schema: Item_ID: int identity Item_Name: varchar(20) Item_Description: varchar(8000) If you wanted to select all fields, you'd have a maximum row size of 8024, since the int field is 4 bytes. Divided into 32,768 bytes, that gives you 4.08 rows as your BLOCKFACTOR value. On the other hand, if you just selected Item_ID and Item_Name, 24 goes into 32,768 bytes over 100 times, so we'd specify the maximum allowed BLOCKFACTOR value, 100. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
And for the most efficiency, why not just always use BLOCKFACTOR=100 ? The maximum allowable value for BLOCKFACTOR is 100, according to Allaire. Why is the default the most inefficient choice? The default will always work. The ability to specify larger record blocks isn't universally supported, and may cause CF to generate an error on platforms which don't support it. Is there any advantage in uses a lower number? You'd always want to set this to the maximum number of rows that you can fit into 32Kb. Potentially, if you set it to a higher number than that, it might not do anything for you. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
They recommend this because your server will turn into a molten heap of metal if you even think about trying it. Today... I had a huge amount of XML and I wanted to grab it. So I turn the setting "Long text retrieval enabled" thinking to myself, this will be easy. Unfortuantely as soon as CFAS hit the database it immediately made CFAS consume all available memory (and then some) rendering the server useless.. Turn the setting off and like magic it works... In Administrator they only allow 65000 as the default and I bet for good reason, CF does not seem to cope well with large chunks of data from the database. It stinks, but oh well. You can turn the amount up but I dont recommend it either... Im not sure if this applies, but my data was simply truncated cut off at byte 64999 out of 65000 bytes for the text retrival buffer. Make the buffer bigger stuff worked. Turn it wide open to where its unspecified and CF just dies horribly.. Jeremy Allen ElliptIQ Inc. -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 21, 2000 4:41 PM To: CF-Talk Cc: [EMAIL PROTECTED] Subject: RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS I don't think you'd always want to simply set BLOCKFACTOR to 100. If you set the BLOCKFACTOR too large, the database driver will lower it - and I'm not sure exactly how it figures out what to lower it to. It might simply lower it back to the default value of 1, which won't serve you well. I wouldn't think it would lower it to 1. That's because you're using common sense. Cut that out! Unfortunately, as you're well aware, things don't always work out optimally, in the world of software or anywhere else. The fact is, I don't know what happens if you specify a value that would work out to being too large for the buffer. However, if Allaire makes such a point out of recommending that you figure out the appropriate rowcount, that indicates to me that it's probably worth our while to do this. Allaire recommends that you divide your maximum record length into 32Kb; I suspect they recommend this because you might not get optimal performance otherwise. I could be wrong about this, though. I don't know enough about how things work at that relatively low level to be sure one way or the other. OK, how do you figure out the max record length? A byte for every character in every field in the record? Or only the fields that are actually included in the query? So, if all the fields have 1024 characters max, it would be 32 / 1 for a blockfactor of 32? You're interested in the maximum length of a single row returned by your query. So, let's say you have a table with this schema: Item_ID: int identity Item_Name: varchar(20) Item_Description: varchar(8000) If you wanted to select all fields, you'd have a maximum row size of 8024, since the int field is 4 bytes. Divided into 32,768 bytes, that gives you 4.08 rows as your BLOCKFACTOR value. On the other hand, if you just selected Item_ID and Item_Name, 24 goes into 32,768 bytes over 100 times, so we'd specify the maximum allowed BLOCKFACTOR value, 100. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
CF will not generate an error if the database does not support block factoring, it's far worse than that. CF has no way to poll the database to see what it supports, so if you specify a number to high it'll try that, if that fails it'll try a lesser number, and then a lesser one, all the way down to 1. And by that time you've lost the entire performance gain, and probably introduced new lag time. Also, setting a block factor too high when it is not needed will hurt performance because allocating and freeing those larger buffers takes time (and takes memory away from where it could be more efficiently used). --- Ben -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 21, 2000 4:55 PM To: CF-Talk Cc: [EMAIL PROTECTED] Subject: RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS And for the most efficiency, why not just always use BLOCKFACTOR=100 ? The maximum allowable value for BLOCKFACTOR is 100, according to Allaire. Why is the default the most inefficient choice? The default will always work. The ability to specify larger record blocks isn't universally supported, and may cause CF to generate an error on platforms which don't support it. Is there any advantage in uses a lower number? You'd always want to set this to the maximum number of rows that you can fit into 32Kb. Potentially, if you set it to a higher number than that, it might not do anything for you. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dave Watts please read - Re: BLOCKFACTOR and MAXROWS
How large are buffers set to? I often use BLOCKFACTOR=100 in a query where I: SELECT ID FROM foo WHERE bar Is this setting a large buffer? best, paul At 06:25 PM 11/21/00 -0500, you wrote: Also, setting a block factor too high when it is not needed will hurt performance because allocating and freeing those larger buffers takes time (and takes memory away from where it could be more efficiently used). ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
