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.

sp_recompile or recreate a stored procedure?

3 posts in General Discussion Last posting was on 2011-12-12 17:16:41.0Z
RayLee Posted on 2011-12-12 16:21:51.0Z
From: RayLee <pp@hot.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: sp_recompile or recreate a 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: <4ee62a1f$1@forums-1-dub>
Date: 12 Dec 2011 08:21:51 -0800
X-Trace: forums-1-dub 1323706911 10.22.241.152 (12 Dec 2011 08:21:51 -0800)
X-Original-Trace: 12 Dec 2011 08:21:51 -0800, vip152.sybase.com
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30756
Article PK: 73648

Hi,

I have a table and some stored procedures reference on this table.
Today, I need to drop the table and recreate the table with some
additional columns, I would like to know if I can issue sp_recompile
command so that those stored procedures reference on the table can
recompile without being recreated?

Or I need to drop and recreate the stored procedures again?

Please kindly give me some advice on it.

I am using sybase 12.5.3 database.

Thanks,
Ray


Bret Halford Posted on 2011-12-12 17:10:00.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: sp_recompile or recreate a stored procedure?
References: <4ee62a1f$1@forums-1-dub>
In-Reply-To: <4ee62a1f$1@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: <4ee63568$1@forums-1-dub>
Date: 12 Dec 2011 09:10:00 -0800
X-Trace: forums-1-dub 1323709800 10.22.241.152 (12 Dec 2011 09:10:00 -0800)
X-Original-Trace: 12 Dec 2011 09:10:00 -0800, vip152.sybase.com
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30757
Article PK: 73646


On 12/12/2011 9:21 AM, RayLee wrote:
> Hi,
>
> I have a table and some stored procedures reference on this table.
> Today, I need to drop the table and recreate the table with some
> additional columns, I would like to know if I can issue sp_recompile
> command so that those stored procedures reference on the table can
> recompile without being recreated?
>
> Or I need to drop and recreate the stored procedures again?
>
> Please kindly give me some advice on it.
>
> I am using sybase 12.5.3 database.
>
> Thanks,
> Ray

You should recreate the procedure.

In ASE, "compile" means "have the optimizer generate
a new query plan from the query tree". A query
plan is stored only in memory (procedure cache)
and specifies how to execute a query (join methods,
table order, index choices).

When you create a procedure, ASE generates a
query tree. A query tree is stored on disk in
the sysprocedures table and specifies what
data is to be returned from what tables, but
not how.

So when you change the structure on an underlying
table, the query tree needs to be rebuilt to
recognize those changes. A rebuilt tree will automatically
result in the compilation of a new query plan
when the procedure is next executed.


Rob V Posted on 2011-12-12 17:16:41.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: sp_recompile or recreate a stored procedure?
References: <4ee62a1f$1@forums-1-dub>
In-Reply-To: <4ee62a1f$1@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: <4ee636f9$1@forums-1-dub>
Date: 12 Dec 2011 09:16:41 -0800
X-Trace: forums-1-dub 1323710201 10.22.241.152 (12 Dec 2011 09:16:41 -0800)
X-Original-Trace: 12 Dec 2011 09:16:41 -0800, vip152.sybase.com
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30758
Article PK: 73649


On 12-Dec-2011 17:21, RayLee wrote:
> Hi,
>
> I have a table and some stored procedures reference on this table.
> Today, I need to drop the table and recreate the table with some
> additional columns, I would like to know if I can issue sp_recompile
> command so that those stored procedures reference on the table can
> recompile without being recreated?
>
> Or I need to drop and recreate the stored procedures again?
>
> Please kindly give me some advice on it.
>
> I am using sybase 12.5.3 database.
>
> Thanks,
> Ray

You need to drop and recreate it. sp_recompile will jsut generate a new
plan based on the schema as it was at the moment when the procedure was
first created.

--
HTH,

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

Certified Sybase Professional DBA for ASE, IQ, Replication Server

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

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------