You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by "Coleman, JohnSteven (Agoda)" <Jo...@agoda.com> on 2019/04/13 03:19:11 UTC
efficient write through
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.
RE: efficient write through
Posted by "Coleman, JohnSteven (Agoda)" <Jo...@agoda.com>.
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 <il...@gmail.com>
Sent: Monday, April 15, 2019 4:14 PM
To: user@ignite.apache.org
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) <Jo...@agoda.com>>:
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.
Re: efficient write through
Posted by Ilya Kasnacheev <il...@gmail.com>.
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) <
JohnSteven.Coleman@agoda.com>:
> 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.
>