Why ASE 15 does not use the indice properly, may we the developer have an option "set optimizer off"

[Former Member]

I have a table with 2,5 million rows called security with unique indices on symbol and cusip. When I access a table using symbol as element of access if the value is passed then optimiozer uses the index and if it a varibale, it does not, is it normal, following examples are what i want to say:

declare @aa char(21)

select @aa=" VRVF                 "

select convert(varchar,cusip),

convert(varchar,desc1),

convert(varchar,rtrim(sec_class)),

convert(varchar,exchange),

convert(integer,round_lot),

convert(varchar,substring(isin,1,2))

from  security where  symbol = @aa

       |ROOT:EMIT Operator (VA = 2)

       |

       |   |RESTRICT Operator (VA = 1)(0)(0)(0)(0)(5)

       |   |

       |   |   |SCAN Operator (VA = 0)

       |   |   |  FROM TABLE

       |   |   |  security

       |   |   |  Table Scan.

       |   |   |  Forward Scan.

       |   |   |  Positioning at start of table.

       |   |   |  Using I/O Size 16 Kbytes for data pages.

       |   |   |  With MRU Buffer Replacement Strategy for data pages.

select convert(varchar,cusip),

convert(varchar,desc1),

convert(varchar,rtrim(sec_class)),

convert(varchar,exchange),

convert(integer,round_lot),

convert(varchar,substring(isin,1,2))

from  security where  symbol =" VRVF                 "

       |ROOT:EMIT Operator (VA = 2)

       |

       |   |RESTRICT Operator (VA = 1)(0)(0)(0)(0)(5)

       |   |

       |   |   |SCAN Operator (VA = 0)

       |   |   |  FROM TABLE

       |   |   |  security

       |   |   |  Index : UNX_smb_su_sym_sed

       |   |   |  Forward Scan.

       |   |   |  Positioning by key.

       |   |   |  Keys are:

       |   |   |    symbol ASC

       |   |   |  Using I/O Size 2 Kbytes for index leaf pages.

       |   |   |  With LRU Buffer Replacement Strategy for index leaf pages.

I have no logical explanation for my observation, as why the optimizer behave differently, why when i pass teh varibale (which is going to be the case in the store dprocedure), optimnizer does atabvle scan. Is it another bug of ASE 15. Our versionis:

Adaptive Server Enterprise/15.0.3/EBF 17779 ESD#4/P/Solaris AMD64/OS 5.10/ase1503/2768/64-bit/FBO/Thu Aug 26 11:42:38 2010.

Please help me understand if I am doing something wrong, if not why SAP Sybase can perhaps fix the bug.

[former_member188958 · Accepted solution]

It doesn't look like you are doing anything wrong.

Your ASE version is a bit old, though.  Have you tried this on a server

running a current rollup such as 15.7 SP60?

I think you would find the sp_opt_querystats procedure introduced in 15.7

very helpful in diagnosing issues like this.

may we the developer have an option "set optimizer  off"

That is essentially what you have if you provide a full abstract plan with the query, the optimizer is bypassed.  To get the results you want, it may be enough to just use forceindex syntax.  After the table name in the FROM clause, put "(index UNX_smb_su_sym_sed)"

[Former Member · Accepted solution]

Thanks Bret, You are always helpful and I must say excellent. Indeed I had also used the force index, but my argument is why one of the indices is properly used and the other is not. I do not normally want to use force index if possible, by forcing the indix, one tell ASE to put off the optimaizer please. By the weay, before running the query I have done a update index stat as well.