Data Partioning...Urgent help req.

hi,
i have a table with 5 million records which has been horizonatal partitioned
into 3 tables. Each of these tables are located on different logical drives
using filegroups in the same database on a single SQL Server Instance.
Each table structure is:
InvID int Identity,
InvDate datetime
InvNumber varchar(50)
InvID and InvDate together form the primary key for each of the tables.
InvDate is my partioning column...which partitions my data according to
years.
I have then created a view using a select * and union all clause which
selects the data from these tables.
When querying data selectively i.e. for a specific year using the partioned
view i get a performance gain of around 10 % over selection of data from a
non partioned view i.e. from a single table containing all the 5 million
records.
Is their someway in which i can get a better performance gain, as i feel 10%
gain is not much...some of the articles i have read claim that we can get a
performance gain of upto 35%.
I also do not get any performance gain when loading data or when firing a
select * i.e. selecting all 5 million records.
Can i get performace gain in these through the concept of horizontal
partitioning.
Plz help.
Regards,
Harman.Harman,
I don't see any value in partitioning the table with such small amount of
data. I am a bit surprised that you saw any performance gain. If you have
much larger amount of data, and you have multiple physical drives, you are
more likely to see the performance benefit especially when multiple
partition elements are hit. The administrative overhead far far outstrip
any (if any) performance improvement even if you want the fastest response
with such small amount of data.
Better solution in your case would be using a clustered covering index --
here basically putting all the three columns into the index.
hth
Quentin
"Harman Dhillon" <harmand@.grapecity.com> wrote in message
news:#d5wIoVcDHA.2632@.TK2MSFTNGP12.phx.gbl...
> hi,
> i have a table with 5 million records which has been horizonatal
partitioned
> into 3 tables. Each of these tables are located on different logical
drives
> using filegroups in the same database on a single SQL Server Instance.
> Each table structure is:
> InvID int Identity,
> InvDate datetime
> InvNumber varchar(50)
> InvID and InvDate together form the primary key for each of the tables.
> InvDate is my partioning column...which partitions my data according to
> years.
> I have then created a view using a select * and union all clause which
> selects the data from these tables.
> When querying data selectively i.e. for a specific year using the
partioned
> view i get a performance gain of around 10 % over selection of data from a
> non partioned view i.e. from a single table containing all the 5 million
> records.
> Is their someway in which i can get a better performance gain, as i feel
10%
> gain is not much...some of the articles i have read claim that we can get
a
> performance gain of upto 35%.
>
> I also do not get any performance gain when loading data or when firing a
> select * i.e. selecting all 5 million records.
> Can i get performace gain in these through the concept of horizontal
> partitioning.
> Plz help.
> Regards,
> Harman.
>
>
>
>