Değişken Ağırlıklarda Rastgele İçerik

Bir web uygulamasında sık karşılaşabilecek küçük bir ayrıntıyı paylaşayım. Sitede bir bölümde rastgele içerik verildiğini düşünelim. Aynı zamanda bu içeriklerin bazılarını zaman zaman ön plana çıkarmak için tüm içerikler toplamları 100 yapacak şekilde farklı ağırlıklarda olsun.

Bu durumda bu içeriğin veritabanından en hızlı şekilde gelmesi gerekir. Bu performansı 2 küçük eklemeyle çözebiliriz.

Bu ağırlıkları tuttuğumuz alan weight olsun. Önce bu tabloya weightTotal isminde bir alan ekleyelim. Bu alan bulunduğu satıra kadar olan alanların (bulunduğu satır dahil) toplamını tutsun. weightTotal alanını da weight alanının güncellendiği durumlarda tüm weightTotal değerlerini güncelleyecek şekilde çalışacak bir trigger ile güncelleyelim.



Verinin çekilmesi esnasında da T-Sql içerisinde 1 ve 100 arasında rastgele bir sayı seçip bu sayıdan büyük en küçük weightTotal değerli alanı seçtiğimizde bu isteği performanslı bir şekilde çözebiliriz.

Normalize Tablodaki Değerleri Birleştirmek

Normalize tasarlanmış bir database'de çoklu seçim alanlarını tek bir alan olarak birleştirilmiş şekilde gösterme ihtiyacı olabilir. Örneğin kullanıcı tablosu var, kullanıcı için o alanın değer tanımlarının olduğu (propertyid,propertyName) şeklinde bir itmProperty tablosu var ve bir kullanıcı için n satır olmak üzere kullanıcıya ait property değerlerinin tutulduğu (userid,propertyid) şeklinde bir userProperty tablosu var. Bizim istediğimiz ise kullanıcıları listelediğimiz sorguda kullanıcıya ait property değerlerinin isimlerini virgülle ayrılmış şekilde tek bir alanda getirmek. Önce aşağıdaki kullanıcı tanımlı fonksiyonu oluşturmak gerekiyor.

CREATE FUNCTION [dbo].[fnGetPropertyList] (@userid bigint)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @propertyList VARCHAR(8000)
SET @propertyList = ''
DECLARE @rslt VARCHAR(8000)
SET @rslt=NULL
SELECT @propertyList = @propertyList + propertyName + ','
FROM itmProperty
WHERE propertyid in
(select propertyid from userProperty where userid=@userid)
ORDER BY propertyName

IF LEN(@propertyList)>0
SET @rslt= LEFT(@propertyList,(LEN(@propertyList) -1))
ELSE
SET @rslt= NULL

RETURN @rslt
END

Bu fonksiyonu sorgu içerisinde aşağıdaki şekilde kullanabiliriz.

Select *,
fnGetPropertyList(U.userid)
from tblUser U

Sql Server 2008 Programlama Yenilikleri

Nedense database yönetim sistemlerinin yeni versiyonları çıktığında bizde çok heyecan yaratmıyor. Nedeni de çok az yeni özellikle gelmeleri. Bunun nedeni ilişkisel veritabanı kavramının yaygın olması sebebiyle yeni özellikten ziyade varolan servislerde performans artışına odaklanılması. Bu sebeple de her yeni versiyonda yüzde olarak performans kazançları belirtilir. Sql Server 2008'de de çok yeni özellik olmasa da ben özellikle veritabanı programlama ile ilgili hoşuma giden birkaç küçük yeniliği yazmak istiyorum.

* Değişken tanımlama
Benim çok canımı sıkan bir durumdu bu. Önce değişkeni tanımlayıp, sonra başlangıç değerini atamak zorunda olmak.

Declare @userid int
Set @userid=999

Geç de olsa bu özellik eklenmiş

Declare @userid=999

* Toplu insert
Daha önce bir kolaylık olarak yazdığım toplu insert konusu union cümlesine gerek kalmadan çözülmüş

insert into user(firstName,lastName,status)
select "Ali","Yazar",1 union all
select "Veli","Okur",0 union all
select "Veysel","Bakar",1

şeklindeki ifade

insert into user(firstName,lastName,status)
values
("Ali","Yazar",1),
("Veli","Okur",0),
("Veyseş","Bakar",1)

bu şekilde yazılabiliyor

* Merge

İki tablonuz olduğunu düşünün. Biri gerçek kullanıcılarınızın olduğu "user" tablosu, diğeri de herhangi bir kaynaktan topladığınız yeni kullanıcılarınız olan "newUSer" tablosu. Yapmak istediğiniz userid bazında bakıp, olmayanları eklemek, olanların ise bilgilerini güncellemek.

Aşağıdaki cümleyle bu işlem yapılabiliyor

Merge user as u
using newUser as n
on u.userid=n.userid
when not matched by target then
insert(firstName,lastName,status)
when matched then
update set
firstName=n.firstName,
lastName=n.lastName,
status=n.status

* Grouplama Setleri
Bu özellikle de birden fazla gruplama alanına göre sonuçlar aynı select cümlesi içinde toplanmış. Msdn sitesindeki örnek bu ihtiyaç için çok uygun olduğu için direk aynısını kullandım.

Select Region,Country,Store,SalePerson,Sum(TotalDue) as TotalSales
from SalesData
group by grouping sets
(
(Region,Country,Store,SalesPerson),
(Region,Country),
(Country),
(Region)
()
)

şeklinde yazılan sorgu sonucu aşağıda şekilde listelenir



Daha fazla bilgiye buradan ulaşabilirsiniz.

Newid Ve Newsequentialid Arasındaki Fark

NEWSEQUENTIALID SQL Server 2005 ile gelen yeni bir fonksiyondur.
Database'in GUID üretmesi için kullandığımız NewId ile, default değeri artan sayı olan durumların birleşimi olarak düşünebiliriz. Yani sıralı Guid üreten bir fonksiyon.
Bu fonksiyon sadece default değer olarak kullanılabilir. Kullanımı aşağıdaki şekildedir.

create table test(
colA uniqueidentifier DEFAULT NEWSEQUENTIALID(),
colB varchar(20)
)

insert into test (ColB) values ('aa')
insert into test (ColB) values ('bb')
insert into test (ColB) values ('cc')
insert into test (ColB) values ('dd')

eklemeleri sonucunda ColA varsayılan değer olarak aşağıdaki şekilde oluştu.

6EAF72CF-E96D-DC11-BD20-0011D8A6F316
6FAF72CF-E96D-DC11-BD20-0011D8A6F316
F0D309E7-E96D-DC11-BD20-0011D8A6F316
F817F4F1-E96D-DC11-BD20-0011D8A6F316

Fakat bu fonksiyon güvenlik gerektiren bir uygulamada kullanılamamalıdır. Çünkü değerler sıralı olduğu için kolayca tahmin edilebilir. Bu durumda NewId daha doğru bir tercih olacaktır.

And Operatörü ile Sorgulama

Veritabanı sorgulamalarında and ifadelerine sahip cümle grubunda 1. şartın sağlanmaması durumunda diğer şartlar kontrol edilmez. Bu durumda and ifadesi öğelerinde en hızlı çalışacak koşulu önce yazmak bize performans kazancı sağlayacaktır.

Örnek verecek olursak;

Bazı durumlarda bir değerin bulunan kayıta göre iki farklı alandan alındığı durumlar olabilmekte. Örneğin müşteri tablomuzda name,surname ve firmname isimli alanlar olsun.
Name ve surname alanları boşsa müşteri ismi firmname kolonundan alınsın.

Eğer biz A ile başlayan müşterileri sorgulamak istersek;

(name like 'A%' and firmname is null) or (firmname like 'A%' and name is null)

şeklinde yazabildiğimiz sorguyu ;

(firmname is null and name like 'A%' ) or (name is null and firmname like 'A%')

şeklinde yazarsak daha performanslı çalışır. Performans kazancı koşulların çok olduğu cümlelerde artacaktır.

Oracle'da Triggerlar

Sql Server'da triggerlar sadece işlem yapıldıktan sonra çalışır.

Oracle'da BEFORE ve AFTER ek komutlarıyla triggerların tetikleme işleminden önce veya sonra olmasına karar verebiliriz.

Bu ek komutlar da iki durumda kullanılabilir.

İşlem bazında BEFORE ve AFTER ek komutları uygulanabilir.

Satır bazında BEFORE ve AFTER ek komutları uygulanabilir.

CREATE TRIGGER trigger_ismi

BEFORE DELETE OR INSERT OR UPDATE

ON tablo_ismi

{pl/sql kod bloğu}

Veritabanı Tarafında Sayfalama (Paging)

Raporlama modüllerinde eğer sayfalamayı grid tarzı bir kontrole bırakmıyorsak aşağıdaki sorguları kullanarak verdiğimiz aralıktaki kayıtları çekebiliriz.Oracle'da daha önce var olan yapı Sql Server 2005 birlikte Sql server kullanıcıları için de eklendi.

Oracle için;

select * from (
select rownum rnum from (
select * from table
) t1
) where rnum between 101 and 200


Sql Server 2005 için (AdwentureWorks veritabanını kullandık)

select AddressLine1,City, ModifiedDate
from (select AddressLine1,City,ModifiedDate,row_number() over (order by
ModifiedDate desc)
as rnum from Person.Address) as RnumAdress
where rnum >= 101 and rnum <= 200

Fakat bu yapıların tek dezavantajı raporlama anında kayıt değişiklikleridir.
Örnek verecek olursak;
Sitede bir panelde online 100 kişi 10'arlı sayfalar halinde siteye giriş sırasına göre gösteriliyor. Eğer bu gezinme esnasında siteye yeni kişiler girerse bu kayıtlarda kaymalar olacaktır. Sayfalarda ilerlemeye devam ettikçe bir önceki sayfanın sonundaki kayıtların bir sonraki sayfanın ilk kayıtları olarak eklendiğini görürüz. Genellikle bu çok da zararlı olmayan durum performansı etkileyecek diğer yöntemlere tercih edilmektedir.

Where Kosulunu Devre Dışı Bırakmak

Blog yazılarımda karşılaşıp bulduğum bazı küçük çözümleri yazmaya çalışıyorum. Ne yazık ki klasik problemlerle ilgili bir çok sonuç bulabilirken çok özel durumlar için bilgi bulmak biraz zor oluyor.

Bir listeleme kontrolünüz olsun. (Treeview, Combo, Listbox ..).
Bu kontrolün elemanları bir sorguyla dolsun ve seçilen elemanın değerini başka bir listelemede kullanalım.

Örneğin ismin ilk harfine göre listeleme olsun ve hangi harf seçilirse diğer listede bu harfle ilgili bir içerik gelsin.

İlk listeyi aşağıdaki sorguyla dolduralım.

select 'A','A'
union select 'B','B'
union select 'C','C'

Diğer listelemede de

Select * from User where username like '@content%'

Buraya kadar herşey normal görünüyor. Fakat bir de bunların en üstünde 'Tüm Kullanıcılar' şeklinde bir durum istenirse nasıl olacak?

Burada sıkıntı olacak 2 durum sözkonusu. Birincisi bu seçeneğin en üstte çıkma gerekliliği, ikincisi ise diğer listeleme sorgusunu kullanarak where koşulunu es geçmek. Eğer like yerine = koşulu olsaydı klasik injection yöntemiyle 'aaa or 1=1' değeriyle çözüme ulaşabilirdik.

Bu durumu ise bir kaç deneme sonucu content değeri için '%' vererek çözebildim. Hem listede en üstte çıktı hemde where koşulunda sınırlama koymadı. Yani (where kolon like '%%') şeklinde where koşulunu işlem dışı yapabiliyoruz.

Belki bir yerlerde işinize yarayabilir.

Tablo ve Kolon Listesi

Çok tablolu sistemlerde tablo ve kolon adlarını hatırlamak çoğu zaman zor olmaktadır. Oracle ve SQL Server'da aşağıdaki sorgularla bu bilgilere ulaşabiliriz.

Oracle:

Tablo listesi
select * from tab
select * from tabs
Kolon listesi
select * from col
select * from cols

SQL Server

Tablo listesi
select * from sysobjects where xtype='U',
select * from INFORMATION_SCHEMA.TABLES
Kolon listesi
select * from syscolumns
select * from INFORMATION_SCHEMA.COLUMNS

Haftanın Gününü Veren Fonksiyon

Sql Server'da verilen tarihin haftanın hangi günü olduğunu döndüren kullanıcı tanımlı bir fonksiyon yazalım. DATEPART(weekday,date) şeklinde dönen değer haftanın gün sırasıdır. Fakat bu değer haftanın ilk günü olarak tanımlanan DATEFIRST server değişkenine göre değişmektedir. Bu yüzden fonksiyonda bu durum için bir önlem alacağız.
Amerikan ingilizcesi dil ayarlarında default değer 7 yani Pazar'dır.

SELECT @@DATEFIRST ile tanımlı değeri alabiliriz
SET DATEFIRST 1 (İlk günü Pazartesi olarak tanımlayabiliriz)

CREATE FUNCTION DayOfWeek(@date DATETIME)
RETURNS VARCHAR(9)
AS
BEGIN
DECLARE @defDayofWeek VARCHAR(10)
DECLARE @dofWeek int
SET @dofWeek= (@@DATEFIRST + DATEPART(weekday,@Date)-1)%7
SELECT @defDayofWeek=CASE @dofWeek
WHEN 0 THEN 'Pazar'
WHEN 1 THEN 'Pazartesi'
WHEN 2 THEN 'Salı'
WHEN 3 THEN 'Çarşamba'
WHEN 4 THEN 'Perşembe'
WHEN 5 THEN 'Cuma'
WHEN 6 THEN 'Cumartesi'
END
RETURN (@defDayofWeek)
END


SET @dofWeek= (@@DATEFIRST + DATEPART(weekday,@Date)-1)%7
ifadesinde ilk günün her zaman Pazartesi olmasını sağladık.

Union All Kullanarak Toplu Insert

UNION komutu kullanarak aynı tipteki değerleri tek transactionda ekleyebiliriz. Örneğin bir kayıt formumuz olsun ve bu formda kişinin okuduğu gazeteleri checkbox tipinde bir kontrolle alalım. Normalizasyonu düşünerek bu değerleri farklı bir tabloda UserId ve GazeteId olarak tutalım. Standart yöntemle bir döngü içerisinde her seçili olan kayıt için bir insert komutu çalıştırılır. Fakat bu döngü içinde seçili olanları UNION ALL ile birleştirip tek sorguda kaydı yapabiliriz.

Sorgumuz aşağıdaki şekilde oluşur;



Kayıt sayısının çok fazla olduğu durumlarda işlem süresindeki kazancımız ciddi oranda artacaktır.

Sql Server'dan Oracle'a Geçiş

Yazılım projelerinde veritabanı olarak Oracle ve Sql Server hatırı sayılır bir büyüklüğe sahiptir. Bu nedenle bir çoğumuz iki veritabanıyla da uğraşmak durumunda kalabiliriz. Sql Server'da iyi derecede olup Oracle'a geçişi kolaylaştırmak için başlangıç düzeyindeki farkları yazmanın faydalı olacağını düşündüm. Verdiğim tüm örneklerde Sql Server ifadelerini büyük harflerle Oracle ifadelerini küçük harflerle yazacağım.

Öncelikle çok kullanılan bir kaç fonksiyona bakalım;
GETDATE() - sysdate
ISNULL - nvl
CONVERT(VARCHAR,x) - to_char(x)
CONVERT(DATETIME,x) - to_date(x)
LEN - length
+ -

SELECT * INTO NEW_TABLE FROM TABLE şeklindeki SELECT INTO kalıbı yerine Oracle'da create table new_table as select * from table ifadesini kullanabiliriz.

İki tarih arasındaki fark için kullandığımız DATE_DIFF in direk karşılığı Oracle'da yok. date_diff_month(date1,date2) fonksiyonuyla ay farkını alabiliriz. Ayrıca date2-date1 ifadesi direk olarak gün cinsinden sonuç vereceği için gün farkı için bunu kullanabiliriz.

Değişken tanımlamada
DECLARE @a
INTEGER SET
@a=3
şeklindeki ifade Oracle'da
declare a number;begin a := 3;end;

Sql Server'da bir objeden bağımsız olarak SELECT 1 şeklinde bir sorgulama yapabiliriz. Oracle'da ise dummy table olarak adlandırılan DUAL tablosundan sorgulama yapmamız gerekecektir.
select 1 from dual