Hi Brook,
Cursors can be useful in certain circumstances, although they carry a number
of limitations and possible problems associated with their use. The first
thing you need to know about cursors is that they're slow in comparison to
standard sql queries which return all the records at once. The second thing
you probably need to know is that there's no native way for ColdFusion to
manipulate them because CF doesn't hold the recordset open the way that ASP
does. I remember at one point trying to return data from a cursor using a
stored procedure, however, instead of returning a single recordset with all
of the resultant rows from the table, it returned a separate recordset for
each row, and there were other problems trying to access those recordsets.
Someone else on the list may have taken this further than I have, however,
my advice if you want to go the route of cursors would be to create a stored
procedure which creates a temp table to contain the data you want, then
select all the data from the temp table as a single recordset ... I'm not
sure off the top of my head, but you might have to still do some fenagling
with the ColdFusion code to get the final recordset. This may or may not be
faster than drawing a single query which contains more data than you need.
But generally speaking, I would try and avoid using a cursor to retreive
data to CF if you can. With SQL Server, you can use top and bottom to grab
whatever data you need explicitely, and indexes and locking hints to improve
performance, for instance:
SELECT BOTTOM 20 firstname, lastname, employeeid
FROM tEmployee WITH (NOLOCK) -- this is a dirty read
WHERE employeeid IN (
SELECT TOP 100 employeeid
FROM tEmployee WITH (NOLOCK)
ORDER BY lastname, firstname
)
ORDER BY lastname, firstname
Note that you have to make sure the order-by clause in the sub-select is the
same as the order-by clause in the outer select statement. The inner select
is TOP #lastrow# and the outer select is BOTTOM #maxresults#
hth
Isaac Dealey
Certified Advanced ColdFusion Developer
www.turnkey.to
954-776-0046
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
