Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Different query plans between prepared JDBC statement and isql

12 posts in General Discussion Last posting was on 2012-04-13 12:57:42.0Z
Schock Reno Posted on 2010-07-27 12:05:35.0Z
User-Agent: Microsoft-Entourage/13.5.0.100510
Subject: Different query plans between prepared JDBC statement and isql
From: Schock Reno <reno.schock@googlemail.com>
Newsgroups: sybase.public.ase.general
Message-ID: <C8749829.22D6%reno.schock@googlemail.com>
Thread-Topic: Different query plans between prepared JDBC statement and isql
Thread-Index: AcsthAE+GgK1LSV7eUuq1lflnTZi8A==
Mime-version: 1.0
Content-type: text/plain; charset="ISO-8859-1"
Content-transfer-encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 27 Jul 2010 05:05:35 -0700
X-Trace: forums-1-dub 1280232335 10.22.241.152 (27 Jul 2010 05:05:35 -0700)
X-Original-Trace: 27 Jul 2010 05:05:35 -0700, vip152.sybase.com
Lines: 109
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29409
Article PK: 78645

Hi all,

I've already asked this question in the jConnect/jdbc group and have been
pointed to this group. Maybe someone has an answer or hint for my problem.

Quoted text from jConnect group:

I have a performance issue using jConnect-6. A query (prepared then
executed) out of a Java application results in a diferent and worse query
plan than executed directly from isql. As I cannot explain by myself why
this happens, I'd welcome any hint to understand the different behaviour.
Please see the examples below:

From application (captured with 'set tracefile'):

SQL Text: SELECT task_id FROM bo_task WHERE (task_id=@p0 AND
task_version>@p1) OR (task_id=@p2 AND task_version>@p3)ì

QUERY PLAN FOR STATEMENT 1 (at line 0).


STEP 1
The type of query is DECLARE.


QUERY PLAN FOR STATEMENT 2 (at line 1).


STEP 1
The type of query is SELECT.

2 operator(s) under root

|ROOT:EMIT Operator (VA = 2)

|
| |RESTRICT Operator (VA = 1)(9)(0)(0)(2)(0)
| |
| | |SCAN Operator (VA = 0)
| | | FROM TABLE
| | | bo_task
| | | Index : idx_bo_task_ver
| | | Forward Scan.
| | | Positioning at index start.
| | | Index contains all needed columns. Base table will not be
read.
| | | Using I/O Size 8 Kbytes for index leaf pages.
| | | With LRU Buffer Replacement Strategy for index leaf pages.

Adaptive Server cpu time: 3000 ms. Adaptive Server elapsed
time: 3033 ms.End of Batch 4400

Directly executed from sqsh/isql with literals:

SELECT task_id FROM bo_task WHERE (task_id=4112784 AND task_version>0) OR
(task_id=4112785 AND task_version>0)

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is EXECUTE.
Executing a newly cached statement (SSQL_ID = 297203852).

Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SELECT.

4 operator(s) under root

|ROOT:EMIT Operator (VA = 4)
|
| |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
| |
| | |SCAN Operator (VA = 0)
| | | FROM OR List
| | | OR List has up to 2 rows of OR/IN values.
| |
| | |RESTRICT Operator (VA = 2)(0)(0)(0)(7)(0)
| | |
| | | |SCAN Operator (VA = 1)
| | | | FROM TABLE
| | | | bo_task
| | | | Index : idx_bo_task_ver
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Index contains all needed columns. Base table will not
be read.
| | | | Keys are:
| | | | task_id ASC
| | | | task_version ASC
| | | | Using I/O Size 8 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy
for index leaf pages.

Adaptive Server cpu time: 0 ms. Adaptive Server elapsed
time: 3 ms.

I do not understand, why there is no positioning by key in the first
example. Setting LITERAL_PARAMS to true is not an option for this
application as our developers mentioned. Any idea on this?

Best regards
Reno


Sherlock, Kevin [TeamSybase] Posted on 2010-07-27 13:55:14.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <C8749829.22D6%reno.schock@googlemail.com>
Subject: Re: Different query plans between prepared JDBC statement and isql
Lines: 121
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c4ee542$1@forums-1-dub>
Date: 27 Jul 2010 06:55:14 -0700
X-Trace: forums-1-dub 1280238914 10.22.241.152 (27 Jul 2010 06:55:14 -0700)
X-Original-Trace: 27 Jul 2010 06:55:14 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29412
Article PK: 78642

it's a difference in how one statement is optimized compared to the other.
Your java prepared statement can only be optimized using unknown values
(parameters), while your isql statement is using literal values and can be
costed more accurately by the optimizer. This is an extremely common FAQ
topic.

"Schock Reno" <reno.schock@googlemail.com> wrote in message
news:C8749829.22D6%reno.schock@googlemail.com...
> Hi all,
>
> I've already asked this question in the jConnect/jdbc group and have been
> pointed to this group. Maybe someone has an answer or hint for my problem.
>
> Quoted text from jConnect group:
>
> I have a performance issue using jConnect-6. A query (prepared then
> executed) out of a Java application results in a diferent and worse query
> plan than executed directly from isql. As I cannot explain by myself why
> this happens, I'd welcome any hint to understand the different behaviour.
> Please see the examples below:
>
> From application (captured with 'set tracefile'):
>
> SQL Text: SELECT task_id FROM bo_task WHERE (task_id=@p0 AND
> task_version>@p1) OR (task_id=@p2 AND task_version>@p3)ì
>
> QUERY PLAN FOR STATEMENT 1 (at line 0).
>
>
> STEP 1
> The type of query is DECLARE.
>
>
> QUERY PLAN FOR STATEMENT 2 (at line 1).
>
>
> STEP 1
> The type of query is SELECT.
>
> 2 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 2)
> |
> | |RESTRICT Operator (VA = 1)(9)(0)(0)(2)(0)
> | |
> | | |SCAN Operator (VA = 0)
> | | | FROM TABLE
> | | | bo_task
> | | | Index : idx_bo_task_ver
> | | | Forward Scan.
> | | | Positioning at index start.
> | | | Index contains all needed columns. Base table will not be
> read.
> | | | Using I/O Size 8 Kbytes for index leaf pages.
> | | | With LRU Buffer Replacement Strategy for index leaf
> pages.
>
> Adaptive Server cpu time: 3000 ms. Adaptive Server elapsed
> time: 3033 ms.End of Batch 4400
>
> Directly executed from sqsh/isql with literals:
>
> SELECT task_id FROM bo_task WHERE (task_id=4112784 AND task_version>0) OR
> (task_id=4112785 AND task_version>0)
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is EXECUTE.
> Executing a newly cached statement (SSQL_ID = 297203852).
>
> Parse and Compile Time 0.
> Adaptive Server cpu time: 0 ms.
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is SELECT.
>
> 4 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 4)
> |
> | |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
> | |
> | | |SCAN Operator (VA = 0)
> | | | FROM OR List
> | | | OR List has up to 2 rows of OR/IN values.
> | |
> | | |RESTRICT Operator (VA = 2)(0)(0)(0)(7)(0)
> | | |
> | | | |SCAN Operator (VA = 1)
> | | | | FROM TABLE
> | | | | bo_task
> | | | | Index : idx_bo_task_ver
> | | | | Forward Scan.
> | | | | Positioning by key.
> | | | | Index contains all needed columns. Base table will
> not
> be read.
> | | | | Keys are:
> | | | | task_id ASC
> | | | | task_version ASC
> | | | | Using I/O Size 8 Kbytes for index leaf pages.
> | | | | With LRU Buffer Replacement Strategy
> for index leaf pages.
>
> Adaptive Server cpu time: 0 ms. Adaptive Server elapsed
> time: 3 ms.
>
> I do not understand, why there is no positioning by key in the first
> example. Setting LITERAL_PARAMS to true is not an option for this
> application as our developers mentioned. Any idea on this?
>
> Best regards
> Reno
>


Schock Reno Posted on 2010-07-27 14:18:47.0Z
User-Agent: Microsoft-Entourage/13.5.0.100510
Subject: Re: Different query plans between prepared JDBC statement and isql
From: Schock Reno <reno.schock@googlemail.com>
Newsgroups: sybase.public.ase.general
Message-ID: <C874B763.22F2%reno.schock@googlemail.com>
Thread-Topic: Different query plans between prepared JDBC statement and isql
Thread-Index: Acstlp4KNlrXpwgPg0GHZuCUBJpTlg==
References: <C8749829.22D6%reno.schock@googlemail.com> <4c4ee542$1@forums-1-dub>
Mime-version: 1.0
Content-type: text/plain; charset="ISO-8859-1"
Content-transfer-encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 27 Jul 2010 07:18:47 -0700
X-Trace: forums-1-dub 1280240327 10.22.241.152 (27 Jul 2010 07:18:47 -0700)
X-Original-Trace: 27 Jul 2010 07:18:47 -0700, vip152.sybase.com
Lines: 133
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29413
Article PK: 78646

This would mean, that a prepared statement would always guess it has to use
e.g. the convert function to convert to the neccessary data type - correct?
Is this a standard behaviour?

Best regards
Reno


Am 27.07.10 15:55 schrieb "Sherlock, Kevin [TeamSybase]" unter
<kevin.sherlock@teamsybase.com> in 4c4ee542$1@forums-1-dub:

> it's a difference in how one statement is optimized compared to the other.
> Your java prepared statement can only be optimized using unknown values
> (parameters), while your isql statement is using literal values and can be
> costed more accurately by the optimizer. This is an extremely common FAQ
> topic.
>
> "Schock Reno" <reno.schock@googlemail.com> wrote in message
> news:C8749829.22D6%reno.schock@googlemail.com...
>> Hi all,
>>
>> I've already asked this question in the jConnect/jdbc group and have been
>> pointed to this group. Maybe someone has an answer or hint for my problem.
>>
>> Quoted text from jConnect group:
>>
>> I have a performance issue using jConnect-6. A query (prepared then
>> executed) out of a Java application results in a diferent and worse query
>> plan than executed directly from isql. As I cannot explain by myself why
>> this happens, I'd welcome any hint to understand the different behaviour.
>> Please see the examples below:
>>
>> From application (captured with 'set tracefile'):
>>
>> SQL Text: SELECT task_id FROM bo_task WHERE (task_id=@p0 AND
>> task_version>@p1) OR (task_id=@p2 AND task_version>@p3)ì
>>
>> QUERY PLAN FOR STATEMENT 1 (at line 0).
>>
>>
>> STEP 1
>> The type of query is DECLARE.
>>
>>
>> QUERY PLAN FOR STATEMENT 2 (at line 1).
>>
>>
>> STEP 1
>> The type of query is SELECT.
>>
>> 2 operator(s) under root
>>
>> |ROOT:EMIT Operator (VA = 2)
>> |
>> | |RESTRICT Operator (VA = 1)(9)(0)(0)(2)(0)
>> | |
>> | | |SCAN Operator (VA = 0)
>> | | | FROM TABLE
>> | | | bo_task
>> | | | Index : idx_bo_task_ver
>> | | | Forward Scan.
>> | | | Positioning at index start.
>> | | | Index contains all needed columns. Base table will not be
>> read.
>> | | | Using I/O Size 8 Kbytes for index leaf pages.
>> | | | With LRU Buffer Replacement Strategy for index leaf
>> pages.
>>
>> Adaptive Server cpu time: 3000 ms. Adaptive Server elapsed
>> time: 3033 ms.End of Batch 4400
>>
>> Directly executed from sqsh/isql with literals:
>>
>> SELECT task_id FROM bo_task WHERE (task_id=4112784 AND task_version>0) OR
>> (task_id=4112785 AND task_version>0)
>>
>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>
>>
>> STEP 1
>> The type of query is EXECUTE.
>> Executing a newly cached statement (SSQL_ID = 297203852).
>>
>> Parse and Compile Time 0.
>> Adaptive Server cpu time: 0 ms.
>>
>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>
>>
>> STEP 1
>> The type of query is SELECT.
>>
>> 4 operator(s) under root
>>
>> |ROOT:EMIT Operator (VA = 4)
>> |
>> | |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
>> | |
>> | | |SCAN Operator (VA = 0)
>> | | | FROM OR List
>> | | | OR List has up to 2 rows of OR/IN values.
>> | |
>> | | |RESTRICT Operator (VA = 2)(0)(0)(0)(7)(0)
>> | | |
>> | | | |SCAN Operator (VA = 1)
>> | | | | FROM TABLE
>> | | | | bo_task
>> | | | | Index : idx_bo_task_ver
>> | | | | Forward Scan.
>> | | | | Positioning by key.
>> | | | | Index contains all needed columns. Base table will
>> not
>> be read.
>> | | | | Keys are:
>> | | | | task_id ASC
>> | | | | task_version ASC
>> | | | | Using I/O Size 8 Kbytes for index leaf pages.
>> | | | | With LRU Buffer Replacement Strategy
>> for index leaf pages.
>>
>> Adaptive Server cpu time: 0 ms. Adaptive Server elapsed
>> time: 3 ms.
>>
>> I do not understand, why there is no positioning by key in the first
>> example. Setting LITERAL_PARAMS to true is not an option for this
>> application as our developers mentioned. Any idea on this?
>>
>> Best regards
>> Reno
>>
>
>


Sherlock, Kevin [TeamSybase] Posted on 2010-07-27 15:16:23.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <C8749829.22D6%reno.schock@googlemail.com> <4c4ee542$1@forums-1-dub> <C874B763.22F2%reno.schock@googlemail.com>
Subject: Re: Different query plans between prepared JDBC statement and isql
Lines: 161
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c4ef847$1@forums-1-dub>
Date: 27 Jul 2010 08:16:23 -0700
X-Trace: forums-1-dub 1280243783 10.22.241.152 (27 Jul 2010 08:16:23 -0700)
X-Original-Trace: 27 Jul 2010 08:16:23 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29414
Article PK: 78643

necessary data types? i didn't say that. i'm not sure what you're getting
at there.

My point is that your prepared statement uses unknown values for search
criteria. The values for those parameters are only known at runtime, not
compile time. However, i'm now wondering to myself when a query plan is
compiled for a prepared statement. If it's at "prepare" time, or "execute"
time. If the latter, then yes, I would wonder too about the datatypes of
the parameters that you declare in your java code and if they match the
datatypes of the columns in the search clause.

Can you post the sp_help output of the table, and the section of java code
that declares the parameters?

"Schock Reno" <reno.schock@googlemail.com> wrote in message
news:C874B763.22F2%reno.schock@googlemail.com...
> This would mean, that a prepared statement would always guess it has to
> use
> e.g. the convert function to convert to the neccessary data type -
> correct?
> Is this a standard behaviour?
>
> Best regards
> Reno
>
>
> Am 27.07.10 15:55 schrieb "Sherlock, Kevin [TeamSybase]" unter
> <kevin.sherlock@teamsybase.com> in 4c4ee542$1@forums-1-dub:
>
>> it's a difference in how one statement is optimized compared to the
>> other.
>> Your java prepared statement can only be optimized using unknown values
>> (parameters), while your isql statement is using literal values and can
>> be
>> costed more accurately by the optimizer. This is an extremely common FAQ
>> topic.
>>
>> "Schock Reno" <reno.schock@googlemail.com> wrote in message
>> news:C8749829.22D6%reno.schock@googlemail.com...
>>> Hi all,
>>>
>>> I've already asked this question in the jConnect/jdbc group and have
>>> been
>>> pointed to this group. Maybe someone has an answer or hint for my
>>> problem.
>>>
>>> Quoted text from jConnect group:
>>>
>>> I have a performance issue using jConnect-6. A query (prepared then
>>> executed) out of a Java application results in a diferent and worse
>>> query
>>> plan than executed directly from isql. As I cannot explain by myself why
>>> this happens, I'd welcome any hint to understand the different
>>> behaviour.
>>> Please see the examples below:
>>>
>>> From application (captured with 'set tracefile'):
>>>
>>> SQL Text: SELECT task_id FROM bo_task WHERE (task_id=@p0 AND
>>> task_version>@p1) OR (task_id=@p2 AND task_version>@p3)ì
>>>
>>> QUERY PLAN FOR STATEMENT 1 (at line 0).
>>>
>>>
>>> STEP 1
>>> The type of query is DECLARE.
>>>
>>>
>>> QUERY PLAN FOR STATEMENT 2 (at line 1).
>>>
>>>
>>> STEP 1
>>> The type of query is SELECT.
>>>
>>> 2 operator(s) under root
>>>
>>> |ROOT:EMIT Operator (VA = 2)
>>> |
>>> | |RESTRICT Operator (VA = 1)(9)(0)(0)(2)(0)
>>> | |
>>> | | |SCAN Operator (VA = 0)
>>> | | | FROM TABLE
>>> | | | bo_task
>>> | | | Index : idx_bo_task_ver
>>> | | | Forward Scan.
>>> | | | Positioning at index start.
>>> | | | Index contains all needed columns. Base table will not
>>> be
>>> read.
>>> | | | Using I/O Size 8 Kbytes for index leaf pages.
>>> | | | With LRU Buffer Replacement Strategy for index leaf
>>> pages.
>>>
>>> Adaptive Server cpu time: 3000 ms. Adaptive Server elapsed
>>> time: 3033 ms.End of Batch 4400
>>>
>>> Directly executed from sqsh/isql with literals:
>>>
>>> SELECT task_id FROM bo_task WHERE (task_id=4112784 AND task_version>0)
>>> OR
>>> (task_id=4112785 AND task_version>0)
>>>
>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>
>>>
>>> STEP 1
>>> The type of query is EXECUTE.
>>> Executing a newly cached statement (SSQL_ID = 297203852).
>>>
>>> Parse and Compile Time 0.
>>> Adaptive Server cpu time: 0 ms.
>>>
>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>
>>>
>>> STEP 1
>>> The type of query is SELECT.
>>>
>>> 4 operator(s) under root
>>>
>>> |ROOT:EMIT Operator (VA = 4)
>>> |
>>> | |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
>>> | |
>>> | | |SCAN Operator (VA = 0)
>>> | | | FROM OR List
>>> | | | OR List has up to 2 rows of OR/IN values.
>>> | |
>>> | | |RESTRICT Operator (VA = 2)(0)(0)(0)(7)(0)
>>> | | |
>>> | | | |SCAN Operator (VA = 1)
>>> | | | | FROM TABLE
>>> | | | | bo_task
>>> | | | | Index : idx_bo_task_ver
>>> | | | | Forward Scan.
>>> | | | | Positioning by key.
>>> | | | | Index contains all needed columns. Base table will
>>> not
>>> be read.
>>> | | | | Keys are:
>>> | | | | task_id ASC
>>> | | | | task_version ASC
>>> | | | | Using I/O Size 8 Kbytes for index leaf pages.
>>> | | | | With LRU Buffer Replacement Strategy
>>> for index leaf pages.
>>>
>>> Adaptive Server cpu time: 0 ms. Adaptive Server elapsed
>>> time: 3 ms.
>>>
>>> I do not understand, why there is no positioning by key in the first
>>> example. Setting LITERAL_PARAMS to true is not an option for this
>>> application as our developers mentioned. Any idea on this?
>>>
>>> Best regards
>>> Reno
>>>
>>
>>
>


Schock Reno Posted on 2010-07-27 16:28:09.0Z
User-Agent: Microsoft-Entourage/13.5.0.100510
Subject: Re: Different query plans between prepared JDBC statement and isql
From: Schock Reno <reno.schock@googlemail.com>
Newsgroups: sybase.public.ase.general
Message-ID: <C874D5B4.2EAD%reno.schock@googlemail.com>
Thread-Topic: Different query plans between prepared JDBC statement and isql
Thread-Index: AcstqK/0xfmMmL+n7E2mtRB7h9oKbA==
References: <C8749829.22D6%reno.schock@googlemail.com> <4c4ee542$1@forums-1-dub> <C874B763.22F2%reno.schock@googlemail.com> <4c4ef847$1@forums-1-dub>
Mime-version: 1.0
Content-type: text/plain; charset="ISO-8859-1"
Content-transfer-encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 27 Jul 2010 09:28:09 -0700
X-Trace: forums-1-dub 1280248089 10.22.241.152 (27 Jul 2010 09:28:09 -0700)
X-Original-Trace: 27 Jul 2010 09:28:09 -0700, vip152.sybase.com
Lines: 194
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29417
Article PK: 78649

I went to the developers and we stepped through the code with a debugger.
The code works as follows:

PreparedStatement stmt = conn.prepareStatement("SELECT task_id FROM bo_task
WHERE (task_id=? AND task_version>?) OR (task_id=? AND task_version>?)");

Afterwards the values are filled in like:

stmt.setLong(1, 415678);
stmt.setInt(2, 0);
stmt.setLong(3, 415679);
stmt.setInt(4, 2);

And at last it is executed like:

ResultSet rs = stmt.executeQuery();

The corresponding table fields are defined as

Task_id numeric(18,0)
Task_version numeric(18,0)

We tried to modify the two Int's to Long's with a debugger but there was no
change. I hope this information may help in further investigation.

Thank you very much and best regards
Reno


Am 27.07.10 17:16 schrieb "Sherlock, Kevin [TeamSybase]" unter
<kevin.sherlock@teamsybase.com> in 4c4ef847$1@forums-1-dub:

> necessary data types? i didn't say that. i'm not sure what you're getting
> at there.
>
> My point is that your prepared statement uses unknown values for search
> criteria. The values for those parameters are only known at runtime, not
> compile time. However, i'm now wondering to myself when a query plan is
> compiled for a prepared statement. If it's at "prepare" time, or "execute"
> time. If the latter, then yes, I would wonder too about the datatypes of
> the parameters that you declare in your java code and if they match the
> datatypes of the columns in the search clause.
>
> Can you post the sp_help output of the table, and the section of java code
> that declares the parameters?
>
> "Schock Reno" <reno.schock@googlemail.com> wrote in message
> news:C874B763.22F2%reno.schock@googlemail.com...
>> This would mean, that a prepared statement would always guess it has to
>> use
>> e.g. the convert function to convert to the neccessary data type -
>> correct?
>> Is this a standard behaviour?
>>
>> Best regards
>> Reno
>>
>>
>> Am 27.07.10 15:55 schrieb "Sherlock, Kevin [TeamSybase]" unter
>> <kevin.sherlock@teamsybase.com> in 4c4ee542$1@forums-1-dub:
>>
>>> it's a difference in how one statement is optimized compared to the
>>> other.
>>> Your java prepared statement can only be optimized using unknown values
>>> (parameters), while your isql statement is using literal values and can
>>> be
>>> costed more accurately by the optimizer. This is an extremely common FAQ
>>> topic.
>>>
>>> "Schock Reno" <reno.schock@googlemail.com> wrote in message
>>> news:C8749829.22D6%reno.schock@googlemail.com...
>>>> Hi all,
>>>>
>>>> I've already asked this question in the jConnect/jdbc group and have
>>>> been
>>>> pointed to this group. Maybe someone has an answer or hint for my
>>>> problem.
>>>>
>>>> Quoted text from jConnect group:
>>>>
>>>> I have a performance issue using jConnect-6. A query (prepared then
>>>> executed) out of a Java application results in a diferent and worse
>>>> query
>>>> plan than executed directly from isql. As I cannot explain by myself why
>>>> this happens, I'd welcome any hint to understand the different
>>>> behaviour.
>>>> Please see the examples below:
>>>>
>>>> From application (captured with 'set tracefile'):
>>>>
>>>> SQL Text: SELECT task_id FROM bo_task WHERE (task_id=@p0 AND
>>>> task_version>@p1) OR (task_id=@p2 AND task_version>@p3)ì
>>>>
>>>> QUERY PLAN FOR STATEMENT 1 (at line 0).
>>>>
>>>>
>>>> STEP 1
>>>> The type of query is DECLARE.
>>>>
>>>>
>>>> QUERY PLAN FOR STATEMENT 2 (at line 1).
>>>>
>>>>
>>>> STEP 1
>>>> The type of query is SELECT.
>>>>
>>>> 2 operator(s) under root
>>>>
>>>> |ROOT:EMIT Operator (VA = 2)
>>>> |
>>>> | |RESTRICT Operator (VA = 1)(9)(0)(0)(2)(0)
>>>> | |
>>>> | | |SCAN Operator (VA = 0)
>>>> | | | FROM TABLE
>>>> | | | bo_task
>>>> | | | Index : idx_bo_task_ver
>>>> | | | Forward Scan.
>>>> | | | Positioning at index start.
>>>> | | | Index contains all needed columns. Base table will not
>>>> be
>>>> read.
>>>> | | | Using I/O Size 8 Kbytes for index leaf pages.
>>>> | | | With LRU Buffer Replacement Strategy for index leaf
>>>> pages.
>>>>
>>>> Adaptive Server cpu time: 3000 ms. Adaptive Server elapsed
>>>> time: 3033 ms.End of Batch 4400
>>>>
>>>> Directly executed from sqsh/isql with literals:
>>>>
>>>> SELECT task_id FROM bo_task WHERE (task_id=4112784 AND task_version>0)
>>>> OR
>>>> (task_id=4112785 AND task_version>0)
>>>>
>>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>>
>>>>
>>>> STEP 1
>>>> The type of query is EXECUTE.
>>>> Executing a newly cached statement (SSQL_ID = 297203852).
>>>>
>>>> Parse and Compile Time 0.
>>>> Adaptive Server cpu time: 0 ms.
>>>>
>>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>>
>>>>
>>>> STEP 1
>>>> The type of query is SELECT.
>>>>
>>>> 4 operator(s) under root
>>>>
>>>> |ROOT:EMIT Operator (VA = 4)
>>>> |
>>>> | |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
>>>> | |
>>>> | | |SCAN Operator (VA = 0)
>>>> | | | FROM OR List
>>>> | | | OR List has up to 2 rows of OR/IN values.
>>>> | |
>>>> | | |RESTRICT Operator (VA = 2)(0)(0)(0)(7)(0)
>>>> | | |
>>>> | | | |SCAN Operator (VA = 1)
>>>> | | | | FROM TABLE
>>>> | | | | bo_task
>>>> | | | | Index : idx_bo_task_ver
>>>> | | | | Forward Scan.
>>>> | | | | Positioning by key.
>>>> | | | | Index contains all needed columns. Base table will
>>>> not
>>>> be read.
>>>> | | | | Keys are:
>>>> | | | | task_id ASC
>>>> | | | | task_version ASC
>>>> | | | | Using I/O Size 8 Kbytes for index leaf pages.
>>>> | | | | With LRU Buffer Replacement Strategy
>>>> for index leaf pages.
>>>>
>>>> Adaptive Server cpu time: 0 ms. Adaptive Server elapsed
>>>> time: 3 ms.
>>>>
>>>> I do not understand, why there is no positioning by key in the first
>>>> example. Setting LITERAL_PARAMS to true is not an option for this
>>>> application as our developers mentioned. Any idea on this?
>>>>
>>>> Best regards
>>>> Reno
>>>>
>>>
>>>
>>
>
>


Sherlock, Kevin [TeamSybase] Posted on 2010-07-27 17:37:56.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <C8749829.22D6%reno.schock@googlemail.com> <4c4ee542$1@forums-1-dub> <C874B763.22F2%reno.schock@googlemail.com> <4c4ef847$1@forums-1-dub> <C874D5B4.2EAD%reno.schock@googlemail.com>
Subject: Re: Different query plans between prepared JDBC statement and isql
Lines: 225
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c4f1974$1@forums-1-dub>
Date: 27 Jul 2010 10:37:56 -0700
X-Trace: forums-1-dub 1280252276 10.22.241.152 (27 Jul 2010 10:37:56 -0700)
X-Original-Trace: 27 Jul 2010 10:37:56 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29420
Article PK: 78652

Well, then I stand corrected. This is purely a datatype mismatch issue as
Neal had originally suggested.

If you can't change the datatypes of your table columns, the only workaround
that I can think of - unless Neal has a better suggestion, is

(1) change the SQL statement to convert your datatypes:

PreparedStatement stmt = conn.prepareStatement("SELECT task_id FROM bo_task
WHERE (task_id=convert(numeric(18,0),?) AND
task_version>convert(numeric(18,0),?)) OR (task_id=convert(numeric(18,0),?)
AND task_version>convert(numeric(18,0),?))");

(2) instead of using prepared statements, execute regular SQL (and
optionally deploy statement cache, literal parameters).

(3) create a stored procedure, and call it with your parameters.

"Schock Reno" <reno.schock@googlemail.com> wrote in message
news:C874D5B4.2EAD%reno.schock@googlemail.com...
>I went to the developers and we stepped through the code with a debugger.
> The code works as follows:
>
> PreparedStatement stmt = conn.prepareStatement("SELECT task_id FROM
> bo_task
> WHERE (task_id=? AND task_version>?) OR (task_id=? AND task_version>?)");
>
> Afterwards the values are filled in like:
>
> stmt.setLong(1, 415678);
> stmt.setInt(2, 0);
> stmt.setLong(3, 415679);
> stmt.setInt(4, 2);
>
> And at last it is executed like:
>
> ResultSet rs = stmt.executeQuery();
>
> The corresponding table fields are defined as
>
> Task_id numeric(18,0)
> Task_version numeric(18,0)
>
> We tried to modify the two Int's to Long's with a debugger but there was
> no
> change. I hope this information may help in further investigation.
>
> Thank you very much and best regards
> Reno
>
>
> Am 27.07.10 17:16 schrieb "Sherlock, Kevin [TeamSybase]" unter
> <kevin.sherlock@teamsybase.com> in 4c4ef847$1@forums-1-dub:
>
>> necessary data types? i didn't say that. i'm not sure what you're
>> getting
>> at there.
>>
>> My point is that your prepared statement uses unknown values for search
>> criteria. The values for those parameters are only known at runtime, not
>> compile time. However, i'm now wondering to myself when a query plan is
>> compiled for a prepared statement. If it's at "prepare" time, or
>> "execute"
>> time. If the latter, then yes, I would wonder too about the datatypes of
>> the parameters that you declare in your java code and if they match the
>> datatypes of the columns in the search clause.
>>
>> Can you post the sp_help output of the table, and the section of java
>> code
>> that declares the parameters?
>>
>> "Schock Reno" <reno.schock@googlemail.com> wrote in message
>> news:C874B763.22F2%reno.schock@googlemail.com...
>>> This would mean, that a prepared statement would always guess it has to
>>> use
>>> e.g. the convert function to convert to the neccessary data type -
>>> correct?
>>> Is this a standard behaviour?
>>>
>>> Best regards
>>> Reno
>>>
>>>
>>> Am 27.07.10 15:55 schrieb "Sherlock, Kevin [TeamSybase]" unter
>>> <kevin.sherlock@teamsybase.com> in 4c4ee542$1@forums-1-dub:
>>>
>>>> it's a difference in how one statement is optimized compared to the
>>>> other.
>>>> Your java prepared statement can only be optimized using unknown values
>>>> (parameters), while your isql statement is using literal values and can
>>>> be
>>>> costed more accurately by the optimizer. This is an extremely common
>>>> FAQ
>>>> topic.
>>>>
>>>> "Schock Reno" <reno.schock@googlemail.com> wrote in message
>>>> news:C8749829.22D6%reno.schock@googlemail.com...
>>>>> Hi all,
>>>>>
>>>>> I've already asked this question in the jConnect/jdbc group and have
>>>>> been
>>>>> pointed to this group. Maybe someone has an answer or hint for my
>>>>> problem.
>>>>>
>>>>> Quoted text from jConnect group:
>>>>>
>>>>> I have a performance issue using jConnect-6. A query (prepared then
>>>>> executed) out of a Java application results in a diferent and worse
>>>>> query
>>>>> plan than executed directly from isql. As I cannot explain by myself
>>>>> why
>>>>> this happens, I'd welcome any hint to understand the different
>>>>> behaviour.
>>>>> Please see the examples below:
>>>>>
>>>>> From application (captured with 'set tracefile'):
>>>>>
>>>>> SQL Text: SELECT task_id FROM bo_task WHERE (task_id=@p0 AND
>>>>> task_version>@p1) OR (task_id=@p2 AND task_version>@p3)ì
>>>>>
>>>>> QUERY PLAN FOR STATEMENT 1 (at line 0).
>>>>>
>>>>>
>>>>> STEP 1
>>>>> The type of query is DECLARE.
>>>>>
>>>>>
>>>>> QUERY PLAN FOR STATEMENT 2 (at line 1).
>>>>>
>>>>>
>>>>> STEP 1
>>>>> The type of query is SELECT.
>>>>>
>>>>> 2 operator(s) under root
>>>>>
>>>>> |ROOT:EMIT Operator (VA = 2)
>>>>> |
>>>>> | |RESTRICT Operator (VA = 1)(9)(0)(0)(2)(0)
>>>>> | |
>>>>> | | |SCAN Operator (VA = 0)
>>>>> | | | FROM TABLE
>>>>> | | | bo_task
>>>>> | | | Index : idx_bo_task_ver
>>>>> | | | Forward Scan.
>>>>> | | | Positioning at index start.
>>>>> | | | Index contains all needed columns. Base table will
>>>>> not
>>>>> be
>>>>> read.
>>>>> | | | Using I/O Size 8 Kbytes for index leaf pages.
>>>>> | | | With LRU Buffer Replacement Strategy for index leaf
>>>>> pages.
>>>>>
>>>>> Adaptive Server cpu time: 3000 ms. Adaptive Server elapsed
>>>>> time: 3033 ms.End of Batch 4400
>>>>>
>>>>> Directly executed from sqsh/isql with literals:
>>>>>
>>>>> SELECT task_id FROM bo_task WHERE (task_id=4112784 AND task_version>0)
>>>>> OR
>>>>> (task_id=4112785 AND task_version>0)
>>>>>
>>>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>>>
>>>>>
>>>>> STEP 1
>>>>> The type of query is EXECUTE.
>>>>> Executing a newly cached statement (SSQL_ID = 297203852).
>>>>>
>>>>> Parse and Compile Time 0.
>>>>> Adaptive Server cpu time: 0 ms.
>>>>>
>>>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>>>
>>>>>
>>>>> STEP 1
>>>>> The type of query is SELECT.
>>>>>
>>>>> 4 operator(s) under root
>>>>>
>>>>> |ROOT:EMIT Operator (VA = 4)
>>>>> |
>>>>> | |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
>>>>> | |
>>>>> | | |SCAN Operator (VA = 0)
>>>>> | | | FROM OR List
>>>>> | | | OR List has up to 2 rows of OR/IN values.
>>>>> | |
>>>>> | | |RESTRICT Operator (VA = 2)(0)(0)(0)(7)(0)
>>>>> | | |
>>>>> | | | |SCAN Operator (VA = 1)
>>>>> | | | | FROM TABLE
>>>>> | | | | bo_task
>>>>> | | | | Index : idx_bo_task_ver
>>>>> | | | | Forward Scan.
>>>>> | | | | Positioning by key.
>>>>> | | | | Index contains all needed columns. Base table
>>>>> will
>>>>> not
>>>>> be read.
>>>>> | | | | Keys are:
>>>>> | | | | task_id ASC
>>>>> | | | | task_version ASC
>>>>> | | | | Using I/O Size 8 Kbytes for index leaf pages.
>>>>> | | | | With LRU Buffer Replacement Strategy
>>>>> for index leaf pages.
>>>>>
>>>>> Adaptive Server cpu time: 0 ms. Adaptive Server elapsed
>>>>> time: 3 ms.
>>>>>
>>>>> I do not understand, why there is no positioning by key in the first
>>>>> example. Setting LITERAL_PARAMS to true is not an option for this
>>>>> application as our developers mentioned. Any idea on this?
>>>>>
>>>>> Best regards
>>>>> Reno
>>>>>
>>>>
>>>>
>>>
>>
>>
>


Sherlock, Kevin [TeamSybase] Posted on 2010-07-27 22:15:11.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <C8749829.22D6%reno.schock@googlemail.com> <4c4ee542$1@forums-1-dub> <C874B763.22F2%reno.schock@googlemail.com> <4c4ef847$1@forums-1-dub> <C874D5B4.2EAD%reno.schock@googlemail.com> <4c4f1974$1@forums-1-dub>
Subject: Re: Different query plans between prepared JDBC statement and isql
Lines: 290
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c4f5a6f$1@forums-1-dub>
Date: 27 Jul 2010 15:15:11 -0700
X-Trace: forums-1-dub 1280268911 10.22.241.152 (27 Jul 2010 15:15:11 -0700)
X-Original-Trace: 27 Jul 2010 15:15:11 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29421
Article PK: 78653

Also, I believe that there a method that you can use with jConnect:

setBigDecimal(1,2343,18,0) -- for example

Here is an excerpt from the java docs for jConnect:

setBigDecimal
public void setBigDecimal(int parameterIndex,
BigDecimal x,
int precision,
int scale)
throws SQLExceptionThe NUMERIC datatype for Sybase has
explicit declarations for the precision (total number of digits) and scale
(digits after the decimal). The java.math.BigDecimal just implicitly figures
out what the scale is, and has no limit on precision. When jConnect converts
a 'setBigDecimal' parameter from BigDecimal to NUMERIC is has to guess on
precision and scale.
Unfortunately, if the parameter is being used for an in/out stored
procedure parameter the guess really matters and we often guess wrong.
Suppose the stored procedure is declared as follows:

CREATE PROCEDURE myNumericProc(@p1 NUMERIC(6,0) OUTPUT) AS
BEGIN
SELECT @p1 = @p1 * 10
END
If the input value is BigDecimal("1") jConnect will send a NUMERIC(1,0).
This results in a numeric overflow when the server returns 10 - doesn't fit.

OTOH, if we just decide to send the value as NUMERIC(38,0) - the maximum
precision, or any other precision > 6, the server will raise an error
because our precision is too high for the stored procedure declaration.

The only way to correct this would be to query the server on the declared
precision/scale of stored procedure parameters, but that would require
jConnect to have a much more complete SQL parser so it could determine
name/order of stored procedure and parameter name, and would incur a
significant performance overhead for additional round-trips to the database
to gather this information.

This alternate signature setBigDecimal extension allows a jConnect JDBC
application to explicitly indicate what NUMERIC precision and scale should
be used.


Parameters:
parameterIndex - index of the parameter being set.
x - value being sent
precision - the maximum number of digits in the value of x
scale - the maximum number of digits to the right of the decimal in the
value of x.
Throws:
SQLException - if precision > 38, precision < 1, or scale < 0, or
precision < scale, or if the value x does not fit in the given
precision/scale range specified.

"Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com> wrote in
message news:4c4f1974$1@forums-1-dub...
> Well, then I stand corrected. This is purely a datatype mismatch issue as
> Neal had originally suggested.
>
> If you can't change the datatypes of your table columns, the only
> workaround that I can think of - unless Neal has a better suggestion, is
>
> (1) change the SQL statement to convert your datatypes:
>
> PreparedStatement stmt = conn.prepareStatement("SELECT task_id FROM
> bo_task
> WHERE (task_id=convert(numeric(18,0),?) AND
> task_version>convert(numeric(18,0),?)) OR
> (task_id=convert(numeric(18,0),?) AND
> task_version>convert(numeric(18,0),?))");
>
> (2) instead of using prepared statements, execute regular SQL (and
> optionally deploy statement cache, literal parameters).
>
> (3) create a stored procedure, and call it with your parameters.
>
> "Schock Reno" <reno.schock@googlemail.com> wrote in message
> news:C874D5B4.2EAD%reno.schock@googlemail.com...
>>I went to the developers and we stepped through the code with a debugger.
>> The code works as follows:
>>
>> PreparedStatement stmt = conn.prepareStatement("SELECT task_id FROM
>> bo_task
>> WHERE (task_id=? AND task_version>?) OR (task_id=? AND task_version>?)");
>>
>> Afterwards the values are filled in like:
>>
>> stmt.setLong(1, 415678);
>> stmt.setInt(2, 0);
>> stmt.setLong(3, 415679);
>> stmt.setInt(4, 2);
>>
>> And at last it is executed like:
>>
>> ResultSet rs = stmt.executeQuery();
>>
>> The corresponding table fields are defined as
>>
>> Task_id numeric(18,0)
>> Task_version numeric(18,0)
>>
>> We tried to modify the two Int's to Long's with a debugger but there was
>> no
>> change. I hope this information may help in further investigation.
>>
>> Thank you very much and best regards
>> Reno
>>
>>
>> Am 27.07.10 17:16 schrieb "Sherlock, Kevin [TeamSybase]" unter
>> <kevin.sherlock@teamsybase.com> in 4c4ef847$1@forums-1-dub:
>>
>>> necessary data types? i didn't say that. i'm not sure what you're
>>> getting
>>> at there.
>>>
>>> My point is that your prepared statement uses unknown values for search
>>> criteria. The values for those parameters are only known at runtime,
>>> not
>>> compile time. However, i'm now wondering to myself when a query plan is
>>> compiled for a prepared statement. If it's at "prepare" time, or
>>> "execute"
>>> time. If the latter, then yes, I would wonder too about the datatypes
>>> of
>>> the parameters that you declare in your java code and if they match the
>>> datatypes of the columns in the search clause.
>>>
>>> Can you post the sp_help output of the table, and the section of java
>>> code
>>> that declares the parameters?
>>>
>>> "Schock Reno" <reno.schock@googlemail.com> wrote in message
>>> news:C874B763.22F2%reno.schock@googlemail.com...
>>>> This would mean, that a prepared statement would always guess it has to
>>>> use
>>>> e.g. the convert function to convert to the neccessary data type -
>>>> correct?
>>>> Is this a standard behaviour?
>>>>
>>>> Best regards
>>>> Reno
>>>>
>>>>
>>>> Am 27.07.10 15:55 schrieb "Sherlock, Kevin [TeamSybase]" unter
>>>> <kevin.sherlock@teamsybase.com> in 4c4ee542$1@forums-1-dub:
>>>>
>>>>> it's a difference in how one statement is optimized compared to the
>>>>> other.
>>>>> Your java prepared statement can only be optimized using unknown
>>>>> values
>>>>> (parameters), while your isql statement is using literal values and
>>>>> can
>>>>> be
>>>>> costed more accurately by the optimizer. This is an extremely common
>>>>> FAQ
>>>>> topic.
>>>>>
>>>>> "Schock Reno" <reno.schock@googlemail.com> wrote in message
>>>>> news:C8749829.22D6%reno.schock@googlemail.com...
>>>>>> Hi all,
>>>>>>
>>>>>> I've already asked this question in the jConnect/jdbc group and have
>>>>>> been
>>>>>> pointed to this group. Maybe someone has an answer or hint for my
>>>>>> problem.
>>>>>>
>>>>>> Quoted text from jConnect group:
>>>>>>
>>>>>> I have a performance issue using jConnect-6. A query (prepared then
>>>>>> executed) out of a Java application results in a diferent and worse
>>>>>> query
>>>>>> plan than executed directly from isql. As I cannot explain by myself
>>>>>> why
>>>>>> this happens, I'd welcome any hint to understand the different
>>>>>> behaviour.
>>>>>> Please see the examples below:
>>>>>>
>>>>>> From application (captured with 'set tracefile'):
>>>>>>
>>>>>> SQL Text: SELECT task_id FROM bo_task WHERE (task_id=@p0 AND
>>>>>> task_version>@p1) OR (task_id=@p2 AND task_version>@p3)ì
>>>>>>
>>>>>> QUERY PLAN FOR STATEMENT 1 (at line 0).
>>>>>>
>>>>>>
>>>>>> STEP 1
>>>>>> The type of query is DECLARE.
>>>>>>
>>>>>>
>>>>>> QUERY PLAN FOR STATEMENT 2 (at line 1).
>>>>>>
>>>>>>
>>>>>> STEP 1
>>>>>> The type of query is SELECT.
>>>>>>
>>>>>> 2 operator(s) under root
>>>>>>
>>>>>> |ROOT:EMIT Operator (VA = 2)
>>>>>> |
>>>>>> | |RESTRICT Operator (VA = 1)(9)(0)(0)(2)(0)
>>>>>> | |
>>>>>> | | |SCAN Operator (VA = 0)
>>>>>> | | | FROM TABLE
>>>>>> | | | bo_task
>>>>>> | | | Index : idx_bo_task_ver
>>>>>> | | | Forward Scan.
>>>>>> | | | Positioning at index start.
>>>>>> | | | Index contains all needed columns. Base table will
>>>>>> not
>>>>>> be
>>>>>> read.
>>>>>> | | | Using I/O Size 8 Kbytes for index leaf pages.
>>>>>> | | | With LRU Buffer Replacement Strategy for index leaf
>>>>>> pages.
>>>>>>
>>>>>> Adaptive Server cpu time: 3000 ms. Adaptive Server elapsed
>>>>>> time: 3033 ms.End of Batch 4400
>>>>>>
>>>>>> Directly executed from sqsh/isql with literals:
>>>>>>
>>>>>> SELECT task_id FROM bo_task WHERE (task_id=4112784 AND
>>>>>> task_version>0)
>>>>>> OR
>>>>>> (task_id=4112785 AND task_version>0)
>>>>>>
>>>>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>>>>
>>>>>>
>>>>>> STEP 1
>>>>>> The type of query is EXECUTE.
>>>>>> Executing a newly cached statement (SSQL_ID = 297203852).
>>>>>>
>>>>>> Parse and Compile Time 0.
>>>>>> Adaptive Server cpu time: 0 ms.
>>>>>>
>>>>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>>>>
>>>>>>
>>>>>> STEP 1
>>>>>> The type of query is SELECT.
>>>>>>
>>>>>> 4 operator(s) under root
>>>>>>
>>>>>> |ROOT:EMIT Operator (VA = 4)
>>>>>> |
>>>>>> | |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
>>>>>> | |
>>>>>> | | |SCAN Operator (VA = 0)
>>>>>> | | | FROM OR List
>>>>>> | | | OR List has up to 2 rows of OR/IN values.
>>>>>> | |
>>>>>> | | |RESTRICT Operator (VA = 2)(0)(0)(0)(7)(0)
>>>>>> | | |
>>>>>> | | | |SCAN Operator (VA = 1)
>>>>>> | | | | FROM TABLE
>>>>>> | | | | bo_task
>>>>>> | | | | Index : idx_bo_task_ver
>>>>>> | | | | Forward Scan.
>>>>>> | | | | Positioning by key.
>>>>>> | | | | Index contains all needed columns. Base table
>>>>>> will
>>>>>> not
>>>>>> be read.
>>>>>> | | | | Keys are:
>>>>>> | | | | task_id ASC
>>>>>> | | | | task_version ASC
>>>>>> | | | | Using I/O Size 8 Kbytes for index leaf pages.
>>>>>> | | | | With LRU Buffer Replacement Strategy
>>>>>> for index leaf pages.
>>>>>>
>>>>>> Adaptive Server cpu time: 0 ms. Adaptive Server elapsed
>>>>>> time: 3 ms.
>>>>>>
>>>>>> I do not understand, why there is no positioning by key in the first
>>>>>> example. Setting LITERAL_PARAMS to true is not an option for this
>>>>>> application as our developers mentioned. Any idea on this?
>>>>>>
>>>>>> Best regards
>>>>>> Reno
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>


Neal Stack [Sybase] Posted on 2010-07-27 13:28:27.0Z
Message-ID: <4C4EDEFB.4070808@nospam.com>
From: "Neal Stack [Sybase]" <nstack@nospam.com>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
To: Schock Reno <reno.schock@googlemail.com>
Subject: Re: Different query plans between prepared JDBC statement and isql
References: <C8749829.22D6%reno.schock@googlemail.com>
In-Reply-To: <C8749829.22D6%reno.schock@googlemail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 27 Jul 2010 06:28:27 -0700
X-Trace: forums-1-dub 1280237307 10.22.241.152 (27 Jul 2010 06:28:27 -0700)
X-Original-Trace: 27 Jul 2010 06:28:27 -0700, vip152.sybase.com
Lines: 9
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29411
Article PK: 78644

Hello,

Are the parameters in your jConnect application of the same data type as
in your ISQL test? Parameter data type mismatches can cause different
query plans.

--
Regards,
Neal


J Posted on 2010-07-27 17:08:32.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Different query plans between prepared JDBC statement and isql
Reply-To: J@bogusemailAddress.com
Message-ID: <4c4f11e1.529213093@forums.sybase.com>
References: <C8749829.22D6%reno.schock@googlemail.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 27 Jul 2010 10:08:32 -0700
X-Trace: forums-1-dub 1280250512 10.22.241.152 (27 Jul 2010 10:08:32 -0700)
X-Original-Trace: 27 Jul 2010 10:08:32 -0700, vip152.sybase.com
Lines: 128
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29419
Article PK: 78651

On 27 Jul 2010 05:05:35 -0700, Schock Reno
<reno.schock@googlemail.com> wrote:

To get the same behavior, don't use a prepared statement in jConnect.
You can use an ordinary query and then if you want to get even better
performance you could turn on "statement cache" and "literal
parameters" to let ASE cache the plan. This all depends on your
versions etc.

You cannot expect in all cases that statements with unknown parameters
are going to choose the same plans as statements where the values are
well known at the time the plan is built.

Maybe you have one bad case or maybe your statistics are way out of
line.

Jay

>Hi all,
>
>I've already asked this question in the jConnect/jdbc group and have been
>pointed to this group. Maybe someone has an answer or hint for my problem.
>
>Quoted text from jConnect group:
>
>I have a performance issue using jConnect-6. A query (prepared then
>executed) out of a Java application results in a diferent and worse query
>plan than executed directly from isql. As I cannot explain by myself why
>this happens, I'd welcome any hint to understand the different behaviour.
>Please see the examples below:
>
>From application (captured with 'set tracefile'):
>
>SQL Text: SELECT task_id FROM bo_task WHERE (task_id=@p0 AND
>task_version>@p1) OR (task_id=@p2 AND task_version>@p3)ì
>
>QUERY PLAN FOR STATEMENT 1 (at line 0).
>
>
> STEP 1
> The type of query is DECLARE.
>
>
>QUERY PLAN FOR STATEMENT 2 (at line 1).
>
>
> STEP 1
> The type of query is SELECT.
>
> 2 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 2)
> |
> | |RESTRICT Operator (VA = 1)(9)(0)(0)(2)(0)
> | |
> | | |SCAN Operator (VA = 0)
> | | | FROM TABLE
> | | | bo_task
> | | | Index : idx_bo_task_ver
> | | | Forward Scan.
> | | | Positioning at index start.
> | | | Index contains all needed columns. Base table will not be
>read.
> | | | Using I/O Size 8 Kbytes for index leaf pages.
> | | | With LRU Buffer Replacement Strategy for index leaf pages.
>
>Adaptive Server cpu time: 3000 ms. Adaptive Server elapsed
>time: 3033 ms.End of Batch 4400
>
>Directly executed from sqsh/isql with literals:
>
>SELECT task_id FROM bo_task WHERE (task_id=4112784 AND task_version>0) OR
>(task_id=4112785 AND task_version>0)
>
>QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is EXECUTE.
> Executing a newly cached statement (SSQL_ID = 297203852).
>
>Parse and Compile Time 0.
>Adaptive Server cpu time: 0 ms.
>
>QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is SELECT.
>
> 4 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 4)
> |
> | |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
> | |
> | | |SCAN Operator (VA = 0)
> | | | FROM OR List
> | | | OR List has up to 2 rows of OR/IN values.
> | |
> | | |RESTRICT Operator (VA = 2)(0)(0)(0)(7)(0)
> | | |
> | | | |SCAN Operator (VA = 1)
> | | | | FROM TABLE
> | | | | bo_task
> | | | | Index : idx_bo_task_ver
> | | | | Forward Scan.
> | | | | Positioning by key.
> | | | | Index contains all needed columns. Base table will not
>be read.
> | | | | Keys are:
> | | | | task_id ASC
> | | | | task_version ASC
> | | | | Using I/O Size 8 Kbytes for index leaf pages.
> | | | | With LRU Buffer Replacement Strategy
>for index leaf pages.
>
>Adaptive Server cpu time: 0 ms. Adaptive Server elapsed
>time: 3 ms.
>
>I do not understand, why there is no positioning by key in the first
>example. Setting LITERAL_PARAMS to true is not an option for this
>application as our developers mentioned. Any idea on this?
>
>Best regards
>Reno
>


Neal Stack [Sybase] Posted on 2010-07-28 14:24:47.0Z
Message-ID: <4C503DAF.7020408@nospam.com>
From: "Neal Stack [Sybase]" <nstack@nospam.com>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Different query plans between prepared JDBC statement and isql
References: <C8749829.22D6%reno.schock@googlemail.com> <4c4f11e1.529213093@forums.sybase.com>
In-Reply-To: <4c4f11e1.529213093@forums.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 28 Jul 2010 07:24:47 -0700
X-Trace: forums-1-dub 1280327087 10.22.241.152 (28 Jul 2010 07:24:47 -0700)
X-Original-Trace: 28 Jul 2010 07:24:47 -0700, vip152.sybase.com
Lines: 20
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29423
Article PK: 78655

Would using SybPreparedStatement() also be preferred?
http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc39001.0700/html/prjdbc0700/prjdbc070054.htm

try
{
PreparedStatement cstmt = _con.prepareStatement(query);
// Downcast this to a SybPreparedStatement so we can access
// the extension
SybPreparedStatement sps = (SybPreparedStatement) cstmt;
BigDecimal discountThreshold = new BigDecimal("8.5");
// force jConnect to send this big decimal with a precision of
// 5 digits, and a scale of 2 digits
sps.setBigDecimal(1, discountThreshold, 5, 2);
displayRows(sps);
cstmt.close();
}

--
Regards,
Neal


J Posted on 2010-07-28 15:43:17.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Different query plans between prepared JDBC statement and isql
Reply-To: J@bogusemailAddress.com
Message-ID: <4c504da3.610047640@forums.sybase.com>
References: <C8749829.22D6%reno.schock@googlemail.com> <4c4f11e1.529213093@forums.sybase.com> <4C503DAF.7020408@nospam.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 28 Jul 2010 08:43:17 -0700
X-Trace: forums-1-dub 1280331797 10.22.241.152 (28 Jul 2010 08:43:17 -0700)
X-Original-Trace: 28 Jul 2010 08:43:17 -0700, vip152.sybase.com
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29424
Article PK: 78656

On 28 Jul 2010 07:24:47 -0700, "Neal Stack [Sybase]"
<nstack@nospam.com> wrote:

Neal,

The option that you point out is a very good one and I had overlooked
it.


The whole idea of preparing a statement is to get a plan that can be
executed over and over without the re-optimization. If one avoids the
"prepare" and uses statement cache and literal parameters enabled then
you meet that objective with less chatter on the wire with ordinary
<Statement>.execute().

Now they 3 choices. (use the convert() in the Prepare being the 3rd
choice).

Jay

>Would using SybPreparedStatement() also be preferred?
> http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc39001.0700/html/prjdbc0700/prjdbc070054.htm
>
> try
> {
> PreparedStatement cstmt = _con.prepareStatement(query);
> // Downcast this to a SybPreparedStatement so we can access
> // the extension
> SybPreparedStatement sps = (SybPreparedStatement) cstmt;
> BigDecimal discountThreshold = new BigDecimal("8.5");
> // force jConnect to send this big decimal with a precision of
> // 5 digits, and a scale of 2 digits
> sps.setBigDecimal(1, discountThreshold, 5, 2);
> displayRows(sps);
> cstmt.close();
> }
>
>--
>Regards,
> Neal