Hi, I was checking for the existence of the row, but that is an extra read that could be avoided. Now I changed my stored procedure to update and then insert if update failed to find a match with the cache key value.
John From: Ilya Kasnacheev <[email protected]> Sent: Monday, April 15, 2019 4:14 PM To: [email protected] Subject: Re: efficient write through Email received from outside the company. If in doubt don't click links nor open attachments! ________________________________ Hello! I'm not aware of the possibility of modifying the object in Cache Store. I would not recommend trying to do that. What's the problem of checking row existence by Id (i.e. Cache Key?) Regards, -- Ilya Kasnacheev сб, 13 апр. 2019 г. в 06:53, Coleman, JohnSteven (Agoda) <[email protected]<mailto:[email protected]>>: 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.
