Re: Sybase 11 to 12 upgrade - SQR performance problems

[Ian Mills <Ian.Mills@ORIX.COM.AU>]

You are right, it is related to dynamic SQL and Sybase 12 being more
fussy about types.

The other team of SQR programmers exploited the features of SQR and
never defined their variables. This made the number variables (#var)
default to FLOAT and then they were using that in a dynamic SQL to
compare to an INTEGER. Sybase then saw a type missmatch and probably
then didn't make use of the indexes on the tables.

They resolves it be declaring all the necessary variables as INTEGER in
an include file and included it in every one of their reports within the
BEGIN-SETUP area of the reports.

Hope this helps,

Regards,
Ian Mills
Analyst/Programmer
ORIX Australia PTY LTD


>>> abe.crabtree@CITICORP.COM 06/02/2002 2:50:57 am >>>
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