To run SQL Server databases successfully, you must be keen on query design. Unfortunately, most people do not give the design process a second thought. As a result, they make simple mistakes that, though easy to avoid, have far-reaching consequences.
For starters, with poorly written queries, you cannot guarantee users lightning-fast retrieval times. Your servers will also be plagued by problems from day one. And in today’s digital world, these are mistakes you cannot afford to make. But, how do you avoid making these mistakes? Here are tips on how to go about it.
1. Failing to review your data model
Your data model determines how users access data. So, think your model through right from the beginning. If you do not, you will have to deal with unwieldy queries and complicated code down the line, and both impact negatively on performance. An easy way to figure out which queries are needed to access data is to print out your data model.
Or, better still, have a data modeling tool do it for you. A print-out or modeling tool lets you see what you are up against. You are, therefore, in a better position to simplify the code, increase coding time, increase accuracy, and improve performance.
2. Failing to consider your technique
What technique do you use? Is it cursor logic, or set-based logic? There is no easy answer to this particular question: it all depends on the performance that best suits your needs. Take set-based logic, for instance. It is the obvious choice for database access. After all, an SQL Server is designed for it. But, cursor logic can in some instances outperform based logic. The key is not to use one technique when the other would be better.
3. Not using old coding techniques
When you use tried-and-tested coding techniques, you seldom land in trouble. Even coding methods you learned from SQL Server 2005 can prove useful today. Try to use the TRY…CATCH error handling technique in your coding. The results may surprise you. Using Common Table Expressions for hierarchies, or the Common Language Runtime (CLR) database engine may also leave you surprised.
If you need help brushing up on old techniques, do some revision and look for some articles online. There are plenty out there. Here and here are a couple of SQL examples.
4. Not taking advantage of peer review
Before deploying your query plans, you should have someone else review it. Chances are that other people will see what you have missed. Their reviews on your indexes and query performance often help you to further improve your code. They could also learn a thing or two from you in the process, and vice-a-versa.
5. Failing to test your queries
Developers hate having to test code. First, it’s rigorous. And second, the testing environment (hardware and data) rarely match the real production environment. But testing is a necessary, and unavoidable, part of coding. So, thoroughly test your code, and where possible, try to mimic the final production environment as closely as possible. Remember, your queries might perform well with a few hundred records, but not against millions in the final environment.
Conclusion
Queries determine the speed and performance of an SQL database. So, try to avoid common mistakes such as not reviewing your data model, or failing to consider which technique to use. Others are failing to use old coding techniques, not taking advantage of peer review mechanisms, and failing to test your queries.
