Using Stored Procedures and Views
Hundreds of books have been written on how to use SQL and optimize it. Although I’ve been working with SQL for two decades, I’m still not an expert on it. But, I do know that if you have to use a complex command object for a report, you are most likely better off writing it as a stored procedure or creating a view. Not only are stored procedures faster, they have the benefit of letting you access the database using the full power of the SQL language. You can do such things as create multiple temporary tables, analyze data row by row, and almost write a small application within one stored procedure. Views are also more efficient than the command object, but they are a bit more limited than stored procedures. However, they do give you the ability to store the SQL for creating a complex join of many tables and saving it within the database server. Crystal Reports only needs to reference that view as if it were a table and it can easily pull data from it and your reports will run faster.
In addition to using stored procedures and views for performance improvements, another benefit is that all the database logic is consolidated in a central location on the server. If you create a stored procedure for your report, anyone writing reports can benefit from your work and use it in their reports as well (assuming they have proper access rights). You can create a database library of stored procedures/views that are specifically designed for reporting.
Depending upon your IT department’s policies, you might not be able to create and save stored procedures/views. Some database administrators are very strict about who is allowed access to the database and many won’t allow report designers to create additional objects on the server. You need to find out what you are allowed to do on the server and what procedures must be followed.
There are times when a stored procedure can adversely affect report performance. Ideally, the purpose of a stored procedure is to process one or more complex SQL statements on the server and let Crystal Reports only work with the final result set. You are pushing the work down to the server so that the report runs much faster. This is all fine and good as long as you are only reporting off of a single stored procedure. The problem arises when you are joining data from a stored procedure to another table or another stored procedure.
In the previous chapter we discussed how using different join types makes your report run more efficiently because only matching records are retrieved from the database. Unfortunately, SQL Server doesn’t let you use the JOIN keyword to link a stored procedure to another table. Because of this, Crystal Reports has to pull all the data into separate result sets and join them manually on the client. To illustrate how this works, Figure 11-6 shows the SQL statement that Crystal Reports uses to join the CustomerDetail stored procedure with the Orders table.
Figure 11-6. Selecting data from the Orders table and Customer Detail stored procedure.
To process this data, Crystal Reports has to make two separate requests to SQL Server. The first request pulls data from the CustomerDetail stored procedure and the second pulls data from the Orders table. Once it retrieves all the data from SQL Server and stores it in local memory, it manually processes each record to get the final result set. Clearly, this is extremely inefficient. If you are working with large data sets and one of the data sources is a stored procedure, this can seriously impact performance.
If you are joining stored procedures on the client and performance needs to be improved, it’s best to convert stored procedures to SQL views. SQL Server treats views the same as a standard table and lets you use them in JOIN statements. By using views, the work of processing all the data is performed by the SQL Server database and Crystal Reports only has to print the final result set. This can give you a huge performance boost depending upon how much data is involved. Unfortunately, a stored procedure that uses parameters can’t be converted to a view because views don’t support parameters. Also, stored procedures that use too much complex SQL can’t be converted to a view and you will have to leave them as is.
One problem I encounter when sharing common logic among reports is testing the reports and keeping track of which report is using which stored procedure/view. At some point, the client will ask for a major change to a report and this requires changing the underlying stored procedure that provides data to the report. In many cases, changing the stored procedure has the potential to break compatibility with the other reports using it. Depending upon how drastic the change is, this can require a lot of testing across multiple reports to ensure that they still work. Sometimes, I have to save my changes as a new stored procedure/view to keep the other reports functioning. But, the worst part is when a report is using a stored procedure and I’ve forgotten about it (and consequently didn’t test it). Just when I think everything is going great and I start to relax, I get a call about some arcane report that suddenly stopped working and I have to fix it immediately. This is why it is important to keep documentation on all stored procedures and views that your reports use.
