Analyzing SQL Using the Microsoft SQL Profiler
There are two distinct stages when a report runs: generating the data and generating the report. Unfortunately, it isn’t very clear about how much time is being spent on each stage. If you have a report that needs to be optimized, how do you know if you should optimize the data access part or the report generation part? You don’t. It’s a bit of guesswork and experimentation to try to get the best performance out of a report. But, if you are using Microsoft SQL Server, you can use the SQL Profiler to give you a more in-depth look at what is happening on the database side. The SQL Profiler shows you every data request made on the server and it details how much time each process took. This information gives you the ability to determine exactly how much time is spent processing data and how much time is spent generating the report.
For example, earlier in the chapter I mentioned that stored procedures are treated differently from tables and are called separately. Consequently, I recommended converting stored procedures to views when possible. If you are curious how I knew that, it’s because of the SQL Profiler. I had a report where I created multiple stored procedures because I ‘knew’ that this was best for performance. But, when I used the SQL Profiler to analyze the performance on the server, I saw that Crystal Reports was generating multiple calls to the database for the report and was therefore joining the result sets on the client side. Upon further experimentation and analysis I decided to change the stored procedures to views to see what would happen. Sure enough, the database calls were reduced and the report ran faster. I would never have known this just by using the Show SQL Query functionality in Crystal Reports.
As another example, a while back I overheard my client complaining that another programmer’s reports were taking almost a full minute to run and they were only one line reports. At the end of the month, they had to run dozens of these reports and most of their day was wasted. I asked him about this and he said it was due to using the UNION clause to join multiple archive tables to the current data. This sounded reasonable, but I wanted to check it out for myself. I decided to use the SQL Profiler to do some research.
I found out that the unions were hammering the database and the performance was horrible. Upon examination of the actual SQL statements, I also saw that none of the filtering was being done on the server. Thus, after waiting for the database to process all the records, Crystal Reports then had to do its own filtering. Clearly, there were opportunities for optimization there. After some experimentation, I moved the WHERE clause to each SELECT statement prior to joining the tables with a UNION clause. My testing in the SQL Profiler showed that this reduced the amount of data the server had to join together and it greatly reduced the number of records passed back to Crystal Reports. After we put the changes into production, the report ran almost instantaneously and the client couldn’t have been happier.
To use the SQL Profiler, you can find it on the Windows Start menu. It is located within the SQL Server group and is simply called ‘Profiler’. It’s a pretty easy tool to get started with and not have to read any documentation. Start a new trace by selecting the menu options File > New Trace. This prompts you to login to the database. After logging in, click on the Events tab and browse through the myriad of events available for tracing. If it’s your first time using the browser, just click the Run button to accept the default events and start the trace. You can always go back and create more detailed traces later.
If there is any activity on the database server it will immediately appear in the trace window. By watching the trace, you can see every database call made to the server and who requested it. As you run your own report, you can see which calls are made to the database and see how long it takes to process each one. This gives you detailed metrics about how well your report optimized its SQL request. An example of the SQL Profiler is shown in Figure 11-7.
Figure 11-7. SQL Profiler running a trace.
You can see in this figure that the trace shows you every SQL statement that has been executed as well as who made the request and how long it took.
If you find that the profiler gives you too much information, when you start it you can deselect which fields and events to show. For example, you can have it only show the final SQL statements processed and ignore all the events leading up to processing the SQL statement. You can also have it filter just on your user name or application name so that you only see events that have to do with your testing.
Overall, the SQL Profiler is a very powerful tool that can be used by advanced users for digging into the details of what is happening behind the scenes of a database call. It helps you make an informed decision about which aspect of your report needs optimizing.
