Gonzo,
There are cases where building a cached array is more efficient. Mainly
when you want indexed Access to records (basically your not going to output
all your data at once).
So consider the following situation lets say you had a table called
documents that you accessed the following way...
/getDocument.cfm?DocumentID=12 and it would pull the document from the db
with that ID out and display it. If you had a cached query your going to
have to loop through it and find the correct document (or do a query of a
query). It's much faster to access an array element here than perform this
comparison.
Here's how you might build such an array, for this general document example:
put this in application.cfm ...
<cflock scope="application" type="exclusive" timeout="20">
<cfif NOT IsDefined("application.document")>
<cfquery datasource="dsn" name="doc">
SELECT DocumentID, Name, Description, Content
FROM documents
</cfquery>
<cfset application.document = ArrayNew(1)>
<cfoutput query="doc">
<cfscript>
application.document[DocumentID] = StructNew();
application.document[DocumentID].Name = doc.Name;
application.document[DocumentID].Description = doc.Description;
application.document[DocumentID].Content = doc.Content;
</cfscript>
</cfoutput>
</cfif>
</cflock>
Now to access an element, something like this...
<cflock scope="application" type="readonly" timeout="20">
<cfoutput>#application.document[url.DocmentID].content#</cfoutput>
</cflock>
Note that if someone types in a DocumentID into the url that is not in the
array an error will be thrown, you need to catch that error. Also do not
use this technique to store large amounts of data, because your server could
run out of RAM.
++++++++++++++++++++++++++++++++++++++++++
Pete Freitag ([EMAIL PROTECTED])
CFDEV.COM
ColdFusion Developer Resources
http://www.cfdev.com/
-----Original Message-----
From: Gonzo Rock [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 31, 2001 3:36 PM
To: CF-Talk
Subject: RE: Efficient Query Manipulation?
Bryan...
Love that you responded... I was beginning to feel invisible on this list
;-)
Ummmmmm... Ok You say the DBServer should absorb the load, not the CFServer.
So Refinding 100 indexed database records is faster/easier/MoreResource
Efficient than CFServer manipulating 100 records stored in an Array. Very
Interesting point.
RE the interface:
Thanks for the ideas... not sure what I'll be doing... Currently I have
three submit buttons for each Row now... insert above this row, delete this
row, edit this row. Just want to work out the best way to do this before
going on because it will be central and used heavily in the rest of this
application.
again, thanks for making me not feel so invisible ;)
Gonzo
At 12:17 PM 8/31/01 -0700, you wrote:
>It is not very efficient to convert query results to an array. The
overhead
>involved is completely avoidable. More often than not the database machine
>will be more powerful (and reliable) than the CF server. When you are
faced
>with a decision between loading up the DB machine or the CF machine I would
>choose the DB machine every time.
>
>You may also want to modify the interface...
>
>Place a checkbox next to each row containing that row's ID and have
multiple
>submit buttons at the bottom such as "Delete All Checked Rows", and "Move
>Checked Rows Up", and "Move Checked Rows Down". This way you can modify
>many rows with one query and you don't have to reload the page more than
>once. You would need to put a little thought into the process in order to
>maintain an un-broken sequence (i.e. how to avoid a gap between 5 and 7
when
>deleting 6) and so on...
>
>
>Bryan Love ACP
>Internet Application Developer
>Telecommunication Systems Inc.
>[EMAIL PROTECTED]
>
>
>
>-----Original Message-----
>From: Gonzo Rock [mailto:[EMAIL PROTECTED]]
>Sent: Friday, August 31, 2001 10:23 AM
>To: CF-Talk
>Subject: Efficient Query Manipulation?
>
>
>I am rather new at CF and am trying to decide how to go about manipulating
>sets of records in a database.
>
>1)Use cached queries
>-- OR --
>2)Build an array.
>
>My dilemma goes like this...
>
>- A user Queries the database and gets a set of say 50 to 100 records in a
>list. On of the fields retrieved is the SEQUENCE of the record in the list.
>- Next the user both inserts new items into and deletes current items from
>this list until they are satisfied with the contents of the list.
>
>
>
>I can update the database on each insert or deletion, modifying many
records
>to update the SEQUENCE value of the records. (An obvious Downside is lots
of
>database activity)
>
>-- OR --
>
>My most current thinking is that stuffing the Query into an Array would be
a
>good idea... Then I could manipulate the Array, inserting and deleting rows
>until a time where the user would commit the Array. (An obvious Downside is
>loss of all work done in the session if the App/OrSomethingElse crashes.)
>
>Is this an acceptable practice?
>
>Are there techniques?
>
>What's the view from the Brainiacs on this list?
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.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
