|
Home > Archive > MS SQL Server > July 2005 > Large table management and Partitioned Views
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Large table management and Partitioned Views
|
|
|
| Just trying to get feedback from people on their practical experience with
managing large tables (>250mil rows). Has anyone used Partitioned Views and
how do they work? Any performance advantage? If not partitioned views, is
there anything else one can do in a large enterprise environment to manage
large tables?
| |
| Leo Leong 2005-07-14, 3:23 am |
| Hi,
from my experience, Partitioned Views will actually reduce IO read (physical
read and read-ahead read).
it depends on how you split your table and file group.
Leo
"LC" wrote:
> Just trying to get feedback from people on their practical experience with
> managing large tables (>250mil rows). Has anyone used Partitioned Views and
> how do they work? Any performance advantage? If not partitioned views, is
> there anything else one can do in a large enterprise environment to manage
> large tables?
| |
|
| Thanks Leo,
Is the reduction in IO reads due to the fact the tables are split
up/smaller? Are there any other benefits or caveats to moving to this type
of architecture?
"Leo Leong" wrote:
[color=darkred]
> Hi,
>
> from my experience, Partitioned Views will actually reduce IO read (physical
> read and read-ahead read).
> it depends on how you split your table and file group.
>
> Leo
>
> "LC" wrote:
>
| |
| Leo Leong 2005-07-14, 8:23 pm |
| before I implemented it, i did a testing in a test environment.
i split a table into 6 where each table is stored in separate data file.
the partition key for the table is TYPR, let say.
so, when i ran a query with "SET STATISTIC IO ON" to select record based on
TYPE = "A", i found that SQL Server will only read the data from the table
that stores TYPE = "A" only.
of course, when the table is split, data file that stores the child tables
is smaller. so, relatively, it will fasten the process of searching a record.
another benefit of partitioned view is you can do distributed database
environment. that means, those child tables can be stored in > 1 database
servers.
Leo
"LC" wrote:
[color=darkred]
> Thanks Leo,
> Is the reduction in IO reads due to the fact the tables are split
> up/smaller? Are there any other benefits or caveats to moving to this type
> of architecture?
>
> "Leo Leong" wrote:
>
|
|
|
|
|