May 20, 2009 at 1:02 pm
Hi,
I partitioned an existing table and i don't see any performance. My original table has around 13Million records.. It has 1 clustered primary key (ID, Date) and 2 non-clustered indexes.. 1) BoxID 2) Date, ID
i did the follow:
Create my filegroup (FG1)
Create my partition function pf_date
Create my partition scheme ps_date
I move the data/table into the new scheme like:
ALTER TABLE [table_name] DROP CONSTRAINT PK_ WITH (MOVE TO [ps_date] (Date))
GO
ALTER TABLE [table_name] ADD CONSTRAINT PK_ PRIMARY KEY(ID, Date)
GO
I checked the partition data and its located in my filegroup and in each partition..
I run some queries to see the difference in time, but Im getting the same result...
Any advice??
May 20, 2009 at 1:07 pm
What do your queries look like? Could you post one or two of them?
May 20, 2009 at 1:12 pm
I did a basic one like:
select * from [table_name] where date > '2009-03-01 00:00:00.000'
i tired on my production DB where the table is not partitioned yet and I tried on my development DB where the table is partitioned and I have the same result (in time)
May 20, 2009 at 1:15 pm
lily1706 (5/20/2009)
I did a basic one like:select * from [table_name] where date > '2009-03-01 00:00:00.000'
i tired on my production DB where the table is not partitioned yet and I tried on my development DB where the table is partitioned and I have the same result (in time)
This query can't use the clustered index. If you were to swap your Id and Date in the clustered index, I think you will see a difference.
May 20, 2009 at 1:23 pm
I did, I created a table with the clustered index like date, ID..
i didnt work either...
do you think cause i move the actual data to the partition scheme can affect this? or i sd create a new table and then insert into the new table?? I need to use the same table_name for my queries...
May 20, 2009 at 1:33 pm
I don't know. You did not provide the actual code for the partition scheme, partition function, or the partition table.
May 20, 2009 at 1:44 pm
PARTITION FUNCTION
CREATE PARTITION FUNCTION pf_NAME (datetime)
AS RANGE RIGHT
FOR VALUES
(
'2008-01-01 00:00:00.000'
,'2008-02-01 00:00:00.000'
,'2008-03-01 00:00:00.000'
,'2008-04-01 00:00:00.000'
,'2008-05-01 00:00:00.000'
,'2008-06-01 00:00:00.000'
,'2008-07-01 00:00:00.000'
,'2008-08-01 00:00:00.000'
,'2008-09-01 00:00:00.000'
,'2008-10-01 00:00:00.000'
,'2008-11-01 00:00:00.000'
,'2008-12-01 00:00:00.000'
,'2009-01-01 00:00:00.000'
,'2009-02-01 00:00:00.000'
,'2009-03-01 00:00:00.000'
,'2009-04-01 00:00:00.000'
,'2009-05-01 00:00:00.000'
,'2009-06-01 00:00:00.000'
,'2009-07-01 00:00:00.000'
,'2009-08-01 00:00:00.000'
,'2009-09-01 00:00:00.000'
,'2009-10-01 00:00:00.000'
,'2009-11-01 00:00:00.000'
,'2009-12-01 00:00:00.000'
)
PARTITION SCHEME
CREATE PARTITION SCHEME Ps_NAME
AS PARTITION pf_NAME
ALL TO (FG1)
MOVE DATA TO NEW SCHEME
ALTER TABLE [table_name] DROP CONSTRAINT PK_ WITH (MOVE TO [ps_name] (Date))
GO
ALTER TABLE [table_name] ADD CONSTRAINT PK_ PRIMARY KEY(ID, Date)
GO
May 20, 2009 at 2:03 pm
What you are trying to accomplish may not benefit from a partitioned table. What is your objective? How much data do you have? What are the normal queries that are run against this table?
May 20, 2009 at 2:15 pm
What you are trying to accomplish may not benefit from a partitioned table. What is your objective? How much data do you have? What are the normal queries that are run against this table?
I just want to get data faster when I query old information (date), its for a report.. I have around 80Millions of records in one table...
I can query just the specific table or i can join this table with other one (this one is not partitioned)
note: Im grouping the data in only one Filegroup, but i guess this make sense.. Should I create more than one filegroup???
What is your advice???
May 20, 2009 at 2:26 pm
What do your typical queries look like, not the one you alread showed me by the way.
May 20, 2009 at 2:57 pm
these 2:
selectdistinct t1.Date, t2.MacAddress
fromTABLE1 t1
joinTABLE2 stb on t2.Id = t1.Id
wheret1.Date between '2009-03-01' and '2009-03-30'
selectt1.MacAddress, t2.date, t1.Id
fromdbo.Table1 t1
joindbo.Table2 t2 on t2.Id = t1.Id
WHEREt1.Id not in
(select t2.Id
from Table2 t2
where t2.date >= '2009-03-01' )
May 20, 2009 at 3:10 pm
First, i think the first query should benefit from the partioned table. Not sure about the second one.
Second, I took the time to rewrite your queries. I think these may be a bit more effecient, but you'll have to test them to see if they work for you.
select distinct
t1.Date,
t2.MacAddress
from
TABLE1 t1
inner join TABLE2 stb
on t2.Id = t1.Id
where
t1.Date >= '2009-03-01' and
t1.date = '2009-03-01')
WHERE
t2.id is null
May 20, 2009 at 3:33 pm
Thank you for your advice..
Im getting the same time, but the second query help me to get the info a little faster but, with less information...
anyway!! I guess the partition is not good for this case..
Thank you again
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply