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:
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:
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!