February 18, 2010 at 1:50 pm
I am currently working with a database that another company designed. They have created many tables where many of the columns are not used. Also, they created table with keys, but did not create any relationships. Finally, many columns have names that are the same in different tables and when we ask about those tables we are told that those columns are not used. So, I am wondering if having tables with many null values create any impact on database performance? Also, are there any drawback in creating a bunch of table with keys and only the company knows what tables those keys relate too? This is an issue since I have to write reports using these tables and I do not have a schema that defines anything.
Any suggestions? If I could prove that their methods have an adverse impact on performance, then I might be able to push to get them to put more effort into a decent design. My fear is that they will continue along and things will only get worse.
Hope to hear something from you.
Charles, Developer
February 18, 2010 at 4:31 pm
you can create your own schema, and see everything that is in the database, the tables, the constraints, everything.
Since you posted this in SQL 2005, I am assuming that you are using SQL 2005.
Open SSMS, and then expand the database, select the "Database Diagram", right click it and select "New Database Diagram".
Your DBA should have told you this. And if you do not have one, you need one. Why would you have someone else design a database like that? The databases that I design have very few NULL values, sometimes you almost cannot get around it, I always try and use a default value.
As far as hurting performance, well. You will need a starting point, and then you can create some performance carts over time, and see what queries are hurting the performance, and what ones are running quickly. use profiler and the query execution plan to see what the query will do, and how much memory and processor time it will take.
If you are writing reports, you need to use stored procedures, create the sprocs and call them from the reporting side. I hope that you are using SQL Reporting Service, if not, you should be. It is free
Asking performance questions, there are so many variables in there, it cannot be easily answered. We will need a lot more information other than the few things about a couple of the tables to determine if the performance is bad. Things like, how many users are connecting at once, how many processors does the box have, what kind of drives are on the box? Are the databases located on a different box? how much memory is reserved for the database, and how much for the OS? How many rows are in a table that is suspect to being poor performing? Are there proper indexes on the correct columns? What was the database query doing this time last week? last month? last year? How many rows have been added since last week? Last month? etc......
We need a little more info to help you
Andrew SQLDBA
February 18, 2010 at 6:17 pm
charles-869346 (2/18/2010)
I am currently working with a database that another company designed. They have created many tables where many of the columns are not used. Also, they created table with keys, but did not create any relationships. Finally, many columns have names that are the same in different tables and when we ask about those tables we are told that those columns are not used. So, I am wondering if having tables with many null values create any impact on database performance? Also, are there any drawback in creating a bunch of table with keys and only the company knows what tables those keys relate too? This is an issue since I have to write reports using these tables and I do not have a schema that defines anything.Any suggestions? If I could prove that their methods have an adverse impact on performance, then I might be able to push to get them to put more effort into a decent design. My fear is that they will continue along and things will only get worse.
Hope to hear something from you.
Charles, Developer
Heh... getting a 3rd party to admit to a cruddy design is an uphill battle all the way. Null columns in tables is generally a bad idea unless you're planning for future expandability. NULL INT and other numeric columns and other fixed length character columns do take the same amount of space on each page as if they had data in them. That means that "reads" will need to read more pages than if the columns weren't there.
However, most of the time, such null columns won't provide as much as a performance hinderance than an insanely low or way too high FILLFACTOR (depending on the primary use of the table, of course) will cause. Even the performance gains there will still be absolutely trivial compared to the 2 worst performance problems of all... crap code and incorrect indexing. Yeah... getting them to change that would probably be the best thing and will be the most difficult thing to do. It's one of those lovely things about many 3rd party vendors.
I've had similar problems with 3rd party vendors. For example, one of our 3rd party providers wrote a dupe-check that was supposed to check for duplicates across 94 databases. The common table in each of those databases only had about 4 million rows each. It's not a huge task but their code took 24 hours to sometimes fail. They said they tried to make it faster many times but our company just had too much data. They left us no choice and I rewrote the process... got it down to 11 minutes with full logging and dupe row sequestering (to name a few of the requirements). We never used their "dupe check feature" ever again. We also didn't give them the code nor even let them know of our fix. 😉
I have to warn you... if you improve a 3rd party process by writing your own code or modifying theirs, you instantly violate any warranty or support agreement you may have. Sooooo.... unless you're willing to commit that violation and the 3rd party leaves you absolutely no choice, the best thing to do is to keep applying pressure to the 3rd party. If they refuse to cooperate, remind them that "word of mouth" complaints travel faster than a bad BBB report. If they continue to refuse to address your problem in a timely manner, perhaps it's time to rewrite all of the code they provide and send them packing.
Even then, you have to be careful... the CEO, CIO, or some other high-on-the-hog manager may be the one that recommended and brought in the 3rd party or there may be a little "family" involved... trying to get rid of the 3rd party may bring shame on them and may bring you to become more familiar with soup lines at the local Salvation Army. Be careful.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply