Re: Performance of View vs. Table
From: mars1972@my-deja.com > >In article <8es2eb$8s6$1@news.xmission.com>, > "Obnoxio The Clown" <obnoxio@hotmail.com> wrote: > > > > From: Richard Spitz <Richard.Spitz@ana.med.uni-muenchen.de> > > > > > >in order to facilitate analysis of certain sub-groups in a table > > >containing about 1 million rows, I was asked to create a new table > > >that just contains certain fields of those rows that meet specific > > >criteria, in the hope that joining this new table with other tables > > >will yield much better performance, especially when "group by" > > >etc. is involved. > > > > > >Will it be sufficient to just create a view that meets the >definition, > > >or must I really create and populate a new table? > > > > I'm fairly certain a view will not change performance, nor will a >"narrower" > > table. Only a "shorter" table (with fewer rows) will change >performance, > > IMHO. So I wouldn't bother with either one. :0) > > > >I have to disagree on the narrower table not increasing performance. By >having fewer bytes per record, more records will fit into a page, >increasing the number of records able to be read during a read-ahead. >Also, by having fewer bytes per record, they may even stay in the buffer >cache longer, depending on other usage on the database. It will also >decrease the time needed to read the row, since the row is shorter. >This won't make much difference when doing singleton selects, but it can >make a huge difference when selecting multiple records. > >I'm still not saying that creating a new table is the way to go, though. > Just trying to make a point. Funnily enough, I won't disagree with you. But in general that saving is not substantial compared to the benefits of working with fewer rows. And the maintenance, oy vey! :0) ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Obnoxio The Clown schrieb: > Funnily enough, I won't disagree with you. But in general that saving is not > substantial compared to the benefits of working with fewer rows. And the > maintenance, oy vey! :0) Thanks to OTC and all others who contributed to this thread. To make my goal clearer, the new table would contain significantly less rows than the original table, in addition to being "narrower". The conclusion is that a view would not result in any performance improvement, but creating the new table with less and narrower rows would. The maintenance question is not an issue here, since the data involved are static in nature. The new table is also only needed for a very limited time. However, I do see the point that was stressed so much by OTC and others. Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. med Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | | Klinikum Grosshadern | FAX : +49-89-7095-6420 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+
In article <8esggd$fi9$1@news.xmission.com>, "Obnoxio The Clown" <obnoxio@hotmail.com> wrote: > > From: mars1972@my-deja.com > > > >In article <8es2eb$8s6$1@news.xmission.com>, > > "Obnoxio The Clown" <obnoxio@hotmail.com> wrote: > > > > > > From: Richard Spitz <Richard.Spitz@ana.med.uni-muenchen.de> > > > > > > > >in order to facilitate analysis of certain sub-groups in a table > > > >containing about 1 million rows, I was asked to create a new table > > > >that just contains certain fields of those rows that meet specific > > > >criteria, in the hope that joining this new table with other tables > > > >will yield much better performance, especially when "group by" > > > >etc. is involved. > > > > > > > >Will it be sufficient to just create a view that meets the > >definition, > > > >or must I really create and populate a new table? > > > > > > I'm fairly certain a view will not change performance, nor will a > >"narrower" > > > table. Only a "shorter" table (with fewer rows) will change > >performance, > > > IMHO. So I wouldn't bother with either one. :0) > > > > > > >I have to disagree on the narrower table not increasing performance. By > >having fewer bytes per record, more records will fit into a page, > >increasing the number of records able to be read during a read-ahead. > >Also, by having fewer bytes per record, they may even stay in the buffer > >cache longer, depending on other usage on the database. It will also > >decrease the time needed to read the row, since the row is shorter. > >This won't make much difference when doing singleton selects, but it can > >make a huge difference when selecting multiple records. > > > >I'm still not saying that creating a new table is the way to go, though. > > Just trying to make a point. > > Funnily enough, I won't disagree with you. But in general that saving is not Funnily enough, you won't disagree with me? Exactly what are you trying to say here?!? Et tu, Brutus? :) > substantial compared to the benefits of working with fewer rows. And the > maintenance, oy vey! :0) > ________________________________________________________________________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com > > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.