Well for those of you who do not know I spend a lot of time on linked in. I love the questions that come up and I think it’s a great way to get networked. Its sort of like SSWUG and the forums there. This question was posted by a Gentleman named Chris Lawson.
Are “people” issues the root cause of many database performance problems?
My experience in the field of Oracle performance tuning, has been that technical factors often have nothing to do with the root cause of performance problems. Instead, “people” issues are at the core of the problem.
I mean things like, “I didn’t know that was my responsibility,” or “We didn’t think we needed a performance test database,” or things like that. In other words, causes that are totally unrelated to technical causes.
I loved the question and all the answers so I thought that I would post it here on my blog and use it for a future article I will do on SSWUG in the next month or so. My answer was:
I agree with everyone else that people are the root cause. But I think we have to define what “people” are. Of course people are humans, but who are these people that create the problems? Let’s look at this from a different angle.
Someone had to order the hardware, but if you have a large table that is not indexed then the person that ordered the hardware is not the issue. It would be the person that is responsible for taking care of that table. Different at every organization.
If there was the same huge table and it was indexed and the index was not fragmented but should have been split on to a different drive or the table should have been partitioned then again it is a person. But this time maybe the DBA, and the hardware guy not talking to get the right combination.
But to the source question… Are these non-technical issues in many cases? I believe they partial are. SQL is an easy language to learn. Many developers will pick it up real easy. I know that I did and I am not a great developer by any means. But the point I am trying to make is that there is a lot more to writing a select statement then just figuring out what the syntax is. Things that should be asked as I am sure you know are:
- Where is the index
- How will this table grow
- Do we need an archive plan
- How should it be backed up
- Does the data change
- Is the data date relevant
I like examples and this is the best one I could thing of off the top of my head, I have a client that has a web page. On this page is where they get most of the business. Now on this page there are a number of search options. Let’s say City, State, Name, and Category (Making these up, hang in there with me). When the first did the web page all the SQL was embedded into the page. I mentioned that with all the options that people could select that it would be difficult to index for all the combinations and still get a fast search. They took my advise and put the code into a stored procedure. It sort of missed my point, the stored procedure would evaluate the passed options and do an If Else then run the Query… End result was that the where clause kept changed and it ended up killing the processors because of all the recompiles. I should have been more specific and said this is the way that we can make it faster and gave them an example. This was a human error on my part.