Hi,

I think I just got a jconnect BUG. I am doing a simple query on a
Sybase Server on a table (AstAddress) having 1,545,282 rows. I have
build an index on the primary key . (I have try clustered and
non-clustered with the same result)

CREATE UNIQUE INDEX CLUSTERED PK_AstAddress on
Corpo.dbo.AstAddress                    (astAddressId) with
FILLFACTOR=100

This is the server specs:
Adaptive Server Enterprise
Adaptive Server Enterprise/12.5.2/EBF 11948 ESD#1/P/NT (IX86)/OS
4.0/ase1252/1838/32-bit/OPT/Sat May 29 03:34:29 2004
jConnect (TM) for JDBC (TM)
jConnect (TM) for JDBC(TM)/5.5(Build 25100)/P/EBF9853/JDK12/Fri Oct 26
17:11:47 2001

These are the columns of the AstAddress, nothing unusual:

astAddressId    (null)  numeric 9
ossBranch       (null)  char    8
ossClient       (null)  char    20
ossAccount      (null)  char    18
astSubAccount   (null)  char    10
ossCountry      (null)  char    3
ossMajorSort    (null)  char    1
ossPostalCode   (null)  char    15
ossMunicipality (null)  varchar 32
ossAddressLine1 (null)  varchar 32
ossAddressLine2 (null)  varchar 32
ossAddressLine3 (null)  varchar 32
ossAddressLine4 (null)  varchar 32
ossAddressLine5 (null)  varchar 32
ossAddressLine6 (null)  varchar 32
ossAddressLine7 (null)  varchar 32
ossAddressLine8 (null)  varchar 32
ossConfirmNumber        (null)  numeric 1
ossConfirmType  (null)  char    1
ossGetStatment1 (null)  char    1
ossGetStatment2 (null)  char    1
ossSupSpecialMsg1       (null)  char    1
ossSupSpecialMsg2       (null)  char    1
ossPrt8NaLines1 (null)  char    1
ossPrt8Nalines2 (null)  char    1
ossHoldMail1    (null)  char    1
ossHoldMail2    (null)  char    1
ossSupPrice1    (null)  char    1
ossSupPrice2    (null)  char    1
ossExtraCopie1  (null)  numeric 2
ossExtraCopie2  (null)  numeric 2
ossStatment61   (null)  char    1
ossStatment62   (null)  char    1
ossProvince     (null)  char    2
ossTypeAddressLine1     (null)  char    1
ossTypeAddressLine2     (null)  char    1
ossTypeAddressLine3     (null)  char    1
ossTypeAddressLine4     (null)  char    1
ossTypeAddressLine5     (null)  char    1
ossTypeAddressLine6     (null)  char    1
astLastMaintUser        (null)  char    10
astLastMaintType        (null)  char    1
astLastMaintJob (null)  char    10
astLastMaintTime        (null)  numeric 6
astLastMaintDate        (null)  numeric 8

This is the query I am trying to run:

select
astaddress0_.astAddressId as astAddre1_0_,
astaddress0_.ossBranch as ossBranch25_0_
from Corpo.dbo.AstAddress astaddress0_
where astaddress0_.astAddressId=798957

The problem is when I am doing a prepare statement to take 2 rows, it
take more than 1 minutes of processing, the cpu of Sybase is at 100%!
It seems Sybase do not use the index to build its query plan! I try
getting only the primary key, and it was much faster.

This code takes 1 minutes to run (I use Spring, and I get the same
result with Hibernate!):

public List selectSqlPreparedStatement(String sql, Object id) {
               logger.debug("inside dao");
               JdbcTemplate jdbcTemplate = new
JdbcTemplate(dataSource);
               Object[] obj = new Object[1];
               obj[0] = id;
               logger.debug("before the query");
               List l = jdbcTemplate.queryForList(sql, obj);
               logger.debug("after the query"); // TAKE 1 minutes!!
               return l;
       }

And when I am executing the same query without any prepared statement,
it only take 18 miliseconds!!

This code take 18 ms to run:
       public void selectSqlWHPS(String sql) throws SQLException {
               logger.debug("inside dao");
               JdbcTemplate jdbcTemplate = new
JdbcTemplate(dataSource);
               logger.debug("before the query");
               Statement s =
jdbcTemplate.getDataSource().getConnection().createStatement();
               ResultSet rs = s.executeQuery(sql);
               rs.next();
               int val = rs.getInt(1);
               logger.debug("int: " + val);
               rs.close();
               s.close();
               logger.debug("after the query"); // TAKE 18 ms!!

       }

Am I missing something, or that is really a bug of Jconnect and Sybase?

Thanks,

Etienne
Montreal