Izenda Customer Portal

Home Search

Welcome Guest ( Login | Register )

How can I get faster reporting performance?

How can I get faster reporting performance?

The best way to improve the performance of the reporting system is through indexing. Indexing provides a quick way to find the exact data item you want.

In this example we are using the Database Engine Tuning Advisor for SQL Server 2005 & 2008. The Database Engine Tuning Advisor helps you select and create an optimal set of indexes, indexed views, and partitions without requiring an expert understanding of the structure of the database or the internals of Microsoft SQL Server. It also analyzes a workload and the physical implementation of one or more databases.

First you will need to create a Workload file:

1. While in the Report Designer, select the SQL button in the toolbar to display the report SQL you wish to index with the advisor.

2. Save the file to a folder as a sql file (ex. Report.sql).

Second Open Microsoft SQL Server Management Studio

1. Once in the Management Studio, select Tools, then select Database Engine Tuning Advisor

2. Select the binoculars next to the Workload box and locate the report SQL file that you saved earlier.

3. Select from the drop down the "Database for workload analysis".

4. Check the boxes of the databases and tables to tune.

5. Click "Start Analysis" on top tool bar.

6. Once analysis is complete it will display the Estimated Improvement percentage and Index Recommendations.

7. Select one of the Definitions in blue to display the recommended index query.

8. Select "New Query" in the toolbar and Run each query to maximize the performance of the report.

Note:
SQL Server 2000 users would utilize the Index Tuning Wizard.
Oracle users can utilize SQL Tuning Sets.

Tags:

Performance, Speed

Details

Type: FAQ

Options

Email Article
Print Article

Print Article
Bookmark Article

Bookmark Article
Export As PDF

Export As PDF