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
