Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
 Posted - 2010-04-22 : 05:30:59
|
| I present a horror story of (luckily caught before) catastrophic proportionsThe scene (Familiar I'm sure to some of you):A Period of rapid development -- the next version of our software pushed hard. A desired feature unavailable in our current version. A management meeting....... and a decision.Cue hack......So. Ended up in production with lots of 1 - 1 tables, the only constraint a foreign key between then (no primary keys, no indices) the foreign keys are all IDENTITY columns......So the consistency of this feature is maintained only by the fact that when you insert into 1 table you *should* insert also into the child tables. The system relied on the fact that the same identity seed would be given to each new row in each table.A period of use...... Many thousands of rows each month.A performance problem!With no indices on the child tables and a push to use the spiffy new feature the database server grinds to a halt -- the reason, endless table scans.So profiler is run. the hibernate sql is sniffed and a quick check of the tables is done (by me). Horrified shock!No time to change now. Indexes added to the foreign key columns. Table scans removed and seeks added. The system runs again (for now)A chat with the engineering manager...... A lengthy explanation.... some arguing..... Time devoted to change this.....The hibernate mappings are examined. The engineer responsible for the design is taken for a little chat.....We redesign the hibernate mappings and the schema. Using symmetric primary keys now.I had to change the schema. Dropping and recreating the tables involved. Copying vital live production data. Table now working sans identity.....Testing now.....Hoping no rogue inserts happen in the meantime.This is the most surprising thing I've ever seen in production! I'm actually glad we had the performance problem!Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
dportas
Yak Posting Veteran
53 Posts |
Posted - 2010-04-22 : 16:36:42
|
| I don't see the relevance of your subject line because the problem was apparently the fault of a poor database designer - nothing specific to Hibernate.One other point. You say you have foreign key constraints but no keys or indexes? But that's impossible in SQL Server! You can't create a foreign key without a unique index on the table it references. Or did you just discover some bizarre new bug? :) |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-23 : 04:36:11
|
quote:
Originally posted by dportas
I don't see the relevance of your subject line because the problem was apparently the fault of a poor database designer - nothing specific to Hibernate.One other point. You say you have foreign key constraints but no keys or indexes? But that's impossible in SQL Server! You can't create a foreign key without a unique index on the table it references. Or did you just discover some bizarre new bug? :)
Yes you are right -- I've amened the heading.re keys: I meant that the children tables had only a foreign key and no primary key. no index of any kind.In context there was 1 parent table (with a proper primary key)And 9 children tables, all with a 1-1 mapping and only a foreign key on the identity column......The query that caused the perf problam selected info from all of them (so requiring 9 table scans)Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-04-23 : 09:00:41
|
do we happen to be a customer of yours?because I recognise the symptons in the same bought-in software we're after spending 10-15m on  |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-04-23 : 10:12:04
|
quote:
Originally posted by Transact Charlie
So the consistency of this feature is maintained only by the fact that when you insert into 1 table you *should* insert also into the child tables. The system relied on the fact that the same identity seed would be given to each new row in each table.
Wow. Just wow.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
|
|
|
|
|
How not to design relationships!