October 9, 2009 at 12:24 pm
What are the pros and cons of, when making sure I don't add a duplicate record: ( i have a unique index constraint on table)
1) do a seek, if there's a dupe record, warn user. If no dupe, add record.
2) simply attempt to add record, see if @@ERROR occurs due to unique index constraint..
seems that #1 might take longer because I am first seeking to see if dupe exists.. any thoughts? tx in advance! - matt
October 9, 2009 at 2:49 pm
I'm sure someone else will have more wisdom to share than I, but since it is a unique index it would be an index seek to check for the dupe so it should be fast and take minimal locks. You should test both and use which one is faster.
I did a quick test on a table with single integer column with a million rows and a unique index on the column. Here are the 2 queries I ran:
DECLARE @i INT;
SET @i = 10;
BEGIN Try
INSERT INTO test
(
id
)
VALUES
(
@i
);
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE();
END CATCH
DECLARE @i INT;
SET @i = 10;
IF NOT EXISTS(SELECT 1 FROM test WHERE id = @i)
BEGIN
INSERT INTO test
(
id
)
VALUES
(
@i
)
END
And interestingly enough, SSMS will not produce an actual execution plan for the query with the try...catch nor does it report any IO statistics. According to Profiler the try..catch query has 4 reads and the If Exists... query has 2 reads.
So, I think it is inconclusive. I like the test myself.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 9, 2009 at 2:54 pm
Duh, I didn't run a test for when the id doesn't exist in the table already, and in that case (I substituted 0 for 10) the try catch is better because it only does one read of the table so it may be better.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 9, 2009 at 3:57 pm
thank you very much Jack! that helps,
Matt, NYC
October 11, 2009 at 8:24 am
Hi,
It would be better in this case to write the query this way:
DECLARE @i INT;
SET @i = 10;
INSERT INTO test
(
id
)
VALUES
(
@i
)
WHERE NOT EXISTS(SELECT 1 FROM test WHERE id = @i)
In other case, it could happen that another transaction inserts key @i between the select statement in IF and actual insert.
Regards
Piotr
...and your only reply is slàinte mhath
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply