Most of the gains in performance derive from good database design, thorough query analysis, and appropriate indexing. The largest performance gains can be realized by establishing a good database design.
1. Update Table Statistics in database.
SYBASE SYNTAX:
update all statistics table_name
Adaptive Server’s cost-based optimizer uses statistics about the tables, indexes, and columns named in a query to estimate query costs. It chooses the access method that the optimizer determines has the least cost. But this cost estimate cannot be accurate if statistics are not accurate. Some statistics, such as the number of pages or rows in a table, are updated during query processing. Other statistics, such as the histograms on columns, are only updated when you run the update statistics command or when indexes are created.
If you are having problems with a query performing slowly, and seek help from Technical Support or a Sybase news group on the Internet, one of the first questions you are likely be asked is “Did you run update statistics?” You can use the optdiag command (IN SYBASE) to see the time update statistics was last run for each column on which statistics exist:
NOTE:
Running the update statistics commands requires system resources. Like other maintenance tasks, it should be scheduled at times when load on the server is light. In particular, update statistics requires table scans or leaf-level scans of indexes, may increase I/O contention, may use the CPU to perform sorts, and uses the data and procedure caches. Use of these resources can adversely affect queries running on the server if you run update statistics at times when usage is high. In addition, some update statistics commands require shared locks, which can block updates.
• Dropping an index does not drop the statistics for the index, since the optimizer can use column-level statistics to estimate costs, even when no index exists. If you want to remove the statistics after dropping an index, you must explicitly delete them with delete statistics.
• Truncating a table does not delete the column-level statistics in sysstatistics. In many cases, ables are truncated and the same data is reloaded. Since truncate table does not delete the olumn-level statistics, there is no need to run update statistics after the table is reloaded, if the data is the same. If you reload the table with data that has a different distribution of key values, ou need to run update statistics.
• You can drop and re-create indexes without affecting the index statistics, by specifying 0 for the number of steps in the with statistics clause to create index. This create index command does not affect the statistics in sysstatistics
(IN SYBASE):
Create index title_id_ix on titles (title_id) with statistics using 0 values
This allows you to re-create an index without overwriting statistics that have been edited with optdiag.
• If two users attempt to create an index on the same table, with the same columns, at the same time, one of the commands may fail due to an attempt to enter a duplicate key value in ysstatistics.
2. Create Indexes on KEY fields. Keep Index statistics up to date.
NOTE:
If data modification performance is poor, you may have too many indexes. While indexes favor “select operations”, they slow down “data modifications”.
ABOUT INDEXES
Indexes are the most important physical design element in improving database performance:
• Indexes help prevent table scans. Instead of reading hundreds of data pages, a few index pages and data pages can satisfy many queries.
• For some queries, data can be retrieved from a nonclustered index without ever accessing the data rows.
• Clustered indexes can randomize data inserts, avoiding insert “hot spots” on the last page of a table.
• Indexes can help avoid sorts, if the index order matches the order of columns in an order by clause.
In addition to their performance benefits, indexes can enforce the uniqueness of data.
Indexes are database objects that can be created for a table to speed direct access to specific data rows. Indexes store the values of the key(s) that were named when the index was created, and logical pointers to the data pages or to other index pages.
Adaptive Server (SYBASE) provides two types of indexes:
• Clustered indexes, where the table data is physically stored in the order of the keys on the index:
• For allpages-locked tables, rows are stored in key order on pages, and pages are linked in key order.
• For data-only-locked tables, indexes are used to direct the storage of data on rows and pages, but strict key ordering is not maintained.
• Nonclustered indexes, where the storage order of data in the table is not related to index keys.
You can create only one clustered index on a table because there is only one possible physical ordering of the data rows. You can create up to 249 nonclustered indexes per table. A table that has no clustered index is called a “heap”.
3. Drop and Re-create the Indexes that hurt performance.
Drop Indexes (In Pre-Session) before inserting data AND Re-Create Indexes (In Post-Session) after data is inserted.
NOTE:
With indexes, inserting data is slower.
Drop indexes that hurt performance. If an application performs data modifications during the day and generates reports at night, you may want to drop some indexes in the morning and re-create them at night. Drop indexes during periods when frequent updates occur and rebuild them before periods when frequent selects occur.
4. Also you can improve performance by
- Using transaction log thresholds to automate log dumps and “avoid running out of space”.
- Using thresholds for space monitoring in data segments.
- Using partitions to speed loading of data.
5. To tune the SQL Query
We can use “Parallel Hints” in the SELECT stmt of SQL Query. Also use the table with large no. of rows last when joining. In other sense, use the table with less no. of rows as a MASTER source. Also Queries that contain ORDER BY or GROUP BY clauses may benefit from creating an index on the ORDER BY or GROUP BY columns. Once you optimize the query, use the SQL override option to take full advantage of these modifications.
6. Registering Multiple Servers
Also performance can be increased by registering multiple servers which point to same repository.
