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.

SQl Advantage query execution time

8 posts in General Discussion Last posting was on 2010-07-20 17:15:36.0Z
res Posted on 2010-06-09 08:18:11.0Z
Sender: 17cf.4c0f4dc2.1804289383@sybase.com
From: res
Newsgroups: sybase.public.ase.general
Subject: SQl Advantage query execution time
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c0f4e43.17d9.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 9 Jun 2010 01:18:11 -0700
X-Trace: forums-1-dub 1276071491 10.22.241.41 (9 Jun 2010 01:18:11 -0700)
X-Original-Trace: 9 Jun 2010 01:18:11 -0700, 10.22.241.41
Lines: 9
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29290
Article PK: 78524

Hi All

I have a simple select statement which when executed via SQL
Advantage takes quite a bit of time while in PB it's
retrieved in no time. When the sql is coded as a SP, the
execution time is really annoying. Please any ideas?

Thanks
Res


Sherlock, Kevin [TeamSybase] Posted on 2010-06-09 13:44:46.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4c0f4e43.17d9.1681692777@sybase.com>
Subject: Re: SQl Advantage query execution time
Lines: 24
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c0f9ace$1@forums-1-dub>
Date: 9 Jun 2010 06:44:46 -0700
X-Trace: forums-1-dub 1276091086 10.22.241.152 (9 Jun 2010 06:44:46 -0700)
X-Original-Trace: 9 Jun 2010 06:44:46 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29291
Article PK: 78525

more than likely it would have to do with the way the query is optimized
when you use a variable vs a literal in a search clause. So, I have some
doubt as to whether the two statements are the same.

Can you post the example of the query that PB executes, and the exact stored
procedure code that runs slower?

Hint: if you use a variable (unknown value) in the stored procedure, the
optimizer makes some guesses as to which is the better access path to the
table. You might also want to post the DDL for the tables, and indexes
involved.

<res> wrote in message news:4c0f4e43.17d9.1681692777@sybase.com...
> Hi All
>
> I have a simple select statement which when executed via SQL
> Advantage takes quite a bit of time while in PB it's
> retrieved in no time. When the sql is coded as a SP, the
> execution time is really annoying. Please any ideas?
>
> Thanks
> Res


res Posted on 2010-06-13 05:53:11.0Z
Sender: 3091.4c146008.1804289383@sybase.com
From: res
Newsgroups: sybase.public.ase.general
Subject: Re: SQl Advantage query execution time
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c147247.3249.1681692777@sybase.com>
References: <4c0f9ace$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 12 Jun 2010 22:53:11 -0700
X-Trace: forums-1-dub 1276408391 10.22.241.41 (12 Jun 2010 22:53:11 -0700)
X-Original-Trace: 12 Jun 2010 22:53:11 -0700, 10.22.241.41
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29298
Article PK: 78531

Thanks for the reply.

As i wrote, it's a simple select with only 3 fields namely
jobno, blno & container number from a base table. the same
select I included as a sp and while running it's causing the
delay. but when the select is executed from PB, the results
are retrieved in no time.

Regards
Res

> more than likely it would have to do with the way the
> query is optimized when you use a variable vs a literal
> in a search clause. So, I have some doubt as to whether
> the two statements are the same.
>
> Can you post the example of the query that PB executes,
> and the exact stored procedure code that runs slower?
>
> Hint: if you use a variable (unknown value) in the stored
> procedure, the optimizer makes some guesses as to which
> is the better access path to the table. You might also
> want to post the DDL for the tables, and indexes
> involved.
>
> <res> wrote in message
> > news:4c0f4e43.17d9.1681692777@sybase.com... Hi All
> >
> > I have a simple select statement which when executed via
> > SQL Advantage takes quite a bit of time while in PB it's
> > retrieved in no time. When the sql is coded as a SP,
> > the execution time is really annoying. Please any
> ideas? >
> > Thanks
> > Res
>
>


Sherlock, Kevin [TeamSybase] Posted on 2010-06-14 15:18:45.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4c0f9ace$1@forums-1-dub> <4c147247.3249.1681692777@sybase.com>
Subject: Re: SQl Advantage query execution time
Lines: 43
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c164855$1@forums-1-dub>
Date: 14 Jun 2010 08:18:45 -0700
X-Trace: forums-1-dub 1276528725 10.22.241.152 (14 Jun 2010 08:18:45 -0700)
X-Original-Trace: 14 Jun 2010 08:18:45 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29299
Article PK: 78533

If you would like help on this, please post the EXACT code for the stored
procedure, and the EXACT code for the "select" statement coded in PB.

<res> wrote in message news:4c147247.3249.1681692777@sybase.com...
> Thanks for the reply.
>
> As i wrote, it's a simple select with only 3 fields namely
> jobno, blno & container number from a base table. the same
> select I included as a sp and while running it's causing the
> delay. but when the select is executed from PB, the results
> are retrieved in no time.
>
> Regards
> Res
>
>> more than likely it would have to do with the way the
>> query is optimized when you use a variable vs a literal
>> in a search clause. So, I have some doubt as to whether
>> the two statements are the same.
>>
>> Can you post the example of the query that PB executes,
>> and the exact stored procedure code that runs slower?
>>
>> Hint: if you use a variable (unknown value) in the stored
>> procedure, the optimizer makes some guesses as to which
>> is the better access path to the table. You might also
>> want to post the DDL for the tables, and indexes
>> involved.
>>
>> <res> wrote in message
>> > news:4c0f4e43.17d9.1681692777@sybase.com... Hi All
>> >
>> > I have a simple select statement which when executed via
>> > SQL Advantage takes quite a bit of time while in PB it's
>> > retrieved in no time. When the sql is coded as a SP,
>> > the execution time is really annoying. Please any
>> ideas? >
>> > Thanks
>> > Res
>>
>>


J Posted on 2010-06-14 16:53:31.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: SQl Advantage query execution time
Reply-To: J@bogusemailAddress.com
Message-ID: <4c165de5.871620656@forums.sybase.com>
References: <4c0f9ace$1@forums-1-dub> <4c147247.3249.1681692777@sybase.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: 14 Jun 2010 09:53:31 -0700
X-Trace: forums-1-dub 1276534411 10.22.241.152 (14 Jun 2010 09:53:31 -0700)
X-Original-Trace: 14 Jun 2010 09:53:31 -0700, vip152.sybase.com
Lines: 53
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29300
Article PK: 78534

On 12 Jun 2010 22:53:11 -0700, res wrote:

Is the select qualified? Capture the exact sql being used. One can
get very different results when literals are used vs parameters
because the plans have to take into account the selectivity of the
data.

Also you have said nothing about the amount of data being returned so
maybe one tool caches all the results before displaying any of it etc.


You need to provide many more details.

Jay

>Thanks for the reply.
>
>As i wrote, it's a simple select with only 3 fields namely
>jobno, blno & container number from a base table. the same
>select I included as a sp and while running it's causing the
>delay. but when the select is executed from PB, the results
>are retrieved in no time.
>
>Regards
>Res
>
>> more than likely it would have to do with the way the
>> query is optimized when you use a variable vs a literal
>> in a search clause. So, I have some doubt as to whether
>> the two statements are the same.
>>
>> Can you post the example of the query that PB executes,
>> and the exact stored procedure code that runs slower?
>>
>> Hint: if you use a variable (unknown value) in the stored
>> procedure, the optimizer makes some guesses as to which
>> is the better access path to the table. You might also
>> want to post the DDL for the tables, and indexes
>> involved.
>>
>> <res> wrote in message
>> > news:4c0f4e43.17d9.1681692777@sybase.com... Hi All
>> >
>> > I have a simple select statement which when executed via
>> > SQL Advantage takes quite a bit of time while in PB it's
>> > retrieved in no time. When the sql is coded as a SP,
>> > the execution time is really annoying. Please any
>> ideas? >
>> > Thanks
>> > Res
>>
>>


res Posted on 2010-07-20 09:33:07.0Z
Sender: 3c28.4c456bc6.1804289383@sybase.com
From: res
Newsgroups: sybase.public.ase.general
Subject: Re: SQl Advantage query execution time
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c456d53.3c61.1681692777@sybase.com>
References: <4c165de5.871620656@forums.sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 20 Jul 2010 02:33:07 -0700
X-Trace: forums-1-dub 1279618387 10.22.241.41 (20 Jul 2010 02:33:07 -0700)
X-Original-Trace: 20 Jul 2010 02:33:07 -0700, 10.22.241.41
Lines: 68
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29381
Article PK: 78611

Let me explain my problem once more:

1. In ASE i've written a stored procedure with only a select
statement in it. ie select jobno, bl_no, container from
table_bl.
2. executed the procedure from sql advantage, it's taking a
long time.
3. the same procedure call is given from Power Builder's
Database Inteface profile's ISQL query entry area, results
are displayed in seconds time.

Hope this is more clear now
Thanks
Res

> On 12 Jun 2010 22:53:11 -0700, res wrote:
>
> Is the select qualified? Capture the exact sql being
> used. One can get very different results when literals are
> used vs parameters because the plans have to take into
> account the selectivity of the data.
>
> Also you have said nothing about the amount of data being
> returned so maybe one tool caches all the results before
> displaying any of it etc.
>
>
> You need to provide many more details.
>
> Jay
>
> >Thanks for the reply.
> >
> >As i wrote, it's a simple select with only 3 fields
> namely >jobno, blno & container number from a base table.
> the same >select I included as a sp and while running it's
> causing the >delay. but when the select is executed from
> PB, the results >are retrieved in no time.
> >
> >Regards
> >Res
> >
> >> more than likely it would have to do with the way the
> >> query is optimized when you use a variable vs a
> literal >> in a search clause. So, I have some doubt as
> to whether >> the two statements are the same.
> >>
> >> Can you post the example of the query that PB executes,
> >> and the exact stored procedure code that runs slower?
> >>
> >> Hint: if you use a variable (unknown value) in the
> stored >> procedure, the optimizer makes some guesses as
> to which >> is the better access path to the table. You
> might also >> want to post the DDL for the tables, and
> indexes >> involved.
> >>
> >> <res> wrote in message
> >> > news:4c0f4e43.17d9.1681692777@sybase.com... Hi All
> >> >
> >> > I have a simple select statement which when executed
> via >> > SQL Advantage takes quite a bit of time while in
> PB it's >> > retrieved in no time. When the sql is coded
> as a SP, >> > the execution time is really annoying.
> Please any >> ideas? >
> >> > Thanks
> >> > Res
> >>
> >>
>


J Posted on 2010-07-20 15:28:00.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: SQl Advantage query execution time
Reply-To: J@bogusemailAddress.com
Message-ID: <4c45be1e.2653578@forums.sybase.com>
References: <4c165de5.871620656@forums.sybase.com> <4c456d53.3c61.1681692777@sybase.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: 20 Jul 2010 08:28:00 -0700
X-Trace: forums-1-dub 1279639680 10.22.241.152 (20 Jul 2010 08:28:00 -0700)
X-Original-Trace: 20 Jul 2010 08:28:00 -0700, vip152.sybase.com
Lines: 107
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29382
Article PK: 78612

On 20 Jul 2010 02:33:07 -0700, res wrote:

I don't know off the top of my head anything about the two different
client applications and the details of how they would execute the
queries. There can be pretty big differences.

For example.

One might execute a store procedure like this:

A. "myproc 123, 456"

This could be sent to the dataserver as a "language event". The values
of 123 and 456 would be character literals.

The dataserver would know the values of the literals and could
construct a plan for accessing the data which could use this
information as it has statistics on various columns.

on the other hand..

B. could use a parameterized RPC (remote procedure call). Saying that
it will execute an rpc called "myproc" with parameters supplied as
values later. Something like "exec RPC(myproc) ?, ?"

These small differences can account for huge differences in access.

Some people troubleshoot these problems by looking at the showplan
outputs other like to look at the details of the queries that are sent
to the dataserver thru Ribo or some tool that captures the calls back
and forth to the dataserver. I am of the later group but in any case
some more details will help isolate the difference.

Learnng to use Ribo is well worth the time unless you have another
tool that gives the same kind of details.

Jay

>Let me explain my problem once more:
>
>1. In ASE i've written a stored procedure with only a select
>statement in it. ie select jobno, bl_no, container from
>table_bl.
>2. executed the procedure from sql advantage, it's taking a
>long time.
>3. the same procedure call is given from Power Builder's
>Database Inteface profile's ISQL query entry area, results
>are displayed in seconds time.
>
>Hope this is more clear now
>Thanks
>Res
>> On 12 Jun 2010 22:53:11 -0700, res wrote:
>>
>> Is the select qualified? Capture the exact sql being
>> used. One can get very different results when literals are
>> used vs parameters because the plans have to take into
>> account the selectivity of the data.
>>
>> Also you have said nothing about the amount of data being
>> returned so maybe one tool caches all the results before
>> displaying any of it etc.
>>
>>
>> You need to provide many more details.
>>
>> Jay
>>
>> >Thanks for the reply.
>> >
>> >As i wrote, it's a simple select with only 3 fields
>> namely >jobno, blno & container number from a base table.
>> the same >select I included as a sp and while running it's
>> causing the >delay. but when the select is executed from
>> PB, the results >are retrieved in no time.
>> >
>> >Regards
>> >Res
>> >
>> >> more than likely it would have to do with the way the
>> >> query is optimized when you use a variable vs a
>> literal >> in a search clause. So, I have some doubt as
>> to whether >> the two statements are the same.
>> >>
>> >> Can you post the example of the query that PB executes,
>> >> and the exact stored procedure code that runs slower?
>> >>
>> >> Hint: if you use a variable (unknown value) in the
>> stored >> procedure, the optimizer makes some guesses as
>> to which >> is the better access path to the table. You
>> might also >> want to post the DDL for the tables, and
>> indexes >> involved.
>> >>
>> >> <res> wrote in message
>> >> > news:4c0f4e43.17d9.1681692777@sybase.com... Hi All
>> >> >
>> >> > I have a simple select statement which when executed
>> via >> > SQL Advantage takes quite a bit of time while in
>> PB it's >> > retrieved in no time. When the sql is coded
>> as a SP, >> > the execution time is really annoying.
>> Please any >> ideas? >
>> >> > Thanks
>> >> > Res
>> >>
>> >>
>>


"Mark A. Parsons" <iron_horse Posted on 2010-07-20 17:15:36.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: SQl Advantage query execution time
References: <4c165de5.871620656@forums.sybase.com> <4c456d53.3c61.1681692777@sybase.com>
In-Reply-To: <4c456d53.3c61.1681692777@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
Message-ID: <4c45d9b8$1@forums-1-dub>
Date: 20 Jul 2010 10:15:36 -0700
X-Trace: forums-1-dub 1279646136 10.22.241.152 (20 Jul 2010 10:15:36 -0700)
X-Original-Trace: 20 Jul 2010 10:15:36 -0700, vip152.sybase.com
Lines: 108
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29383
Article PK: 78615

By 'same procedure call' do you also mean that the same exact input parameters are provided for both procedure calls?

Are both applications being run from the same machine and therefore using the same network connection(s) to the dataserver?

Assuming the same exact procedure call, with the same exact input parameters, and using the same exact plan from
procedure cache, executing from the same machine ... I'd guess there's an issue with the SQL Advantage application (and
how it's configured).

To know for sure, and assuming the dataserver is configured properly for MDA table usage, I'd suggest having both
applications start a new connection to the dataserver, run the proc, and then from isql (or some other app) query the
following MDA pipe tables immediately after each proc has completed its run:

monSysStatement - cpu and IO stats for each statement executed by the session; also shows stored proc query plan id #
(is it the same for both sessions?)

monSysPlanText - all query plans used by each session

monSysSQLText - all SQL text submitted by each session (may not be visible if application is submitting prepared
statements and/or RPCs)

------------

You haven't mentioned how long is 'long time' for the SQL Advantage version.

If it's 5+ seconds then you should be able to query some additional resources while it's running in order to get a
better idea of what's going on:

monProcessActivity - preference is to poll this a couple times in order to a feel for how the numbers are changing

monProcessObject - preference is to poll this a couple times in order to see up-to-date activity against the table(s)
being accessed by the currently running query

monProcessWaits - preference is to poll this a few times in order to see up-to-date wait activity for the SPID in question

sysprocesses - 'status' column will be of interest, eg, 'send sleep' will indicate either an issue with the network or a
client/front-end application that's not processing it's result set very quickly/efficiently

sp_showplan - capture query plan of statement currently being run by a given session

Obviously this is a bit easier if you've got all of this in a T-SQL script that you can kick off to continuously loop
while the SQL Advantage version is running.

res wrote:
> Let me explain my problem once more:
>
> 1. In ASE i've written a stored procedure with only a select
> statement in it. ie select jobno, bl_no, container from
> table_bl.
> 2. executed the procedure from sql advantage, it's taking a
> long time.
> 3. the same procedure call is given from Power Builder's
> Database Inteface profile's ISQL query entry area, results
> are displayed in seconds time.
>
> Hope this is more clear now
> Thanks
> Res
>> On 12 Jun 2010 22:53:11 -0700, res wrote:
>>
>> Is the select qualified? Capture the exact sql being
>> used. One can get very different results when literals are
>> used vs parameters because the plans have to take into
>> account the selectivity of the data.
>>
>> Also you have said nothing about the amount of data being
>> returned so maybe one tool caches all the results before
>> displaying any of it etc.
>>
>>
>> You need to provide many more details.
>>
>> Jay
>>
>>> Thanks for the reply.
>>>
>>> As i wrote, it's a simple select with only 3 fields
>> namely >jobno, blno & container number from a base table.
>> the same >select I included as a sp and while running it's
>> causing the >delay. but when the select is executed from
>> PB, the results >are retrieved in no time.
>>> Regards
>>> Res
>>>
>>>> more than likely it would have to do with the way the
>>>> query is optimized when you use a variable vs a
>> literal >> in a search clause. So, I have some doubt as
>> to whether >> the two statements are the same.
>>>> Can you post the example of the query that PB executes,
>>>> and the exact stored procedure code that runs slower?
>>>>
>>>> Hint: if you use a variable (unknown value) in the
>> stored >> procedure, the optimizer makes some guesses as
>> to which >> is the better access path to the table. You
>> might also >> want to post the DDL for the tables, and
>> indexes >> involved.
>>>> <res> wrote in message
>>>>> news:4c0f4e43.17d9.1681692777@sybase.com... Hi All
>>>>>
>>>>> I have a simple select statement which when executed
>> via >> > SQL Advantage takes quite a bit of time while in
>> PB it's >> > retrieved in no time. When the sql is coded
>> as a SP, >> > the execution time is really annoying.
>> Please any >> ideas? >
>>>>> Thanks
>>>>> Res
>>>>