Partitions and performance

I copied 5million records into a table that has no indexes and partitions and
it took 90mins.I then truncated this table and setup a Partition and created
clustered index based on the Partitioning Column. I copied the same 5million
records into this table but it took only 60mins.
Shouldnâ't the copy in the second case be taking more time because of the
presence of the Clustered Index? Or did the created partition increase the
performance?
Later, I subjected the table to a set of queries and compared the
performance against the non-partitioned table and I could not see much of a
performance gain.
Considering all else equal, ie the query is based on the Partitioned Column
which is inturn based on one of the keys in the Clustered Index, I expected
good performance gain. But I was disappointed to see that it did not offer
much.
I just wanted to hear from others and see what their experiences are.
Thanks,
rgnHi
You don't give information about the partitioning or the clustered index and
what the data is that you are inserting. Posting DDL and an example would
help. You may answer the first question yourself by loading into the table
with the clustered index and no partition.
Check out the query plans to see if your partioning is working.
John
"rgn" wrote:
> I copied 5million records into a table that has no indexes and partitions and
> it took 90mins.I then truncated this table and setup a Partition and created
> clustered index based on the Partitioning Column. I copied the same 5million
> records into this table but it took only 60mins.
> Shouldnâ't the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned Column
> which is inturn based on one of the keys in the Clustered Index, I expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>|||rgn
I don't think that Partition is designed to gain performance benefit, in my
opinion it is more for structuring the data , however , I'm sure that
performance plays its role as well
Read Nigel's great article
http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:ED3E72A3-962E-439B-A6E3-60DA95C529FB@.microsoft.com...
>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
> Shouldn?t the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of
> a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned
> Column
> which is inturn based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>|||>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
I think there must be something else going on here. Was the database space
pre-allocated? Were the non-partitioned and partitioned tables on the same
filegroups and physical disks? Is the source data in the same sequence as
the clustered index?
> Considering all else equal, i.e. the query is based on the Partitioned
> Column
> which is intern based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
Partitioning is more for manageability than performance. For example,
partitioning can reduce intermediate space requirements for (re)building a
clustered index or allow you to place historical read-only data on different
filegroups. Partitioning can improve performance of certain queries through
partition elimination but this mostly helps scan operations.
Indexing is the real key to performance of both partitioned and
non-partitioned tables. The real performance sweet spot for partitioning is
when a design allows mass data load/archive using SWITCH.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:ED3E72A3-962E-439B-A6E3-60DA95C529FB@.microsoft.com...
>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
> Shouldnâ't the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of
> a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned
> Column
> which is inturn based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>