Help with Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with Query

Author

Topic

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-12-19 : 05:55:16

Hi,

I am having 3 tables. Load,Balance,Log.

I am comparing the load and log tables and adding amount of the new records in the load table that does not exists in the log table.

Step 1: Updating the Balance in the balance table from the added amount.

Step 2: Next step I am inserting the records in the log table.

The problem is

The records in the loads table is inserted every second rather every 100 milliseconds. These are transactional data.

The queries used are as follows.

STEP 1:

UPDATE Bal
SET Bal.AvlBalance = (Bal.AvlBalance - LoadBal.TotalLoadAmt)
FROM dbo.[Balance] Bal
INNER JOIN (SELECT Load.CardProduct,sum(LoadAmt) AS TotalLoadAmt
FROM [Loads] Load
LEFT JOIN dbo.Log BalLog
ON BalLog.ID = Load.ID
AND BalLog.CardProduct = Load.CardProduct
WHERE BalLog.ID IS NULL
AND Load.CardProduct IN ('cp51','cp52','cc51','LP01','AP01','AP02')
GROUP BY Load.CardProduct) LoadBal
ON LoadBal.[CardProduct] = Bal.[ProductCode]
WHERE Bal.ProductCode IN ('cp51','cp52','cc51','LP01','AP01','AP02')

STEP 2:

INSERT INTO dbo.Log
SELECT Load.ID,Load.CardProduct,GETDATE(),LoadAmt,'update'
FROM [Loads] Load
LEFT OUTER JOIN dbo.Log BalLog
ON BalLog.ID = Load.ID
AND BalLog.CardProduct = Load.CardProduct
WHERE
BalLog.ID IS NULL
AND
Load.CardProduct IN ('cp51','cp52','cc51','LP01','AP01','AP02')

The processing time between step 1 and step 2 is approximate 200ms. So the records are not updating but inserting into the log table.

How to handle this situation.??

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 06:23:27

why not put the update/inserts in its own transaction?
so that unless insertion part is over, updation part wont execute

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-12-19 : 06:26:12

I need to update first and then Insert those updated records in the log table.

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 06:32:15

hmm? then you can put insert logic inside update trigger to do insertion automatically for each update operation. or if you want to do it inline you can make use of composable DML to do the insert inline with the update

see

http://weblogs.sqlteam.com/peterl/archive/2009/04/08/Composable-DML.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2012-01-20 : 07:15:30

Thanks Visakh. Kindly can you let me know how to rewrite these below statements using composed DML.

UPDATE Bal
SET Bal.AvlBalance = (Bal.AvlBalance - LoadBal.TotalLoadAmt)
FROM dbo.[Balance] Bal
INNER JOIN (SELECT Load.CardProduct,sum(LoadAmt) AS TotalLoadAmt
FROM [Loads] Load
LEFT JOIN dbo.Log BalLog
ON BalLog.ID = Load.ID
AND BalLog.CardProduct = Load.CardProduct
WHERE BalLog.ID IS NULL
AND Load.CardProduct IN ('cp51','cp52','cc51','LP01','AP01','AP02')
GROUP BY Load.CardProduct) LoadBal
ON LoadBal.[CardProduct] = Bal.[ProductCode]
WHERE Bal.ProductCode IN ('cp51','cp52','cc51','LP01','AP01','AP02')

STEP 2:

INSERT INTO dbo.Log
SELECT Load.ID,Load.CardProduct,GETDATE(),LoadAmt,'update'
FROM [Loads] Load
LEFT OUTER JOIN dbo.Log BalLog
ON BalLog.ID = Load.ID
AND BalLog.CardProduct = Load.CardProduct
WHERE
BalLog.ID IS NULL
AND
Load.CardProduct IN ('cp51','cp52','cc51','LP01','AP01','AP02')

Thanks,
Sandesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources