| Author |
Topic |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-01-28 : 12:20:15
|
I'll wait for Lumbago to spill his heart out...
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-28 : 13:04:52
|
| I had an app a few years back that was getting constant deadlocks in dev testing (constant as in 60-100 a minute). I had a look at the DB and found that the app's main table (used as a queue table for a message processing function) was terribly indexed. There were 22 indexes, every single one had the same leading column (a column that was never filtered on). 15 had the same second column, etc. The one index was 12 columns wide. Every single query did an index scan. I made a copy of the database, dropped all indexes off that table, did some analysis of the app and created 5 new indexes. The app was then tested against this database. 0 deadlocks and the message throughput increased by an order of magnitude.We called our rep and 'demanded' a conference call with technical people (we were their biggest customer at the time). During the meeting I tried to explain why the current index strategy was inadequate and why there were deadlocks. The lead database developer then uttered this piece of brilliance. "Locks have nothing to do with deadlocks", as well as telling me that I didn't know what I was talking about.They reluctantly allowed us to make the changes to the DB (it was that or we dropped them a a vendor), proving we provided them a copy of the index changes, so they could analyse them for potential problems.2 months later the vendor released a service pack with a note that their developers had done some performance tuning and that customers should see a slight improvement in message processing. I checked the change script. Only thing in there were index changes. My index changes in fact, naming still intact just with all my comments stripped out.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 13:11:41
|
"The Cheque is in the post"?  |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-01-28 : 15:18:17
|
He really told you "you don't know what you are talking about", or that was implied?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-28 : 15:38:28
|
| He stated it out loud, during a conference call, after I tried to explain why locks did have something to do with deadlocks. On my side it was me, another developer and the manager in charge of the app (not my manager). I don't know who was on their end other than our rep and the developer.--Gail ShawSQL Server MVP |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-01-28 : 15:54:09
|
It's a strange world... The irony is maybe that you now are a MVP, but the "lead database developer" probably is CTO or similar within the vendor.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-01-29 : 03:52:00
|
| Holy smokes Michael...I was hoping not ever having to think about his again!! Let's just say that I was hired to admin this 130ish GB CRM database that had more than 1000 tables and not a single clustered index. Me: "What the he** is this, there are no clustered indexes?? We are having massive performance problems here!"Vendor: "Umh...clustered what? The performance problems are due to fragmentation. Run a defrag moron."Me: "Grrrrrrrrrrrrrrrrrrrr"Did I mention that the database was entirely based on the EAV-model and that *everything* was done using cursors?- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-29 : 04:40:29
|
"[i]130ish GB CRM database that had more than 1000 tables"One table per client? I can't begin to imagine how CRM needs 1000 tables ... but Heck! if it inflates the sticker-price maybe I should consider it in my Database Architecture 101 for new recruits  |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-01-29 : 05:22:40
|
| It was a very large java-based "shelf-ware" system for the telecom business and half of the tables were for auditing purposes so the 1000 tables could be justified. No clustered indexes however... *shiver* A hundred or so tables were what they referred to as sequence tables. No tables had identity properties on the primary key column, so to simulate the functionality they added sequence tables that had one column and one row that held the current ID if the last record in the "real" table... (i.e. customers, customers_log and customers_seq). So for each insert into customers you would have to run a procedure that locked the customers_seq table, updated it by adding 1 to whatever number was there before and returning it so that it could be inserted into customers as a new row. Pretty smooth stuff...- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-01-29 : 06:30:38
|
| Try an large international software company (being a subsidiary or ex-subsidiary of "not-Microsoft") which sell a "universal banking system". They have pride in having a "....database that has more than 1000 tables" which ignores the rules of "normalisation" and also "indexing for performance gains". As well as a UI which looks to have been designed by 20 disconnected summer students all on acid and a table/column naming strategy that seems to have been inspired by "throw-a-dart at the dictionary"....for each and every table. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-01-29 : 10:35:03
|
| Ah, cursors!I was asked to look at poor performance of the database server for a system that collected time clock information. The CPU ran at 100% all the time, and the vendor suggested that there was something wrong with our hardware.When I looked into if, I found that each time clock had an application instance that fed information into the database. The technique the application used was to run a server side cursor to scan through the table to see if a particular time entry was already in the database, and insert if it wasn’t. The only selection criteria for the cursor was time clock ID, so every row for a time clock was being returned to the client one row at a time for each row to be inserted. With over a million rows in the database for each clock, this was a slow process. Performance monitor showed that the server was processing about 33,000 batch requests/second to service 16 time clocks; I was absolutely shocked by this number, because I had never seen a system processing more that 2000 batch requests/second.When I made various suggestions to improve the performance, the vendor said that all the problems were fixed in the newest version, and that we should buy the upgrade for $30,000. Since the system had only been in service for 6 months, management was not keen on that idea.CODO ERGO SUM |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2010-01-29 : 11:14:46
|
| ah yes i've seen this being done in a well known ERP system too. 20k rows and the IO fully choked. best of all the solution was to upgrade the ERP.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.7 out! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-01-21 : 12:53:37
|
| Had a vendor tell me this today when we reported that their application was getting deadlocks:"It's not normal for SQL to have deadlocks. They are caused by performance of the SQL server, so you should look into a hardware upgrade."When I suggested implementing READ_COMMITTED_SNAPSHOT they said this:"READ_COMMITTED_SNAPSHOT being turned on is not something we have tested.... We believe that if it would have helped, we would have configured the DB that way."When I asked how they would have known if it helped if they never tested it: silenceCODO ERGO SUM |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-01-21 : 13:58:09
|
quote:
Originally posted by KristenI can't begin to imagine how CRM needs 1000 tables
HAHAHAHAHAHAHAHA!Ever look at a SAP database schema? Neither have I (I wouldn't be surprised if their license agreements forbid it...getting information out of them is impossible) Anyway, a contractor told me the most basic, minimal install of SAP has 10,000+ tables, and the most common config can reach upwards of 30,000 tables in a single DB. Granted it's an ERP package, not really CRM, but it does have some modules for it. And if you order the SAP BI package... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-01-21 : 14:06:31
|
quote:
Originally posted by robvolk
quote:
Originally posted by KristenI can't begin to imagine how CRM needs 1000 tables
HAHAHAHAHAHAHAHA!Ever look at a SAP database schema? Neither have I (I wouldn't be surprised if their license agreements forbid it...getting information out of them is impossible) Anyway, a contractor told me the most basic, minimal install of SAP has 10,000+ tables, and the most common config can reach upwards of 30,000 tables in a single DB. Granted it's an ERP package, not really CRM, but it does have some modules for it. And if you order the SAP BI package...
I've never looked at an SAP database, but I saw 15,000 tables and about 8,000 views in a Peoplesoft database.All objects and columns had names that were 19 characters or less to support some RDBMS that I never heard of, so everything was a bit terse.I think all those systems are designed to provide employment for armies of consultants. I've never met one who understood more than a small portion of the application.CODO ERGO SUM |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-01-24 : 07:20:57
|
quote:
Originally posted by robvolk
quote:
Originally posted by KristenI can't begin to imagine how CRM needs 1000 tables
HAHAHAHAHAHAHAHA!Ever look at a SAP database schema? Neither have I (I wouldn't be surprised if their license agreements forbid it...getting information out of them is impossible) Anyway, a contractor told me the most basic, minimal install of SAP has 10,000+ tables, and the most common config can reach upwards of 30,000 tables in a single DB. Granted it's an ERP package, not really CRM, but it does have some modules for it. And if you order the SAP BI package...
Yeah, had to write an import routine for data from a buy out company to import into SAP, that was fun*.. That was only importing into a few of the add on modules tables and it still took about 3 months to write..*by fun, I mean a complete and utter pain in the rear.. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2011-01-26 : 01:19:25
|
quote:
Originally posted by Lumbago
130ish GB CRM database that had more than 1000 tables and not a single clustered index. Did I mention that the database was entirely based on the EAV-model and that *everything* was done using cursors?
I thought with EAV you only needed 1 table. isn't that the point of it? a 1000 table EAV would be quite "flexible"
elsasoft.org |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-01-26 : 09:46:14
|
quote:
Originally posted by jezemine
quote:
Originally posted by Lumbago
130ish GB CRM database that had more than 1000 tables and not a single clustered index. Did I mention that the database was entirely based on the EAV-model and that *everything* was done using cursors?
I thought with EAV you only needed 1 table. isn't that the point of it? a 1000 table EAV would be quite "flexible"
elsasoft.org
Sounds like a perfect storm of all possible bad database design options.All the data is in an EAV table, but you have no idea which one. Maybe the same entity is distributed over multiple EAV tables. Maybe the same data is duplicated over multiple EAV tables. Maybe the data isn’t there at all. The possibilities are shocking to contemplate.CODO ERGO SUM |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2011-01-26 : 10:16:51
|
another CRM on a similar scale to SAP is greatplains. I had a customer that told me it had 1200 tables and 16000 procs. wtf?
elsasoft.org |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-26 : 10:55:33
|
| GP doesn't have anywhere close to that number of objects. They must've done some heavy customization.JDE does though. And with such meaningful table names like F106006. |
|
|
|
Next Page
|
That Darn Vendor