Other strategies to pursue are to use query caching if you go the next 500
record route and helping the user focus on/drill down to the records they
are interested in of the 5000.
best, paul
At 11:46 AM 1/5/01 -0500, you wrote:
>William,
>
>The bottleneck you are experiencing can be caused by four things: DB
>Processing Time, CF Processing Time, bandwidth bottleneck, Browser Rendering
>time. I'm not sure which you are experiencing, but its most likely a
>combination of all three. But there are probably ways to address each and
>make them more efficient.
>
>DB Processing Time
>-------------------
>You said your DB is processing the Query well, but I'm sure you could still
>squeeze a bit more performance (from the end users perspective) out of the
>DB. Possibly you could do some ordering or grouping that you are not doing
>now. Possibly use a stored procedure (if you aren't already). Maybe you
>could run more efficient Joins, have you done any profiling on the query to
>check its efficiency? Or maybe you could get into some kooky tricks like
>returning and XML packet to be rendered on the browser instead of by CF (if
>you have a controlled SQL 2000, IE environment) thereby turning CF into a
>simple conduit OR you could trying taking your current record set and
>turning it into a record set that contains HMTL, e.g. You might have started
>with something like this :
>
>EmpID EmpName Emp Email
>1 Joe [EMAIL PROTECTED]
>2 Bill [EMAIL PROTECTED]
>
>Possibly you could return something like this instead:
>EmpID EmpLink
>1 </a href="mailto:[EMAIL PROTECTED]">Joe</a>
>2 </a href="mailto:[EMAIL PROTECTED]">Bill</a>
>
>In this manor you might be able to reduce the Logic that CF has to perform
>during this monster loop, but it depends a bit on exactly what you are
>doing. Oh yeah, don't forget about CF query caching... you might just be
>able to eliminate the database all together.
>
>CF Processing Time
>-------------------
>If you are returning 5000+ records, my guess is that you have CF doing a
>good deal of looping through CFOUTPUT. You can address this by trying to
>reduce any unnecessary processing time inside the loop. For example if you
>have any/multiple AND/ OR conditions inside this output ...this could be a
>good source of the processing time. Sometimes you can move the logic to the
>outside of the loop and simply use a boolean evaluation in the loop. I
>would recommend trying different code combinations inside this loop with a
>sample record set (5000 records) and watch the debug processing time while
>holding all other parts of the processing time constant. You might also
>want to use SETTICKCOUNT to time sections of your code to figure out where
>the bottleneck is being caused (most likely the output statement). If your
>using an older CF are you possibly using LOOP instead of CFOUTPUT? At the
>end of the day you probably just need to tinker with this code and you'll be
>able to squeeze a good 20% increase in speed out of it.
>
>I'd also bet that you could get some pretty good performance increases by
>just returning a WDDX packet to the browser and using JS to render the info
>on the client end.
>
>Bandwidth Bottleneck
>----------------------
>If your pushing down 5000+ records bandwidth may be an issue. The HTML to
>encapsulate 5000 records in a table (I assume) is probably pretty large.
>Look at the HMTL is their a way to reduce the white space ? Can you write
>the HTML more efficiently ? Probably yes.
>
>You might also want to push the record set down as a WDDX packet and render
>the output on the client, pound for pound its more compact than the HMTL and
>could also help in other areas.
>
>
>Browser Rendering
>------------------
>When talking about the speed of large database queries you can optimize for
>one of two things....overall response time or time for first response. For
>browsers it might be similar situation. I imagine the data you are
>returning is being displayed in a table. Rendering a 5000 row table is no
>small feat, their is a lot of processing that needs to take place. In
>Netscape particularly table rendering is slow slow slow. There are things
>you can do to optimize the way browsers render tables include parameters
>that define the height and width of cells that way the browser knows how to
>render each cell as it receives the info. Without this info the browsers
>needs to wait till it has all the info to figure out how big each cell
>should be. But, this is optimizing the total response time. An alternate
>path might be to break your record set into multiple tables. For example
>every 20 (50 or 100) records end the current table and start a new table.
>This will allow the browser to render the small table much more rapidly.
>Although this will probably add a marginal hit in overall response time the
>end user will see the first N records completely rendered on the screen much
>more quickly than waiting for the whole table to be rendered.
>
>You could also use WDDX and work some JS magic to dynamically build the
>result set on the screen. Using WDDX and JS you could most likely give the
>user a much more responsive interaction with the data.
>
>
>Hope this helps.
>-eric
>------------------------------------------------
>If to please people we offer what we ourselves disapprove, how can we
>afterwards defend our work.
>-George Washington
>
>Eric Barr
>Zeff Design
>(p) 212.714.6390
>(f) 212.580.7181
>
>
>-----Original Message-----
>From: William J Wheatley [mailto:[EMAIL PROTECTED]]
>Sent: Friday, January 05, 2001 10:52 AM
>To: CF-Talk
>Subject: LARGE QUERY RETURNS
>
>
>Ok I have found that when i return alot of returns to a page, 5000+ it adds
>quite a bit of time to the processing on the CF side with displaying the
>information as opposed to it being database bottleneck.
>
>In Fact the database is Hustling returning the 70,000 records in about 4
>seconds but it takes CF quiet a bit of time to display them. Now is there
>anything i can do to cut down on the time it takes to show those, other then
>doing a Next Previous system??
>
>
>Bill Wheatley
>Director of Development
>AEPS INC
>Allaire ColdFusion Consulting Partner
>Allaire Certified ColdFusion Developer
>http://www.aeps.com
>ICQ: 417645
>http://www.aeps2000.com
>954-472-6684 X303
>
>IMPORTANT NOTICE:
>This e-mail and any attachment to it is intended only to be read or used by
>the named addressee. It is confidential and may contain legally privileged
>information. No confidentiality or privilege is waived or lost by any
>mistaken transmission to you. If you receive this e-mail in error, please
>immediately delete it from your system and notify the sender. You must not
>disclose, copy or use any part of this e-mail if you are not the intended
>recipient. The RTA is not responsible for any unauthorized alterations to
>this e-mail or attachment to it
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
