SQL can get complicated. You are simple. That's just the way it is.
You like issuing commands like...
select * from orders
...when all you need is a few columns. But the SQL Server will
dutifully fetch all the data you requested, no matter the cost to the
performance of the server, the network, and your fellow database users.
The above query might not be too bad, but when you see what an order
looks like...
...you figure out you need to join in some additional data to make a
meaningful report.
so you join to the customer table, and your query becomes:
select o.*, b.billto_firstname+' '+b.billto_lastname as CustName from orders o
join buyerdata b on b.OrderID = o.OrderID
...giving us a concatenated name...
Ok, time goes on and you add joins to categorize the orders by state,
product type, salesman, etc. etc. etc. until your SQL gets into a state
we like to call gnarly. Another thing that happens is that your
SQL is written to make life easier for you, not for the database
server. Soon, your query gives you the perfect data, and your
report/web site/whatever looks amazing - after minutes of crunching to
gather the data.
This article is written to help you find and correct some common things
that make queries easy for us, but difficult for your database server.
This process in general, is called query optimization and
companies pay big bucks for people who are experts at this. Imagine a
business with 800 users entering data on their desktops. Some are on
the phone with vendors or clients, and system performance is mission
critical. Suddenly you launch off the un-optimized query for your
weekly TPS report, and the entire database starts chugging like The Little Engine That Could.
This means hundreds of your business clients all start hearing words like "Geez, hang on - this screen is
loading...". You can see the business need here.
Even if your business is small, why tax your systems needlessly if you
can take a few steps to write better SQL that gives you the data you
need faster? I promise - if your small business grows big, that query
that generates a report out of your 1MB database in 3 seconds now will
probably take 30 seconds when you reach 10MB and 5 minutes at 100MB.
Tip 1: Never use Select *
Select * is great for when you just want to get a feel for what data is
in a table. But it's the least efficient thing you can do in a
production query. Think about it. First, the parser has to access the
data dictionary and fetch the names and types of all the columns in the
table (or multiple, joined tables). Then your '*' is replaced with all
the names of the columns - which (by the way) are not guaranteed to be
in any particular order under ANSI SQL specs. If you are writing a
query for production use, one that will potentially execute thousands of
times a day, take the time to tell it which columns to return.
Tip 2: Indexes
Indexes are designed to help queries. If you're summarizing sales by
agent, then adding a secondary index to the agent column in your sales
table will save you a lot of time. Technically the time is being spent,
but it's being used in tiny slices whenever you change data in the
sales table. That triggers the maintenance of the index, which can then
be searched much faster than the whole table.
Tip 3: Case Insensitive Searches
Consider:
Select fname, lname, email from Customers where lastname='McHenry'
This query might work, unless you're looking for all people who entered
any of these: McHenry, mchenry, Mchenry, MCHENRY, McHENRY. ok - so you
want to just find the customers with a case insensitive search. So you
do this:
Select fname, lname, email from Customers where lowercase(lastname)=lowercase(@searchname)
What happens now? The query is executed not against the field "lastname"
(and any indexes applied to it), it is instead executed against the
function lowercase(), which has no index. There are two
common workable solutions to this issue. Neither of the solutions is
to make sure all data is entered using strict capitalization
standards. No one wants to be the Capitalization Nazi!
- Use a function index. Oracle supports these. you can actually make
an index on "lowercase(field)" or "getmonth(datefield)". This is not a
widespread feature, but is really useful in these cases. - Add a lower or upper column in your data to use for joining and
searching. If you have an ASP.NET membership provider database, note
that the aspnet_users table has a column named LoweredUserName.
If this approach is good enough for Microsoft, it's good enough for me.
Tip 4: Data Warehousing
Sometimes you just *must* have that report that performs a 23 table join
against a 40 million record database. There's just no way you can put
out your TPS reports without it. What many companies do is perform
certain data mining and harvesting tasks once per day, rolling up and
summarizing certain data into a "data warehouse" for offline reporting
needs. This data is often de-normalized to make faster queries and it's
usually stored on a different database server and even a different
network to prevent reporting from slowing down the workday.
The idea is that you create a query that performs certain common joins
and relations... rolls up hourly/daily/monthly or whatever kind of
totals, creates various types of crosstabs, and stores the rolled up
data in a reporting database.
These summaries are run during off hours (or infrequently during the
workday) to prevent the database servers from clogging with work while
you're open for business.
Managers and their lackeys can hammer away at the reporting database all
day long and your main operation could not care less.
Tip 5: Joins vs. Sub-Selects
Sometimes when you just want a single lookup from a table (say decoding a
County ID to a name) , it's faster to not join in the county table.
Some SQL servers can detect this kind of lookup and perform this
optimization for you. Note...
select U.UserName, RR.RoleName from aspnet_Users U
join aspnet_UsersInRoles R on R.UserId=U.UserId
join aspnet_Roles RR on RR.RoleId=R.RoleId
We have joined the aspnet_UsersInRoles table to get the ROLEID of the
user's roles, and joined the aspnet_Roles table to get the name of the
roles for the user.
Note the difference here:
select U.UserName,
(select RR.RoleName from aspnet_Roles RR where RR.RoleId=R.RoleId) as Role
from aspnet_Users U
join aspnet_UsersInRoles R on R.UserId=U.UserId
See how we're using the sub-select to pick up one field? The thing
about this tip is that sometimes the former way is faster and sometimes
the latter. You have to experiment.
Tip 6: Explain Plan/Query Optimizers
Ok, most SQL Servers have a module called a Query Optimizer. This
module can take your weak, human SQL and tweak it before execution to
improve performance. But there is also usually a UI that you can use to
performance tune your queries. I know Oracle has this, and have used
it many times. It breaks your query up into chunks called a query
plan, and assigns an estimated cost to each part of the
plan. You can then alter the text of your query to improve the overall
performance.
Tip 7: Query Hints
On the rare occasions when your SQL is tricking the optimizer into
taking the wrong approach to your query, Oracle and SQL Server have the
ability for you to add query hints to your SQL. These hints can
tell the optimizer to use a specific kind of cursor, or prefer a
different index, and alter the query plan used during execution.
The syntax differs greatly between the various SQL servers
(Oracle/MSSQL/etc.), and will be shown only as an example here.
Tip 8: Views
Sometimes creating a view can help optimize your query. The idea is to
optimize the SQL for certain common transactions and save it as a view,
making the need to reinvent the wheel significantly lower. But there is
also such a thing as a materialized view. This is a type of view
that is maintained like an index, as the live data is stored in the
main tables. A normal view does not store it's own data, but a
materialized view does. The view's data can then be queried without
having to do whatever complex joins were needed to generate the data.
Of course the trade-off here is storage space.
