Re: Sybase 11 to 12 upgrade - SQR performance problems

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

Thanks Ian.

Our particular problem concerns a table with an indexed NUMERIC(10,0).  I can
declare the sqr variable type as DECIMAL(10) (which should be the same,) but
this still does not help.

If I embed:

CONVERT(NUMERIC(10,0), #gfcid)

into the SQL statement, this works in the particular case (although often a
CONVERT will also throw Sybase.)

It's impossible to know exactly the SQL that SQR sends to the Server.  I don't
completely trust -S, because I don't think what I got would have caused the
Server any trouble (but I cannot be sure.)  Curiously other queries with
indexed NUMERIC(14,0) columns do not suffer from thre problem.

If you or your colleagues have any specific experience of using sqr variables
in WHERE clauses with NUMERICs (or other ideas,) I'd be interested to hear.

I think we are going to need to be more careful with our embedded SQL with
Sybase 12, perhaps using CONVERTs, stored procedures, or other techniques.

Thanks for your answer,

Regards,

Abe

-----Original Message-----
From: Ian.Mills [mailto:Ian.Mills@ORIX.COM.AU]
Sent: 06 February 2002 23:36
To: SQR-USERS
Subject: Re: Sybase 11 to 12 upgrade - SQR performance problems


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