Created On:  18 November 2010

Problem:

I migrated my mainframe DB/2 application to Microsoft SQL Server, why does the application run so slow?

Resolution:

A mainframe application is not optimally designed for a distributed environment and making some adjustments with SQL code and architecture will help improve the performance of the application.  Below are some recommended ways to accomplish this. 

·         Create Indexes

o   Decide which queries are heavily used and create the appropriate index.

o   Use as few columns as possible in the index, and don’t over-index the tables.

o   The uniqueness data within the index will affects its performance. When possible, implement unique indexes.

o   Clustered Indexes

§  Must be unique, only one index per table, it will stores the actual data rows at the leaf level of the index, sorts in either ascending or descending order and is much faster than when using a non-clustered index.

o   NonClustered Indexes

§  Contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves, cannot be sorted and more than one index can be created per table or view.

·         Limit the SQL statements within the code

o   For example, if you only require data from 2 columns do not use SELECT *, which will retrieve all column data.

·         Use Stored Procedures

o   Transact-SQL should be encapsulated within a Stored Procedure because the code is already compiled consequently reducing network traffic.

·         Create Table Joins

o   Should be used if you have multiple tables that are frequently used together.

o   The columns should have unique values to prevent table scans.

o   An index should also be created for the columns used for the joins.

·         Configure Cursors Properly

o    Minimize cursor usage as much as possible, using SELECT INTO construct if applicable. Result set processing is always expensive, so make sure it is absolutely necessary before using.

o   Make your result sets as small as possible!  This can be done via the WHERE clause in your SQL SELECT statement.

o   Make sure your read-only cursors become  SQL Server “firehose” cursors – see OpenESQL BEHAVIOR directive documentation for more information.

·         Consult and use Microsoft “Performance Tools”

o   Database Engine Tuning Advisor for performance suggestions.

o   SQL Server Profile for tracing and debugging performance issues.

**For additional information on SQL Performance, please see www.microsoft.com