Dynamic SQL Kullanımında Exec Yerine sp_ExecuteSQL Komutunu Tercih Edin
Bildiğiniz gibi her SQL komutu çalıştırıldığında ilgili komut için bir Query Plan oluşturulur ve Plan Cache’de saklanır. Eğer ilgili sorgu parameterize edilebilen bir sorgu ise (Stored Procedure gibi) ve bu sorgu için daha önce bir Query Plan oluşturulduysa, tekrar bir Query Plan oluşturulmaz ve daha önce Plan Cache’de saklanan Query Plan kullanılır.
Query Plan oluşturulma işlemi masraflı bir işlemdir. Bu yüzden oluşturulmuş Query Plan’larının saklanıp kullanılarak tekrar tekrar Query Plan oluşturulmaması performans açısından önemli bir durumdur.
İşte bu bakış açısıyla exec ve sp_executesql komutları ile Dynamic SQL kullanımında ortaya çıkan farklılıkları bu makalede inceleyip, sp_ExecuteSQL’in exec yerine neden tercih edilmesi gerektiğini örnekler ile görüyor olacağız.
[more]
Her iş gereksinimi sabit SQL komutları ile Stored Procedure’lere gömülemeyebilir. Örneğin parametre olarak verilen tablo adına çekilecek sorguda Dynamic SQL kullanma zorunluluğumuz ortaya çıkabilir.
Ya da başka bir örnek verecek olursak; sorguda bulunan where bloğunda kullanılacak ifadeler duruma göre farklılık gösterebilir ya da parametre olarak gönderilebilir.
İşte böyle bir durumda sabit bir SQL ifadesi yerine Dynamic SQL kullanmamız gerekebilir. Peki ama komut olarak Exec mi yoksa sp_executeSQL komutunu mu kullanmalıyız?
Bu soruya bir örnek ile cevap bulmaya çalışalım.
Örneğimizde kullandığımız where bloğu değişkenden alındığı için Dynamic SQL kullanmak zorunda kalıyoruz. İlk blokta exec komutu ile kullanıp Cache’lenen Plan’larının tekrar kullanılmadığını göreceğiz.
İkinci örnekte ise aynı işlemi sp_executesql ile yapacağız ve cache’lenen planların tekrar kullanıldığını göreceğiz.
Exec ile Dynamic SQL Kullanımı
--İşlemlere başlamadan önce Plan Cache'i temizliyoruz.
--DİKKAT:Bu işlemi production ortamında YAPMAYINIZ!!
dbcc freeproccache
--exec komutu ile Dynamic SQL kullanıyoruz.
declare @str varchar(max)='',
@param1 varchar(50)='',
@param2 varchar(50)=''
declare @strin varchar(50)
set @param1='1'
set @param2='2'
set @str='select * from AdventureWorks.Person.Address where AddressID in ('+@param1+','+@param2+')'
exec(@str)
--Cache'lenen query Planlara bakalım.
select st.text,*
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where (st.text like '%select * from AdventureWorks%')
and st.text not like '%select st.text%'
--exec komutu ile aynı sorguyu tekrar çekiyoruz
declare @str varchar(max)='',
@param1 varchar(50)='',
@param2 varchar(50)=''
declare @strin varchar(50)
set @param1='3'
set @param2='4'
set @str='select * from AdventureWorks.Person.Address where AddressID in ('+@param1+','+@param2+')'
exec(@str)
--Cache'lenen query Planlara bakalım.
select st.text,*
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where (st.text like '%select * from AdventureWorks%')
and st.text not like '%select st.text%'
--Her 2 sorgu için 2 ayrı plan oluşturulduğunu görüyoruz.
sp_executeSQL ile Dynamic SQL Kullanımı
Şimdi aynı işlemi sp_executeSQL ile tekrarlayalım.
--İşlemlere başlamadan önce Plan Cache'i temizliyoruz.
--DİKKAT:Bu işlemi production ortamında YAPMAYINIZ!!
dbcc freeproccache
--sp_executesql ile aynı işlemi yapalım
--sp_executesql 1
declare @param1 int,
@param2 int
set @param1=1
set @param2=2
exec sp_executesql N'select * from AdventureWorks.Person.Address where AddressID in (@1,@2)'
,N'@1 int, @2 int'
,@param1, @param2
--Cache'lenen query Planlara bakalım.
select st.text,*
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where (st.text like '%select * from AdventureWorks%')
and st.text not like '%select st.text%'
--sp_executesql 2
declare @param1 int,
@param2 int
set @param1=3
set @param2=4
exec sp_executesql N'select * from AdventureWorks.Person.Address where AddressID in (@1,@2)'
,N'@1 int, @2 int'
,@param1, @param2
--Cache'lenen query Planlara bakalım.
select st.text,*
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where (st.text like '%select * from AdventureWorks%')
and st.text not like '%select st.text%'
--Aynı Query Plan'ın 2 defa kullanıldığını
--tekrar yeni bir Query Plan oluşturulmadığını görüyoruz
Gördüğünüz gibi her 2 sorgu için ayrı ayrı Query Plan oluşturulmadı ve aynı Query Plan kullanıldı.