Re: Loading data from a webservice call into MSSQL.
> > Rob I sent your updated code to you via email Sam ~| 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:317752 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Loading data from a webservice call into MSSQL.
Hi Sam, > Oh, and the relative lifetime of these objects drops significantly as > well. > the disk table is going to last for LOTS of milliseconds (10ms per I/O > at least, times too many to count). here you won't incur ANY I/O > unless paging gets involved. > so you reduce the elapsed time by orders of magnitude for these table > operations as well, if you have just 10ms of CODE to populate the in > memory table it would be a HUGE table).. > > not to mention all the I/O impact on the real table. disk arm movement, > blah blah. > (and impact on the sql system buffering, cache flushes, etc.. ) > > Sam All this certainly sounds promising, minimizing the locks on the resources should solve the problems I've been having, very cool news indeed. Now, I'm not used to working with QofQ's at all, can you provide a little example of how I can query the in memory query and populate my real life tables with it? For instance my current queries using the temp table look something like: INSERT INTO RealTable (col1, col2, col3) SELECT Col1, Col2, Col3 FROM #TempTable How can this be reworked to draw from the in memory query opposed to the temp table? Thanks Sam, once I've got this I'll do some load testing and see how she holds up. Rob ~| 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:317707 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Loading data from a webservice call into MSSQL.
> That sounds fair about the disk I/O, we have contingency built into > the client applications so that if an exception is thrown by the web > service then the client will reattempt to upload the records in its > next request so the odd error every now and then is not essential but > I'm looking to limit it as much as possible. > > Thanks Sam, > > Rob Oh, and the relative lifetime of these objects drops significantly as well. the disk table is going to last for LOTS of milliseconds (10ms per I/O at least, times too many to count). here you won't incur ANY I/O unless paging gets involved. so you reduce the elapsed time by orders of magnitude for these table operations as well, if you have just 10ms of CODE to populate the in memory table it would be a HUGE table).. not to mention all the I/O impact on the real table. disk arm movement, blah blah. (and impact on the sql system buffering, cache flushes, etc.. ) Sam ~| 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:317706 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Loading data from a webservice call into MSSQL.
> Hi Sam, > > > You should be able to create an in memory query to represent the > > received data (your temp 'table' is rows and columns, same as a > > 'query'), and then your other operations would proceed normally with > a > > few minor changes in syntax, change the datasource= to dbtype=) > > Ok, so you're suggesting that I use an in memory query instead of the > temp table, that's fair enough. So, are you suggesting a ColdFusion > query? or a SQL Variable such as @TempTable? What are likely to be the > performance limitations on larger datasets? say I want to up the post > limit to 500 records per request from a client, will this hold up do > you think? > > > this would remove all the disk I/O and locking for the temp table, > and > > make this db access truely concurrent(well writes to the REAL tables > > > still invoke locking). You can use the caching options to help > protect > > against outage I would expect. what are the design constraints about > > > losing one of the updates in the event of a crash? > > That sounds fair about the disk I/O, we have contingency built into > the client applications so that if an exception is thrown by the web > service then the client will reattempt to upload the records in its > next request so the odd error every now and then is not essential but > I'm looking to limit it as much as possible. > > Thanks Sam, > > Rob I'd use the CF query. a tiny bit more code to move the data from xml, but I htink lower overhead overall (cf, to sql driver, to sql engine, to in memory, to disk and back, vs you writing this minor code, which you have to do most of it anyhow to get the data lined up for the insert).. 500 records is noise.. if you were doing 10,000 I might be concerned. (you said 10-100 is normal, so 500 would still be an exception), but the pathlength difference (not counting no locking) would still be enormous. I think then the rest of your code should be pretty much the same (see query of queries for any sql syntax details) (and you don't need the drop table either). Sam ~| 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:317705 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Loading data from a webservice call into MSSQL.
Hi Sam, > You should be able to create an in memory query to represent the > received data (your temp 'table' is rows and columns, same as a > 'query'), and then your other operations would proceed normally with a > few minor changes in syntax, change the datasource= to dbtype=) Ok, so you're suggesting that I use an in memory query instead of the temp table, that's fair enough. So, are you suggesting a ColdFusion query? or a SQL Variable such as @TempTable? What are likely to be the performance limitations on larger datasets? say I want to up the post limit to 500 records per request from a client, will this hold up do you think? > this would remove all the disk I/O and locking for the temp table, and > make this db access truely concurrent(well writes to the REAL tables > still invoke locking). You can use the caching options to help protect > against outage I would expect. what are the design constraints about > losing one of the updates in the event of a crash? That sounds fair about the disk I/O, we have contingency built into the client applications so that if an exception is thrown by the web service then the client will reattempt to upload the records in its next request so the odd error every now and then is not essential but I'm looking to limit it as much as possible. Thanks Sam, Rob ~| 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:317704 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Loading data from a webservice call into MSSQL.
> Posted this yesterday but seems to have disappeared and not come to > the list :-s > > Afternoon Guys, > > I publish a web service which receives statistical data in an array of > complex objects from clients. This data then gets processed and > inserted into a SQL Server db to be reported from at a later time. > > Iâve been having a few issues in the past with deadlocks on the server > because the import process isnât very efficient and is locking sql > resources for too long, this hasnât been a major issue however with > recent growth within the business and an increase in our client base > weâre seeing it more and more regularly and I want to nip it in the > bud before too long. > You should be able to create an in memory query to represent the received data (your temp 'table' is rows and columns, same as a 'query'), and then your other operations would proceed normally with a few minor changes in syntax, change the datasource= to dbtype=) this would remove all the disk I/O and locking for the temp table, and make this db access truely concurrent(well writes to the REAL tables still invoke locking). You can use the caching options to help protect against outage I would expect. what are the design constraints about losing one of the updates in the event of a crash? Sam ~| 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:317703 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Loading data from a webservice call into MSSQL.
Afternoon Adrian, thanks for getting back to me. Let me try and explain a little about what the temp table is used for. Basically lets use an example database schema for this, for now let's say we have two tables: Manufacturer Manufacturer_ID Manufacturer_Name Products Product_ID Manufacturer_ID Product_Name Now, for an example of the data which comes in from the web service call in the complex objects: Product Name: Manufacturer Name: iPodApple iPhone Apple Tastey Cake Grandma Now, product names that are posted are always new and never seen before, however, the manufacturer of the product may or may not already exist in the database. So, I load the data from the complex objects into the temporary table which mimics that structure, the first query then checks to see if any new manufacturers exist in the data which has been posted, if they have then it inserts them so they can be references as a FK in the products table when they are inserted by the final query. Does that make sense? I hope thatâs a clear example. Many thanks, Rob > Each of those cfqueries is a separate call to the DB, could you wrap > it all in one cfquery and see if it makes a difference? > > Why are you using the temp table for? Explain that a bit more as it > might not be the best way or needed at all. > > Seems awfully complex code for the problem you're describing. > > Adrian > > > -Original Message- > > From: Robert Rawlins [mailto:robert.rawl...@thinkbluemedia.co.uk] > > Sent: 10 January 2009 11:41 > > To: cf-talk > > Subject: Loading data from a webservice call into MSSQL. > > > > Posted this yesterday but seems to have disappeared and not come to > the > > list :-s > > > > Afternoon Guys, > > > > I publish a web service which receives statistical data in an array > of > > complex objects from clients. This data then gets processed and > > inserted into a SQL Server db to be reported from at a later time. > > > > Iââ¬â¢ve been having a few issues in the past with deadlocks on the > > server because the import process isnââ¬â¢t very efficient and is > locking > > sql resources for too long, this hasnââ¬â¢t been a major issue > however > > with recent growth within the business and an increase in our > client > > base weââ¬â¢re seeing it more and more regularly and I want to nip it > in > > the bud before too long. > > > > Regards to loading of data, weââ¬â¢re looking at around 50 clients, > each > > client makes a request once every minute, posting around 10 records > per > > request, with a maximum of 100 records per request. > > > > Without getting too specific at this stage, the current import > process > > in the web service function looks somewhat like this: > > > > > > > > > > > > CREATE TABLE #MyTempTable > > > > > > > >> index="LOCAL.i"> > > > > > > INSERT INTO... > > > > > > > > > > > > INSERT INTO FkTable1 ( > > SELECT col1 > > FROM #MyTempTabl > > > > > > > > > > INSERT INTO FkTable2 ( > > SELECT col1 > > FROM #MyTempTabl > > > > > > > > > > INSERT INTO PrimaryTable ( > > SELECT FkTable1.id_col, > > FkTable2.id_col, > > #MyTempTable.SomeColumn1, > > #MyTampTable.SomeColumn2 > > FROM #MyTempTable > > INNER JOIN FkTable1 ON FkTable1.col1 = #MyTempTable. > col1 > > INNER JOIN FkTable2 ON FkTable2.col1 = #MyTempTable. > col1 > > > > > >> -> > > > > DROP TABLE #MyTempTable... > > > > > > > > Now, what I hope you can see from that example is that I first > create a > > temporary staging table for the data which is to be loaded, I then > loop > > over the array of complex objects passed to the service method and > > populate the temporary table with them. I then have a couple of > queries > > which insert any records which donââ¬â¢t already exist into the two > FK > > tables. These FK tables are then referenced in the main data import. > > > Finally the temporary staging t
RE: Loading data from a webservice call into MSSQL.
Each of those cfqueries is a separate call to the DB, could you wrap it all in one cfquery and see if it makes a difference? Why are you using the temp table for? Explain that a bit more as it might not be the best way or needed at all. Seems awfully complex code for the problem you're describing. Adrian > -Original Message- > From: Robert Rawlins [mailto:robert.rawl...@thinkbluemedia.co.uk] > Sent: 10 January 2009 11:41 > To: cf-talk > Subject: Loading data from a webservice call into MSSQL. > > Posted this yesterday but seems to have disappeared and not come to the > list :-s > > Afternoon Guys, > > I publish a web service which receives statistical data in an array of > complex objects from clients. This data then gets processed and > inserted into a SQL Server db to be reported from at a later time. > > Iââ¬â¢ve been having a few issues in the past with deadlocks on the > server because the import process isnââ¬â¢t very efficient and is locking > sql resources for too long, this hasnââ¬â¢t been a major issue however > with recent growth within the business and an increase in our client > base weââ¬â¢re seeing it more and more regularly and I want to nip it in > the bud before too long. > > Regards to loading of data, weââ¬â¢re looking at around 50 clients, each > client makes a request once every minute, posting around 10 records per > request, with a maximum of 100 records per request. > > Without getting too specific at this stage, the current import process > in the web service function looks somewhat like this: > > > > > > CREATE TABLE #MyTempTable > > > >index="LOCAL.i"> > > > INSERT INTO... > > > > > > INSERT INTO FkTable1 ( > SELECT col1 > FROM #MyTempTabl > > > > > INSERT INTO FkTable2 ( > SELECT col1 > FROM #MyTempTabl > > > > > INSERT INTO PrimaryTable ( > SELECT FkTable1.id_col, > FkTable2.id_col, > #MyTempTable.SomeColumn1, > #MyTampTable.SomeColumn2 > FROM #MyTempTable > INNER JOIN FkTable1 ON FkTable1.col1 = #MyTempTable.col1 > INNER JOIN FkTable2 ON FkTable2.col1 = #MyTempTable.col1 > > >-> > > DROP TABLE #MyTempTable... > > > > Now, what I hope you can see from that example is that I first create a > temporary staging table for the data which is to be loaded, I then loop > over the array of complex objects passed to the service method and > populate the temporary table with them. I then have a couple of queries > which insert any records which donââ¬â¢t already exist into the two FK > tables. These FK tables are then referenced in the main data import. > Finally the temporary staging table is dropped. > > Would you guys handle this challenge in the same way? If you have > regular request from multiple clients with data which had to be > prepared and saved like this how would you go about it? > > My key concern is efficiency, I want the final solution to be scalable > so as we grow the client base itââ¬â¢ll continue running. Iââ¬â¢m just > not > sure of my best approach at the moment, am I running along the right > tracks? Or should I be taking a totally different approach? > > Iââ¬â¢m sure this is a fairly common task so look forward to getting a > little advice based on your experiences. > > Cheers for now guys, > > Rob ~| 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:317699 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Loading data from a webservice call into MSSQL.
Posted this yesterday but seems to have disappeared and not come to the list :-s Afternoon Guys, I publish a web service which receives statistical data in an array of complex objects from clients. This data then gets processed and inserted into a SQL Server db to be reported from at a later time. Iâve been having a few issues in the past with deadlocks on the server because the import process isnât very efficient and is locking sql resources for too long, this hasnât been a major issue however with recent growth within the business and an increase in our client base weâre seeing it more and more regularly and I want to nip it in the bud before too long. Regards to loading of data, weâre looking at around 50 clients, each client makes a request once every minute, posting around 10 records per request, with a maximum of 100 records per request. Without getting too specific at this stage, the current import process in the web service function looks somewhat like this: CREATE TABLE #MyTempTable INSERT INTO... INSERT INTO FkTable1 ( SELECT col1 FROM #MyTempTabl INSERT INTO FkTable2 ( SELECT col1 FROM #MyTempTabl INSERT INTO PrimaryTable ( SELECT FkTable1.id_col, FkTable2.id_col, #MyTempTable.SomeColumn1, #MyTampTable.SomeColumn2 FROM #MyTempTable INNER JOIN FkTable1 ON FkTable1.col1 = #MyTempTable.col1 INNER JOIN FkTable2 ON FkTable2.col1 = #MyTempTable.col1 DROP TABLE #MyTempTable... Now, what I hope you can see from that example is that I first create a temporary staging table for the data which is to be loaded, I then loop over the array of complex objects passed to the service method and populate the temporary table with them. I then have a couple of queries which insert any records which donât already exist into the two FK tables. These FK tables are then referenced in the main data import. Finally the temporary staging table is dropped. Would you guys handle this challenge in the same way? If you have regular request from multiple clients with data which had to be prepared and saved like this how would you go about it? My key concern is efficiency, I want the final solution to be scalable so as we grow the client base itâll continue running. Iâm just not sure of my best approach at the moment, am I running along the right tracks? Or should I be taking a totally different approach? Iâm sure this is a fairly common task so look forward to getting a little advice based on your experiences. Cheers for now guys, Rob ~| 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:317697 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
