SQL Server query design: 10 mistakes to avoid
Jeremy Kadlec of Edgewood Solutions
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in November 2006
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…
With SQL Server 2005 comes a whole new set of opportunities for your
queries. So doing things the old way may still work, but it may be time to consider the latest
options. The
TRY…CATCH error-handling method is one of the first techniques you should
embrace in your code. Additional considerations are
common table
expressions for working with hierarchies; and a final consideration is to extend the
capabilities of the relational database engine:
common
language runtime (CLR). These three technologies are significantly changing how you can work
with SQL Server, and they're just the tip of the iceberg.
- 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.
Top 5 SQL Server query
tips
Home: Introduction
Tip 1: SQL Server query design: 10 mistakes to avoid
Tip 2: Troubleshoot
SQL Server queries and improve I/O
Tip 3: Tracking
query execution with SQL Server 2005 Profiler
Tip 4: Find
and fix resource-intensive SQL Server queries
Tip 5: Running
analytical queries with Analysis Services
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
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation