Partitioning Problem.

[hdv212]

سلام و خسته نباشید
من Dat Partitioning رو روی جدول Sales.SalesOrderDetail و Sales.SalesOrderDetail2 به صورت زیر تست کردم :

alter database AdventureWorks
add filegroup fg2001
go
alter database AdventureWorks
add filegroup fg2002
go
alter database AdventureWorks
add filegroup fg2003
go
alter database AdventureWorks
add filegroup fg2004
go
alter database AdventureWorks
add filegroup fg2005

alter database AdventureWorks
add file(name=data2001,filename='c:\data2001.ndf') to filegroup fg2001
go
alter database AdventureWorks
add file(name=data2002,filename='c:\data2002.ndf') to filegroup fg2002
go
alter database AdventureWorks
add file(name=data2003,filename='c:\data2003.ndf') to filegroup fg2003
go
alter database AdventureWorks
add file(name=data2004,filename='c:\data2004.ndf') to filegroup fg2004
go
alter database AdventureWorks
add file(name=data2005,filename='c:\data2005.ndf') to filegroup fg2005

select * from Sales.SalesOrderDetail2

create partition function pf1(datetime)
as range left
for values('20011231 23:59:59:997','20021231 23:59:59:997','20031231 23:59:59:997','20041231 23:59:59:997')

create partition scheme ps1
as partition pf1
to(fg2001,fg2002,fg2003,fg2004,fg2005)

select * from Sales.SalesOrderDetail2

alter table Sales.SalesOrderDetail2
add constraint ix1 primary key clustered (SalesOrderID,SalesOrderDetailID,ModifiedDate) on ps1(ModifiedDate)
جدول Sales.SalesOrderDetail2 یک کپی از جدول Sales.SalesOrderDetail هست که فقط خواستم تاثیر Data partitioning رو در اون ببینم، سپس از دو query زیر استفاده کردم :

-- With Partitioning
select * from Sales.SalesOrderDetail2 where datepart(year,ModifiedDate)='2001'

-- Without Partitioning
select * from Sales.SalesOrderDetail where datepart(year,ModifiedDate)='2001'

و Execution Plan هردو query رو دیدم، با کمال ناباوری دیدم که query دوم (یعنی بدون استفاده از Data Partitiioning) درصد 39 رو به خودش اختصاص داده و 61 در صد واسه query اول بود، یعنی performance بالایی به جای اینکه بیشتر باشه، کمتر شد، علت چیه و چکار کنم که performance اولی زیاد بشه ؟

توجه : همانطور که در کد اول دیدید، clustered Index بر روی سه فیلد گذاشته شده، در صورتی که در جدول اصلی بر روی دو فیلد گذاشته شده، گفتم شاید به خاطر این موضوع باشه.

[AminSobati]

سلام حامد جان،
به نظر میرسه وقتی از DatePart استفاده میکنین، Query قادر نیست دقیقا تشخیص بده کدوم Partition هدف شماست چون توابع روی اصل یک Value تغییر ایجاد میکنند. مثلا در Query دوم شما اگر به Exec Plan دقت کنین، یک Constant Scan وجود داره که هر 5 پارتیشن رو استخراج میکنه. اما اگر Query به این شکل انجام بشه مشکلی ندارین:


select * from Sales.SalesOrderDetail2 where ModifiedDate between '20010101' and '20011231'

من این حالت رو هم آزمایش کردم که جواب درست رو میده اما 2 پارتیشن استخراج میشه:


select * from Sales.SalesOrderDetail2 where ModifiedDate >'2001' and ModifiedDate <'2002'

[hdv212]

درسته آقای ثباتی عزیز، حق با شماست.
در مورد query اولی که پیشنهاد کردید، من تست کردم :

-- Without Partitioning
select * from Sales.SalesOrderDetail where ModifiedDate between '20010101' and '20011231'

-- With Partitioning
select * from Sales.SalesOrderDetail2 where ModifiedDate between '20010101' and '20011231'
و دیدم که query دوم که از Partitioning استفاده میکنه، performance بالاتری داره (16%) و اون یکی هم 84% داره، به هر حال ممنون و متشکرم و خسته نباشید.
به امید دیدار.