Normal şartlarda beğendiğim İngilizce yazılmış bir makaleyi Türkçe’ye çevirmeyip sadece linkini blog’umda paylaşırım. Ama bu sefer konu oldukça önemli ve müşterilerimde sıkça rastladığım bir problem olduğu için daha fazla ilgi çekmesi açısından kendim de birşeyler katarak Türkçe yazmaya karar verdim.

[more]

Yazıma ilham veren makaleye SQL Server MCM’lerinden Gail Shaw’ın (blog | twitter) blog’unda rastladım. Konunun özü; opsiyonel olarak verilen yani null olma ihtimali olan parametrelerin where clause’da “OR” koşulu ile yazıldığında nasıl performans sıkıntısına sebebiyet verdiği ve bu sıkıntının nasıl çözümlenebileceği.

Ufak bir örnek verecek olursak aşağıdaki SP’de kullanılan 4 parametre de opsiyonel ve sadece değer verildikleri durumda where koşulunda kullanılıyor, aksi durumda yani NULL olarak bırakıldıklarında where koşulunda herhangi bir sınırlama etkisi yaratmıyorlar.

CREATE PROCEDURE SearchHistory(
	@Product int = NULL, 
	@OrderID int = NULL, 
	@TransactionType char(1) = NULL, 
	@Qty int = NULL)
AS
SELECT ProductID, 
	   ReferenceOrderID, 
	   TransactionType, 
	   Quantity,
	   TransactionDate, 
	   ActualCost 
from Production.TransactionHistory
WHERE (ProductID = @Product Or @Product IS NULL)
	AND (ReferenceOrderID = @OrderID OR @OrderID Is NULL)
	AND (TransactionType = @TransactionType OR @TransactionType Is NULL)
	AND (Quantity = @Qty Or @Qty is null)
GO

 

Bildiğiniz gibi SP'ler parameterize edilebilen objelerdir. Yani SP’lerin içerisindeki statement’lar için ilk çalıştırıldıkları parametreler ile Query Plan oluşturulur ve cache’lenir. Daha sonra aynı SP çağırıldığında tekrar Query Plan oluşturulmaz ve cache’lenen bu Query Plan kullanılarak statement’lar çalıştırılır.

İşte aslında problem biraz da bununla alakalı. Yukarıdaki sorguda farklı parametrelere göre farklı Query Plan’lar kullanılması gerekiyor. Örneğin sadece ProductID ve TransactionType parametrelerinin gönderildiği durumda farklı bir Query Plan, sadece Quantity gönderildiğinde ise apayrı bir Query Plan kullanılmalı. Ama bir önceki paragrafta da anlattığım gibi SP için 1 kez Query Plan oluşturulup daha sonra hep bu oluşturulan plan kullanıldığı için daha sonra gelen sorgular için bu plan uygun olmayabiliyor.

Diğer bir problem ise; sorgu ilk çalıştığında Query Optimizer tüm parametreleri dikkate alacak/cover edecek şekilde bir Query Plan oluşturuyor ki daha sonraki sorgularda da bu plan çok ta kötü bir plan olmasın. Lakin bu yaklaşımda da oluşturulan Query Plan ne ilk gelen parametreler için uygun plan oluyor ne de sonraki gelecek parametreler için iyi bir plan oluyor.

Peki çok sık karşılaşılan bu problemi nasıl çözeceğiz? Kullanacağımız yöntem Dynamic SQL!

Buyrun örneğe geçelim;

use AdventureWorks2012
GO
--Calisma Proseduru create ediyoruz
--Prosedur 4 parametre aliyor fakat bu parametreler null olabiliyor
--Where kosulunda parametrelerin null'ligi kontrol ediliyor
CREATE PROCEDURE SearchHistory(
	@Product int = NULL, 
	@OrderID int = NULL, 
	@TransactionType char(1) = NULL, 
	@Qty int = NULL)
AS
SELECT ProductID, 
	   ReferenceOrderID, 
	   TransactionType, 
	   Quantity,
	   TransactionDate, 
	   ActualCost 
from Production.TransactionHistory
WHERE (ProductID = @Product Or @Product IS NULL)
	AND (ReferenceOrderID = @OrderID OR @OrderID Is NULL)
	AND (TransactionType = @TransactionType OR @TransactionType Is NULL)
	AND (Quantity = @Qty Or @Qty is null)
GO

--Parametre gruplarından birinde kullanmak uzere
--	ProductID ve TransactionType uzerine nonclustered index olusturalim
create nonclustered index idx_TranHistory_TranTypeProductID 
	on Production.TransactionHistory (ProductID,TransactionType)

--io istatistigini acalim
set statistics io on

--Proseduru ilk olarak Product ve TransactionType parametreleri ile cagiralim.
--Normalde Product=978 ve TransactionType='W' sorgusu index seek yapacakken
--	diger parametrelerden dolayi index scan yapiyor
--Logical Read = 398
EXEC SearchHistory @Product = 978, @TransactionType = 'W'

--Sorguyu adhoc cekerek index seek yaptigini gorelim
--Logical Read = 170
SELECT ProductID, 
	   ReferenceOrderID, 
	   TransactionType, 
	   Quantity,
	   TransactionDate, 
	   ActualCost 
from Production.TransactionHistory
WHERE (ProductID = 978)
	AND (TransactionType = 'W')


--Ayni proseduru farklı parametre ile cagirinca gene ayni plan kullaniliyor
--Cunku prosedur icin plan bir kez olusturuldu ve cache'lendi
--IO : 347658 
EXEC SearchHistory @Qty = 100

--Oysaki Adhoc olarak sadece Quantity = 100 kullanildiginda cok daha optimal bir plan var
--IO 797
SELECT ProductID, 
	   ReferenceOrderID, 
	   TransactionType, 
	   Quantity,
	   TransactionDate, 
	   ActualCost 
from Production.TransactionHistory
WHERE (Quantity = 100)

--Peki bu problemi nasil asabiliriz
--En uygun yontem dynamic sql ile parametrelere gore sql script'i olusturmak
CREATE PROCEDURE SearchHistory_Dynamic (
	@Product int = NULL, 
	@OrderID int = NULL, 
	@TransactionType char(1) = NULL, 
	@Qty int = NULL)
AS
	DECLARE @sSQL NVARCHAR(2000), @Where NVARCHAR(1000) = ''
	SET @sSQL = 'SELECT ProductID, ReferenceOrderID, TransactionType, Quantity, TransactionDate, ActualCost
	from Production.TransactionHistory '
 
	IF @Product is not null
		SET @Where = @Where + 'AND ProductID = @_Product '
	IF @OrderID is not null
		SET @Where = @Where + 'AND ReferenceOrderID = @_OrderID '
	IF @TransactionType IS NOT NULL
		SET @Where = @Where + 'AND TransactionType = @_TransactionType '
	IF @Qty IS NOT NULL
		SET @Where = @Where + 'AND Quantity = @_Qty '
 
	IF LEN(@Where) > 0
		SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3)
 
	EXEC sp_executesql @sSQL,
		N'@_Product int, @_OrderID int, @_TransactionType char(1), @_Qty int',
		@_Product = @Product, @_OrderID = @OrderID, @_TransactionType = @TransactionType, @_Qty = @Qty
 
GO

--Daha once index scan - 398 IO yapan sorgu simdi
--	olmasi gereken sekilde index seek - 170 IO yapiyor 
EXEC SearchHistory_Dynamic @Product = 978, @TransactionType = 'W'
 
--Ayni SP'yi cagirmamiza ragmen yeni bir query plan olusuyor ve
--	daha once 347658 IO yapan sorgu olmasi gereken sekilde 797 IO yapiyor
EXEC SearchHistory_Dynamic @Qty = 100

--create ettigimiz objeleri siliyoruz
drop procedure SearchHistory
drop procedure SearchHistory_Dynamic
drop index idx_TranHistory_TranTypeProductID on Production.TransactionHistory

 

Bu yöntemde akla gelen sorulardan ilki “aynı parametreler gelse dahi her defasında yeni bir query plan'mı oluşuyor?” Cevabımız hayır. Aşağıdaki sorgu ile oluşan Query Plan’ları ve kaç kez kullanıldıklarını görebilrsiniz. Resimde gördüğümüz üzere “her gelen farklı parametre grubu” için 1 query plan oluşturulup cache’leniyor ve daha sonraki sorgularda kullanılıyor.

--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 ProductID, ReferenceOrderID, TransactionType, Quantity, TransactionDate, ActualCost%')
and st.text not like '%select st.text%'

 

image

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan