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.

stored procedure with #temp table

2 posts in General Discussion Last posting was on 2012-12-12 16:26:11.0Z
Sid. Posted on 2012-12-12 14:40:20.0Z
Sender: 609f.50c895b9.1804289383@sybase.com
From: Sid.
Newsgroups: sybase.public.ase.general
Subject: stored procedure with #temp table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50c89754.60fa.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 12 Dec 2012 06:40:20 -0800
X-Trace: forums-1-dub 1355323220 172.20.134.41 (12 Dec 2012 06:40:20 -0800)
X-Original-Trace: 12 Dec 2012 06:40:20 -0800, 172.20.134.41
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31594
Article PK: 1158254

If inside the stored procedure I have #temp table created
using either create table or select into does this proc
always get new query plan during execution? As the object id
for #temp table changes does ASE needs to recompile the
plan?

Thanks,
Sid.


Rob V Posted on 2012-12-12 16:26:11.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: stored procedure with #temp table
References: <50c89754.60fa.1681692777@sybase.com>
In-Reply-To: <50c89754.60fa.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: <50c8b023@forums-1-dub>
Date: 12 Dec 2012 08:26:11 -0800
X-Trace: forums-1-dub 1355329571 172.20.134.152 (12 Dec 2012 08:26:11 -0800)
X-Original-Trace: 12 Dec 2012 08:26:11 -0800, vip152.sybase.com
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31596
Article PK: 1158291


On 12-Dec-2012 15:40, Sid. wrote:
> If inside the stored procedure I have #temp table created
> using either create table or select into does this proc
> always get new query plan during execution? As the object id
> for #temp table changes does ASE needs to recompile the
> plan?
>
> Thanks,
> Sid.
>

It depends on the ASE version and whether 'automatic update statistics'
and 'deferred compilation' is used. See my Techwave 2010 presentation
for more details (at http://www.sypron.nl/downloads.html), there is too
much to summarize here.

HTH,

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

Certified Professional DBA for Sybase 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"
"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
-----------------------------------------------------------------