RE: CFQUERY BLOCKFACTOR
> CF Docs on the Subject are as follows: > > "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." > > A recent article in SQL Server Magazine (October 2000, p 75) > discusses "BLOCKING FACTOR" > > "BLOCKING FACTOR" bears some resemblance to "BLOCKFACTOR," > but I'm not sure how close it is. > > BLOCKING FACTOR appears to be determined by the width of a > RECORD, as it exists in the DB (that is, it includes all columns). > > I've assumed BLOCKFACTOR to be determined by the width of the > RETURNED DATA in each record, which does not have to include > all columns. > > I further assumed a CFQUERY that returned a single column of > integers could have BLOCKFACTOR set at the maximum of 100, > without fear of loss of efficiency as in those cases where > 100 happened to be too large and CF would then have to try > 99, then 98, etc. > > So the question is: Is 100 ALWAYS OK when CFQUERY returns a > single column of integers? Your assumptions about how BLOCKFACTOR works are correct. There's a formula for determining the appropriate BLOCKFACTOR value for a given query: 1. The size of the buffer used by BLOCKFACTOR is 32k. 2. You can store the number of rows that will fit within the buffer. So, the formula is: 32768 bytes / row size in bytes Of course, in many cases, you might only have a rough number (when using varchar columns, for example). > PS> Keeping in mind that CF4.01 Administrator (but not 4.51 > Administrator) is now known to have incorrectly stated there > to be limit of 100 cached queries, whereas there really is no > fixed limit, is BLOCKFACTOR _really_ limited to 100? I don't know. I haven't tested this. However, in real-life use, it's unlikely that you'd even get to 100 using most queries. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ 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: CFQUERY BLOCKFACTOR
I get to 100 all the time. For example, I use Verity to get a list of Record IDs whose data matches my search term, and then use SQL with CFQUERY and BLOCKFACTOR = 100 to filter the VERITY result set. Of course, in this case any savings in having BLOCKFACTOR greater than 100 is probably in the noise level compared to VERITY inefficiencies. BTW, are there any good resources on speeding up VERITY? best, paul At 01:17 PM 10/2/00 -0400, you wrote: > > PS> Keeping in mind that CF4.01 Administrator (but not 4.51 > > Administrator) is now known to have incorrectly stated there > > to be limit of 100 cached queries, whereas there really is no > > fixed limit, is BLOCKFACTOR _really_ limited to 100? > >I don't know. I haven't tested this. However, in real-life use, it's >unlikely that you'd even get to 100 using most queries. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ 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: Cfquery Blockfactor and Oracle9i
> I have been reading in many places to increase performance, > increate the BLOCKFACTOR of queries to 100. If I have a > query that generally returns 5 rows should a blockfactor of > 100 benefit, hurt, or not affect performance. > > I have tried using the blockfactor in the past by going > through the app and if a query on average returned 30 rows I > set the blockfactor to 30. I didnt notice a performance > increase at all and in fact in some places I notice some > actual decrease. So I removed the setting from all cfquery > tags. But I didn't try just making all query blockfactor's 100. I don't really know whether BLOCKFACTOR makes much of a performance difference in CFMX; I'm basically assuming that it works the same way as in previous versions, but I haven't tested that. That said, you should set the BLOCKFACTOR based on the size of an individual row. The BLOCKFACTOR attribute lets you put more than one row within a buffer, but the buffer in CF 5 at least is a fixed size of 32Kb if I recall correctly. So, you should set your BLOCKFACTOR value equal to the number of rows that will fit in 32Kb. If you set it to a higher number, the BLOCKFACTOR attribute is ignored, I think. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
