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 |
|
lauramayer
Posting Yak Master
152 Posts |
 Posted - 2005-03-31 : 07:13:46
|
| Good morning,This is mainly a design question. We have a VB developer who decided to write an app using SQL server. No, I wasn't consulted, this was before they even had a DBA, and yes it's in production. He had no idea about good database design and when I was asked to look at it, response times were so slow the app was dying, I noticed quite a few things:1. there are 60 tables 36 of which have no primary keys, indexes or relationships to any other table.2. there are 2 "islands" of joins. Meaning there is one group of 7 tables with realtionships, joins and indexes. And another group of 4 tables with realtionships, joins and indexes but just to themselves. They relate in no way to rest of the database.3. the rest of the tables (10) have clustered primary indexes with a fill factor of 90% these tables delete and insert data all the time. There are no relationships on any of these tables.He insists that this has nothing to do with the response time. He says it's a network issue, not his design. When I did a trace I saw he uses temp tables A LOT. Like for most of his queries, when he's not creating temp tables, he doing table scans on the other tables.What is the best way to explain that database design is not something extra and nice but an essential part of the application?Any thoughts or suggestions would be appreciated.ThanksLaura |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-03-31 : 07:46:20
|
| "He says it's a network issue, not his design"....prove him (or his manager) wrong in a small area of contention.....and then show him the course literature for "DB performance fundamentals 101"...and tell him to get his ass in gear.Or get him to prove "it's a network issue"....ie reverse the burden of proof.Or tell him to read SQLTeam.com....under the search topic "poor performance"Good Luck!!! |
 |
|
|
lauramayer
Posting Yak Master
152 Posts |
Posted - 2005-03-31 : 07:53:36
|
| Thanks Andrew,This is my second experience here with VB developers trying to be DBA's and then being told to clean up the mess. It's getting old let me tell you. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-31 : 07:55:36
|
| I feel your pain...I you just need a way to PROVE that the database/query/SP designs are causing the slowness rather than the network, just capture some of the slow calls in Profiler and run the statements in query analyzer and compare the duration difference with the performance in the application. If you calmly present your opinion and back it up with hard evidence what can choice will they have? Alternative solutions are also always nice things to bring to the table.Be One with the OptimizerTG |
|
|
|
lauramayer
Posting Yak Master
152 Posts |
Posted - 2005-03-31 : 08:28:32
|
| I tried that but almost every one of his queries uses either a temp table or a cursor. When you're looking at design that bad or a complete lack of understanding of SQL where do you start to explain? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-31 : 08:40:08
|
| I'm a fan of the "cursors are tweezers" analogy, repeated/explained here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47319Best thing to do is pick a couple of procedures you know you can rewrite quickly, then show them the improvement. Rewrite 5 or 6, for example, and show only the 3 that show the greatest improvement...unless they are all improved by the same amount.And I'm also confronting a lot of legacy applications written exactly the same way as yours, so I definitely sympathize. Best advice I can give is take it one step at a time.Also, temp tables are not bad at all if they're used correctly. They probably suck in his code because none of them are indexed. Put an index on a few of them and you may get a rather pleasant surprise for very little work. |
|
|
|
|
|
|
|
|
Database application slow response time