cfquery results--memory resident?

[hofar...@houseoffusion.com]

Hi,

A question came up recently with one of my client developers who is potentially
returning a large # of rows from a query. The question was whether the result
set is stored in memory or spooled to disk somewhere. I didn't know but assumed
it was memory resident.

Anyone know the answer to this? Can it be controlled and/or limited? (CF 9)

Thanks!

--Ben

--
Ben Conner b...@webworldinc.com
Web World, Inc. 888-206-6486 or
PO Box 1122 480-704-2000
Queen Creek, AZ 85142



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353753

[hofar...@houseoffusion.com]

> A question came up recently with one of my client developers who is potentially
> returning a large # of rows from a query. The question was whether the result
> set is stored in memory or spooled to disk somewhere. I didn't know but assumed
> it was memory resident.
>
> Anyone know the answer to this? Can it be controlled and/or limited? (CF 9)

All CF variables are stored in memory. You can limit the size of the
resultset by writing your SQL accordingly, but that's it really.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or onsite.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353761

[hofar...@houseoffusion.com]

That's what I suspected. Much appreciated.

--Ben

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353786

[hofar...@houseoffusion.com]

Actually there are ways to step through a result set using database
functions so that you do bot store huge resultsets in memory, if you google
it then you will find some examples.

2 simple solutions are.

1. Dont query all the data at all, instead provide a search form to get at
specific records, which is usually much simpler thsn paging through
hundreds or thousands of records.
2. Only query the primary keys, and then loop over that list grabbing x
records at a time and doing a new query to get all rows for those keys.

Regards
Russ Michaels
www.michaels.me.uk
www.cfmldeveloper.com - Free CFML hosting for developers
www.cfsearch.com - CF search engine

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353787

[hofar...@houseoffusion.com]

>>2. Only query the primary keys, and then loop over that list grabbing x
records at a time and doing a new query to get all rows for those keys.

This is a pretty good method.
I tested it on a database containing about 450000 records with a seach template.
I give a very loose criterion on purpose, the query takes 53 sec and returns 44500 records.
This is ridiculous since the user will only see ten records at a time, using startRow and maxRows in a CFOUTPUT.
And for the next page, it takes another 50sec :-(
A certain amount of time is also taken by CFX_highlight which highlights every occurence of the search string,
again in the 44500 records!

With this method, the query returns only the 10 records needed, and it takes about 5 sec the first time and 3 sec any subsequent times.
And the CFX_highlight is applied on only 10 records at a time. A big difference.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353790

[hofar...@houseoffusion.com]

Glad it helped. Also dont forget u.can cache the original primary key query
too.

Regards
Russ Michaels
www.michaels.me.uk
www.cfmldeveloper.com - Free CFML hosting for developers
www.cfsearch.com - CF search engine

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353791

[hofar...@houseoffusion.com]

Very slick. Thanks guys!

--Ben

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353792

[hofar...@houseoffusion.com]

Another method, assuming you're using MS SQL Server (not sure how far
this goes back compatibility-wise) is to toss in the criteria for the
full search, but then to only pull back X rows:

WITH Results_Full AS (
SELECT Field1, Field2, Field3, ROW_NUMBER()
OVER (ORDER BY Field1) AS RowNum
FROM tableName
)
SELECT Field1, Field2, Field3
FROM Results_Full
WHERE RowNum >= #StartRowNum# AND RowNum <= #EndRowNum#

Where StartRowNum/EndRowNum are whatever you set.

I've found this method to be superfast when querying large recordsets.
Works great especially if you're doing a "show X rows on a page" thing
with forward/back/etc.

--Scott

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353793

[hofar...@houseoffusion.com]

On Sat, Jan 5, 2013 at 8:25 AM, Russ Michaels <ru...@michaels.me.uk> wrote:

> 2 simple solutions are.
>
> 1. Dont query all the data at all, instead provide a search form to get at
> specific records, which is usually much simpler thsn paging through
> hundreds or thousands of records.
> 2. Only query the primary keys, and then loop over that list grabbing x
> records at a time and doing a new query to get all rows for those keys.
>

If you know you are going to have huge datasets shouldn't you also use
blockfactor?

my assumption is that while it might not speed things up, it's at
least clearing things out of buffer while it works on more records.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353794

[hofar...@houseoffusion.com]

A related question: I have a table of towns that has ~20,000 records. The dataset I need has the record ID, town name, a memo field (usually empty), and fields holding an index into other tables (township, county, state, country). In my form I have a select field to pick the town. I don't want to populate that with 20,000 options so I have a way for the user to pare down the possibilities based on the first letter of the town name. I then make an ajax call and use jquery/javascript to populate the select field options.

Which is better: run a query of the table at the top of the application and store the results in an APPLICATION variable, then do a query of queries for each subset, or just query the table directly for each subset?

My initial thinking is the former but now I'm wondering. I do know the first option runs quickly, returning about 300 records for "M" and populating the select field in 1-2 seconds (I haven't actually timed it, and that's lightning fast for most users of the app).

Larry Stephens

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353795

[hofar...@houseoffusion.com]

list of towns is not really a huge query, just do it once and cache and
then do a query of query for your auto complete.

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353796