Solved

Paging

Posted on 2003-12-08
10
232 Views
Last Modified: 2013-12-24
0
Comment
Question by:Tacobell777
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
10 Comments
 
LVL 17

Expert Comment

by:anandkp
ID: 9901822
ok - there is something called as BLOCKFACTOR & MAXROWS in CFQuery / CFStoredProc !
I generally use this for my queries - run thru paging.

Few Optimization tips :

1. Use CFQueryParam to the Fullest [Bind Variables are a gr8 help - with text / numbers / dates / list etc ...]
2. Use BlockFactor in CFQuery / CFStoredProc. [the value of block factor shld be ur page size (maxrows in paging file)]
3. Use MAxrows in CFQuery / CFProcResult [the value as - StartRow + Maxrows]

Eg :
<CFQUERY NAME="Qry_" DATASOURCE="Atomic_Dsn" BLOCKFACTOR="#Maxrows#" MAXROWS="#StartRow+MaxRows#">
    Select .....
</CFQUERY>

<CFSTOREDPROC PROCEDURE="PKG.PRC_ProcName" DATASOURCE="Dsn" BLOCKFACTOR="#Maxrows#">
<CFPROCRESULT NAME="Qry_1" RESULTSET="1" MAXROWS="#StartRow+MaxRows#">

using this - u only fetch the required number of records from the DB [50 if maxrows=50] & not all 2000

PS : read thru for more information !
http://www.macromedia.com/devnet/server_archive/articles/cfqueryparam_oracle_databases.html
http://www.macromedia.com/devnet/server_archive/articles/cf_best_practices_oracle.html

hth

K'Rgds
Anand
0
 
LVL 17

Author Comment

by:Tacobell777
ID: 9901881
Is this an Oracle thingy?
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9901906
Nope works with SQL as well ... but teh article was talking abt Oracle in particular

Ive used the sample example [given above] with both Oracle & MS SQL ...
give it a try & see for urself !

The articles were just a means of giving u more information on my example !
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 17

Author Comment

by:Tacobell777
ID: 9901975
I tried it and it doesn't do anything for me. Then I did some research and foudn the following
----------
maxrows specifies the maximum number of rows. blockfactor specifies the number of rows that CF will attempt to store in the database driver's storage buffer upon each row fetch. The two don't have anything to do with each other. The buffer is 32 Kb, so you can figure out how many rows (assuming the maximum possible row length) can fit into the buffer, then specify that number as the blockfactor attribute value. This is a performance enhancement; it makes getting the data faster. It doesn't affect how many rows will be returned by your query.
----------

I don't think blockfactor does what you think it does..
Sorry.. Proof me wrong - please, coz it would be nice if it would work.
0
 
LVL 17

Author Comment

by:Tacobell777
ID: 9902008
Straight form the source "Corfield"

Adding blockFactor to a query can significantly improve performance. To add blockFactor, examine the data that is being returned. Determine the maximum size (in bytes) of each row. Take that size and determine how many times that number would divide into 32k. That number is your blockFactor, but be aware that the max blockFactor is 100. So, if for example you were getting 200 bytes per row, you could easily fit over 100 rows into the 32k buffer that CF 'grabs' at one time.

If you know at runtime that you will have less then 100 rows returned, for example you're writing a query that either returns 0 or 1 rows, do not bother adding the blockFactor attribute.
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9902032
yes 2 are not related

blockfactor : will retrive the records faster from teh DB.
maxrows : Specifies the maximum number of rows you want returned in the record set.

by giving the parameter as [startrow+maxrows] - u'll be fetching only 1+50 = 51 records the first time & not all the 200 records
however on 2nd page - u will be fetching 51+50 = 101 records
& on 3rd page - u will be fetching 101+50 = 151 records

yes i agree - with this u cannot limit the fetch count to 50 all the time ... but i guess its better than retrieving all the 2000 everytime. & also how many users u think wld wanna go beyond the first 10 pages ... thats not more than 500 records for u ... still better than 2000 !

read thru this for getting the PRROF u asked for :)
http://rtb.home.texas.net/cf/cf4.htm

hth

K'Rgds
Anand

PS : I hope u are not misinterpreting my suggestion - i didnt relate maxrows & blockfactor... cos when i use paging - i set the startrow & maxrow for each page & those are the variables used in the CFquery - so ur query wld actually look without those variables as

<CFQUERY NAME="Qry_" DATASOURCE="Atomic_Dsn" BLOCKFACTOR="50" MAXROWS="1+50#">
instead of
<CFQUERY NAME="Qry_" DATASOURCE="Atomic_Dsn" BLOCKFACTOR="#Maxrows#" MAXROWS="#StartRow+MaxRows#">

I hope that sorts out teh confusion in coldfusion :)
0
 
LVL 17

Author Comment

by:Tacobell777
ID: 9902075
I'll have a read through your answer in detail later, but I was aware of Maxrows, that still gets more records than I want.
If your on page 20 and displaying 10 records per page, that are 200 records you are retrieving....

0
 
LVL 17

Expert Comment

by:anandkp
ID: 9902092
yes thats the best - i can do for u at this point of time ...
Incase i get more info & a better solution - i wld let u know !

K'Rgds
Anand
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 10848587
Closed, 250 points refunded.
modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Suggested Courses

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question