 |
|
| SQL Server Tips: |
|
|
|
DATABASE MANAGEMENT AND ADMINISTRATION
SQL Server query design: 10 mistakes to avoid
By Jeremy Kadlec of Edgewood Solutions
11.13.2006
Rating: -3.97- (out of 5)
|
With the substantial and sustained data growth of SQL Server databases, coupled with the sub-second response times expected by users, it is critical to avoid poorly written queries. In this tip we will outline 10 common query design mistakes to avoid. Take a look to make sure you are not falling victim to these mistakes and consider the recommendations as a means of correcting your queries.
Top 10 list
- Data model and subsequent queries
Not thinking about how to access the data as you are building the data model can result in unwieldy queries. You may have unnecessary JOINs that overcomplicate the code and hurt performance.
To correct this problem, think about the queries needed to access the data. If the query is not clear at this stage of the process, then it will be even more difficult to code. Odds are that the database design may be overcomplicated and can be simplified to improve query performance.
On a related note, if you are a visual person, be sure to print out the data model or review the online model in your data modeling tool of choice. This should improve your coding time and accuracy.
- What's the best technique?
This is the notorious cursor- versus set-based logic debate. Conventional wisdom says to use set-based logic for all database access. In general, I would agree that is the best rule of thumb. Using cursors when set-based logic is the right choice can also impose significant performance penalties. SQL Server is designed for set-based logic and should be used in most processing.
On the other side of the coin is this Cursor example. In this situation, cursor logic outperformed set based logic. The takeaway from this information would be to determine the type of processing you are performing and select the technique that best suits the need.
- Doing it the old way…
- Would you take a gander over here?
Reviewing your code and scheduling a peer review is a must-have before code is deployed. Reviewing your code and specifically the query plans is critical to ensure that proper indexes are being used and that the query will perform as expected.
On a related note, one of the simplest means to ensure the code is accurate is to have another set of eyes review it. This can be a learning experience for both the developer and his or her peer to see how other developers and DBAs approach problems. It's also a means to trade ideas to improve each other's skills.
- It's the classic mistake.
Issuing a SELECT * statement, thinking that the table will never change, is a classic query design mistake. Even in the simplest situations, inevitably the table changes and that leaves you to review the code to make sure the additional column is not included. Or, worse yet, you must wait for the application to break and then fix those issues. The best practice is to only include columns that are needed in your queries and change them as necessary. Don't disrupt your day to search through code in firefighting mode.
- I have no comments!
Unfortunately, most code that I see has few or no comments. So making a change is a daunting task even for the developer and/or DBA who originally developed the application. Commenting your code is truly a quick and painless process, and it is critical for future developers to understand and change the code in a safe and timely manner.
- Sure, I will test it…
Few developers and DBAs enjoy simple testing, nor do they enjoy rigorous testing prior to releasing the code to the production environment. Furthermore, development environments typically are not to the scale of the production environment in terms of the hardware and volume of data. That said, simple queries will perform well with a couple hundred or even a few thousand records, but in production that is not the case. There is no better preparation for your queries than testing against millions of records in fragmented tables in the test environment to make sure the queries will perform as expected.
- Let me have it, I mean it!
Issuing SELECT statements without a WHERE clause and expecting the middle tier or front end to process the data in a more efficient manner than SQL Server is a tough sell. SQL Server is designed for query processing and does so very efficiently. Moving large sets of data is only going to bog down systems and networks to a point where they are flooded. Be sure to filter your data set as much as possible to avoid the performance implications.
- I would like a query with a view, please.
Views fulfill the need to simplify coding for complex queries. They are used often to help power users query the database. Unfortunately, too much of a good thing can severely impact performance. The view is simply a SELECT statement and the view's SELECT statement must be issued each time your SELECT statement is issued. Limit the use of views and prevent them from querying other views. Or, build a stored procedure to query the data and pass in the needed parameters to fulfill application or user needs.
- No, it's not my code…
We all make mistakes, and the last system we worked on would benefit from the knowledge we are gaining on our current project. So, record the items you have learned and share them with your team for the organization to benefit. When you have an opportunity, go back to those previous systems and improve on them with the knowledge you have gained since that project.
Conclusion
If you are making these mistakes or others with your queries, recognize the mistake and make the effort to correct it. That may be easier said than done, but correcting the issue will reap benefits for the organization and for the application's reputation. As a takeaway from this tip, begin to build a personal coding guide to use for your current and future projects.
For more help, check out our FAQ: SQL Server query errors and explanations.
ABOUT THE AUTHOR:
Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He delivers frequent presentations at regional SQL Server users groups and nationally at SQL PASS. He is the author of the Rational Guide to IT Project Management. Kadlec is the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
Copyright 2006 TechTarget
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
