1

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).

STEP 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.

Thank you for your help.

user2891742
  • 11
  • 3
  • I would start by taking a look at the query plan to see if the index is being used. http://infocenter.sybase.com/archive/index.jsp?topic=/com.sybase.dc00743_1500/html/qp_abstrpln/CACHHDHB.htm – Mike Gardner Oct 16 '15 at 01:02
  • Michael, thank you for your response. I ran the query plan and it looks like the index IS being used (see below), even though the CPU goes through the roof when I execute the query. What else can I try? – user2891742 Oct 16 '15 at 16:35
  • STEP 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. – user2891742 Oct 16 '15 at 16:41
  • Michael, do you have any more insights? Thank you. – user2891742 Oct 19 '15 at 14:39

1 Answers1

0

If they have a massive data skew of null data in the column you can look at sp_modifystats to tell the optimizer to ignore the data skew (i.e. the mass of NULLs).

Info here:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1572/html/sprocs/CHDGHEGE.htm

Not to be used without significant testing and bear in mind you need to re-modify after each run of update statistics on the table.

Rich Campbell
  • 566
  • 2
  • 9