Unanswered: Poor query performance on an indexed string field when all values are NULL (SYBASE)
I have a database table 'TABLE_A' with a nullable string field 'field_x' having non-clustered index on it. A query
SELECT * FROM TABLE_A WHERE field_x = 'some value'
executes quickly in most environments. In one client's environment all values of this field are NULL and this query runs very slowly.
Question #1: Is there a way in SYBASE to optimize for this specific scenario? It seems like something that SYBASE should already handle.
Question #2: Is there a way to optimize this query? Filtering out NULLS (as in
SELECT * FROM TABLE_A WHERE field_x IS NOT NULL
) does NOT help with the performance.
I gathered performance statistics by looking at Plan Viewer inside Interactive SQL. When there is some data in this table for field_x then Logical I/O, Physical I/O, and CPU are very close to zero. When all values are NULL then I/O and CPU values are similar to scanning the whole table.
The query execution plan shows that the index is being used:
QUERY PLAN FOR STATEMENT 1 (at line 1).
The type of query is SELECT.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
| |SCAN Operator (VA = 0)
| | FROM TABLE
| | TABLE_A
| | Index : INDEX_X
| | Forward Scan.
| | Positioning by key.
| | Keys are:
| | field_x ASC
| | Using I/O Size 16 Kbytes for index leaf pages.
| | With LRU Buffer Replacement Strategy for index leaf pages.
| | Using I/O Size 16 Kbytes for data pages.
| | With MRU Buffer Replacement Strategy for data pages.
Also, I'm not sure if this problem is reproducible for other databases, but I observed it on SYBASE ASE 15.