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: iPod Apple 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: > > > > <!--- Start the transaction ---> > > <cftransaction> > > <!--- Create the temporary memory table. ---> > > <cfquery name="LOCAL.qCreate" datasource="MyDSNName"> > > CREATE TABLE #MyTempTable.... > > </cfquery> > > > > <!--- Loop over the array of complex objects and populate the > > temporary table. ---> > > <cfloop from="1" to="#arrayLen(ARGUMENTS.Statistics_Data)#" > > index="LOCAL.i"> > > <!--- Load the data into the temporary table. ---> > > <cfquery name="LOCAL.qLoad" datasource="MyDSNName"> > > INSERT INTO... > > </cfquery> > > </cfloop> > > > > <!--- Load the data from staging into the first table if they > > donââ¬â¢t already exist to ensure FK's exist for the primary table > > insert. ---> > > <cfquery name="LOCAL.CreateFKs1" datasource="MyDSNName"> > > INSERT INTO FkTable1 (.... > > SELECT col1 > > FROM #MyTempTabl > > </cfquery> > > > > <!--- Load the data from staging into the first table if they > > donââ¬â¢t already exist to ensure FK's exist for the primary table > > insert. ---> > > <cfquery name="LOCAL.CreateFKs2" datasource="MyDSNName"> > > INSERT INTO FkTable2 (.... > > SELECT col1 > > FROM #MyTempTabl > > </cfquery> > > > > <!--- Load the data into the primrary table ---> > > <cfquery name="LOCAL.InsertPrimary" datasource="MyDSNName"> > > 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 > > </cfquery> > > > > <!--- Cleanup the oppertation by dropping the temporary table. > -- > > -> > > <cfquery name="LOCAL.qCleanup" datasource="MyDSNName"> > > DROP TABLE #MyTempTable... > > </cfquery> > > </cftransaction> > > > > 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:317702 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
