Understanding the Performance Issue with Sybase ASE's COUNT(*) Query: Optimization Strategies for Better Performance on SuSE Linux

Understanding the Performance Issue with Sybase ASE’s COUNT(*) Query

=============================================

In this article, we’ll delve into the performance issue experienced by users of Sybase ASE 16.0 on SuSE Linux when running a simple SELECT COUNT(*) query against a large table with two indexes. We’ll explore possible causes and provide guidance on how to optimize the query.

Table Setup and Index Creation

The problem arises from a table named ig_bigstrings with approximately 18 million rows, which contains two indexes: ind_ig_bigstrings and ig_bigstrings_syb_id_col. The SELECT COUNT(*) FROM ig_bigstrings query takes an astonishing ~5 minutes to complete, while the same query executed on a PostgreSQL 13.1 server completes in just 15 seconds.

Query Plan Analysis

The query plan for the SELECT COUNT(*) FROM ig_bigstrings statement reveals that the optimizer has chosen to use Serial Mode, which is unusual given the presence of two indexes. The plan also shows that the index ind_ig_bigstrings is being used during the execution of the query.

1&gt; select count(*) from ig_bigstrings
2&gt; go

QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Serial Mode


    STEP 1
        The type of query is SELECT.

    2 operator(s) under root

       |ROOT:EMIT Operator (VA = 2)
       |
       |   |SCALAR AGGREGATE Operator (VA = 1)
       |   |  Evaluate Ungrouped COUNT AGGREGATE.
       |   |
       |   |   |SCAN Operator (VA = 0)
       |   |   |  FROM TABLE
       |   |   |  ig_bigstrings
       |   |   |  Index : ind_ig_bigstrings
       |   |   |  Forward Scan.
       |   |   |  Positioning at index start.
       |   |   |  Index contains all needed columns. Base table will not be read.
       |   |   |  Using I/O Size 4 Kbytes for index leaf pages.
       |   |   |  With LRU Buffer Replacement Strategy for index leaf pages.


             
 ----------- 
    18917916

Possible Causes and Solutions

Several possible causes can contribute to the performance issue experienced by users of Sybase ASE:

Fragmentation

Table or index fragmentation may result in low statistics, leading the query optimizer to choose a table scan over an index walk. To address this issue, update table and/or index statistics using update statistics statement or run reorg rebuild/drop index/create index.

-- Update statistics for the table
UPDATE STATISTICS ig_bigstrings;

-- Rebuild the index
REORG REBUILD INDEX ind_ig_bigstrings;

I/O Contention and Slow Disk Performance

I/O contention and slow disk performance can also cause performance issues. It’s essential to ensure that the system has sufficient resources and proper configuration for optimal disk performance.

-- Check the current data cache size
SELECT sp_cacheconfig('default data cache', 'size');

-- Increase or grow the data cache as needed
sp_cacheconfig 'default data cache', 'size' = 1024;  -- Example value

-- Configure a large I/O pool in the data cache
sp_poolconfig 'data cache', 'size' = 131072, 'page size' = 4096;

Forwarded Rows

Sybase ASE attempts to fit multiple rows into a single page when possible. However, if this fails, a forwarded row is created, which can significantly impact performance. To address this issue, run reorg forwarded rows.

-- Reorganize and remove unnecessary forwarded rows
REORG FORWARDED ROWS;

By addressing these potential causes and following the guidance provided in this article, users of Sybase ASE 16.0 on SuSE Linux can optimize their queries for better performance.

Conclusion

The performance issue experienced by users of Sybase ASE when running a simple SELECT COUNT(*) query against a large table with two indexes is a complex problem that requires attention to several factors. By understanding the possible causes and implementing the suggested solutions, users can significantly improve the performance of their queries.

Last modified on 2024-10-17