Faydalı sql sorguları ve projeler

Bu yazıda veritabanı üzerinde gerçekleştirdiğimiz operasyonel işlerin otomatize edilmesi ve harcanan zamanın azaltılması için faydalı olabilecek örnek sorgular ve projeleri anlatıyor olacağım.

Geliştirilen veri merkezli (Data-centric) uygulamalar da veritabanına ait operasyonel bir çok işlem yapılır.Bu operasyonel işler veritabanı sayısına, depoladığı verinin büyüklüğüne ve organizasyon yapınıza göre değişiklik gösterebilir.Bu işler neler olabilir biraz düşünelim

  • Geliştirme takımız çok fazla kişi den oluşuyor ve uzun süre yazılımınıza yeni fonksiyonellikler ekliyor ve bug ları gideriyorsanız anlık yedekler alarak veri kaybını önlemeniz beklenir.Alınan bu yedek tablolar,stored procedure ler belirli bir zaman sonra fazlalaşır ve veritabanı performansını etkiler bunları silmeniz gerekebilir
  • Yazılım ürününe dair bir çok ortamınız(DEV-UAT-PREPROD-PROD) ve aynı şeklide bu ortamlara ait bir çok veritabanı sunucunuz var ise data senkronizasyonunu sağlamak için sunucular arasında data import export yapıyorsunuzdur
  • Veritabanına ait geliştirmelerinizi ortamlar arasında manuel taşıyor olabilirsiniz.
  • Binlerce veri içeren tablolar da arama yapıyor olabilirsiniz
  • Eklediğiniz verinin diğer ortamlara taşınması için (insert veya update) scriptler hazılıyor olabilirsiniz
  • Veritabanı üzerindaki listelemeler yavaş ise kesinlikle açık bir transaction vardır.Transaction ın gerçekten çalışmasının gerekip gerekmediğini transaction detayına bakarak öğrenmelisiniz.Sonrasında bu işin KILL edilmesi gerekir.
  • Saklı yordam lar içerisinde belirli hard-coded ifadelerin veya tablo isimlerinin değiştirilmesinin istendiğini düşünün

ve daha aklıma gelmeyen bir çok iş var.Bu gibi işler için çok fazla zaman harcıyoruz.Bu bir problem.Bu problemi çözmek için neler yapabiliriz bir düşünelim.

  • Projelerimizde zaman zaman gerekli olan veritabanı operasyonu için bir console uygulması yazabiliriz.
  • Veritabananı içerisindeki operasyonlar için scripler hazırlayabiliriz.
  • Var olan eklenti ve programları kullanabiliriz örneğin Redgate firmasının free toolarını burada bulabilirsiniz.
  • Kendiniz tüm ihtiyaçlara cevap veren web veya windows form tabanlı programlar yazabilirsiniz.Örneğin şirketlerin en çok ihtiyaç duyduğu veritabanı nesnelerinin deploy edilme işlemidir.Bunun için MS Sql server SMO kütüphane  lerini kullanarak efektif uygulamalar yapabilirsiniz.

Şimdi aşağıda bu gibi bazı işlemler için yazılan ve işinize yarayacak saklı yordamları ve örnek projeleri göreceksiniz.

Saklı yordamlar ve projeler MS Sql Server kullanan sistemler için kullanılabilir ve t-sql ile yazılmıştır.

Scriptler

1 – Search.sql

Veritabanı objeleri (stored procedure, trigger, function … ) içerisinde metin arama için kullanabileceğimiz bir search saklı yordamını aşağıda görebilirsiniz.Aşağıda bu search işlemi yapmak için sistem tablolarını kulanıyoruz.Özellikle büyük ölçekli veritabanı içerisinde çok etkili bir sp olduğunu söyleyebilirim

[sql] — Veritabanı nesne içerikleri
SELECT * FROM sys.syscomments(NOLOCK)

— Şema isimlerinin tutulduğu tablo
SELECT * FROM sys.schemas(NOLOCK)

— Bütün veritabanı nesne isimleri
SELECT * FROM sys.all_objects(NOLOCK)

— ana select ifadesi
SELECT S.name AS SP_SCHEMA,
O.name AS SP_NAME,
C.text AS SP_TEXT
FROM sys.syscomments(NOLOCK) AS C
JOIN sys.all_objects(NOLOCK) AS O
ON C.id = O.object_id
JOIN sys.schemas AS S
ON S.schema_id = O.schema_id
WHERE O.type in (‘P’,’FN’,’IF’,’FS’,’AF’,’X’,’TF’,’TR’,’PC’) AND
C.text like ‘%’ + ‘ARANACAK KELIME’ + ‘%’
[/sql]

2-  Nested_Search.sql

İç içe arama yapacağınız bir sql yordamı

[sql] DECLARE @text1 VARCHAR(MAX),
@text2 VARCHAR(MAX),
@text3 VARCHAR(MAX),
@text4 VARCHAR(MAX),
@text5 VARCHAR(MAX),
@dbname VARCHAR(64)

SET @dbname=’DB_NAME’
SET @text1=’TEXT1_TO_SEARCH’
SET @text2=’TEXT2_TO_SEARCH’
SET @text3=’TEXT3_TO_SEARCH’
SET @text4=’TEXT4_TO_SEARCH’
SET @text5=’TEXT5_TO_SEARCH’

DECLARE @sql VARCHAR(MAX)
SELECT @sql = ”

SELECT @sql = @sql + ‘SELECT * FROM (‘
SELECT @sql = @sql + ‘SELECT * FROM (‘
SELECT @sql = @sql + ‘SELECT * FROM (‘
SELECT @sql = @sql + ‘SELECT * FROM (‘

select @sql = @sql + ‘SELECT ”’ + @dbname + ”’ AS db, o.name,m.definition ‘
select @sql = @sql + ‘ FROM ‘+@dbname+’.sys.sql_modules m ‘
select @sql = @sql + ‘ INNER JOIN ‘+@dbname+’..sysobjects o on m.object_id=o.id’
select @sql = @sql + ‘ WHERE [definition] LIKE ”%’+@text1+’%”’

SELECT @sql = @sql + ‘ ) X WHERE [definition] LIKE ”%’+@text2+’%”’
SELECT @sql = @sql + ‘ ) X WHERE [definition] LIKE ”%’+@text3+’%”’
SELECT @sql = @sql + ‘ ) X WHERE [definition] LIKE ”%’+@text4+’%”’
SELECT @sql = @sql + ‘ ) X WHERE [definition] LIKE ”%’+@text5+’%”’

–PRINT @sql
execute (@sql)
[/sql]

3- OpenTransactionListingAndKill.sql

Şirket veritabanlarında birden fazla kişi ve takım işlem yapar.Bu işlemlerden bazıları sonlanmayabilir ve bu sonlanamayan transactionlar veritabanı performansını doğrudan etkiler.Aşağıda sql server üzerinde sistem tablolarını kullanarak açık kalan (sonlanmayan) transactionları listeleyebilir ve bu transactionları (kill) sonlandırabilirsiniz.

[sql] — Açık olan transactionları listeler

SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id

–SPID ile açık olan transactionları KILL ediyoruz

–KILL SPID
[/sql]

4-  INSERT.sql

Bu saklı yordam ; bir tabloya ekledimiş olduğunuz veriler için insert işelmini yapan ifadeyi hazırlar.Şöyle düşünün test ortamında bir tabloya bir sürü veri eklediniz eklediğiniz bu verileri diğer ortamlara kolayca taşımak istiyorsunuz.bunun için tek tek insert scripti hazırlamanız gerekiyor.Onun yerine bu sp ile hızlı ve pratik bir şekilde insert scriptinizi oluşturabilirsiniz.

[sql] GO
/****** Object: StoredProcedure [dbo].[INSERT] Script Date: ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
–drop proc [dbo].[INSERT]

CREATE procedure [dbo].[INSERT] (
@Query Varchar(MAX)
)

AS

SET nocount ON

DECLARE @WithStrINdex AS INT
DECLARE @WHEREStrINdex AS INT
DECLARE @INDExtouse AS INT

DECLARE @SchemaANDTAble VArchar(270)
DECLARE @Schema_name varchar(30)
DECLARE @Table_name varchar(240)
DECLARE @Condition Varchar(MAX)

SET @WithStrINdex=0

SELECT @WithStrINdex=CHARINDEX(‘With’,@Query )
, @WHEREStrINdex=CHARINDEX(‘WHERE’, @Query)

IF(@WithStrINdex!=0)
SELECT @INDExtouse=@WithStrINdex
ELSE
SELECT @INDExtouse=@WHEREStrINdex

SELECT @SchemaANDTAble=LEFT (@Query,@INDExtouse-1)
SELECT @SchemaANDTAble=LTRIM (RTRIM( @SchemaANDTAble))

SELECT @Schema_name= LEFT (@SchemaANDTAble, CharINdex(‘.’,@SchemaANDTAble )-1)
, @Table_name = SUBSTRING( @SchemaANDTAble , CharINdex(‘.’,@SchemaANDTAble )+1,LEN(@SchemaANDTAble) )

, @Condition=SUBSTRING(@Query,@WHEREStrINdex+6,LEN(@Query))–27+6

DECLARE @COLUMNS table (Row_number SmallINT , Column_Name VArchar(Max) )
DECLARE @CONDITIONS AS varchar(MAX)
DECLARE @Total_Rows AS SmallINT
DECLARE @Counter AS SmallINT

DECLARE @ComaCol AS varchar(MAX)
SELECT @ComaCol=”

SET @Counter=1
SET @CONDITIONS=”

INsert INTO @COLUMNS
SELECT Row_number()Over (Order by ORDINAL_POSITION ) [Count] ,Column_Name FROM INFORMATION_SCHEMA.columns WHERE Table_schema=@Schema_name
AND table_name=@Table_name
AND Column_Name NOT IN (‘FTP_PASSWORD’,’FTP_ENCRYPTED_PASSWORD’)

SELECT @Total_Rows= Count(1) FROM @COLUMNS

SELECT @Table_name= ‘[‘+@Table_name+’]’

SELECT @Schema_name='[‘+@Schema_name+’]’

While (@Counter<=@Total_Rows )
begIN
–PRINT @Counter

SELECT @ComaCol= @ComaCol+'[‘+Column_Name+’],’
FROM @COLUMNS
WHERE [Row_number]=@Counter

SELECT @CONDITIONS=@CONDITIONS+ ‘ +Case When [‘+Column_Name+’] is null then ”Null” Else ””””+

Replace( Convert(varchar(Max),[‘+Column_Name+’] ) ,””””,”” )

+”””” end+’+”’,”’

FROM @COLUMNS
WHERE [Row_number]=@Counter
AND Column_name NOT IN (‘FTP_PASSWORD’,’FTP_ENCRYPTED_PASSWORD’)

SET @Counter=@Counter+1

End

SELECT @CONDITIONS=RIGHT(@CONDITIONS,LEN(@CONDITIONS)-2)

SELECT @CONDITIONS=LEFT(@CONDITIONS,LEN(@CONDITIONS)-4)
SELECT @ComaCol= substrINg (@ComaCol,0, len(@ComaCol) )

SELECT @CONDITIONS= ”’INSERT INTO ‘+@Schema_name+’.’+@Table_name+ ‘(‘+@ComaCol+’)’ +’ Values( ‘+”” + ‘+’+@CONDITIONS

SELECT @CONDITIONS=@CONDITIONS+’+’+ ”’)”’

–PrINt(@Condition)
SELECT @CONDITIONS= ‘SELECT ‘+@CONDITIONS +’FROM ‘ +@Schema_name+’.’+@Table_name+’ With(NOLOCK) ‘ + ‘ WHERE ‘+@Condition
–prINt(@CONDITIONS)
Exec(@CONDITIONS)

/****** Kullanımı ******/
EXEC [dbo].[INSERT] ‘dbo.TABLE WHERE COLUMN_NAME=”TEXT”’
[/sql]

Projeler

1 – Db.ProcedureDeployer

Birden fazla yazılım geliştirme  ortamınız (DEV-UAT-PRE-PROD) var. Bu ortamların birinde saklı yordamlarda çokca kullanılan bir tablonunn  ismini değiştirmek zorunda kaldınız ve değiştirdiniz.Tablo ismini değiştirdiğiniz için bu tabloyu kullanan yüzlerce saklı yordam , trigger view de haliyle etkilenecektir.Tablo isminde yapılan değişikliği bu tabloyu kullanan veritabanı nesneleri üzerinde de yapmalısınız. Ama bu değişikliği tek tek yapmak hem yük hem de efektif bir çözüm değil işte bunun için bu konsol uygulamasını yazdım.Bu uygulama belirlenen key(önceki metin) ve value (yeni metin) ya göre veritabının da tek tek tarama yapar ve eski metnin geçtiği yerleri yenisi ile değiştirir ve saklı yordamı (sp) yeniler. Bir anlam da veritabanı seviyesinde sp içeriklerini eşitler.

Projede kullanılan veritabanı işlemleri SQL Server Management Objects (SMO) ile yapılıyor.Bu kütüphane ile sql server management strudio aracılığıyla yaptığımız sp,tablo,trigger gibi veritabanı nesneleri üzerinde yapılabilecek işlemlerin tamamını yapabilirz.Örneğin ; bir veritabanı deployer aracı veya veritabanı yönetimi için web tabanlı bir araç yapılabilir. Microsoft.SqlServer.Smo muhtemel dizin ‘C:Program Files (x86)Microsoft SQL Server110SDKAssembliesMicrosoft.SqlServer.Smo.dll

Daha fazla detay için burayı bi ziyarety edin. :D  http://technet.microsoft.com/en-us/library/ms162557.aspx

2 –  Db.JunkFinder

Bu proje veritabanı üzerinde oluşturulan geçici ve yedeklediğimiz veritabanı nesnelerini (stored procedure,table,trigger,view,function) bulmak ve bunları txt dosyasına kaydetmek için kullanabileceğimiz bir konsol uygulamsıdır.Çok fazla geliştiricinin çalıştığı kurum veritabanları üzerinde özellikle bu tip süreçleri senede bir kez yapmak veritabanı sorgu performansı arttıracağı gibi bellek üzerinde de yer açılacaktır.Veritabanı üzerinde bu tip operasyonel işler için konsol uygulaması yazmak daha mantıklı ve kontrollü olduğu malumunuzdur.

Başlıklara tıklayarak github sayfalarına gidebilirsiniz.

Şimdilik bu kadar iyi kodlamalar

No Comments

Bir Cevap Yazın