Editor’s note: This blog post comes from David Magar, Program Manager, Windows Azure SQL Reporting.

Although it’s tempting to take an existing Reporting Services project and push it out to the cloud as-is, you really shouldn’t. A report that runs great on a local server might not be quite as performant after you deploy it on a SQL Reporting report server.

Fortunately, there are 3 simple modifications that almost always result in a faster running report. This blog post explains each one in detail.

Best Practice #1: Reconfigure the ReportViewer Control

If you are using the ReportViewer Control (RVC) in an ASP.Net page or Windows forms applications, you need to make the following configuration changes:

1. Disable the default proxy in the initialization of your application by calling: WebRequest.DefaultWebProxy = null
2. Set all your Windows Azure SQL Reporting report parameters at once by calling SetParameters and not SetParameter. Setting parameters results in calls to the Windows Azure SQL Reporting data tier, located in Windows Azure SQL Database. Reducing the readwrite cycles by issuing one call instead of several can help a great deal.
3. Configure your application’s RVC to use cookies for authentication instead of making log-on calls. This will force your users or application to login once and then return a cookie later on for faster rendering. Keep in mind that the Report Server only allows for cookies that were created within the last 60 minutes so you must take this fact into account when designing your application. You can read how to achieve cookie authentication here.

Best Practice #2: Co-locate Web applications and databases in the same data center.

The ReportViewer control communicates frequently with the report server. You can’t eliminate this behavior, but you can minimize its cost by deploying your Windows Azure application and report server in the same data center.

The same considerations apply when choosing where to deploy your Windows Azure SQL database. Each query sent to a SQL Database comes with a certain amount of overhead. Authentication, authorization, handling requests, etc. are all actions that contribute to delays between the initial connection and when the page renders. Putting the database in the same data center as the other application components reduces the amount of time these actions take, saving significant rendering time and yielding better performance.

You can detect the location of your database, application, and report server as well as understand exactly how much time it takes to bring in the data for each report rendering, by reading this blog post by David Bahat from our team.

Best Practice #3: Write efficient queries

When authoring the report, set the query to bring only the data required by a report visualization (in particular, avoid “Select *” type of SQL Statements when designing a query). This best practice ensures your reports are optimized for the fastest rendering possible.

In conclusion, I hope these 3 suggestions will help resolve some of the performance issues you might be having with applications that work with SQL Reporting. I look forward to your feedback and comments below.

Sincerely,
David Magar
Program Manager, Windows Azure SQL Reporting