I have to update two tables
UPDATE TableA
SET a="a", b = 'b', date = GETDATE()
WHERE c="c"
IF @@ROWCOUNT = 0
INSERT INTO TableA (c, a, b, date)
VALUES ('c', 'a', 'b', GETDATE())
UPDATE TableB
SET d = 'd', date = GETDATE()
WHERE e="e" AND f="f"
IF @@ROWCOUNT = 0
INSERT INTO TableB (e, f, d, date)
VALUES ('e', 'f', 'd', GETDATE())
They are about 10k rows in each of those tables and I need to perform this as fast as possible because the data will be updated 100x per second.
I thought about this
BEGIN TRANSACTION;
UPDATE TableA
SET a="a", b = 'b', date = GETDATE()
WHERE c="c";
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO TableA (c, a, b, date)
VALUES ('c', 'a', 'b', GETDATE());
END
UPDATE TableB
SET d = 'd', date = GETDATE()
WHERE e="e" AND f="f";
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO TableB (e, f, d, date)
VALUES ('e', 'f', 'd', GETDATE());
END
COMMIT TRANSACTION;
But the thing is that the insert part will be executed only once for each device, the rest of the time it will be just updated. Is my method efficient? Perhaps I should prefill the table with initial values and omit the inserts in my operation?
You need to sign in to view this answers