vertical partitioning

[Dan Osborn]

What are the advantages of vertical partitioning if you still have a
one-to-one relationship after the partitioning.

For instance, we have a 90-column table of which 75 fields may be
non-duplicated and somewhat useful. The "somewhat" useful columns contain
data that is important to our client, but for which we perform no real
operation... it's supplemental... and all we do is echo it on to the client.

I *could* partition the information vertically into a "core" table, that
which is useful in our operation, and a "supplemental" table, that which we
receive, store, and forward. These tables will always have a one-to-one
relationship, even if there is slight de-normalization in the supplemental
table. My question is whether there is any value in this step other than
reducing table size of my operating table? Is there any speed impovement
with the smaller table? Any logical reason that would be better?

Thanks for the input...

[BP Margolin]

Dan,

Performance is frequently directly proportional to I/O.

SQL Server, whenever it is asked to read a row from a table, actually reads
a data page (actually it usually reads 8 data pages, but let's ignore that
for the moment). Thus the more rows that can fit onto a single data page,
the higher the probability is that a previous read will have brought a data
page into cache, thus avoiding an additional physical I/O. Thus moving
infrequently accessed columns to a separate table can offer significant
performance improvements ... of course that is largely a matter of how often
the infrequently accessed columns are actually used.

To address it another way ... let's say that moving the 75 columns to a
separate table reduces the size of the row by half. Then approximately twice
as much data can fit onto each data page. So if you have to do a table scan,
you will have halved the amount of physical I/O ... and probably improved
performance by 50%.

----------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Dan Osborn" <dos...@flash.net> wrote in message
news:cQjg6.463$y03....@news.flash.net...

[Wojciech Galazka]

Dan Osborn napisał(a) w wiadomości: ...

>What are the advantages of vertical partitioning if you still have a
>one-to-one relationship after the partitioning.

[...]
- My question is whether there is any value in this step other than

>reducing table size of my operating table? Is there any speed impovement
>with the smaller table? Any logical reason that would be better?
>

consider a table scan over a table that has a milion rows and 90 columns ...
...now consider the same table but with 15 columns ...

[Dan Osborn]

BP,

Thanks for your input... I really appreciate it.

Now, let me ask you this... What if I was to put a view on top of these two
tables to maintain the "look" of a single table? This would be necessary
since we have an existing application that would be difficult to re-engineer
for the two-table structure. Would this maintain the I/O benefits for
SELECT statemtents (assuming of course that the SELECT only looks at one of
the underlying tables)? The catch here I think is the restrictions on
updating views.

Any thoughts would be useful.

Regards,
Dan

BP Margolin <bpm...@attglobal.net> wrote in message
news:3a81f...@news3.prserv.net...

[Dan Osborn]

BP,

Again your input is appreciated...

You had responded to a question of mine (which is now gone) about views,
included below. I was wondering if you have a reference to any books or
documentation that I can use in the report I'm creating? (Somehow, I don't
think my boss is going to accept "some guy on the internet" as a viable
source) Specifically, the description of how views are resolved by
replacing the select statement into the query and that they are uncompiled.

Maybe you've written an article on the topic or something?

Thanks in advance,
Dan

> > Dan,
> >
> > Views neither improve nor degrade performance. Views are NOT
pre-compiled
> > (at least not in SQL Server). The way views "work" in SQL Server is that
> > when a view is referenced, the view is replaced by the definition of the
> > view, and this is what is sent to the query optimizer. Perhaps an
example
> > might help ...
> >
> > use pubs
> > go
> >
> > create view v_authors as select au_id, au_lname, au_fname from authors
> > go
> >
> > select * from v_authors
> >
> > Internal to SQL Server, the select query morphs to ...
> >
> > select * from (select au_id, au_lname, au_fname from authors) as
v_authors
> >
> > If you take a look at the query plan for the select query using the view
> and
> > the select query using the view definition, you'll see that the plans
are
> > identical.
> >
> > Aside from the reasons you list for views, let me add one that I
consider
> > way more important.
> >
> > Views (and stored procedures) allow me as the database designer to
> separate
> > the physical database from the logical database. This way, with a bit of
> > luck, even if I have to make a change to the physical database, I can
keep
> > the logical database the same, and thus the interface from the client
> > application to the database can remain unchanged.

[Dan Osborn]

BP,

Regards,
Dan

> ----------------------------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.
>

[BP Margolin]

Dan,

According to my newsgroup reader, this post has a date of today (Feb 13),
but I responded to this back on Feb 10. Are you unable to access that
response?

----------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Dan Osborn" <dos...@flash.net> wrote in message

news:jFei6.25$Sx5....@news.flash.net...