Morning All,I'm completely new to the forum and hoping we'll be able to crack this problem. I've inherited some code which INSERTs data into a 2k5 database from a webservice however its causing me a great number of 'deadlock' errors and I've tasked this as my first challenge of the year to solve as its causing me headaches and planned expansions in the business and application usage have got me worried that its going to get messy.I'm hoping to either modify my current script to solve the deadlock or create a completely new method for loading the data, I'm really not bothered which, I just want something scalable and stable.Let me start by explaining a little bit about the use case that we have.I have at present roughly around 30 remote clients making calls to my applications webservice, each client makes a call roughly every minute posting on average around 5-10 rows of data with a maximum of 500 rows of data in any one call. The x-factor here is the number of remote clients calling the service, this is set to grow over the coming months so I want a solution which is going to scale nicely.The current import process takes the data which is posted to the webservice and loads it into a temporary table, the data is then extracted from this temporary table and inserted into other tables building FK relationships as it goes.This is what the current import transaction looks like, you'll have to excuse the Adobe CF server side code, I'm sure you can read around it :-)<!--- Create the temporary memory table. ---><cfquery name="LOCAL.qCreate" datasource="MyDSN"> CREATE TABLE ##MessageStaging ( DeviceAddress char(17) Collate Database_Default, PushPort smallint , LogDateTime datetime, LogClass varchar(50) Collate Database_Default, Media_ID int, Campaign_ID int )</cfquery> <cfloop from="1" to="#arrayLen(ARGUMENTS.Statistics_Data)#" index="LOCAL.i"> <!--- Load the data into the temporary table. ---> <cfquery name="LOCAL.qLoad" datasource="MyDSN"> INSERT INTO ##MessageStaging ( DeviceAddress, PushPort, LogDateTime, LogClass, Media_ID, Campaign_ID ) VALUES ( <cfqueryparam value="#ARGUMENTS.Statistics_Data[LOCAL.i].address#" cfsqltype="cf_sql_varchar" />, '0', <cfqueryparam value="#ARGUMENTS.Statistics_Data[LOCAL.i].created#" cfsqltype="cf_sql_timestamp" />, <cfqueryparam value="#ARGUMENTS.Statistics_Data[LOCAL.i].result#" cfsqltype="cf_sql_varchar" />, <cfqueryparam value="#ARGUMENTS.Statistics_Data[LOCAL.i].message_id#" cfsqltype="cf_sql_integer" />, <cfqueryparam value="#ARGUMENTS.Statistics_Data[LOCAL.i].campaign_id#" cfsqltype="cf_sql_integer" /> ) </cfquery></cfloop> <!--- Load the data from staging into the statistics database properly. ---><cfquery name="LOCAL.qClever" datasource="MyDSN"> /* ---------------------------------------------------------- Capture OUIs we have not seen before from MessageStaging ---------------------------------------------------------- */ INSERT INTO OUI ( OUI , Manufacturer ) SELECT DISTINCT LEFT(##MessageStaging.DeviceAddress, 8) , 'Unknown OUI, ' + CONVERT(VARCHAR(25), GETDATE(), 121) FROM ##MessageStaging LEFT OUTER JOIN OUI ON OUI.OUI = LEFT(##MessageStaging.DeviceAddress, 8) WHERE OUI.OUI IS NULL /* ---------------------------------------------------------- Capture Devices we have not seen before from ##MessageStaging ---------------------------------------------------------- */ INSERT INTO Device ( OUI_ID , Address , PushPort ) SELECT DISTINCT OUI.OUI_ID , SUBSTRING(##MessageStaging.DeviceAddress,10,8) , ##MessageStaging.PushPort FROM ##MessageStaging INNER JOIN OUI ON OUI.OUI = left(##MessageStaging.DeviceAddress,8) LEFT OUTER JOIN Device ON Device.OUI_ID = OUI.OUI_ID AND Device.Address = SUBSTRING(##MessageStaging.DeviceAddress,10,8) WHERE Device.OUI_ID IS NULL /* ---------------------------------------------------------- Update Device NULL PushPorts (if any) from ##MessageStaging ---------------------------------------------------------- */ UPDATE Device SET PushPort = ##MessageStaging.PushPort FROM ##MessageStaging INNER JOIN OUI ON OUI.OUI = left(##MessageStaging.DeviceAddress,8) INNER JOIN Device as D2 ON D2.OUI_ID = OUI.OUI_ID AND D2.Address = SUBSTRING(##MessageStaging.DeviceAddress,10,8) AND ( D2.PushPort IS NULL or D2.PushPort <> ##MessageStaging.PushPort ) /* ---------------------------------------------------------- Import any new log classes. ---------------------------------------------------------- */ INSERT INTO LogClass (Name) SELECT DISTINCT ##MessageStaging.LogClass FROM ##MessageStaging LEFT OUTER JOIN LogClass ON LogClass.Name = ##MessageStaging.LogClass WHERE LogClass.Name IS NULL /* ---------------------------------------------------------- Import into MessageLog table ---------------------------------------------------------- */ INSERT INTO MessageLog ( Device_ID , LogDateTime , LogClass_ID , Media_ID , Campaign_ID , ThinkTank_ID ) SELECT Device.Device_ID , ##MessageStaging.LogDateTime , LogClass.LogClass_ID , ##MessageStaging.Media_ID , ##MessageStaging.Campaign_ID , '#LOCAL.tank_id#' FROM ##MessageStaging INNER JOIN LogClass ON LogClass.Name = ##MessageStaging.LogClass INNER JOIN OUI ON OUI.OUI = LEFT(##MessageStaging.DeviceAddress,8) INNER JOIN Device ON Device.OUI_ID = OUI.OUI_ID AND Device.Address = SUBSTRING(##MessageStaging.DeviceAddress,10,8) </cfquery>
one small note, I know I have double pound signs however this is NOT a global temp table, ColdFusion uses pound signs itself so I have to escape them, at execution time this is just a single pound sign and creates a local temp table.The data posted to the server mirrors the datatypes of the temporary staging table so you can see its not massive data, just a couple of short strings, some int's and a couple of dates, no large blob data or anything like that.Now, presumably these deadlock issues I've been seeing are caused by a slow running transaction!?!?! which means I either need to modify the process above to shorten its execution so its not hogging the resources for other client requests trying to import into the same tables, or I need to find another route.If you guys had this kind of data which needed to be loaded what method would you take? Should I perhaps not be loading this with a SQL script from my application? perhaps have my app write this all to the FS and then have some form of SSIS script which runs every minute and loads the data in?I'm really open to ALL suggestions on how to achieve this, my key concern are:- minimize performance hit on the server.
- ensure its scalable so as the number of remote clients grows the process will cope.
- import needs to be as 'live' as possible so data appears in the database quickly, this rules out SSIS tasks that run every hour or something like that, too much latency on the data, clients need to report on it pretty shortly after its been posted back to the server.
I don't want to run into issues as the number of remote clients increases to 100's.If you have any questions then please to let me know, I'll be only too happy to supply any further info.Many thanks guys,Heston
New years data insert challenge :-)
Posted - 2009-01-02 : 06:12:01