Does 'enable select into in tran' slows down the ASE?

[pietercoene]

Hi,

we've been using ASE for more than 20 years (started with 10.x if I remember correctly). All those time we could not create #-tables while in a transaction (leading to the creation of a lot 'normal' tables but used as a temp-table). Also i remember a lot of confusion in the past about the 'ddl in tran' option (and performance) and some warnings about using this option, but that was all a long time ago...

But now i accidenlty bump into the 'enable select into in tran' option. I realise that the ASE has changed a lot since the early days so its hard to tell which 'stories' are still true...

My basicly my question is: can we just enable the 'enable select into in tran' and start using this (because this is a feature we want for many years) without serious impact on the performance of the ASE (for example: locks on the tempdb)?

PS: we are using SAP ASE 16 SP03 PL03...

I've tested the option and this is how i got it working (i had to enable the 'ddl in tran'):

sp_dboption tempdb, "ddl in tran", true
sp_configure "enable select into in tran", 1
use mySampleDB
begin tran
select top 5 *into #t from <myTable>
rollback

Greetz
Pieter

[former_member89972 · Accepted solution]

Do not see any huge performance issues.,

Side effect may be on filling up the transction log if DDL-transaction is not commited quickly.

Lon trnasctions can hold up replication, e.g. you have lots of million-row "select * into #<tablename>"

Transaction logs dumps --- if controlled by thresholds -- may fire more frequently.

Other than that it is a normal transaction.

HTH

Avinash

[pietercoene · Accepted solution]

Hi Avinash,

i understand what you are saying. The #-tabel creation is mainly usefull for us to perform some checks while in a transaction so that would normally be a small amount of rows into the #-table.

But: i enabled the behaviour yesterday-morning on our production environment, and we already got our first stack trace from it. Apparently, creating an index in a while-loop on a #-table causes a stacktrace. Of course, this is a 'bug' in our code, but without the 'ddl in tran' option enabled, ASE used to give a proper error that the index could not be created. I will create a 'ticket' with the SAP Support for this stackrace...

Greetz
Pieter