Who Is Behind The Wheel? | SQLRockstar

Who are the people driving the design work in your shop? Here, all database design work is done by the application developers. In most cases, they have little or no experience with database design. Words like normalization, foreign keys, and indexes are concepts that they have never been introduced to in their line of work. That is not their fault. They did not show up to interview for their job and tell their future boss that they had such skills (and, for some, if they did do that, they should be ashamed for lying).

Having been a former developer I can remember more than one training class on building applications with some underlying technology. It did not matter what the platform, the point of the class was to show you how to build the application using their tools. It was not to spend time on building a proper database design. I can count on one hand the number of times I was in a class for application development and we spent time on database design…zero. If you want to learn those things, then you need to spend the time and money to take a different class. Besides, let the DBA figure out how to make things run better, right?

I have nothing against job security. I enjoy the fact that there is enough design work at my present company to keep a team of a hundred database administrators busy for the next ten years. I just hope my bosses see the value in keeping us around, even as we continue to sink under the volume of work that continues to pile up. But what I would like to see, and I imagine other database administrators around the world would like to see, are some changes be made to the application design structure currently being offered in classroom training.

First up, stop using ‘select *’ as an example for any query. As a matter of fact, go out of your way to spend two minutes explaining why ‘select *’ is one of the worst things you can send to the optimizer in MS SQL Server. This statement will force a full table scan, regardless of any indexes available, which can be a performance killer. It also makes for sloppy issues upstream and/or downstream if the underlying table structure changes. Too often I have seen system choke because columns suddenly appear (or disappear) overnight. A seemingly innocent change for one turns into a nightmare for others.

Second, explain to people that queries performed against the database server do not time out, they will run forever. If a person is working in an application (particularly a web application) and they get back a ‘query timeout’ issue, the query timed out due to a setting in IIS, and not the database server. In my opinion, no query should take more than three seconds and I understand that some need to take longer. I know that I do not want to be staring at a web page for a long period of time wondering if anything is happening, and three seconds is about the limit for most people’s patience. So, if you have written a query that takes longer than say twenty seconds, pick up the phone and call a DBA and ask for help tuning your query. Trust me, we live for stuff like that. And we are more than happy to teach you how to tune your own, which is a skill you can master over time.

Third, learn some basic security. Understand the difference between Windows Authentication and a SQL Server login. Understand the different database roles and fixed server roles, and when each should be used. Stop relying on the use of elevated permissions to do your development work, or start using different credentials when unit testing your application. Not having a basic understanding of these concepts leads to poor overall design as well as frustration when basic questions cannot be answered. For example, if you tell me that you are not able to login, and I ask “how are you trying to connect”, you should be able to give me an answer other than “I double-click to run the application from my desktop”. If you either built the application, or are responsible for its upkeep, then be prepared to explain how the application connects to the database and why it needs to be a member of any particular database role.

Lastly, do not allow people to teach application design classes unless they have an understanding of the three ideas listed above. Even if they tell their students to “consult a DBA before proceeding with your design” it would be a wonderful step. And in a pinch, if they do not know the answer, they can do exactly what we always do and lead with “it depends”.

“It depends, but check with your DBA before going so far that you do not want to come back.”

Would that be so hard to repeat a few times a day?

sheeri

One more tip — learn to test queries. So often I’ve seen query problems that are actually connection problems. The solution? “go into the database and run your query manually. If it works, in 99% of the times the problem is not the query.”

It wastes my time when someone has come to me saying “why doesn’t this query work?” (or worse, “the query you designed for me doesn’t work!”) and I connect to the db (as the application user) and can run the query just fine.

(the other 1% are permissions errors, flaky networking, and yes, the occasional programming error)
cjh

Select * causes a table scan? In what universe? Sure it’s bad, because it returns an indeterminate number of columns, but no way does it affect the way the rows are fetched, other than to make them full-width.
Ryan

Crazy DBA, I think you might want to clarify your comment on “SELECT *” resulting in a full table scan. It’s not the “SELECT *” (or rather, the ommission of a field list) that results in a full table scan. Rather, its the lack of a “WHERE” clause the will result in this behavior. The “SELECT *” will negate your ability to use covering indexes and will always require a bookmark lookup when using a NC index. Not to mention, you will be passing more data than is required back to your client interface.
Hilarion

You’ve got a point in this. Programmers having almost none or really none knowledge on DB modeling and SQL are a real pain, but…
As cjh and ryan wrote I do not see how using “SELECT *” in query like “SELECT * FROM somewhere WHERE something = some_value ORDER BY something_else” would cause full table scan. Yes, it causes many problems.
The other thing is that you are focusing on MS SQL Server here, but the problem applies also in case of other DBMS engines with slightly different issues to be discussed. Regarding this I think the DB classes should mostly cover general DB modeling and SQL and engine specific things should be covered by separate classes.
ewoo

Oh jeez. Another tight-assed DBA. Why don’t you just kindly educate your app dev folks instead of ranting on your blog?

BTW, there’s no evil in “Select *” per se–it’s the lack of “WHERE” clause that’ll cause the full table scan. I guess you need to brush up on your own db knowledge.
Crazy DBA

Thanks for the help ewoo. This post is a year old, and I thought I had corrected that mistake. You, and the others, are correct.

But you already knew that.

Sorry for the slip. Going forward I will try to be more careful.