Oddly enough, trying to improve performance technically falls into the ‘optional’ category of SQL Server writing and administration.  The truth is, the database server will operate just fine without ever having to pay attention to things like query optimization and index tuning.  If everyone is happy with how things are going and nobody is complaining that reports are taking too long, then consider yourself lucky that you probably don’t have to do anything in this area.

However, most analysts and developers at some point will have to deal with the grumbling or complaints about the long waits for something to run.  Some of our hard-core Meditech users may be reluctant to start adopting the DR anyway and making them wait hours for their new reports certainly doesn’t help. 

An unofficial comment here:  Starting down the path of tackling perfect performance on a database this size can literally be a full time job to keep up with and do correctly.  Since most of us probably don’t have that luxury, I’d rather spend our time looking at the basic principles and some good general practice theories.  Just knowing a few tips and general good advice will get you 80% of the way there.  If you do feel drawn to learn more, there are several books and tutorials available that can explain this art form in much greater detail.  And yes, it is somewhat of an art form.

 One of the most exciting things about writing new queries or reports is just getting the darn thing to run.  We usually don’t care how or why it works, just that the results are right.  I’ll be the first to admit that after spending several hours on a complex query, I’m very guilty of just stopping there and not caring if it’s good, bad, right, wrong, or thinking about a better way to do it.

When dealing with small databases or smaller tables, worrying about performance usually isn’t an issue.  Modern hardware and upgrades in the server engine handle most of the easy decisions.  However when we start dealing with larger tables combined with more complex queries, hits on performance start to add up quickly and we need to pay much closer attention to what we’re doing. 

Top tips for optimizing your SQL SELECT statements:

1. Avoid using SELECT * in your queries.  Only retrieve the fields you need!  This seems obvious, but so many people just bring in everything because they’re too lazy to type the fields.  (I know this is a pain, but it is also good practice to fully qualify your fields by prefixing each of them with the source table name.  Some reporting tools require this but it can help with debugging too.)
2. Select only the rows needed.  Don’t pass a bunch of data around your network if you don’t need to.  If you can reduce your results on the statement itself, this is MUCH better than implementing the groups and filters in the reporting tool!
3. Try to sequence your WHERE criteria from most restrictive to least restrictive.  It does make a difference!
4. Especially when using dates or numbers, try to use the BETWEEN syntax rather than: Field>[value] AND Field<[value]
5. It’s tempting to use the Include IN() statement to get a list of values from somewhere.   Instead, try to use an INNER JOIN against that sub query to limit the results.
6. Query larger tables only once if you can.  Try not to join the same table twice if you can provide the right criteria to avoid the engine from scanning the same table twice. 
7. Understand the different types of joins and how to use them!  Try to visualize what’s happening when you join tables together.  Don’t always assume that everything is a LEFT OUTER all the time.  Using the correct joins can not only save you performance wise, but also avoid problems with incorrect results.  Be mindful of joining on multiple fields too!  Working with multiple join fields is almost always the culprit when you start getting duplicate results.
8. Avoid using SUBSTRING() when LIKE() will work.
9. Don’t use ORDER BY unless you really need it.  That adds some overhead that may not be necessary.  Especially when the default sort is already what you are looking for.
10. Experiment with writing your statements in different ways.  We don’t always have time to do this, but it’s a great way to gain experience and learn what works and what doesn’t. 

Make use of the Query tools in the SQL Management Studio.  If you don’t see the icon, you can find it under Query -> ’Include Actual Execution Plan’.  When activated, this creates a new tab on your output window that shows a diagram of how the server actually processes your query.   Experiment with different kinds of queries and see what happens to the execution plan.  We’ll make use of this when we talk about indexes.

 For those of you who are savvy enough to create and use Stored Procedures:

1. Use stored procedures with parameters when possible to retrieve data.  Since they are pre-compiled on the server, they are by nature more efficient and help to centralize your logic.  For support reasons, it’s usually much easier to tweak a stored procedure than to edit, recompile, and distribute a report.
2. Avoid looping and cursors whenever possible.  It’s very easy for people with a programming background want to loop through all the conditions and values they need.  Think of a way to minimize this and get what you need in one shot.
3. Experiment and learn how to use #temp tables to store sub results instead of one giant query.  This can greatly simplify the logic in the procedure but in some cases, it’s not always better. 
4. Learn about table functions!  VERY handy and are known to have good performance.

There are dozens of other tips, but take some time to research what applies to you and your needs.  Little things can add up in the long term so developing good habits early on is always a good idea.  Keep in mind that we don’t have the luxury of changing the primary keys on the DR tables so DON’T MESS WITH THEM!    

In the next part, we’ll start to take a look at indexes and the important role they play in our database.

Check out Howard's new DR/SQL group on MEDITECH Connect!