Re: CFtransaction questions
that really shouldn't cause you to only be able to run 1 request at a time. what actually happens if you run the page twice at the same time ? On Tue, Nov 1, 2011 at 12:39 AM, Matt Blatchley m...@bridgeleaf.com wrote: Win2003 Standard I removed the cflock and I'm currently using cftransaction isolation=READ_COMMITTED ~| 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:348399 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
I thought that would be the case as well after researching a bit further, but I haven't been able to get the two to finish at the same time. Could just be a timing thing. The code that is executed once the XML response is returned is quite lengthy, performing a number of checks and rolling up the daily data into monthly values and re-inserting those as well. I'm going to go back through and double check to make sure I don't have anything nested, or if there is anything else I can find that might be causing this. Thank you both for the direction, looks like I've got a bit more to work through. Matt ~| 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:348400 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
well don;t forget that CF now has debugging again, so you could use cfeclipse to step through and see what is happening. I wouldn't expect 2 concurrent requests to run exactly the same, as presumably each would be working on different data surely ? On Tue, Nov 1, 2011 at 11:22 AM, Matt Blatchley m...@bridgeleaf.com wrote: I thought that would be the case as well after researching a bit further, but I haven't been able to get the two to finish at the same time. Could just be a timing thing. The code that is executed once the XML response is returned is quite lengthy, performing a number of checks and rolling up the daily data into monthly values and re-inserting those as well. I'm going to go back through and double check to make sure I don't have anything nested, or if there is anything else I can find that might be causing this. Thank you both for the direction, looks like I've got a bit more to work through. Matt ~| 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:348401 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
Very true. Thanks Russ. ~| 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:348402 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
On Tue, Nov 1, 2011 at 1:13 AM, Matt Blatchley wrote: Microsoft SQL Server 2008 (SP1) Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) Database table stores the Metrics by daily values so it's constantly being hit by the imported data and the user interface. That is what snapshot isolation is designed for: http://msdn.microsoft.com/en-us/library/tcbchxcb%28v=vs.80%29.aspx Use with updlock and as short lock acquisition timeout to grab items from a queue table and the snapshot isolation will make sure the concurrent updates to the metrics table won't affect eachother. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| 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:348403 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
That is what snapshot isolation is designed for: http://msdn.microsoft.com/en-us/library/tcbchxcb%28v=vs.80%29.aspx Great information, thank you Jochem. ~| 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:348404 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CFtransaction questions
Greeting all, I'm running into an issue and was wondering if someone could give me some sort of feedback if this can be accomplished. Background: The project I'm working on involves running a CFC that calls the Google AdWords API for a number of specific accounts and then dumps the data into a table. In total we make about 200 calls to the API throughout the course of the night to gather this data for reporting purposes. This part works great when the API is running quickly (right now I have it timeout after 5 minutes) To do this I created a Queue and a Queue manager. The Queue manager runs twice a day to insert or re-insert items into the queue that need to be updated (reports, API calls, alerts etc). The queue itself is executed by a single scheduled task that runs every 5 minutes and so far that runs smoothly, but it takes about 10-12 hours to finish everything in the queue. And I need to somehow cut that time in half. So my question is this: Is it possible to have multiple queues running on different machines and dumping the data into the same table? Using cftransaction, the second queue attempts to run but has to wait until the main Queue finishes before the secondary queue starts. I've read into the Isolation attribute in the cftransaction tag and the READ_COMMITTED but still not getting far. Would I have to use TEMP tables or is there some other way of accomplishing this? I'm currently using CF9 on the main machine and CF7 on the other. Thanks, ~| 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:348368 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
How about using cfthread and processing multiple items with each request, that will at least half the time it takes. Regards Russ Michaels From my mobile On 31 Oct 2011 19:57, Matt Blatchley m...@bridgeleaf.com wrote: Greeting all, I'm running into an issue and was wondering if someone could give me some sort of feedback if this can be accomplished. Background: The project I'm working on involves running a CFC that calls the Google AdWords API for a number of specific accounts and then dumps the data into a table. In total we make about 200 calls to the API throughout the course of the night to gather this data for reporting purposes. This part works great when the API is running quickly (right now I have it timeout after 5 minutes) To do this I created a Queue and a Queue manager. The Queue manager runs twice a day to insert or re-insert items into the queue that need to be updated (reports, API calls, alerts etc). The queue itself is executed by a single scheduled task that runs every 5 minutes and so far that runs smoothly, but it takes about 10-12 hours to finish everything in the queue. And I need to somehow cut that time in half. So my question is this: Is it possible to have multiple queues running on different machines and dumping the data into the same table? Using cftransaction, the second queue attempts to run but has to wait until the main Queue finishes before the secondary queue starts. I've read into the Isolation attribute in the cftransaction tag and the READ_COMMITTED but still not getting far. Would I have to use TEMP tables or is there some other way of accomplishing this? I'm currently using CF9 on the main machine and CF7 on the other. Thanks, ~| 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:348384 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
On Mon, Oct 31, 2011 at 8:56 PM, Matt Blatchley wrote: So my question is this: Is it possible to have multiple queues running on different machines and dumping the data into the same table? Yes. Using cftransaction, the second queue attempts to run but has to wait until the main Queue finishes before the secondary queue starts. That is probably caused by the exact SQL used in combination with the locking strategy your database is using. You have to either make sure your database doesn't use predicate locking, or that you don't run multi row statements by separating the table that holds the actual queue from the one where you lock your records in a transaction More details on database schema's, vendors and versions will result in more details in the answer :) Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| 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:348391 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
I'm not sure how much flexibility I have on this version because it's a hosted solution in a shared environment: Microsoft SQL Server 2008 (SP1) Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) Database table stores the Metrics by daily values so it's constantly being hit by the imported data and the user interface. The other thing is both CF versions are Standard, not Enterprise, so I'm limited in the use of CFThread, but I did try it anyway :) Thanks Matt On Mon, Oct 31, 2011 at 5:45 PM, Jochem van Dieten joch...@gmail.com wrote: On Mon, Oct 31, 2011 at 8:56 PM, Matt Blatchley wrote: So my question is this: Is it possible to have multiple queues running on different machines and dumping the data into the same table? Yes. Using cftransaction, the second queue attempts to run but has to wait until the main Queue finishes before the secondary queue starts. That is probably caused by the exact SQL used in combination with the locking strategy your database is using. You have to either make sure your database doesn't use predicate locking, or that you don't run multi row statements by separating the table that holds the actual queue from the one where you lock your records in a transaction More details on database schema's, vendors and versions will result in more details in the answer :) Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| 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:348392 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
what type of cftransaction are you using, do u have cflocking as well, if so what type. are you really using Windows NT ? i'm pretty sure the minimum requirements for SQL Server 2008 is windows 2003 server. Russ ~| 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:348393 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
Win2003 Standard I removed the cflock and I'm currently using cftransaction isolation=READ_COMMITTED ~| 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:348395 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
