Partition table query taking more time

Last Post 18 May 2007 11:36 AM by neel6872. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
neel6872
New Member
New Member

--
17 May 2007 12:49 PM
I have a sql2005 database with metdata table tables and one Large Table with 300+ millions rows. I partitioned the table on "date" on seperate file groups and indexed it on the partition.

I am running a store procedure which gets the data from metadata tables and then gets the data from "Large Table".

Question is, Why Procedure is taking double time on "database with Partition" than "database with no partition". I would have expected otherwise.

Thanks in Advance.

NM
SQLUSA
New Member
New Member

--
18 May 2007 12:33 AM
Certainly, that is the point of partitioning: speed.

Is the query hitting all the partitions?

Kalman Toth, Database Architect
SQL Server Training - http://www.sqlusa.com
neel6872
New Member
New Member

--
18 May 2007 10:44 AM
Attaching the samle procedure.

Case I :

Database with DataTable partitioned on DataDate

exec sp_Part_Test -- Takes "nn" time

Case II :

Database with DataTable not partitioned

exec sp_Part_Test -- Takes double time than Case I (2 x nn)

Why ?

create proc sp_Part_Test
as

declare @Value smallmoney
declare @date as datetime
set @date = '1/1/2007'

while @date <= '2/2/2007'
begin
select @Value = DataValue from DataTable where DataDate = @date
set @date = dateadd(mi, 10, @date)

---- other calculations...
end





neel6872
New Member
New Member

--
18 May 2007 11:36 AM

I made error in previous email.

Correction :

"Case I" took double time than "Case II".

Procedure on Partiton table is taking more time than on non-partition table.

neel6872
New Member
New Member

--
22 May 2007 10:45 AM
Yes I did. They claim to have fixed the bug in SP2. I installed the SP2 but didn't see any improvement in the store proc timings.

Thanks
ratherbegolfing
New Member
New Member

--
01 Aug 2007 11:44 AM
Does the where statement or the join clause take into account your partition key?
SQLUSA
New Member
New Member

--
02 Aug 2007 01:32 AM
Can you test it with a VIEW over the partitions? Let us know.

Kalman Toth, Database Architect
SQL Server 2005 Training - http://www.sqlusa.com/order2005grandslam
You are not authorized to post a reply.

Acceptable Use Policy