Sybase 11 to 12 upgrade - SQR performance problems

[Abe Crabtree <abe.crabtree@CITICORP.COM>]

Some of our reports run slower on Sybase v12.  I believe it is because Sybase
12 is more fussy about when it will use an index.  If the left hand and right
hand side of a WHERE are different types (INT/SMALLINT, or different size of
NUMERIC,) then an index may not be used.

I think the problem is arising from dynamic SQL in our reports, where the Right
Hand Side is an SQR number.  E.g.:

Begin-select
g.gfcid                         &gfcid
g.business_name                 &business_name
.
.
from  std_cust_clusters c, std_gfcids g
where c.customer_id = #customer_id
and   c.gfcid       = g.gfcid
and   c.gfcid      != c.customer_id

End-select


I know that if I change this to:

where c.customer_id = CONVERT(NUMERIC(10,0),#customer_id)

the query goes back to full speed.

I am unclear about what is going on within SQR though.  I know that
#customer_id is defaulting to FLOAT, but it does not help to declare it as any
size of DECIMAL.  If I declare it as INTEGER, it does help - however that is
unsatisfactory, because it is too small a type.

Evidently sqr is translating its own types to SQL inaccurately in some way, so
that the Server is getting a query that cannot use an index (Sybase 12 is more
pernickety about datatypes when deciding whether it can use and index.)


Does anyone have any experience of any Sybase 11 to 12 upgrade issues where
they affect SQR reports, either the one I am trying to find, or any other?

Can you add anything to my explanation, or do you have any other workrounds?


Thanks and regards,

Abe Crabtree