Re: Data Modeling Problem

Viswanath Aiyah <Viswanath_Aiyah@engr.usask.ca> wrote: > In your experience, how much difference does it make in terms >of performance, if the data types of fields used in joins are characters, >as opposed to numbers? If we added serial/integer fields to be the >primary/foreign keys respectively in this application, would that be worth >a 4 day effort to implement the change? As for the possibility of >propagating primary key (tag) changes, we could easily write an update >trigger/stored procedure to do it. We would like to discourage our users >to change tags, but real-world experience tells us that it does happen on >occasion. We realize that integer comparisons are faster than >character ones, but have no quantitative measure for the difference. > In terms of performance, 4-byte (or 8-byte) serial numbers can improve the performance very much if the cardinality of the tables are real big enough. Assuming you will have B-tree indices on the primary key, the number of index pages with serial number might be (almost) up to 8 times less than with the character-based primary key (32/4 bytes). Considering the disk page size and the cardinality of the tables, you can compute the exact number of index pages for both serial and character based primary keys (somewhere in tutorial Informix all these nice formulas are given). In any case, your savings can be approximately as many as "10ms x (8-1) x # index pages for serial key". If the index pages supposed to be 100 (ie you have approximately 50000 tuples in your table with disk page size being 4K), then you can gain 7 seconds. This analysis is in terms of I/O. Then there is CPU costs for both. But it is too little to put it here. Whether it is a big gain or not to be considered by you. -Mehmet ---- Mehmet Bayraktar mehmet@cs.umd.edu Graduate Student 1112 Dunoon Rd. Computer Science Dept. Silver Spring, MD 20903 University of Maryland Phone: (301) 434-9290 College Park, MD 20742