Designing without DBAs

One of my duties is to review applications, that my employer would like to buy, from a database technical perspective. This may involve reviewing and comparing a number of competitor products. The best product for the business can sometimes be the worst for technical quality.

I continue to find the many development companies do not have a database administrator. A quick glance at the database schema is usually enough to identify potentially difficult products.

Application developers will know the .NET, Java, or whatever application language the use inside out but they can make very poor design decisions on the database ‘because it is only data’ after all.

However, it becomes very embarrassing when one, as a customer, has to explain to a development company why their application performs so badly. It get worse when one has to advise them on how these should be corrected.

Most obvious reasons are
1) Large CHAR fields (instead of VARCHARs)
2) Fetching large result sets from the database and applying changes at the application level in a loop (an application driven cursor over the network)
3) No primary keys
4) No indexes on foreign keys
5) Too many indexes (usually after having had none first)
6) Failure to bind variables
7) Failure to use the Query Analyser to review execution plan of large queries or stored procedures.

SQL Server and Windows come with excellent tools for assisting a developer in optimising their applications (Query Analyser, Profiler, Performance Monitor and Enterprise Manager).

As an ex-developer myself, I am still embarrassed at the basic mistakes that I made when writing SQL to the database.

Advertisements
Previous Post
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: