Ah, the life of the database consultant is truly magical, always dealing with the best and brightest of implementations!
...and now we return to Planet Earth :)
Here's some information about a production SQL Anywhere 8 database; can you see anything, um, unusual about these numbers?
Hint: It has nothing to do with the number of rows; four million is not a big number.
7 comments:
1 K page? Are they serious? Also, 226 - 201 = 25 tables have no primary key.
...and?
(keep going, one more :)
2 options:
1) Is it that there are "0 materialized views" indicating that a version 10 server is returning the stats, since that is where SQL Anywhere introduced support for materialized views, but the page size is 1k, no longer supported by version 10?
2) There are 4 million plus rows in 226 tables with NO foreign keys between them, ensuring that queries will run slow?
Chrisk: as Breck said, it is a V8 server. And no foreign keys is what I've missed (I don't believe in 226 independent tables).
stuffitrans and chrisk are both right (at least, about what *I* think is unusual :)... zero foreign keys among 200+ tables is never a good thing.
About the zero materialized views: It's a flaw in the tool (Foxhound) used to display the counts, it doesn't suppress the count of materialized views for a version of SQL Anywhere that doesn't support them (v8). Another way to look at it: if the count was *other* than zero, that would really be unusual :)
No foreign keys makes me think this is a copy of a MySQL database, or the work of someone who learned database design using MySQL.
Richard B: That's a excellent conclusion (no foreign keys implies possible MySQL heritage), but in fact this database is even stranger than that: It has a *lot* of referential integrity (foreign key) triggers originally generated by ERWin. So, from an integrity point of view it's not too bad... but from a performance point of view it has the *expense* of referential integrity checking without the *benefit* of the associated indexes that SQL Anywhere automatically generates when you create *declarative* foreign key relationships. Not to mention the fact the only place the relationships are explicitly documented is back in the ancient ERWin diagram which (AFAIK) only exists in printed form today. An ERWin trigger is just another steenking hidden trigger unless you know to go and look at it.
Post a Comment