Fastest way to get Id or insert value from Microsoft Sql

Question

I'm having poor database performance while inserting a big number of values. Before inserting I have to check if the value already exists and get its Id if it does, like in this query:

DECLARE @p0 int
DECLARE @p1 VARCHAR(255)

SET @p0 = 1
SET @p1 = '123123123123'

IF NOT EXISTS(SELECT TOP (1) Id 
           FROM Orders 
           WHERE MyId = @p0 AND Number = @p1) 
BEGIN       
   INSERT Orders (MyId, Number) VALUES  (@p0, @p1) 
END 
SELECT TOP (1) Id FROM Orders WHERE MyId = @p0 AND Number = @p1

Is there a better way to perform the operation?

Do you have an index on MyID and Number (one index, two fields)? — The Sharp Ninja, Dec 10 '15 at 19:00
Check this [link](http://stackoverflow.com/questions/639854/check-if-a-row-exists-otherwise-insert) out — amuz, Dec 10 '15 at 19:19
Make sure that you have proper indexes and do it in batches using `MERGE`, not one row at a time. — Vladimir Baranov, Dec 10 '15 at 22:42
@TheSharpNinja there is only an external index, both fields are not indexed — Bastianon Massimo, Dec 11 '15 at 08:04

onupdatecascade · Accepted Answer · 2015-12-10T19:54:58.343

2

That should be fast with the right indexing on the table (per comments above) so address that first.

However, you are querying the table twice, when you might be able to hit it once. Pseudocode:

declare @id int 

select @id = id FROM Orders WHERE MyId = @p0 AND Number = @p1

if ( @id is null )
begin

insert into ....

set @id = scope_identity()

end

select @id ...

edited Dec 10 '15 at 19:54

answered Dec 10 '15 at 19:38

onupdatecascade

3,336
22
35

The insert performances is almost 3-5% faster, thank you for the solution :-) – Bastianon Massimo Dec 11 '15 at 18:06

Fastest way to get Id or insert value from Microsoft Sql

1 Answers1