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 batch vs. individual commands

7 posts in General Discussion Last posting was on 2009-09-01 19:30:34.0Z
John Flynn Posted on 2009-08-11 19:10:13.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
Subject: SQL batch vs. individual commands
Lines: 32
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.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a81c215$1@forums-3-dub.sybase.com>
Date: 11 Aug 2009 12:10:13 -0700
X-Trace: forums-3-dub.sybase.com 1250017813 10.22.241.152 (11 Aug 2009 12:10:13 -0700)
X-Original-Trace: 11 Aug 2009 12:10:13 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28133
Article PK: 77381

Hi.

I am most familiar with ASE 11.9.2 and I am becoming familiar with ASE 15. A
coworker has observed what seems like a big difference in behavior between
the two.

He has a series of SQL commands, that is basically like this: Create and
populate a temporary table, then run a big select query that joins the
temporary table to some other big tables. The difference comes in whether
it's all submitted in one command batch or two. (And here, by "batch" I'm
referring to the quantity of SQL one would type into isql before typing
"go".)

In ASE 11, I am almost positive that there was never any difference. I.e. I
could create and populate the temp table in one batch, then submit the big
select in a second batch, and it ran in exactly the same way as if I had
submitted it all in one big batch. In practice, I never really needed to pay
attention to how I batched my SQL commands. Sybase appeared to not care.

But in ASE 15, there appears to be a big difference. If I create and
populate the temp table in one batch, and submit the big select in a second
batch, the whole shebang runs instantaneously. But if I submit all of it in
one big batch, it takes several seconds to run.

Before we investigate this further, I was wondering if this is a known
difference between ASE 11 and 15? Or is it impossible, and we're just making
a dumb mistake somewhere?

Thanks.
- John.


Rob V Posted on 2009-08-11 22:18:53.0Z
Reply-To: "Rob V" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4a81c215$1@forums-3-dub.sybase.com>
Subject: Re: SQL batch vs. individual commands
Lines: 65
Organization: Sypron BV / TeamSybase / Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a81ee4d$3@forums-3-dub.sybase.com>
Date: 11 Aug 2009 15:18:53 -0700
X-Trace: forums-3-dub.sybase.com 1250029133 10.22.241.152 (11 Aug 2009 15:18:53 -0700)
X-Original-Trace: 11 Aug 2009 15:18:53 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28140
Article PK: 77384

Can't say much from here.
What exact ASE 15 version are you using?
And what optimization goal is active?

It would be interesting to compare the query plan of that big select in your
two cases in ASE 15, as well as the output you get with 'set statistics
timeon' and 'set statistics resource on' when running both cases.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

"John Flynn" <jflynn@miqs.com> wrote in message
news:4a81c215$1@forums-3-dub.sybase.com...
> Hi.
>
> I am most familiar with ASE 11.9.2 and I am becoming familiar with ASE 15.
> A coworker has observed what seems like a big difference in behavior
> between the two.
>
> He has a series of SQL commands, that is basically like this: Create and
> populate a temporary table, then run a big select query that joins the
> temporary table to some other big tables. The difference comes in whether
> it's all submitted in one command batch or two. (And here, by "batch" I'm
> referring to the quantity of SQL one would type into isql before typing
> "go".)
>
> In ASE 11, I am almost positive that there was never any difference. I.e.
> I could create and populate the temp table in one batch, then submit the
> big select in a second batch, and it ran in exactly the same way as if I
> had submitted it all in one big batch. In practice, I never really needed
> to pay attention to how I batched my SQL commands. Sybase appeared to not
> care.
>
> But in ASE 15, there appears to be a big difference. If I create and
> populate the temp table in one batch, and submit the big select in a
> second batch, the whole shebang runs instantaneously. But if I submit all
> of it in one big batch, it takes several seconds to run.
>
> Before we investigate this further, I was wondering if this is a known
> difference between ASE 11 and 15? Or is it impossible, and we're just
> making a dumb mistake somewhere?
>
> Thanks.
> - John.
>
>


Cory Sane [TeamSybase] Posted on 2009-08-12 05:11:07.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.general
References: <4a81c215$1@forums-3-dub.sybase.com>
In-Reply-To: <4a81c215$1@forums-3-dub.sybase.com>
Subject: Re: SQL batch vs. individual commands
Lines: 43
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a824eeb$4@forums-3-dub.sybase.com>
Date: 11 Aug 2009 22:11:07 -0700
X-Trace: forums-3-dub.sybase.com 1250053867 10.22.241.152 (11 Aug 2009 22:11:07 -0700)
X-Original-Trace: 11 Aug 2009 22:11:07 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28141
Article PK: 77385

Beware that interactive sessions (isql) work much differently than stored procedures.
I've found that a stored procedure will stop in mid-run and rebuild the query plan due to a new index or other event BUT an
interactive session (isql) may not do the same.

These may run differently.
Create #table
insert #table
create index i1 on #table(c1)
select c1 from #table

A single proc will see the index but a isql session may not.


--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

"John Flynn" <jflynn@miqs.com> wrote in message news:4a81c215$1@forums-3-dub.sybase.com...
> Hi.
>
> I am most familiar with ASE 11.9.2 and I am becoming familiar with ASE 15. A coworker has observed what seems like a big
> difference in behavior between the two.
>
> He has a series of SQL commands, that is basically like this: Create and populate a temporary table, then run a big select
> query that joins the temporary table to some other big tables. The difference comes in whether it's all submitted in one
> command batch or two. (And here, by "batch" I'm referring to the quantity of SQL one would type into isql before typing "go".)
>
> In ASE 11, I am almost positive that there was never any difference. I.e. I could create and populate the temp table in one
> batch, then submit the big select in a second batch, and it ran in exactly the same way as if I had submitted it all in one
> big batch. In practice, I never really needed to pay attention to how I batched my SQL commands. Sybase appeared to not care.
>
> But in ASE 15, there appears to be a big difference. If I create and populate the temp table in one batch, and submit the big
> select in a second batch, the whole shebang runs instantaneously. But if I submit all of it in one big batch, it takes several
> seconds to run.
>
> Before we investigate this further, I was wondering if this is a known difference between ASE 11 and 15? Or is it impossible,
> and we're just making a dumb mistake somewhere?
>
> Thanks.
> - John.
>
>


Carl Kayser Posted on 2009-08-12 10:06:24.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4a81c215$1@forums-3-dub.sybase.com> <4a824eeb$4@forums-3-dub.sybase.com>
Subject: Re: SQL batch vs. individual commands
Lines: 33
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a829420$4@forums-3-dub.sybase.com>
Date: 12 Aug 2009 03:06:24 -0700
X-Trace: forums-3-dub.sybase.com 1250071584 10.22.241.152 (12 Aug 2009 03:06:24 -0700)
X-Original-Trace: 12 Aug 2009 03:06:24 -0700, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28143
Article PK: 77387


"Cory Sane [TeamSybase]" <cory!=sane> wrote in message
news:4a824eeb$4@forums-3-dub.sybase.com...
> Beware that interactive sessions (isql) work much differently than stored
> procedures.
> I've found that a stored procedure will stop in mid-run and rebuild the
> query plan due to a new index or other event BUT an interactive session
> (isql) may not do the same.
>
> These may run differently.
> Create #table
> insert #table
> create index i1 on #table(c1)
> select c1 from #table
>
> A single proc will see the index but a isql session may not.
>
>
> --
> Cory Sane
> [TeamSybase]
> Certified Sybase Associate DBA for ASE 15.0
> "John Flynn" <jflynn@miqs.com> wrote in message
> news:4a81c215$1@forums-3-dub.sybase.com...
>> Hi.

(SNIP)

Deferred compilation (on by default) may also be a factor. See
http://blogs.sybase.com/database/2009/07/avoiding-headaches-with-deferred-compilation-in-ase-1502/


Rob V Posted on 2009-08-12 11:05:04.0Z
Reply-To: "Rob V" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4a81c215$1@forums-3-dub.sybase.com> <4a824eeb$4@forums-3-dub.sybase.com> <4a829420$4@forums-3-dub.sybase.com>
Subject: Re: SQL batch vs. individual commands
Lines: 60
Organization: Sypron BV / TeamSybase / Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a82a1e0$3@forums-3-dub.sybase.com>
Date: 12 Aug 2009 04:05:04 -0700
X-Trace: forums-3-dub.sybase.com 1250075104 10.22.241.152 (12 Aug 2009 04:05:04 -0700)
X-Original-Trace: 12 Aug 2009 04:05:04 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28145
Article PK: 77388


"Carl Kayser" <kayser_c@bls.gov> wrote in message
news:4a829420$4@forums-3-dub.sybase.com...
>
> "Cory Sane [TeamSybase]" <cory!=sane> wrote in message
> news:4a824eeb$4@forums-3-dub.sybase.com...
>> Beware that interactive sessions (isql) work much differently than stored
>> procedures.
>> I've found that a stored procedure will stop in mid-run and rebuild the
>> query plan due to a new index or other event BUT an interactive session
>> (isql) may not do the same.
>>
>> These may run differently.
>> Create #table
>> insert #table
>> create index i1 on #table(c1)
>> select c1 from #table
>>
>> A single proc will see the index but a isql session may not.
>>
>>
>> --
>> Cory Sane
>> [TeamSybase]
>> Certified Sybase Associate DBA for ASE 15.0
>> "John Flynn" <jflynn@miqs.com> wrote in message
>> news:4a81c215$1@forums-3-dub.sybase.com...
>>> Hi.
>
>
> (SNIP)
>
> Deferred compilation (on by default) may also be a factor. See
> http://blogs.sybase.com/database/2009/07/avoiding-headaches-with-deferred-compilation-in-ase-1502/
>

Actually, deferred compilation won't be a factor in what is described, since
DC applies to stored procs only, not to batches.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------


John Flynn Posted on 2009-08-12 13:30:14.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
References: <4a81c215$1@forums-3-dub.sybase.com> <4a824eeb$4@forums-3-dub.sybase.com>
Subject: Re: SQL batch vs. individual commands
Lines: 15
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.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a82c3e6$4@forums-3-dub.sybase.com>
Date: 12 Aug 2009 06:30:14 -0700
X-Trace: forums-3-dub.sybase.com 1250083814 10.22.241.152 (12 Aug 2009 06:30:14 -0700)
X-Original-Trace: 12 Aug 2009 06:30:14 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28148
Article PK: 77392


Cory Sane [TeamSybase] wrote:
> Beware that interactive sessions (isql) work much differently than
> stored procedures. I've found that a stored procedure will stop in mid-run
> and rebuild
> the query plan due to a new index or other event BUT an interactive
> session (isql) may not do the same.

I have certainly been bitten by that before!

But in this case there are no stored procedures.

Thanks.
- John.


John Flynn Posted on 2009-09-01 19:30:34.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
References: <4a81c215$1@forums-3-dub.sybase.com>
Subject: Re: SQL batch vs. individual commands
Lines: 16
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a9d765a$1@forums-1-dub>
Date: 1 Sep 2009 12:30:34 -0700
X-Trace: forums-1-dub 1251833434 10.22.241.152 (1 Sep 2009 12:30:34 -0700)
X-Original-Trace: 1 Sep 2009 12:30:34 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28261
Article PK: 77505

Well, my coworker is now not 100% sure that he was not smoking crack that
day when he claimed to see the problem I described in the original post. And
since then the symptom does not appear to be occurring anymore, and we are
not motivated to restore all the conditions to a point where we can
reproduce the symptom and investigate this further. Also it seemed at the
time that the symptom might have changed depending on which client tool he
was using to submit the SQL batch (isql vs. SqlDbx vs. SQL Advantage), which
in and of itself is suspicious.

So for now, we're going to forget about this and just wait for it to happen
again. I feel comfortable doing that since no one here seemed to be familiar
with the issue.

Thanks.