stored procedure  

veritabanı sunucusunda sorguların kaydedilip, fonksiyon gibi çağırılması mantığına dayanan bir yöntem.

her gelişmiş rdbms'de bulunması elzem bir olay. işin özüne bakıldığında bunun başlı başına bir programlama platformu olduğu anlaşılabilir. sp'lerin avantajlarını sayalım:

- sp'ler çalıştırılırken sıradan sorgulardan farklı olarak tekrar parse edilmez. bu anlamlı bir güç kazancı sağlar.

- bilhassa veritabanı sunucusu ve uygulama sunucusu arası iletişimin çok da şahane olmadığı durumlarda arada giden gelen data önemli ölçüde azalır. sadece parametreler ve sonuçlar iletişime dahil edilir.

- web uygulamaları başta olmak üzere tüm uygulamalarda potansiyel tehlike olan sql injection'dan doğal korunma yöntemidir. uygulamada injection'u önlemek için gereken eforu azaltır. bir anlamda prezervatif kullanmak yerine sadece adet döneminde seks yapmak gibi bir olaydır.

sp alanlar şunları da aldı:

(bkz: trigger) (bkz: view)

geçen yıl aldığım bir kitapta türkçeye depolanmış yordam olarak çevrilmesiyle kopmama neden olan terim.

şöyle bir şeydir.

-- ================================================
-- template generated from template explorer using:
-- create procedure (new menu).sql
--
-- use the specify values for template parameters
-- command (ctrl-shift-m) to fill in the parameter
-- values below.
--
-- this block of comments will not be included in
-- the definition of the procedure.
-- ================================================
set ansi_nulls on
go
set quoted_identifier on
go
-- =============================================
-- author: <karizmatik>
-- create date: <2007-09-02>
-- description: <stored procedure template for itusozluk>
-- =============================================
create procedure <procedure_name, sysname, procedurename>
-- add the parameters for the stored procedure here
<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>,
<@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>
as
begin
-- set nocount on added to prevent extra result sets from
-- interfering with select statements.
set nocount on;

-- insert statements for procedure here
select <@param1, sysname, @p1>, <@param2, sysname, @p2>
end
go

ancak doğru kullanıldığı zaman performans kazancı sağlayan yordamlardır. öncelikle insanların stored procedure’leri neden tercih ettiklerine bir göz atalım.

- stored proceduler normal prepared statement’lara göre çok daha performanslıdırlar. bunun sebebi procedure’lerin bir kere compile edilip her seferde kullanılmasıdır. ilk compile zamanında stored procedure için bir execution plan çıkarılır ve bu plan’a göre kodlar derlenir.
- stored procedur’lerin yönetilmesi, değiştirilmesi, eklenmesi çok daha kolaydır.
- client ile server arasındaki bağlantının sağlıksız olduğu durumlardan etkilenmez.

genellikle bu özellikleri stored procedure’lerin terich edilmelerine sebep olur. dezavantajları yok mudur elbette vardır.

- uygulamayı ve uygulamaları veritabanı bağımlı yapar. yani data’larınızı başka bir veritabanına taşıdığınız zaman porcedure’leri tekrar yazmanız gerekmektedir.
- normal programcılık mantığında iş mantığını (business logic) veritabanına gömmek çok istenmeyen bir durumdur.

biraz önce belirttiğim gibi stored procedure’ler ancak doğru kullanıldığı zaman performans kazancı sağlarlar. bunu biraz açmak istiyorum. stored procedure’ler bir kez derlenip bir execution plan çıkarırlar. ve daha sonraki sorguları bu execution plan’a göre çalıştırırlar.* execution plan kabaca bir sorgunun nasıl davranacağını belirleyen bir süreçtir. yani hangi tablo’yu table scan olarak okuyacak hangi tablo’ya index ile erişecek bunu belirlemek için çıkarılır. hani hep diyoruz bir kere derleniyor diye, peki ne zaman derleniyor ve nerede saklanıyor? stored procedure’ler create edildikten sonra ilk kez “exec” komutu ile çağrıldığı zaman derlenirler. o zaman memory’ye yazılırlar. ve memory dolana kadar orada kalırlar. granted workspace dolunca en uzun süredir kullanılmayan stored procedure’ün derlenmiş hali memory’den atılır. ta ki tekrar çalıştırılana kadar.

işte burada bizi bir tehlike bekliyor. stored procedure yazarken bu noktaya dikkat etmek gerekmektedir. aşağıdaki gibi bir stored procedure üzerinde örenek verecek olursak:

create procedure sel_salesreport
@mindate smalldatetime,
@maxdate smalldatetime,
@customerid int,
@salespersonid int,
@branchid int,
@productid
.
.
as

select * from sales
where ....

yukarıdaki stored procedure’de klasik bir satış raporu hazırlanmaktadır. ve fazlaca parametresi bulunmaktadır. bu raporun oldukça farklı kombinsayonları mevcuttur. mesela bir ürüne göre yapılan satışlar, bir müşteriye yapılan satışlari iki tarih arasında yapılan satışlar vs. vs. bu procedure ilk çalıştırıldığında execution plan nasıl oluşacak? bunun kestirebilmemiz mümkün gözükmemektedir. çünkü execution plan ilk çalıştırıldığında hangi parametreler verilmiş ise ona göre değişkenlik arz edecektir. çünkü siz müşteri bazlı bir rapor aldığınızda müşteri tablosuna index ile erişmek daha mantıklı oalcaktır. tarih bazlı bir rapor alırsanız da müşteri tablosuna table scan yapması daha mantıklı olabilecektir. bu nedenle ilk çağırdığınızdaki parametrelere göre çıkarılan execution plan her durum için en iyi çözüm olmayacaktır. hatta bazı durumlar için de performansı çok düşük kalacaktır.

yukarıda bahsetmeye çalıştığım “duğru kullanma” işte bu noktada devereye giriyor. yani stored procedure’leri yazarken mümkün olduğu kadar parametrelere dikkat etmek gerekmektedir. bu sorunu aşmak için en güzel yöntem stored procedure’ü parçalara ayırıp kombinasyonları kısıtlamaktır. yani iki tarih aralığı verilirse müşteri no verilemesiz, müşterino verilise şubeno verilemesin gibi (sözgelimi söyledim bu örnekleri böyle daha performanslı olur demek tabloları iyi bilmek gerekmektedir) ve bu stored procedure!ü çağırdığımızda içerinde başka procedure’leri çağırsın mesela:

create procedure sel_salesreport
@mindate smalldatetime,
@maxdate smalldatetime,
@customerid int,
@salespersonid int,
@branchid int,
@productid
.
.
as

if @customerid is not null
begin
exec sel_salesreport_by_customerid
end
else
.
.
.

gibi...

en güzel katkısı query değişiminde kod'a girmenize gerek kalmaması ve sql injection yemenizi engellemesi olan saklı yordamdır.