Any kind of software typically has to interface with a database. Often times software developers develop the best possible N Tier system and lose focus of the database design.Some developers write RAW SQL with little or no importance to the query.
There are N number of ways I have seen database queries and optimizations used. I remember two years back in one of the projects, all of a sudden the system slowed down drastically.
At that time we were using dbcp, an Apache Commons project that did DB pooling. After many hours of fire fighting, the slowness was simply due a property that was accidentally turned back to false in the configuration files.
Often times in learning new Buzz words SOA, INDIGO, Hibernate, Inversion of control, Spring Framework and so on, basics are forgotten. This list is a quick reminder of tips that apply to any relational database that everyone seems to know but may fail to follow
Normalize database tables. Unless the database is meant strictly for reporting purposes, it is almost always beneficial to model a database to the third or fourth normal form. Databases from SQL server to MySQL are very efficient. Use them as they are supposed to be in relational design.
Do not put business rules in the database. Database is strictly for storing data. It is not a rule engine. Try not to put any rules that are business specific or any complex logic in stored procedures.
Use value or look up tables in a database. Look up tables are meant to be small and specific to store data that can be looked up quickly. They also fit very well with the normalization rules. There is absolutely nothing wrong in having lookup tables that are similar in structure.
Use stored procedures for CRUD only.
- This is a yes or no situation. In some companies stored procedures are a big NO. . Particularly in Java shops. In others there is no debate on using stored procedures. In my opinion stored procedures nicely encapsulate the database schema from users.
- Stored procedures are a way to protect your database.Use simple stored procedures. Do not put any business logic in stored procedures.
- Do not write UI Specific stored procedure. Pretty soon you will have a maintenance nightmare with developers desperately hunting for the stored proc they want and most often adding a new one even though there may be one that does exactly what they want.
- A simple way to arrange stored procedures is to have for every table three stored procedures, GET
- Define a specific user that can access only the stored procedures and let that user be used by the application.
- o Stored procedures if used correctly provide security from SQL injection attacks.
Do not use stored procedures to load many levels of data. This is a common problem even when loading a object tree. How deep do you load. Example when you load a Employee, do you load the employee details, Employee Work Items and so on. One way to do this would be to write one stored procedure that returns three record sets. One for Employee, One for Employee Details and One for Employee Work Items.
This model seems to work in the beginning but pretty soon you will have a need to only load the Employee and the Employee Items. Now you will have to write another stored procedure to do this.
Instead look at patterns for lazy loading mechanism in your code. Frameworks like Hibernate for both Java and .NET provide such functionality nicely out of the box.
Trust your Database Team, if you have one. In many places, this role is performed by developers who do not have database design experience. OO centric database designers tend to make relational database object centric, inexperienced leads end up making relational databases flat. Having an experienced DB designer and DB developer is key.
Use GUID as primary keys with caution. In many cases simple numerical primary keys will suffice and are better optimized by the SQL runtime.
Do not fill values in tables using triggers . This is adding some kind of business logic in the database layers. Triggers slow down the database response time. Stay away from Triggers if you can.
Use indexes with caution . Efficient index design is key to performance. Most database systems provide an index tuning mechanism. I am sure that DB2 and SQL Server does. They give tips on using Indexes well. Often times indexing on wrong columns may end up slowing the system.
Most important – Trust the database vendors to do their job well. Spend time learning specifics of the database. There is a lot more beyond TSQL that is specific to the database. Work with your DBA or learn more to understand the inner workings of the database being used.
Be Different... 