STORED PROCEDURE YAPISI VE TİPLERİ
Stored Procedure'ler, bir veya daha fazla Transact-SQL ifadesinden oluşan bir koleksiyon olarak tanımlanabilirler. Stored Procedure'ler temel olarak; SQL'de çok kullanılan işlevleri her defasında yeniden yazmak yerine, bu ifadeleri tek bir başlık altında tanımlayabilme ve gerektiğinde sadece bu başlığı çağırabilme kolaylığı sağlarlar.
SQL Server 5 farklı stored procedure tipini destekler:
1) System Stored Procedure'ler (sp_) : Master veritabanında tutulurlar ve " sp_ " önekiyle çağırılırlar. Sistem tabloları hakkında bilgi edinmek amacıyla kullanılırlar. System stored procedure'ler HER veritabanı altında (system databases, user-defined databases) çalıştırılabilirler.
2) Local Stored Procedure'ler : Kullanıcı bazlı (user-defined) veritabanlarında oluşturulan stored procedure'lerdir.
3) Temporary Stored Procedure'ler : Temporary Stored Procedure'ler "Local" veya "Global" olabilirler. Local Temporary Stored Procedure'ler, "#" işareti ile , Global Temporary Stored Procedure'ler ise "##" işaretiyle başlayacak şekilde isimlendirilirler. Ayrıca, Local Temporary Stored Procedure'ler "tek kullanıcı oturumu"nda (single user session) çalıştırılabilirken, Global Temporary Stored Procedure'ler ise "tüm kullanıcı oturum"larında (all user sessions) çalıştırılabilirler.
4) Remote Stored Procedure'ler : Bir "Remote SQL Server"dan çağrılabilen stored procedurelerdir. Fakat, Remote Stored Procedure'ler SQL Server'ın eski yapılarından biridir ve işlevselliği azdır. SQL Server versiyon 7.0 ile birlikte gelen "Distributed Queries" ile bu işlevsellik artırılmıştır.
5) Extended Stored Procedure'ler (xp_) : Extended Stored Procedure'ler birer DLL (Dynamic-Link Libraries) olup, SQL Server ortamının dışından çalıştırılabilen stored procedure'lerdir ve " xp_ " önekiyle çalıştırılırlar.
Stored Procedure'ler ;
- Diğer mevcut Stored Procedure'leri de çağırabilme özelliğine sahiptirler.
- Input (giriş) ve Output (çıkış) Parametreleri alabilirler.
- Çalıştırılan işleme göre tek veya çoklu sonuç değeri döndürebilirler.
- Bu bahsettiklerimize bir sonraki makalemde değineceğiz.
STORED PROCEDURE'LERİN ÇALIŞMA MEKANİZMASI :
Stored Procedure'ler ilk çalıştırıldıklarında (initially) ve sonraki çalıştırılmalarında (on subsequent execution) farklı çalışma mekanizmaları ile çalışırlar. Bu iki mekanizmayı da inceleyelim:
1) İlk Çalıştırılma Mekanizması (Initial Processing) :
[resim1]
ŞEKİL 1
Stored Procedure'ler ilk kez çalıştırıldıklarında, "Execution Plan" dediğimiz yapılar, "procedure cache"de oluşturulur. Procedure cache, her çeşit Transact-SQL ifadesinin execution plan'ini tutan bir havuzdur.
Bir stored procedure'ün ilk kez çalıştırılması (Initial processing) ; stored procedure'ün yaratılması (Creation) ve stored procedure'ün ilk kez uygulanması ("Execution") safhalarını içerir.
A) CREATION :
Bir stored procedure yarattığımızda, SQL Server bu stored procedure'ün içerdiği Transact-SQL ifadesinin doğru olup olmadığını kontrol eder. Bu işleme "parsing" adı verilir. Doğruluk onaylandıktan sonra stored procedure'ün ismi, oluşturulduğu veritabanının "sysobjects" sistem tablosuna, içerdiği Transact-SQL ifadeleri ise "syscomments" sistem tablosuna kaydedilir. Eğer, ifadelerin doğruluğu onaylanmadı ise, SQL Server "syntax" hatası verir ve stored procedure oluşturulmaz.
Burada " Delay Name Resolution " unsuruna değinelim: "Delay Name Resolution" , bir stored procedure metninde geçen obje isimlerinin, stored procedure yaratılırken SQL Server'da olmaması (henüz yaratılmaması) durumunda bile stored procedure'ün yaratılmasına izin verilmesini sağlayan bir unsurdur. Yani stored procedure'de bir tablo sorgulanıyor olsa ve bu tablo SQL Server'da hiç yaratılmamış olsa bile sorunsuz bir şekilde bu stored procedure yaratılacaktır. Ancak; stored procedure çalıştırılmadan önce bu objeler server'da yaratılmalıdır. Kısacası; "Delay Name Resolution", stored procedure'ün yaratılması ile çalıştırılması arasında kolaylık sağlayan bir süreçtir.
B) EXECUTION :
Stored procedure'ün ilk kez çalıştırılması sırasında "Query Processor" stored procedure'ü okumaya başlar. Bu safhaya "Resolution Stage" denir. Stored procedure, ikinci veya daha ileriki çalıştırılmalarında, yine bu safhadan geçecektir. Ve eğer stored procedure'de herhangi bir değişiklik sözkonusu ise SQL Server bu stored procedure'a ait Execution Plan'i yeniden düzenleyecektir. SQL Server stored procedure'de şu değişiklikler yapıldıysa Execution Plan'i yeniden düzenler:
- Stored procedure sorgusunun içerdiği tablo veya view'larda yapısal bir değişiklik varsa (ALTER Table, ALTER View)
- İstatistikler güncellendiyse (UPDATE Statistics)
- Execution Plan'da varolan bir index kaldırıldıysa (Dropping index)
- Bir tabloda "Insert" veya "Delete" ifadeleri çalıştırıldıysa.
Execution Safhası da kendi içinde 2 safha daha içerir.
a) OPTIMIZATION :
Stored Procedure "Resolution Stage"i başarıyla geçince, "Query Optimizer" stored procedure içindeki Transact-SQL ifadelerini analiz eder ve bu ifadelerin sonucundaki verilere ulaşabileceği en hızlı yolun planını oluşturur. "Query Optimizer" bu planı oluştururken şu nicelikleri inceler:
- Sorgulanan tablolardaki verilerin büyüklüğü
- Tablo indexleri ve indexlenen kolonlardaki verilerin dağılımı
- WHERE cümleciğinde geçen operatörler ve değerler
- JOIN,UNION,GROUP BY ve ORDER BY ifadeleri.
b) COMPILATION :
Stored Procedure'ün çalıştırılmasındaki son safhadır. Analiz edilen stored procedure ve procedure cache'de oluşturulan execution plan bu safhada kullanılır ve stored procedure sorgusundaki sonuç değerleri geri döndürülerek, stored procedure'ün ilk çalıştırılması tamamlamış olur.
2) Sonraki Çalıştırılmalarda Uygulanan Mekanizma (Subsequent Processing) :
[resim2]
ŞEKİL 2
Subsequent Process, Initial Process'ten daha hızlıdır. Yani, bir stored procedure sonraki çalıştırılmalarında, ilk çalıştırılmasına göre daha hızlı çalışır. Bunun sebebi, stored procedure'ün execution plan'inin sadece ilk çalıştırılmasında oluşturulması ve daha sonraki çalıştırılmalarında bu mevcut execution plan'in kullanılmasıdır. Stored procedure her çalıştırıldığında yeniden bir execution plan oluşturulmaz. Önceden de bahsettiğimiz nedenler sözkonusu olursa ancak ozaman execution plan yenilenir. Aksi hâlde;
- Mevcut execution plan'in oluşturulduğu server ortamı (environment), stored procedure'ün çalıştırıldığı ortam ile aynı ise (bu ortam Server, Veritabanı ve Server Bağlantı Ayarları (connection settings)' na bağlıdır) ,
- Stored procedure'ün sorguladığı objeler "Name Resolution" gerektirmiyorsa, yani server'da aynı isimde iki veya daha çok farklı obje yoksa (böyle bir durum varsa SQL Server, stored procedure her çalıştırıldığında aynı isimli objelerin hangi kullanıcıya, veritabanına veya tabloya v.s. ait olduğunu gözden geçirecektir) bu durumlarda SQL Server mevcut execution plan'i kullanarak "Subsequent Processing" ile stored procedure'ü çalıştıracaktır.
SQL Server'da Execution Plan'in yapısına da biraz değinmek istiyorum:
Execution Plan 2 ana yapıdan oluşur:
a) Execution Plan : Aynı ismi taşıyan bu yapı, salt-okunur (read-only) ve tüm kullanıcıların erişebildiği, kullanabildiği bir planı ifade eder. Bir execution plan, bu planın adımlarını izleyerek ilerler.
b) Execution Context : Her kullanıcının çalıştırdığı sorgu, "bağlantısından (connection settings)" ve "kullanıcı adından (login)" dolayı kendine özgüdür. Benzer şekilde, bir sorgudaki parametreler değiştiğinde de o sorgu, kendine özgü bir yapıya sahip olur. Bahsetmek istediğim şey şu; SQL Server aynı execution plan'a sahip sorgular için, bu farklılıklardan doğan özelliklere ait bilgileri de "Execution Context" adı verilen yapıda saklar.
Bu yapıyı iki örnekle açıklayalım:
- SQL Server, örneğin; "Select * from customers" sorgusu çalıştırıldığında, bu sorgu için bir execution plan oluşturur. Eğer bu sorguyu "X" kullanıcısı çalıştırdıysa Execution Context'e de bu "X" kullanıcısının bu sorguyu çalıştırdığına dair bilgi ekler. Aynı sorguyu "Y" kullanıcısı çalıştırdığında ise, execution plan aynıdır fakat Execution Context'e bu kez "Y" kullanıcısının bu sorguyu çalıştırdığına dair bilgi eklenir.
- Şekil 2'de de gösterilen "Select * from dbo.member where member_no=?" sorgusu için, bu sorgudaki "member_no" kısmına her farklı değer verdiğimizde execution plan aynı olacak fakat bu execution plan için execution context'teki değeri farklı şekilde tutulacaktır.
Ayrıca, yine Şekil 2'de görüldüğü gibi, Procedure Cache'deki boş alan yeterli olmadığında, SQL Server eski ve sık kullanılmayan execution plan'ları temizler ("Unused plans aged out").
Procodure Cache'i "tamamen" temizlemek için ise, SQL Query Analyzer'da "DBCC FREEPROCCACHE" ifadesini çalıştırabiliriz.
[resim3]
ŞEKİL 3
Son olarak, Stored Procedure'lerin kullanım avantajlarını listeleyerek makalemizi sonlandıralım:
- Server'da bulunan table ve view'lar üzerinde erişim hakkı bulunmayan kullanıcılara stored procedure'ler ile belirli amaçlar doğrultusunda, bu objeler üzerinde sorgu çalıştırabilme yeteneği kazandırılabilir.
- Stored Procedure'ler server performansını artırıcı yönde etki yaratır. Çünkü, bir stored procedure'ün bir çok Transact-SQL ifadesini içerdiğini düşünürsek, her stored procedure için tek bir execution plan oluşturulurken, her Transact-SQL ifadesi için ayrı ayrı excecution plan oluşturulacaktı.
- Stored Procedure'ler network trafiğini azaltır. Benzer şekilde, stored procedure diye bir yapı sözkonusu olmasaydı, her Transact-SQL ifadesi için server'a bir sorgu gönderilecek ve bu sorguya karşılık bir cevap alınması beklenecekti. Oysa ki, stored procedure yapısı sayesinde server'a tek bir sorgu gönderilmekte ve tek bir yanıt alınmaktadır.
Bir sonraki makalemde, stored procedure oluşturacak, değiştirecek, silecek ve çalıştıracağız.
Görüşmek Üzere
SERKAN ERSANLI
serkanersanli@yahoo.com
serkanersanli@gmail.com
Stored Procedure'ler, bir veya daha fazla Transact-SQL ifadesinden oluşan bir koleksiyon olarak tanımlanabilirler. Stored Procedure'ler temel olarak; SQL'de çok kullanılan işlevleri her defasında yeniden yazmak yerine, bu ifadeleri tek bir başlık altında tanımlayabilme ve gerektiğinde sadece bu başlığı çağırabilme kolaylığı sağlarlar.
SQL Server 5 farklı stored procedure tipini destekler:
1) System Stored Procedure'ler (sp_) : Master veritabanında tutulurlar ve " sp_ " önekiyle çağırılırlar. Sistem tabloları hakkında bilgi edinmek amacıyla kullanılırlar. System stored procedure'ler HER veritabanı altında (system databases, user-defined databases) çalıştırılabilirler.
2) Local Stored Procedure'ler : Kullanıcı bazlı (user-defined) veritabanlarında oluşturulan stored procedure'lerdir.
3) Temporary Stored Procedure'ler : Temporary Stored Procedure'ler "Local" veya "Global" olabilirler. Local Temporary Stored Procedure'ler, "#" işareti ile , Global Temporary Stored Procedure'ler ise "##" işaretiyle başlayacak şekilde isimlendirilirler. Ayrıca, Local Temporary Stored Procedure'ler "tek kullanıcı oturumu"nda (single user session) çalıştırılabilirken, Global Temporary Stored Procedure'ler ise "tüm kullanıcı oturum"larında (all user sessions) çalıştırılabilirler.
4) Remote Stored Procedure'ler : Bir "Remote SQL Server"dan çağrılabilen stored procedurelerdir. Fakat, Remote Stored Procedure'ler SQL Server'ın eski yapılarından biridir ve işlevselliği azdır. SQL Server versiyon 7.0 ile birlikte gelen "Distributed Queries" ile bu işlevsellik artırılmıştır.
5) Extended Stored Procedure'ler (xp_) : Extended Stored Procedure'ler birer DLL (Dynamic-Link Libraries) olup, SQL Server ortamının dışından çalıştırılabilen stored procedure'lerdir ve " xp_ " önekiyle çalıştırılırlar.
Stored Procedure'ler ;
- Diğer mevcut Stored Procedure'leri de çağırabilme özelliğine sahiptirler.
- Input (giriş) ve Output (çıkış) Parametreleri alabilirler.
- Çalıştırılan işleme göre tek veya çoklu sonuç değeri döndürebilirler.
- Bu bahsettiklerimize bir sonraki makalemde değineceğiz.
STORED PROCEDURE'LERİN ÇALIŞMA MEKANİZMASI :
Stored Procedure'ler ilk çalıştırıldıklarında (initially) ve sonraki çalıştırılmalarında (on subsequent execution) farklı çalışma mekanizmaları ile çalışırlar. Bu iki mekanizmayı da inceleyelim:
1) İlk Çalıştırılma Mekanizması (Initial Processing) :
[resim1]
ŞEKİL 1
Stored Procedure'ler ilk kez çalıştırıldıklarında, "Execution Plan" dediğimiz yapılar, "procedure cache"de oluşturulur. Procedure cache, her çeşit Transact-SQL ifadesinin execution plan'ini tutan bir havuzdur.
Bir stored procedure'ün ilk kez çalıştırılması (Initial processing) ; stored procedure'ün yaratılması (Creation) ve stored procedure'ün ilk kez uygulanması ("Execution") safhalarını içerir.
A) CREATION :
Bir stored procedure yarattığımızda, SQL Server bu stored procedure'ün içerdiği Transact-SQL ifadesinin doğru olup olmadığını kontrol eder. Bu işleme "parsing" adı verilir. Doğruluk onaylandıktan sonra stored procedure'ün ismi, oluşturulduğu veritabanının "sysobjects" sistem tablosuna, içerdiği Transact-SQL ifadeleri ise "syscomments" sistem tablosuna kaydedilir. Eğer, ifadelerin doğruluğu onaylanmadı ise, SQL Server "syntax" hatası verir ve stored procedure oluşturulmaz.
Burada " Delay Name Resolution " unsuruna değinelim: "Delay Name Resolution" , bir stored procedure metninde geçen obje isimlerinin, stored procedure yaratılırken SQL Server'da olmaması (henüz yaratılmaması) durumunda bile stored procedure'ün yaratılmasına izin verilmesini sağlayan bir unsurdur. Yani stored procedure'de bir tablo sorgulanıyor olsa ve bu tablo SQL Server'da hiç yaratılmamış olsa bile sorunsuz bir şekilde bu stored procedure yaratılacaktır. Ancak; stored procedure çalıştırılmadan önce bu objeler server'da yaratılmalıdır. Kısacası; "Delay Name Resolution", stored procedure'ün yaratılması ile çalıştırılması arasında kolaylık sağlayan bir süreçtir.
B) EXECUTION :
Stored procedure'ün ilk kez çalıştırılması sırasında "Query Processor" stored procedure'ü okumaya başlar. Bu safhaya "Resolution Stage" denir. Stored procedure, ikinci veya daha ileriki çalıştırılmalarında, yine bu safhadan geçecektir. Ve eğer stored procedure'de herhangi bir değişiklik sözkonusu ise SQL Server bu stored procedure'a ait Execution Plan'i yeniden düzenleyecektir. SQL Server stored procedure'de şu değişiklikler yapıldıysa Execution Plan'i yeniden düzenler:
- Stored procedure sorgusunun içerdiği tablo veya view'larda yapısal bir değişiklik varsa (ALTER Table, ALTER View)
- İstatistikler güncellendiyse (UPDATE Statistics)
- Execution Plan'da varolan bir index kaldırıldıysa (Dropping index)
- Bir tabloda "Insert" veya "Delete" ifadeleri çalıştırıldıysa.
Execution Safhası da kendi içinde 2 safha daha içerir.
a) OPTIMIZATION :
Stored Procedure "Resolution Stage"i başarıyla geçince, "Query Optimizer" stored procedure içindeki Transact-SQL ifadelerini analiz eder ve bu ifadelerin sonucundaki verilere ulaşabileceği en hızlı yolun planını oluşturur. "Query Optimizer" bu planı oluştururken şu nicelikleri inceler:
- Sorgulanan tablolardaki verilerin büyüklüğü
- Tablo indexleri ve indexlenen kolonlardaki verilerin dağılımı
- WHERE cümleciğinde geçen operatörler ve değerler
- JOIN,UNION,GROUP BY ve ORDER BY ifadeleri.
b) COMPILATION :
Stored Procedure'ün çalıştırılmasındaki son safhadır. Analiz edilen stored procedure ve procedure cache'de oluşturulan execution plan bu safhada kullanılır ve stored procedure sorgusundaki sonuç değerleri geri döndürülerek, stored procedure'ün ilk çalıştırılması tamamlamış olur.
2) Sonraki Çalıştırılmalarda Uygulanan Mekanizma (Subsequent Processing) :
[resim2]
ŞEKİL 2
Subsequent Process, Initial Process'ten daha hızlıdır. Yani, bir stored procedure sonraki çalıştırılmalarında, ilk çalıştırılmasına göre daha hızlı çalışır. Bunun sebebi, stored procedure'ün execution plan'inin sadece ilk çalıştırılmasında oluşturulması ve daha sonraki çalıştırılmalarında bu mevcut execution plan'in kullanılmasıdır. Stored procedure her çalıştırıldığında yeniden bir execution plan oluşturulmaz. Önceden de bahsettiğimiz nedenler sözkonusu olursa ancak ozaman execution plan yenilenir. Aksi hâlde;
- Mevcut execution plan'in oluşturulduğu server ortamı (environment), stored procedure'ün çalıştırıldığı ortam ile aynı ise (bu ortam Server, Veritabanı ve Server Bağlantı Ayarları (connection settings)' na bağlıdır) ,
- Stored procedure'ün sorguladığı objeler "Name Resolution" gerektirmiyorsa, yani server'da aynı isimde iki veya daha çok farklı obje yoksa (böyle bir durum varsa SQL Server, stored procedure her çalıştırıldığında aynı isimli objelerin hangi kullanıcıya, veritabanına veya tabloya v.s. ait olduğunu gözden geçirecektir) bu durumlarda SQL Server mevcut execution plan'i kullanarak "Subsequent Processing" ile stored procedure'ü çalıştıracaktır.
SQL Server'da Execution Plan'in yapısına da biraz değinmek istiyorum:
Execution Plan 2 ana yapıdan oluşur:
a) Execution Plan : Aynı ismi taşıyan bu yapı, salt-okunur (read-only) ve tüm kullanıcıların erişebildiği, kullanabildiği bir planı ifade eder. Bir execution plan, bu planın adımlarını izleyerek ilerler.
b) Execution Context : Her kullanıcının çalıştırdığı sorgu, "bağlantısından (connection settings)" ve "kullanıcı adından (login)" dolayı kendine özgüdür. Benzer şekilde, bir sorgudaki parametreler değiştiğinde de o sorgu, kendine özgü bir yapıya sahip olur. Bahsetmek istediğim şey şu; SQL Server aynı execution plan'a sahip sorgular için, bu farklılıklardan doğan özelliklere ait bilgileri de "Execution Context" adı verilen yapıda saklar.
Bu yapıyı iki örnekle açıklayalım:
- SQL Server, örneğin; "Select * from customers" sorgusu çalıştırıldığında, bu sorgu için bir execution plan oluşturur. Eğer bu sorguyu "X" kullanıcısı çalıştırdıysa Execution Context'e de bu "X" kullanıcısının bu sorguyu çalıştırdığına dair bilgi ekler. Aynı sorguyu "Y" kullanıcısı çalıştırdığında ise, execution plan aynıdır fakat Execution Context'e bu kez "Y" kullanıcısının bu sorguyu çalıştırdığına dair bilgi eklenir.
- Şekil 2'de de gösterilen "Select * from dbo.member where member_no=?" sorgusu için, bu sorgudaki "member_no" kısmına her farklı değer verdiğimizde execution plan aynı olacak fakat bu execution plan için execution context'teki değeri farklı şekilde tutulacaktır.
Ayrıca, yine Şekil 2'de görüldüğü gibi, Procedure Cache'deki boş alan yeterli olmadığında, SQL Server eski ve sık kullanılmayan execution plan'ları temizler ("Unused plans aged out").
Procodure Cache'i "tamamen" temizlemek için ise, SQL Query Analyzer'da "DBCC FREEPROCCACHE" ifadesini çalıştırabiliriz.
[resim3]
ŞEKİL 3
Son olarak, Stored Procedure'lerin kullanım avantajlarını listeleyerek makalemizi sonlandıralım:
- Server'da bulunan table ve view'lar üzerinde erişim hakkı bulunmayan kullanıcılara stored procedure'ler ile belirli amaçlar doğrultusunda, bu objeler üzerinde sorgu çalıştırabilme yeteneği kazandırılabilir.
- Stored Procedure'ler server performansını artırıcı yönde etki yaratır. Çünkü, bir stored procedure'ün bir çok Transact-SQL ifadesini içerdiğini düşünürsek, her stored procedure için tek bir execution plan oluşturulurken, her Transact-SQL ifadesi için ayrı ayrı excecution plan oluşturulacaktı.
- Stored Procedure'ler network trafiğini azaltır. Benzer şekilde, stored procedure diye bir yapı sözkonusu olmasaydı, her Transact-SQL ifadesi için server'a bir sorgu gönderilecek ve bu sorguya karşılık bir cevap alınması beklenecekti. Oysa ki, stored procedure yapısı sayesinde server'a tek bir sorgu gönderilmekte ve tek bir yanıt alınmaktadır.
Bir sonraki makalemde, stored procedure oluşturacak, değiştirecek, silecek ve çalıştıracağız.
Görüşmek Üzere
SERKAN ERSANLI
serkanersanli@yahoo.com
serkanersanli@gmail.com
0 yorum:
Yorum Gönder