Re: What the heck is happening during CFQUERY?
On Monday 09 Mar 2009, Claude Schneegans wrote: Each memo field needs some special treatment and only some specific words are retrieved. So I need to simply read all records and loop on them. Use maxrows/startrow to do it in blocks of 1000 or something. -- Tom Chiverton Helping to centrally seize attention-grabbing efficient B2C markets as part of the IT team of the year, '09 and '08 This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is available for inspection at the registered office together with a list of those non members who are referred to as partners. We use the word partner to refer to a member of the LLP, or an employee or consultant with equivalent standing and qualifications. Regulated by the Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.co ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320316 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
On Sun, Mar 8, 2009 at 11:42 PM, Claude Schneegans wrote: I've always thought that all what CFQUERY was doing was to create some connection to the database, and then the actual content of all records would be read as needed during some loop on the result set. That would be impossible for CF to do because: - if the resultset were never used the query would never be run on the server; - if the resultset were used multiple times the query would be run multiple times on the server; - the number of records would be unknown. If you want that, declare a cursor and deal with those consequences yourself. SELECT armeId, armTexte FROM armesArmoriaux The query semms to take for ever. Actually, the output says: getArmes.recordCount = 303203 cfquery.ExecutionTime = 614469 More than 10 min. just to settle a result set? That does not make sense. Ok, armTexte is a memo field, but is CFQUERY supposed to read all of them? I do not think 2 ms per record is all that much for Access. So, what the heck CF is doing within CFQUERY? Read all content? That does not make sense. Is the problem with CF, or the ODBC driver? There is a reason the docs warn not to use ODBC But you might be trashing memory as well.. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320254 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: What the heck is happening during CFQUERY?
That doesn't work because getArmes contains only a count of the records in armesArmoriaux, not the actual records. -Original Message- From: Al Musella, DPM [mailto:muse...@virtualtrials.com] Sent: Sunday, March 08, 2009 7:37 PM To: cf-talk Subject: Re: What the heck is happening during CFQUERY? Change it to this and the time should go down by a factor of about 100,000 : CFQUERY NAME=getArmes DATASOURCE=Armoriaux SELECT count (*) as N FROM armesArmoriaux /CFQUERY CFOUTPUTgetArmes.recordCount = #getArmes.n#BR cfquery.ExecutionTime = #cfquery.ExecutionTime#BR/CFOUTPUTCFABORT ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320258 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
Yeah, but what are you going to do with 300,000 actual records in a single recordset? In his original post, Claude never specified what the requirements of his CFQUERY were - if we knew what he was trying to achieve, we could provide better advice on how to do it... On 9 Mar 2009, at 13:44, Billy Cox wrote: That doesn't work because getArmes contains only a count of the records in armesArmoriaux, not the actual records. -Original Message- From: Al Musella, DPM [mailto:muse...@virtualtrials.com] Sent: Sunday, March 08, 2009 7:37 PM To: cf-talk Subject: Re: What the heck is happening during CFQUERY? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320260 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
More specifically, what are you going to do with 300,000 ntext fields ... that could potentially be many GB of data, which the server will be holding in active memory. As a general rule, I leave my ntext fields out of any query that's pulling a list of more than a few items. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320263 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
It gets a connection from the pool, queries the DB, retrieves all the data and finally returns the connection to the pool. Well, I'm stunned. I was sure CF was better designed than that. I thought that data was retrieved as loops were going. Obviously course, with 300k records, CF is reading all data from disk, from a system especially designed to handle and retrieve data, put it back on disk, and then gets it back again into memory as loops goes. This is ridiculous. Even dBase was designed to retrieve data by chunks only when needed. I can't believe this. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320265 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
I don't think this is unexpected behaviour It is not only unexpected, it is completely retarded. All ODBC/JDBC functions are designed so the database can be connected, then the SQL statement be compiled, then data retrieved row by row, as needed. Even dBase, Clipper, Foxpro worked this way. - you just need to examine what you actually require from the query Well, I made a request to get only the record Id, it take only 6 seconds, then I read again each record with the data I need in the loop. I am creating an index on words found in memo fields, and I need to loop on all records in the table. I'm doing this once for all on my development server, I don't care if it takes hours, but 10 min, just to create the query, it looked like infinity to me. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320268 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
There are a number of reasons why running the select directly in Access may appear much faster. 1) You are probably running it locally so there is no costly transfer of data between servers. I'm running the CF app locally also, so the difference does not come from connexion time. Anywa, the result is just a 2 lines message. 2) In my experience, Access spools the data behind the scenes and only loads in the records which are visible Of course, and there is nothing extraordinary there : ALL database systems work this way. They will get the records only when needed, and ODBC also has also been designed in that purpose. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320269 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
Change it to this and the time should go down by a factor of about 100,000 Thanks, you bet this is what I would have done if I only needed the number of records ;-) The output of recordCount was there only for illustration purpose. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320270 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
On Mon, Mar 9, 2009 at 4:18 PM, Claude Schneegans wrote: All ODBC/JDBC functions are designed so the database can be connected, then the SQL statement be compiled, then data retrieved row by row, as needed. CF just needs all the records all the time because that is the only way to get a recordcount. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320271 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
More specifically, what are you going to do with 300,000 ntext fields I am creating a cross table index on word found in memo fields. Each memo field needs some special treatment and only some specific words are retrieved. So I need to simply read all records and loop on them. I found another way to read records one at a time. This is a one time job on a database. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320272 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
- if the resultset were never used the query would never be run on the server; Well, if the result set is not used, what is the advantage of having the query run anyway? - if the resultset were used multiple times the query would be run multiple times on the server; If the result set is too large to fit in memory, it will be rewritten and reread from disk every time anyway. - the number of records would be unknown. Most of the time, one only needs to know if there are records or not. This can be checked reading the first record only. May be some parameter in CFQUERY (ie RECORDS=onTheFly) could be used to create on the fly queries. It will be up to the programmer to use it if he expects the query to generate lots of data. In that sense, the parameter blockFactor in CFQUERY is missleading: it says Specifies the maximum number of rows to fetch at a time from the server. The range is 1 (default) So I thought that CF was getting rows one at a time. Apparently, it is getting all rows one at a time in the same time ;-) Doesn't make much sense to me ;-) ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320273 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
CF just needs all the records all the time because that is the only way to get a recordcount. While I don't think that is quite true, I do think there is a good reason for having the resultset downloaded at once. ColdFusion expects you to use the recordset within the single request and this differs from an offline application where it can make sense to keep a connection open to a resultset. Indeed, while the initial query may take longer, it is perhaps better performing than hitting the db on each iteration within the request. Thoughts? Dominic ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320274 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
just out of curiosity: did you try experimenting with BLOCKFACTOR attribute of cfquery? i am just curious, since you already are retrieving such a large dataset, if using blockfactor makes any difference at all on processing time... Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Claude Schneegans wrote: Change it to this and the time should go down by a factor of about 100,000 Thanks, you bet this is what I would have done if I only needed the number of records ;-) The output of recordCount was there only for illustration purpose. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320275 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
IIRC, blockFactor is only relevant on the Oracle drivers, and it refers specifically to how Oracle expects to batch and return large recordsets. If Oracle is allowed to spool out large recordsets without returning them in blocks, it will often spin the DB server out of threads, which then cascades to the waiting app threads ... and then things die. Blockfactor, therefore, controls how the DB server batch responds to the request, but it does not impact how CF brings data back into its CFQUERY struct. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320277 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
because that is the only way to get a recordcount. A big price in efficiency to pay for something we use only sometimes. Most of the time, we only need to know if there are records or not. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320279 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
If that's all you need to know, use SELECT TOP 1... On Mon, Mar 9, 2009 at 11:05 AM, Claude Schneegans schneeg...@internetique.com wrote: because that is the only way to get a recordcount. A big price in efficiency to pay for something we use only sometimes. Most of the time, we only need to know if there are records or not. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320280 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
What we are really talking about here is having the right tool for the job. dBase, Clipper and FoxPro are all database management systems, and so are designed to work efficiently in examples like the one you are citing. ColdFusion is a Web Application server, designed to interact with a database to deliver web content. By the very nature of Web applications, you wouldn't want to be leaving open database connections all over the place - the surest way to crash a server under heavy load... Really, all the heavy lifting should be done within the database itself; but out of preference I don't think I'd be using Acces for this! However, you can do what you want using ColdFusion; I've done similar stuff in the past when the updating has required extra ColdFusion logic. I would just recommend doing it in chunks of data - maybe 5000 records at a time? Grab the first 5000 records; process them; then do the next 5000... I don't think this is unexpected behaviour It is not only unexpected, it is completely retarded. All ODBC/JDBC functions are designed so the database can be connected, then the SQL statement be compiled, then data retrieved row by row, as needed. Even dBase, Clipper, Foxpro worked this way. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320282 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
Indeed, while the initial query may take longer, it is perhaps better performing than hitting the db on each iteration within the request. Thoughts? Perfectly right for small result sets. But as soon as the virtual memory must be used, each record will generate some read-write-reread action, pretty bad on performance indeed. IMHO, CF should should be able to buffer the record set, ie. read a certain number of records until a certain amount of memory is full, then do as usual if the whole record set fits in memory, and read the next buffer when needed. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320284 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
did you try experimenting with BLOCKFACTOR attribute of cfquery? i am just curious, since you already are retrieving such a large dataset, if using blockfactor makes any difference at all on processing time... The blockfactor is supposed to be one by default, but I tried it anyway, and no, it makes no difference. The blockfactor is either serving some other purpose and then not properly documented, or it is inoperant. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320285 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
blockFactor is only relevant on the Oracle drivers CF 5 doc says This parameter applies to ORACLE native database drivers and to ODBC drivers This is ambiguous, If it applies only to Oracle, it should be stated: This parameter applies to ORACLE native database drivers and to ORACLE ODBC drivers And CF 7 : Might not be supported by some database systems. with no other details. IMO everything but ORACLE should not be called some database systems either... ;-/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320286 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
On Mon, Mar 9, 2009 at 4:44 PM, Claude Schneegans wrote: - if the resultset were never used the query would never be run on the server; Well, if the result set is not used, what is the advantage of having the query run anyway? A select isn't necessarily idempotent (if CF were able to determine it was a select in the first place).. - if the resultset were used multiple times the query would be run multiple times on the server; If the result set is too large to fit in memory, it will be rewritten and reread from disk every time anyway. But if the query isn't deterministic you wouldn't necessarily get the same results each time. May be some parameter in CFQUERY (ie RECORDS=onTheFly) could be used to create on the fly queries. It will be up to the programmer to use it if he expects the query to generate lots of data. I'm not really interested in such a parameter, but if you submit it as a feature request you should consider naming it usecursor or something. That explains the way it would work much better. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320290 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
On Mon, Mar 9, 2009 at 5:05 PM, Claude Schneegans wrote: because that is the only way to get a recordcount. A big price in efficiency to pay for something we use only sometimes. Most of the time, we only need to know if there are records or not. That is what maxrows is for. The query will still execute to completion on the database server, but CF will stop fetching results after it has reached its maximum. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320291 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
On Mon, Mar 9, 2009 at 4:41 PM, Dominic Watson wrote: CF just needs all the records all the time because that is the only way to get a recordcount. While I don't think that is quite true It is not strictly true. You can declare a cursor, do a move end, read from the metadata how many records you moved forward, then do a move start and wait for the commands to fetch rows. But that is hardly portable and will perform even worse then just fetching everything into a detached recordset on the client. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320292 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
if cf did not get the full dataset from the db, would it still be able to show query debugging / execution times / etc, and would cf monitor still work and be able to show you long-running/unoptimized queries / etc? Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320293 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
(if CF were able to determine it was a select in the first place) Well, it must be able somehow to determine the query returns data, otherwise, how would it create a structure from any result set? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320294 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: What the heck is happening during CFQUERY?
A cfquery tag can potentially contain any number of SQL commands which may or may not return a result set. CF doesn't do any parsing of the SQL contained within, it just runs it. (The only exception being the SELECT, UPDATE, DELETE, DROP etc security set up in your datasource settings-- but that's not bullet proof) Regardless, CF wouldn't know if the cfquery was going to return a result set until after it had sent the commands to the database for execution and waited for the results to come back, and that would sort of defeat the purpose, wouldn't it? ~Brad Original Message Subject: Re: What the heck is happening during CFQUERY? From: Claude Schneegans schneeg...@internetique.com Date: Mon, March 09, 2009 1:11 pm To: cf-talk cf-talk@houseoffusion.com (if CF were able to determine it was a select in the first place) Well, it must be able somehow to determine the query returns data, otherwise, how would it create a structure from any result set? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320296 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
Regardless, CF wouldn't know if the cfquery was going to return a result set until after it had sent the commands to the database for execution and waited for the results to come back, and that would sort of defeat the purpose, wouldn't it? No, because the driver does not return the result set, but only the presence of a result set. It is then up to the application to get rows one by one: See http://msdn.microsoft.com/en-us/library/ms711012(VS.85).aspx In particular: If the statement is a SELECT statement, the application calls a CLI function to return the results in application buffers. Typically, this function returns one row or one column of data at a time. At least, this is how it works for ODBC, with native drivers, I don't know. Furthermore, if it is a SELECT statement, and if the application calls a CLI function to return the results, one may suppose that the application must know one way or another that there is some result set to get. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320302 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
It seems like you know a bit about databases. Why not write the looping code in the database using T-SQL, VB, or .NET and keep CF out of the picture entirely? What is CF providing that makes you want to use it for this index building task? It doesn't sound like you are serving up Web pages to users with this code, which is what CF is designed to do. -Mike Chabot ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320310 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
Yes, CFQUERY reads all the content of the query. It gets a connection from the pool, queries the DB, retrieves all the data and finally returns the connection to the pool. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/3/9 Claude Schneegans schneeg...@internetique.com: Hi, I've always thought that all what CFQUERY was doing was to create some connection to the database, and then the actual content of all records would be read as needed during some loop on the result set. I have this statement on some Access database : CFQUERY NAME=getArmes DATASOURCE=Armoriaux SELECT armeId, armTexte FROM armesArmoriaux /CFQUERY CFOUTPUTgetArmes.recordCount = #getArmes.recordCount#BR cfquery.ExecutionTime = #cfquery.ExecutionTime#BR/CFOUTPUTCFABORT The query semms to take for ever. Actually, the output says: getArmes.recordCount = 303203 cfquery.ExecutionTime = 614469 More than 10 min. just to settle a result set? That does not make sense. Ok, armTexte is a memo field, but is CFQUERY supposed to read all of them? If I run the same query directly from Access, it runs so fast, I cannot even measure the time it takes. So, what the heck CF is doing within CFQUERY? Read all content? That does not make sense. Is the problem with CF, or the ODBC drive ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320240 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
The CFQUERY connects to the database, returns the whole of the resulting query result into the specified variable, then closes the connection; the variable is held in memory for the lifetime of the request. So, if you run a query that, as in this case, returns more than 300,000 records, there is going to be a significant overhead as the data is read out of the database and into ColdFusion. Especially if one of those fields is a memo field. I don't think this is unexpected behaviour - you just need to examine what you actually require from the query, and tailor your SQL to that. It is highly unlikely that you actually need to work with 300,000 records at one time... Seb Duggan Web ColdFusion Developer e: s...@sebduggan.com t: 07786 333184 w: http://sebduggan.com On 8 Mar 2009, at 22:42, Claude Schneegans wrote: Hi, I've always thought that all what CFQUERY was doing was to create some connection to the database, and then the actual content of all records would be read as needed during some loop on the result set. I have this statement on some Access database : CFQUERY NAME=getArmes DATASOURCE=Armoriaux SELECT armeId, armTexte FROM armesArmoriaux /CFQUERY CFOUTPUTgetArmes.recordCount = #getArmes.recordCount#BR cfquery.ExecutionTime = #cfquery.ExecutionTime#BR/ CFOUTPUTCFABORT The query semms to take for ever. Actually, the output says: getArmes.recordCount = 303203 cfquery.ExecutionTime = 614469 More than 10 min. just to settle a result set? That does not make sense. Ok, armTexte is a memo field, but is CFQUERY supposed to read all of them? If I run the same query directly from Access, it runs so fast, I cannot even measure the time it takes. So, what the heck CF is doing within CFQUERY? Read all content? That does not make sense. Is the problem with CF, or the ODBC driver? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320241 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
By the time the cfquery tag has finished executing the entire data set has been returned from the database and is loaded into memory in ColdFusion. If the cfquery tag is taking a very long time to complete then: 1) The actual SQL is taking a long time to complete. 2) and/or you are returning a very large amount of data to the ColdFusion server 3) and/or you have very slow connection between your SQL server and your CF server. 302 thousands records is quite a bit-- especially if you have a memo field in there. Can you even add indexes in Access? If so, you might want to consider it. If you just want a count, then just do select count(1) and only a single record has to pass from your database to CF. There are a number of reasons why running the select directly in Access may appear much faster. 1) You are probably running it locally so there is no costly transfer of data between servers. 2) In my experience, Access spools the data behind the scenes and only loads in the records which are visible on the screen which gives your query a very fast turn-around and then it lazy-loads the data in as your scroll down. ~Brad - Original Message - From: Claude Schneegans schneeg...@internetique.com To: cf-talk cf-talk@houseoffusion.com Sent: Sunday, March 08, 2009 5:42 PM Subject: What the heck is happening during CFQUERY? Hi, I've always thought that all what CFQUERY was doing was to create some connection to the database, and then the actual content of all records would be read as needed during some loop on the result set. I have this statement on some Access database : CFQUERY NAME=getArmes DATASOURCE=Armoriaux SELECT armeId, armTexte FROM armesArmoriaux /CFQUERY CFOUTPUTgetArmes.recordCount = #getArmes.recordCount#BR cfquery.ExecutionTime = #cfquery.ExecutionTime#BR/CFOUTPUTCFABORT The query semms to take for ever. Actually, the output says: getArmes.recordCount = 303203 cfquery.ExecutionTime = 614469 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320242 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
Change it to this and the time should go down by a factor of about 100,000 : CFQUERY NAME=getArmes DATASOURCE=Armoriaux SELECT count (*) as N FROM armesArmoriaux /CFQUERY CFOUTPUTgetArmes.recordCount = #getArmes.n#BR cfquery.ExecutionTime = #cfquery.ExecutionTime#BR/CFOUTPUTCFABORT Hi, I've always thought that all what CFQUERY was doing was to create some connection to the database, and then the actual content of all records would be read as needed during some loop on the result set. I have this statement on some Access database : CFQUERY NAME=getArmes DATASOURCE=Armoriaux SELECT armeId, armTexte FROM armesArmoriaux /CFQUERY CFOUTPUTgetArmes.recordCount = #getArmes.recordCount#BR cfquery.ExecutionTime = #cfquery.ExecutionTime#BR/CFOUTPUTCFABORT The query semms to take for ever. Actually, the output says: getArmes.recordCount = 303203 cfquery.ExecutionTime = 614469 More than 10 min. just to settle a result set? That does not make sense. Ok, armTexte is a memo field, but is CFQUERY supposed to read all of them? If I run the same query directly from Access, it runs so fast, I cannot even measure the time it takes. So, what the heck CF is doing within CFQUERY? Read all content? That does not make sense. Is the problem with CF, or the ODBC driver? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320243 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
