Re: Performance of View vs. Table
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) ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Obnoxio The Clown 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. "Narrower" tables can cause dramatic improvements in performance, simply by reducing the amount of pages that need to be scanned to satisfy one's query. Additionally, BUFFER usage improves. I've had personal experience of a query's performance improving by a factor of 10 when we vertically split a table (added complexity to our programs, though!). In fact, that's why its recommended that TEXT columns be kept in their own dbspace. Rudy > Only a "shorter" table (with fewer rows) will change performance, > IMHO. So I wouldn't bother with either one. :0) > ________________________________________________________________________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.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. 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.
Instead of creating a separate table or a view, another option to look into is the advanced indexing technology of OMNIDEX from DISC (Dynamic Information Systems Corporation). (Attention: Promotional information follows. Please disregard if not interested in another solution.) OMNIDEX uses specialized Multidimensional Keyword Indexes to deliver full text searches and unlimited multidimensional analysis by any number of criteria or columns. OMNIDEX layers on top of your existing database, and it supports numerous databases and document files, including Informix, Oracle, SQL Server, Sybase, flat files, HTML and Word documents. OMNIDEX is ideal for ad-hoc querying, it works well for both high and low cardinality data, and is very efficient in terms of build time and disk space. How much OMNIDEX can help depends on your needs and environment. If you would be interested in a free performance analysis or more information, please contact me. Cheryl Grandy DISC cgrandy@disc.com 303 444-4000 www.disc.com/home OMNIDEX - for the fastest applications ever! 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) > ________________________________________________________________________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com > > -- Cheryl Grandy DISC Get OMNIDEX for the fastest applications ever Sent via Deja.com http://www.deja.com/ Before you buy.
OK Cheryl NOW you have graduated from an annoying promoter to a despicable SPAMMER! Posting the exact same response to multiple postings using a different email address each time indicates that you are even aware you may have to deal with SPAM filters. Cut it out. If you want to make helpful suggestions and when appropriate mention your companies product, welcome. If you just want to SPAM, get lost. Mehdi from Querix made the transition from SPAMMER to contributor, you can too. Otherwise be gone! Art S. Kagel cgrandy@disc.com wrote: > > Instead of creating a separate table or a view, another option to look > into is the advanced indexing technology of OMNIDEX from DISC (Dynamic > Information Systems Corporation). > (Attention: Promotional information follows. Please disregard if not > interested in another solution.) > > OMNIDEX uses specialized Multidimensional Keyword Indexes to deliver > full text searches and unlimited multidimensional analysis by any number > of criteria or columns. OMNIDEX layers on top of your existing > database, and it supports numerous databases and document files, > including Informix, Oracle, SQL Server, Sybase, flat files, HTML and > Word documents. > > OMNIDEX is ideal for ad-hoc querying, it works well for both high and > low cardinality data, and is very efficient in terms of build time and > disk space. > > How much OMNIDEX can help depends on your needs and environment. If you > would be interested in a free performance analysis or more information, > please contact me. > > Cheryl Grandy > DISC > cgrandy@disc.com > 303 444-4000 > www.disc.com/home > > OMNIDEX - for the fastest applications ever!
As I replied to Art in personal email last week, I would like to apologize if I overstepped the bounds of good etiquette on posting. I am not intending to spam, only to promote a viable solution for people with problems that the OMNIDEX software can solve. I switched email from my "deja.com" one to my actual one at "disc.com" only to make contact with me more direct. Art indicated that a minimalist reply would be acceptable to relevant posts, and I will eliminate the advertisement info in any future posts. Any other feedback would also be appreciated as I am new to this. Again my apologies to the group. Cheryl Grandy cgrandy@disc.com 303 444-4000 www.disc.com/home In article <392C3F8E.B1F6712F@bloomberg.net>, kagel@bloomberg.net wrote: > OK Cheryl NOW you have graduated from an annoying promoter to a despicable > SPAMMER! Posting the exact same response to multiple postings using > a different email address each time indicates that you are even aware you > may have to deal with SPAM filters. Cut it out. If you want to make > helpful suggestions and when appropriate mention your companies product, > welcome. If you just want to SPAM, get lost. Mehdi from Querix made the > transition from SPAMMER to contributor, you can too. Otherwise be gone! > > Art S. Kagel > > cgrandy@disc.com wrote: > > > > Instead of creating a separate table or a view, another option to look > > into is the advanced indexing technology of OMNIDEX from DISC (Dynamic > > Information Systems Corporation). > > (Attention: Promotional information follows. Please disregard if not > > interested in another solution.) > > > > OMNIDEX uses specialized Multidimensional Keyword Indexes to deliver > > full text searches and unlimited multidimensional analysis by any number > > of criteria or columns. OMNIDEX layers on top of your existing > > database, and it supports numerous databases and document files, > > including Informix, Oracle, SQL Server, Sybase, flat files, HTML and > > Word documents. > > > > OMNIDEX is ideal for ad-hoc querying, it works well for both high and > > low cardinality data, and is very efficient in terms of build time and > > disk space. > > > > How much OMNIDEX can help depends on your needs and environment. If you > > would be interested in a free performance analysis or more information, > > please contact me. > > > > Cheryl Grandy > > DISC > > cgrandy@disc.com > > 303 444-4000 > > www.disc.com/home > > > > OMNIDEX - for the fastest applications ever! > -- Cheryl Grandy DISC Get OMNIDEX for the fastest applications ever Sent via Deja.com http://www.deja.com/ Before you buy.