Best practice for conditional insert else select?

I have several places where I need to get the id (primary key) of a resource, inserting a row if the resource does not exist (i.e. an artificial key to be used as an FK for another table). I should probably change this varchar key lookup to use a hash index, but that is beside the point.

So the table is essentially like:

CREATE TABLE MyLookup(id int identity primary key nonclustered, mykey varchar(256));

CREATE CLUSTERED INDEX mylookup_cidx_mykey ON MyLookup(mykey);

I see two main approaches for how I can do my get-id-with-insert-if-needed.

(Approach 1)

DECLARE @.id INT;

SELECT @.id = id FROM MyLookup WHERE mykey = 'some key value';

IF (@.id is null)

BEGIN

INSERT MyLookup ('some key value');

SET @.id = SCOPE_IDENTITY();

END

(Approach 2)

DECLARE @.id INT;

INSERT MyLookup SELECT 'some key value' WHERE NOT EXISTS (SELECT id FROM MyLookup WHERE mykey = 'some key value');

IF (@.@.ROWCOUNT = 0)

SELECT @.id = id FROM MyLookup WHERE mykey = 'some key value';

ELSE

SET @.id = SCOPE_IDENTITY();

From some quick tests in profiler, approach 2 seems to be a bit faster and have lower resource utilization. But I'm not sure if it maybe takes some more aggressive locks even in the unnecessary case where the mykey row value of 'some key value' already exists. Approach 2 also looks cleaner to me, but I don't mind a bit of extra code if it gives me better scalability through less lock contention.

Any tip on what is considered the best practice for a conditional insert like this, or a tip on how to get detailed lock info for a query? The lock info for profiler was all greek to me, it just had a hex value with each lock acquired/released, so I have no idea what it was telling me. Is my only solution to just run exhaustive tests and look at the perf numbers from the black box?

I went ahead and did some testing, no big surprises. Although the tests were single client, so it doesn't give me any info about locking.

I found unsurprisingly that the more sparse the table, the narrower the gap between the two options. But as the likelihood of needing an insert went down, the first approach became more effective. In my production environment I would hazard a guess that I need an insert around 5% of the time, so I'll probably go with approach 1 in general. Although I did change my approach 1 to actually incorporate approach 2 with in - first I do a select, and then if the id was null then I do a conditional insert. There is a slight increase in maintenance cost since I have to duplicate the code for the existential check, but I think it is worthwhile in my cases that are fairly high traffic.

Any other points of view on this?

best approach

I have two tables with same datatypes. I need to view all the rows
from both tables and check to see if they exist in a third table while
viewing the data. My plan thus far is to use a union query and a
function that returns a bit showing if they are in third table. Should
I use this approach (shown below) or should this be a join query
between the union portion and third table with consideration to speed
and viability?
select chemical, fncExist(cid) as exist
from table1
union all
select chemical, fncExist(cid) as exist
from table2
where:
CREATE FUNCTION dbo.fncExist
(
@.cid int
)
RETURNS bit
AS
BEGIN
declare @.exist int
declare @.bln bit
select @.exist =Count(id) from table3 where vid = @.cid
if @.exist>0
begin
@.bln=1
end
else
begin
@.bln=0
end
RETURN @.bln
ENDUsing a scalar function like this can really bring performance down to
snail-on-glue speed.
Try something like this:
select <column list>
,exist
= case when <third table>.<unique key> is null
then 0
else 1
end
from (
select chemical
,cid
from table1
union all
select chemical
,cid
from table2
) Both
left join <third table>
on <third table>.cid = Both.cid
ML
http://milambda.blogspot.com/|||Thanks thats what I needed to know.