Re: Performance of View vs. Table

The problem with creating a narrower table is that you're gonna end up with some one-to-one joins with the rest of the original table, which could make inserts/updates a hassle. I'd look at creating an index with all the columns you need. If you can get the query plan to do a key-only scan, then you don't need a narrower table at all. If the index is detached then all the better. >>> <mars1972@my-deja.com> 05/04/00 04:28pm >>> 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. Later ________________________________________________________________________ > 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.