Query of Query Timeout
Has anyone ever experienced a query of query timeout? I have a Query of Query that I am running against a result set that has several thousand records in it (no more than 8,192). From time to time (more often than not) the page fails on a 'The request has exceeded the allowable time limit Tag: CFQUERY' error. This is a Q of Q that is basically filtering out some records in the original result set. It uses a LIKE search (which I know is a no-no on databases, but haven't had trouble before on QofQ's). Any thoughts? ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262919 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Query Timeout
I've seen that same error quite a few times but I only get it in emails sent by error handling templates. I've never been able to recreate it myself. When I get 1 though, I get many right behind it. It's as if the server just had gas for a second then let out a big one. Everything goes right back to normal before I can catch it. ..:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Dave Phillips [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 05, 2006 10:57 AM To: CF-Talk Subject: Query of Query Timeout Has anyone ever experienced a query of query timeout? I have a Query of Query that I am running against a result set that has several thousand records in it (no more than 8,192). From time to time (more often than not) the page fails on a 'The request has exceeded the allowable time limit Tag: CFQUERY' error. This is a Q of Q that is basically filtering out some records in the original result set. It uses a LIKE search (which I know is a no-no on databases, but haven't had trouble before on QofQ's). Any thoughts? ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262921 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query of Query Timeout
The amount of time that ColdFusion will allow for a tag to complete is a setting in the administrator that defaults to 60 seconds. You can bump this up if required. Alternately you can replace your QofQ with potentially better performing logic. At a conference a couple of months ago, a CF engineer pointed out that CF is NOT a database management system and it will never be as efficient as an enterprise DBMS at query parsing and processing. He suggested, if one is working with large record sets, to either pass the heavy lifting back to the DBMS to have it return the desired results or turn the large record set into a structure and do any desired filtering and processing on the structure. ColdFusion is much more efficient at working with structures. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA - | 1 | | - Binary Soduko | | | - C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262923 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Query Timeout
It would be an array of strutures. Each array being one row, and containing a struture key for each column Russ -Original Message- From: Dave Phillips [mailto:[EMAIL PROTECTED] Sent: 05 December 2006 17:30 To: CF-Talk Subject: RE: Query of Query Timeout Interesting. In this case, the query result is actually coming from a CFSEARCH, so I'm not even getting it from the DBMS. How would that work converting the query into a structure? Is that basically an array of structures or a structure of arrays? Or a structure of structures? Dave -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 05, 2006 11:20 AM To: CF-Talk Subject: RE: Query of Query Timeout The amount of time that ColdFusion will allow for a tag to complete is a setting in the administrator that defaults to 60 seconds. You can bump this up if required. Alternately you can replace your QofQ with potentially better performing logic. At a conference a couple of months ago, a CF engineer pointed out that CF is NOT a database management system and it will never be as efficient as an enterprise DBMS at query parsing and processing. He suggested, if one is working with large record sets, to either pass the heavy lifting back to the DBMS to have it return the desired results or turn the large record set into a structure and do any desired filtering and processing on the structure. ColdFusion is much more efficient at working with structures. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA - | 1 | | - Binary Soduko | | | - C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262926 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query of Query Timeout
Is that basically an array of structures or a structure of arrays? Or a structure of structures? Any of the above, you would just output the query into a structure of your design and then use that structure for future filtering and processing. This is apparently one of those 9 out of 10 kind of thing where a well designed structure is usually better performing then running QofQ's on a record set. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA - | 1 | | - Binary Soduko | | | - C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262927 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Query Timeout
It sounds like getting a stack trace during execution might help you prove what the offending line of code is. I know I've sung this song a lot, but SeeFusion is an excellent resource for this and I love it. www.seefusion.com ~Brad -Original Message- From: Dave Phillips [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 05, 2006 11:05 AM To: CF-Talk Subject: Re: Query of Query Timeout Here's another question. I added a cfoutput#getTickCount()#/cfoutputbr line directly after my CFSEARCH tag (and before my QofQ) and now the timeout is happening on that CFOUTPUT tag. Taht seems very strange. It is almost as if the CFSEARCH tag is the one timing out, but CF is reporting it on the next available tag. Is it possible that if the CFSEARCH takes longer than the 60 second limit, that as soon as Verity has returned handling to CF, the request then terminates because it has exceeded the 60 seconds? If so, what accounts for the fact that some searches actually finish and report in the server log as taking longer than the 60 seconds? ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262931 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
CF Query Timeout
Hello, I have a unique situation that I only have limited control over. I was hoping someone on the list could provide some assistance. Here is my issue. A have a CF Site on a shared hosting account at Crystaltech. The database is Access. This was fine for a certain amount of time but now either there is too much activity on the shared box or the query are returning too much data that the CFQUERY timeout is becoming and issue. The data in access is being supplied from the client via FTP and the CF app accesses it. It is over written over night. I can change the way the client gives me the data it is an automated process. So I was thinking that I would mirror the exact tables in MS SQL then once a night select all the content from Access and insert it into SQL. Then have all my users access the SQL tables. Sounds logical, the only issue is that I can get the all the records to insert into the MSSQL database in a timely fasion. I keep getting the CFQERY timeout. Maybe its the way that I am doing it that is inefficient. I am using both DSN like so, cfquery name=GetPayments datasource=#request.AccessDsn# Select * from Client_Pmt /cfquery cfoutput query=GetPayments cfquery datasource=#request.MSSQLDsn# name=qry_insertPayments insert into Client_Pmt (Acct_id, PMT_Date,Pmt_Code,PMTAMT) Values (#Getpayments.DEBT_ID#, '#Getpayments.PMT_DATE#', '#Getpayments.PMT_CODE#', #Getpayments.PMTAMT#) /cfquery /cfoutput Is there a better way to do this? Thanks Mike ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220500 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: CF Query Timeout
A fairly normal problem with access, if it is a big database. If it is not a particularly big db, try doing a repair on it and see if that improves things, in which case you need to get your client to regularly do this. When a access database is regularly in use it tends to become bloated with crap and doing a repair optimises it. Of course on a shared host, you will have hundreds of other people on your server using access databases as well, and most of them will be using it beyond it's abilities and putting too many connections through it, which will be causing some problem for the CF ODBC service and for CF itself as it queues up all those connections. Best, don't use Access. Get your client to export the data to a CSV and import that. Russ -Original Message- From: Mickael [mailto:[EMAIL PROTECTED] Sent: 09 October 2005 14:32 To: CF-Talk Subject: CF Query Timeout Hello, I have a unique situation that I only have limited control over. I was hoping someone on the list could provide some assistance. Here is my issue. A have a CF Site on a shared hosting account at Crystaltech. The database is Access. This was fine for a certain amount of time but now either there is too much activity on the shared box or the query are returning too much data that the CFQUERY timeout is becoming and issue. The data in access is being supplied from the client via FTP and the CF app accesses it. It is over written over night. I can change the way the client gives me the data it is an automated process. So I was thinking that I would mirror the exact tables in MS SQL then once a night select all the content from Access and insert it into SQL. Then have all my users access the SQL tables. Sounds logical, the only issue is that I can get the all the records to insert into the MSSQL database in a timely fasion. I keep getting the CFQERY timeout. Maybe its the way that I am doing it that is inefficient. I am using both DSN like so, cfquery name=GetPayments datasource=#request.AccessDsn# Select * from Client_Pmt /cfquery cfoutput query=GetPayments cfquery datasource=#request.MSSQLDsn# name=qry_insertPayments insert into Client_Pmt (Acct_id, PMT_Date,Pmt_Code,PMTAMT) Values (#Getpayments.DEBT_ID#, '#Getpayments.PMT_DATE#', '#Getpayments.PMT_CODE#', #Getpayments.PMTAMT#) /cfquery /cfoutput Is there a better way to do this? Thanks Mike ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220504 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Query Timeout
Why does this never actually work. I am running a query on an ODBC datasource. The ODBC is connecting to an informix database. I don't want the query to take longer then 30 seconds. Not that coldfusion cares what I tell it the timeout value is. The main question is: Why does the timeout not work. The second question is Is there any way to wrap things with a timeout value as to say if what is inside of here take more then x time throw an error. Also is there a way to run a query in what would be like a seperate thread similar to doing a callback so that while the query is proccessing other things can run and then set a wait point or something? Tyler Clendenin GSL Solutions, Inc. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query Timeout
Why does this never actually work. I am running a query on an ODBC datasource. The ODBC is connecting to an informix database. I don't want the query to take longer then 30 seconds. Not that coldfusion cares what I tell it the timeout value is. The main question is: Why does the timeout not work. The second question is Is there any way to wrap things with a timeout value as to say if what is inside of here take more then x time throw an error. Also is there a way to run a query in what would be like a seperate thread similar to doing a callback so that while the query is proccessing other things can run and then set a wait point or something? AFAIK the Timeout on a CFQUERY is a connection timeout So if the query is taking longer than 30 seconds, then it won't be timed out I'm not sure if you can put something to timeout around a CFQUERY... ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
