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.

Tuning queries in stored procedure

2 posts in General Discussion Last posting was on 2011-10-20 14:30:32.0Z
George Brink Posted on 2011-10-20 14:24:15.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Tuning queries in stored procedure
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: <4ea02f0f@forums-1-dub>
Date: 20 Oct 2011 07:24:15 -0700
X-Trace: forums-1-dub 1319120655 10.22.241.152 (20 Oct 2011 07:24:15 -0700)
X-Original-Trace: 20 Oct 2011 07:24:15 -0700, vip152.sybase.com
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30626
Article PK: 73517

Adaptive Server Enterprise/15.0.3/EBF 17775 ESD#4/P/RS6000/AIX
5.3/ase1503/2768/64-bit/FBO/Thu Aug 26 07:36:52 2010

Question: How to gather plans for all queries performed by stored procedure?

I have a very large stored procedure which uses a lot of temporary
tables (created inside SP itself), so running queries one-by-one outside
of SP does not look like a good idea.


"Mark A. Parsons" <iron_horse Posted on 2011-10-20 14:30:32.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Tuning queries in stored procedure
References: <4ea02f0f@forums-1-dub>
In-Reply-To: <4ea02f0f@forums-1-dub>
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: <4ea03088$1@forums-1-dub>
Date: 20 Oct 2011 07:30:32 -0700
X-Trace: forums-1-dub 1319121032 10.22.241.152 (20 Oct 2011 07:30:32 -0700)
X-Original-Trace: 20 Oct 2011 07:30:32 -0700, vip152.sybase.com
Lines: 17
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30627
Article PK: 73518

A very basic method:

===============================
set showplan on
set statistics io,time on
go
exec <procname> ... args ...
go
===============================

On 10/20/2011 10:24, George Brink wrote:
> Adaptive Server Enterprise/15.0.3/EBF 17775 ESD#4/P/RS6000/AIX 5.3/ase1503/2768/64-bit/FBO/Thu Aug 26 07:36:52 2010
>
> Question: How to gather plans for all queries performed by stored procedure?
>
> I have a very large stored procedure which uses a lot of temporary tables (created inside SP itself), so running queries
> one-by-one outside of SP does not look like a good idea.