ODBC cursors for update problem

[Andrzej Wilkoszewski]

Sorry for crossposting, but I didn't kwon where to put my question.

I'm developing application which connects among others to Sybase 12.5 via
ODBC.
Recenlty I found big performance problem. After some investigation I came to
following conclusions.

There is a table TBL - about 7 mln records with primary key (TBL_PRIMARY)
and index on one of fields TBL_FLD.

after runnig query in ISQL
SELECT * FROM TBL WHERE FLD = 'xxx'
server correctly uses index TBL_FLD, and query takes about 5 seconds.
(returning about 100 records)

runnig exaclty the same query from my app (via ODBC driver) with
SQLExecDirect() takes about 13 minutes !!!

I have found that since I have ODBC driver set to use server cursors this
query is transformed to sequence

DECLARE CURSOR cccc FOR SELECT * FROM TBL WHERE FLD = 'xxx'
open cccc

This sequence is interpreted by SQL server as UPDATEABLE cursor, so it is
looking for UNIQ index to identify each row. So the choice is TBL_PRIMARY
index resulting in sequential scanning of etire TBL.

The worst is, that ODBC driver is (I think on purpose) changes READ ONLY
cursors to UPDATEABLE.
Even with:
1. Specifying in query 'select * from tbl where fld = 'xxx' FOR READ ONLY'
2. Setting SQLStmtAttr to Curor_type = READ_ONLY
still it is converted to updateable cursor.

Is there any way to change this behaviour ??

This is totally blocking performance of SQL Server. (same app with ASA on
smaller machine is performing better)

TIA

Andrzej Wilkoszewski
NETSoft SA

[Brian_Beattie]

Two thoughts --

(1) No surprise that ASA runs better via ODBC - ODBC is a "native" protocol
for ASA.

(2) You might try adding an "order by" clause to your query - You add the
overhead to sort 100 rows, but may succeed in convincing the server cursor
to go read-only...

Good Luck
-- Brian

BTW: make sure "autocommit" is doing what you expect, and set isolation
level to 1 by default if you can...

[Andrzej Wilkoszewski]

<Brian_Beattie> wrote in message
news:0F3939655A0D82F7004F5FB585256D35.0056A72685256D34@webforums...

> Two thoughts --
>
> (1) No surprise that ASA runs better via ODBC - ODBC is a "native"
protocol
> for ASA.
>
> (2) You might try adding an "order by" clause to your query - You add the
> overhead to sort 100 rows, but may succeed in convincing the server cursor
> to go read-only...
>

I wish it was so easy.
adding ORDER BY caueses SQL server call optimized twice (don't ask my why),
and still it uses worst strategy.

I add dbcc traceon (302) before query and I see, that optimizer is only
considering uniqu indexes. It doesn't even try to use any other index.
As I investigated strategy plans for many queries I saw, that SQL optimized
takes different (worse) strategy in almost any query that should involve use
on indexes.
I'm going to try it, but I'm affraid, that it works the same way with ADO.
It means, that almost all software written for Windows can't use the full
power of ASE !!!.

Andrzej Wilkoszewski
NET Soft S.A.

[Ilya Zvyagin]

Hello, Brian_Beattie!
You wrote on Thu, 29 May 2003 10:26:58 -0400:

BB> (2) You might try adding an "order by" clause to your query - You add the overhead to sort
BB> 100 rows, but may succeed in convincing the server cursor to go read-only...

DISTINCT is much better here. Using ORDER BY here, ASE's optimizer can still
use a corresponding index to produce sorted results and not use a temporary table.

It's high time for ASE to have explicit insensitive cursors.
--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port
E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
ICQ UID: 29427861(MasterZIV)