Performance of Dynamic sql query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Performance of Dynamic sql query

Hello everyone, I want to know which one among the following will be faster.
I am using linked server with name ‘ReServer’ and databse ‘Rdb’ 1. writing the sql directly
insert into ReServer.rdb.dbo.Customer (id , name, addr1) values (@Id, @Name, @Addr1)
2 Creating a dynamic sql and executing it
Set @dbname = ‘ReServer.rdb.dbo.’
set @sql = ‘insert into ‘[email protected] +”Customer (id , name, addr1)
values (@Id, @Name, @Addr1)’
exec sp_executesql @sql, N’@Id int, @Name varchar(80), @addr1 varchar(200)’,
@id, @name, @Addr1 What I want to do is depending on a table "Permissions" i want to transfer the recrods present in source database to the destination database which is on another server.
I have already created a linked server, but there is a possibility of changing the linked servername as well as the name of the destination databse on linked server. I am confused between these two methods. I cann’t use the package to do this things, as while transfering the data i have to check many conditions. Pls do let me know about your comments or any other way to do the same thing. Thanks, Best Regards!
Pallavi

Query without dynamic SQL is faster. Set the execution plan and see. If there is change in the linked server name simply update that in the query Madhivanan Failing to plan is Planning to fail
If your procedure copies data into databases on different servers, all of which are linked, then the best way is probably to use dynamic T-SQL, so the proper server name is inserted at run-time. Don’t confuse the flexibility offered by sp_ExecuteSQL with performance gains available through sp_ExecuteSQL – they are totally separate issues. Since you are passing a single row of data using a VALUES clause, this INSERT statement has no performance to be improved upon, as the statement is not collecting data from tables or anything. If you were using an INSERT INTO … SELECT … query, without any filtering, then you will not see any improvement in performance by using sp_ExecuteSQL. There might be a difference in performance with sp_ExecuteSQL only if …
(1) the query is executed frequently and with filtering,
and (2) you are handling the filtering criteria properly through sp_ExecuteSQL’s additional parameters.
… and other than that, the remote server might be busy, which can cause a major slow-down anyway.
Thanks Adriaan and Madhivanan thanks for the suggestions. I will definatedly try the same Thanks again Best regards
Pallavi

]]>