Currently my writethrough executes a stored procedure, but it has to identify
whether to insert or update which is inefficient as well as using locks and
transaction. If I disable write behind caching my cache put processing slows by
a factor of 10.
I'm thinking of adding a rowID field to the cache values that the database will
return and add to the value after writing, so that I will know if the value is
already stored or is a new item and needs inserting.
What approaches are recommended for ID fields? It would be nice if I didn't
have to have a different key value and row ID field, but catch 22 is I can't
wait for the Db to assign an ID when I put. At present I let my code assign the
cache key value, maybe I should use a Guid rather than a sequence?
Suggestions please.
John
CREATE PROCEDURE [dbo].[updateorinsert_simple_transaction]
(@id int
, @charge_amount decimal(18,6)
, @fee_amount decimal(18,6)
, @event_status tinyint)
AS
BEGIN TRANSACTION
IF exists (select 1 FROM [dbo].[simpletransaction] WITH (updlock,serializable)
WHERE id = @id)
BEGIN
UPDATE [dbo].[simpletransaction] SET charge_amount = @charge_amount,
fee_amount = @fee_amount, event_status = @event_status
WHERE id = @id
END
ELSE
BEGIN
INSERT INTO [dbo].[simpletransaction] (id, charge_amount, fee_amount,
event_status)
VALUES(@id, @charge_amount, @fee_amount, @event_status)
END
COMMIT TRANSACTION
GO
________________________________
This message is confidential and is for the sole use of the intended
recipient(s). It may also be privileged or otherwise protected by copyright or
other legal rules. If you have received it by mistake please let us know by
reply email and delete it from your system. It is prohibited to copy this
message or disclose its content to anyone. Any confidentiality or privilege is
not waived or lost by any mistaken delivery or unauthorized disclosure of the
message. All messages sent to and from Agoda may be monitored to ensure
compliance with company policies, to protect the company's interests and to
remove potential malware. Electronic messages may be intercepted, amended, lost
or deleted, or contain viruses.
