WHAT DO YOU THINK

Hi to every one: i have a question , i'm wotking in some test andi come with an idea of this structure of one table the schema is like this one table some_table( num_id int, col_001 int, col_002 int, * * * * * * * col_365 int, dt_day datetime year to day)extent bla next size bla i'm curius about the way this table it would be perform, and if the overall performance would be afected, against a table like this one table some_table2( num_id int, some_count int, another_count int, some_date date time year today)extent bla, the difference is that the second table could end up with 10'000,000 of records and the first one it would end up with 505,000 records, but what do you think of the first schemaagainst the use of the second , the first schem, a uses the julian date format like 1..366 , and what kind of implications the first schema has on performance __________________________________________________ Do You Yahoo!? Yahoo! Photos - Share your holiday photos online! http://photos.yahoo.com/

Jair Huerta wrote in message <92vrve$fo5$1@news.xmission.com>... > > Hi to every one: > > > i have a question , i'm wotking in some test andi >come with an idea of this structure of one table the >schema is like this one > > table some_table( > > num_id int, > col_001 int, > col_002 int, > * > * > * > * > * > * > * > col_365 int, > dt_day datetime year to day)extent bla next size >bla > > i'm curius about the way this table it would be >perform, and if the overall performance would be >afected, against a table like this one > > table some_table2( > num_id int, > some_count int, > another_count int, > some_date date time year today)extent bla, > > the difference is that the second table could end >up with 10'000,000 of records and the first one it >would end up with 505,000 records, but what do you >think of the first schemaagainst the use of the second >, the first schem, a uses the julian date format >like 1..366 , and what kind of implications the >first schema has on performance > What you are proposing in the first table is to store an "array" or a "repeating group" and that is considered a big no-no in classical database theory. The table is not even in 1st Normal Form as it stands. The problem is, it reduces your query flexibility quite considerably. However, when you consider performance, this is a relatively common trick used to increase performance, although quite often I rarely see any real analysis of the so-called performance gain expected. Space savings from the trick depend totally on whether all the elements will generally be used, or whether most of them will be "empty" in which case you are looking at quite a waste of space. If you are using a 9.XX or the 2000 series equivalent, you could use a list datatype: days list (col integer not null) these days, the object-relational theorists are accepting this kind of thing despite the apparent violation of traditional Normalisation theory. The reason is, the list can be treated as a "single data item" when you look at things with your object oriented glasses on, thus it's not a repeating group. And since it's a structure that seems to be what you need, no doubt it will give you the advantages of stronger programming facilities, although I don't see 4GL adding much in the way of assistance yet...

As always, this does depend some on the usage of the table. Some things to look at: Rows/Page for assuming a 2k page size. (2048-24)/(Rowsize+4) (I think this is right it might be off a little) I assumed DATETIME was 8 bytes because I didnt feel like looking it up, Rows/Page some_table = TRUNCATE(2020/(366*4 + 8 +4)) = 1 Rows/Page some_table2 = TRUNCATE(2020/(20+4) = 84 some_table is held in 505000 Pages some_table2 is held in 119048 Pages So if you were doing sequential scans, the i/o for some_table would be 5 times slower than for some_table2 I imagine the cache rate is likely to be higher for some_table2, because it is smaller, and has more rows on a page. But the really important thing to know is what is the difference in usage of these tables. If you are joining with some_other_table, and the join to some_table retrieves 1 row per row in some_other_table, but the join to some_table2 needs to join to 365 rows per row in some_other_table. Then some_table might have a performance benefit. If with either schema you only retrieve one row, I can't imagine some_table being faster, but benchmarks are the only way to know for sure. Hope this helps, Will P.S. I say go with some_table2 almost no matter what. There might be a case where I might go with some_table, but I have yet to see it. In article <92vrve$fo5$1@news.xmission.com>, Jair Huerta <huertajair@yahoo.com> wrote: > > Hi to every one: > > i have a question , i'm wotking in some test andi > come with an idea of this structure of one table the > schema is like this one > > table some_table( > > num_id int, > col_001 int, > col_002 int, > * > * > * > * > * > * > * > col_365 int, > dt_day datetime year to day)extent bla next size > bla > > i'm curius about the way this table it would be > perform, and if the overall performance would be > afected, against a table like this one > > table some_table2( > num_id int, > some_count int, > another_count int, > some_date date time year today)extent bla, > > the difference is that the second table could end > up with 10'000,000 of records and the first one it > would end up with 505,000 records, but what do you > think of the first schemaagainst the use of the second > , the first schem, a uses the julian date format > like 1..366 , and what kind of implications the > first schema has on performance > > __________________________________________________ > Do You Yahoo!? > Yahoo! Photos - Share your holiday photos online! > http://photos.yahoo.com/ > Sent via Deja.com http://www.deja.com/