asked
Point of update when using case in an Update statement
hi there, I have cut this down to keep it simple but here goes... I have a table with 4 columns - OrderID, InitialOrderAmount, RemainingOrderAmount, CompletedDate Rule is when the remaining order amount is 0, set the completed date. I have a stored proc that takes in the ID, and the an order amount filled so I want to substract the amount filled from the remaining amount and if its 0 set the completed date. my code is something like this... UPDATE [OrderTable] SET RemainingOrderAmount = RemainingOrderAmount - ABS(@orderAmountFilled ), -- reduce the amount left to close ClosingTime = (CASE WHEN (RemainingOrderAmount - ABS(@orderAmountFilled)) = 0 THEN GETUTCDATE() ELSE NULL END) WHERE OrderID = @orderID ok so what I believed would happen is that even though the update statement updates the RemainingOrderAmount first, the case statement has to test by subtracting the amount as the first part of the update hasnt been committed... so I need the current value and subtract the amount filled. Is this correct. The reason I ask, is that in a test run... 13000 records updated fine but 4 had the maths right in that the RemainingOrderAmount was set to 0 but the completed date didnt get set. Any ideas... thanks Mick
looks like it should work, is there more to this that might be affecting the result?
It's all part of a single transaction, so the values are not being modified first, they're being modified at the same time. Any reads are gathered and you won't see the affects of writes until they're committed. You'd probably need to break this down into two statements within a transaction to make it work.
@Grant +1 - as you said, the values are both calculated at the same time and then updated. @Doiremik - This can be seen in the execution plan, there would be a table scan/clustered index scan/index seek or similar to find the OrderId, then a calculate scalar (with ALL values being calcualted at the same time) and then the update of the table (and any indexes that needed it too). Could you be running into concurrency issues?
Thinking about it some more, it might even be a valid place for a trigger, although, I'd still rather see it in two, controlled steps. I'm not a fan of triggers.
I agree with the above answers, consider this example
DECLARE @temp TABLE
(ID INT ,
Amt INT,
Dt DATE)
INSERT INTO @temp
SELECT 1,1000,GETDATE()
UPDATE @temp
SET Amt = Amt+1000,
Dt = CASE WHEN Amt = 2000 THEN NULL ELSE GETDATE()END
SELECT * FROM @temp
--Result------------
ID Amt Dt
1 2000 2010-10-26
thanks lads. yes indeed I did think on a trigger but the actual implementation is a bit more complex and thats why I do it in the update instead of a trigger as I have some more business rules. I just want to check was there some mad bug or something that I didnt know about. I have had 13000 records all get updated and set correctly except 4 of them and they are the same as the rest. @Grant. Yes indeed Grant I did think there was a concurrency issue but when I checked the logs I keep, I'm only updating this one row at a time as I use the PK ID to do it and its only in my logs once. I also catch exceptions if there is a lock and then retry the operation and this looks clean to. However Grant, is it not the same code if I break it out into two statements except that I have to do a round trip to the database. Thats why I built it into the case, instead of another update statement. Worst thing is, that its obviously a fluky thing and trying to track this down will be fun. @Cyborg - nice clean example cyborg... thanks for the confirmation also regards Mick
ok, you asked for it lol.. here is the actual statement. It does basically what the other simple statement does. I get in a value which can be negative or positive but I just use the abs on it. I'm closing out volume and in this case I know its a close. So I subtract the @VolumentAmount from the amount closing.. add it to the amount closed.. do some working out on pricing etc and then set the ClosingTime. The worst thing is that all the other values are all correct for the 4 values that didnt have their ClosingTime set when it should have. I have this enclosed in a try catch also so I would hope any locks or concurrency issues would be caught. The reason I dont think its concurrency is that the other values are all correct and I get no errors. I also know that only one call was made to update this record. So should I put in an error check and test if the date is not null and throw an exception. Just for the craic, I'm going to run the values through the database to test... might take an hour or so... thanks again... a very confussed Mick -- Dont reduce the amount of VolumeOpened by the amount closed as it has already been reduced on the ScaleOutPosition. -- Position is closed when Opened = Closed UPDATE [Position] SET VolumeClosing = VolumeClosing - ABS(@VolumeAmount), -- reduce the amount left to close VolumeClosed = VolumeClosed + ABS(@VolumeAmount), -- add to the amount Closed FilledReturnValueOnClosing = FilledReturnValueOnClosing + (ABS(@VolumeAmount) * @ActualMarketExecutionPrice), QuotedMarketAskCostOnClosing = (CASE WHEN AccountBaseCurrency != SUBSTRING(Asset,5,3) -- if we are closing a long position and the Base currency doesnt match the Base Asset then we need to store the ask for conversions in RPL THEN (QuotedMarketAskCostOnClosing + (ABS(@VolumeAmount) * @QuotedMarketAskRate)) ELSE QuotedMarketAskCostOnClosing END), -- track the ask price. Used for RPL and only set on closing. Will be the same price as @ActualMarketExecutionPrice for long positions ClosingTime = (CASE WHEN (VolumeClosed + ABS(@VolumeAmount)) = TotalVolumeEverOpened THEN GETUTCDATE() ELSE NULL END) WHERE PositionID = @positionID
@Doiremik - is this happening consistently with just 4 records? If so, have you tried just selecting the values you show here instead of updating? I would be interested to see the result of that. Another question, why update one at a time, can't you do this in one? Maybe that could be a change enough to help out
@William - no william, this is intermittent from what I can see. If I select them, I would need to pump them to a file or something and then wait for the issue to arise again. As for updating one at a time, what actally happens is, I have a broker system that recieves fills for orders. They might be partially filled in which case only when there is nothing left to fill I set the Closed Time, if they get filled completely on the one go I update the closed Time on the order. So its handling orders as they stream into the system and I dont get a batch in the one go... if this makes sense... regards Mick
OK... just want to throw this out there... is there any chance that a call to GETUTCDATE() could return NULL. I know its a bit mad to think but it would explain the issue alright. What I was going to do was clean down the database and try this again to see will it happen. It can take hours. If it does... then replace the GETUTCDATE with a harded coded value and then try again. Opinions very welcome... regards mIck
