Windows Azure SQL Reporting best practices for development

Editor's note: This blog post comes from the Windows Azure SQL Reporting project manager David Magar.

Although people tend to adopt an existing Reporting Services project and place it on the cloud, you really shouldn't. Reports that are running well on the local server are deployed to the SQL Reporting reporting server may not have the same performance on the local server.

Luckily, 3 simple modifications can produce faster running reports. This blog post will detail each of these changes.

Best practice #: Reconfiguring the ReportViewer control

If you use the ReportViewer Control (RVC) in a asp.net page or in a Windows Forms application, you need to change the following configuration:

1. Disable the default proxy server in your application initialization by calling: Webrequest.defaultwebproxy = null;

2. Use cookies for authentication by configuring the application's RVC without logging calls. This will force your user or application to log in once and return a cookie later for faster rendering. Keep in mind that the server only allows cookies created within 60 minutes, so when designing your application, you must apply this cookie to your account.

3. Call setparameters and not Setparameter, and set all Windows Azure SQL report parameters. Setting parameters causes calls to the Windows Azure SQL Reporting data tier in Windows Azure SQL database. By making a call instead of several, there is a lot of help in reducing the read write cycle.

Best practices #2: co-locate WEB applications and databases in the same data center.

ReportViewer controls and report servers communicate frequently. This behavior is unavoidable, but you can minimize costs by deploying your Windows Azure applications and report servers in the same datacenter.

The same considerations apply when you choose where to deploy your Windows Azure SQL database. Every query sent to SQL database has a certain amount of system cost. Authentication, authorization, processing requests during page rendering, and so on, all of these actions help delay between initial connections. Put the database in the same data center, take this behavior other applications reduce time, save rendering time and produce better performance.

By reading blog posts from our team David Bahat, you can detect the location of your databases, applications, and report servers, and learn exactly how much time it takes to introduce data for each report presentation.

Best practices #3: Writing Efficient queries

When authoring a report, the setting can only bring the data needed to report visualization (especially when designing a query, avoid the "Select *" SQL declaration type). This best practice ensures the fastest presentation of your reports.

Finally, I want these 3 suggestions to help address some of the performance issues associated with applications and SQL reporting.