CODEWITHSUNDEEP
sqlsql-serverperformanceoptimizationquery-optimization
user3574857
user3574857

Reputation: 395

Optimize a SQL query to avoid repeating

I have a long sql query that will be executed with 4 different parameters, so I have to repeat this query 4 times. I'm trying to optimize it but it's not working. Here is my query: 

IF EXISTS (SELECT TOP 1 Id_Unique FROM Table1 WHERE Parameter= @Parameter1)
BEGIN
    UPDATE Table1 
    SET Value = 'True'
    WHERE Parameter = @Parameter1
END
ELSE
BEGIN
    INSERT INTO Table1 (Parameter, Value)
    VALUES(@Parameter1, 'True')
END

The only thing that will change with each iteration is the parameter name: @Parameter1 / @Parameter2 / @Parameter3 /@Parameter4. Table1 has only 3 columns (Id, Parameter, Value).

Does anyone know how to optimize this query?

Upvotes: 0

Views: 322

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7692

You can put your parameters and their values into a table variable and then perform a single MERGE statement, if version of your SQL Server instance supports it (2008 and later).

Such as:

-- Incoming set of parameter values
declare @x xml = N'<Parameters>
    <Item Name="Parameter1" Value="Value1" />
    <Item Name="Parameter2" Value="Value2" />
    <Item Name="Parameter3" Value="Value3" />
    <Item Name="Parameter4" Value="Value4" />
</Parameters>';

declare @t table(
    Name varchar(100) primary key,
    Value nvarchar(max) not null
);

insert into @t (Name, Value)
select t.c.value('./@Name', 'varchar(100)') as [Name],
    t.c.value('./@Value', 'nvarchar(max)') as [Value]
from @x.nodes('/Parameters[1]/Item') t(c);

merge dbo.Table1 t
using (select * from @t) s
on t.Parameter = s.Name
when not matched by target then
    insert (Parameter, Value)
    values (s.Name, s.Value)
when matched then
    update set Value = s.Value;

Or, you can use any other way to populate the table variable that is most suitable in your situation - that's up to you.

Upvotes: 1

Related Questions