Temp. tables in transaction. How ?

I'd still persue looking into #7 if I were you. Your assertion that the
optimizer is "too weak" is sometimes overused. I'll bet that there is a viable
solution to those kinds of queries more than half of the time (ie, my guess is
that #temp tables aren't necessary in those situations more often than not).

If catalog locking in tempdb is a major concern and you MUST have DDL IN TRAN in
your tempdb in order to do your work, then maybe this is a good reason to look
into multiple tempdbs.


"Eugene Korolkov" wrote in message
news:44a2ad6b$1@forums-2-dub...
Hi gurus,

Sybase ASE 12.x:

How to use temp tables #t in transaction if :
1. select into/bulkcopy usually is off on production db

2. create #t table, insert into works only when db_option
create 'ddl in tran' is on tempdb, but that option usually is off in prod.
too
(at least according to Sybase TS guide, err.2762)

3. Replacing #t by views often helps (worktables + no locks on system tables +
session copies like for t# tables),
but transitive closure for join between views based on views still does
not work properly.
=> table scan often + a lot of "internal" views

4. Replacing view by dynamic from clause still not supported.

5. Replacing #t by regular tables helps, but 1) a lot of intermediate tables,
(moreover 2) immediate locking/deadlocking pblm arises, cause still no
snapshot isolation)

6. Replacing #t by pseudotables (@@spid) again like 5.1 and looks too
artificial.

7. Replacing steps with intermediate # t by complex sql-query (without any #t)
often leads to optimizer problem, cause optimizer often too weak and works
fine in loop with join with couple tables and putting results in the next
intermediate key #t.

Upgrade to 15.x (where 7. suppose to be OK) ?

Regards,
Eugene

Actually we are using approach 7. most of the time and trying not to
use #t at all,
but optimizer "weakness" includes fragile query plans for
relatively complex queries,
and surprisingly stable plans with couple of #t key-tables approach
(perhaps because sp with #t recompiles every time or/and simpler itself),
but without transaction support.

Eugene

Sherlock, Kevin wrote:

>I'd still persue looking into #7 if I were you. Your assertion that the
>optimizer is "too weak" is sometimes overused. I'll bet that there is a viable
>solution to those kinds of queries more than half of the time (ie, my guess is
>that #temp tables aren't necessary in those situations more often than not).
>
>If catalog locking in tempdb is a major concern and you MUST have DDL IN TRAN in
>your tempdb in order to do your work, then maybe this is a good reason to look
>into multiple tempdbs.
>
>
>"Eugene Korolkov" wrote in message
>news:44a2ad6b$1@forums-2-dub...
>Hi gurus,
>
>Sybase ASE 12.x:
>
>How to use temp tables #t in transaction if :
>1. select into/bulkcopy usually is off on production db
>
>2. create #t table, insert into works only when db_option
> create 'ddl in tran' is on tempdb, but that option usually is off in prod.
>too
> (at least according to Sybase TS guide, err.2762)
>
>3. Replacing #t by views often helps (worktables + no locks on system tables +
>session copies like for t# tables),
> but transitive closure for join between views based on views still does
>not work properly.
> => table scan often + a lot of "internal" views
>
>4. Replacing view by dynamic from clause still not supported.
>
>5. Replacing #t by regular tables helps, but 1) a lot of intermediate tables,
> (moreover 2) immediate locking/deadlocking pblm arises, cause still no
>snapshot isolation)
>
>6. Replacing #t by pseudotables (@@spid) again like 5.1 and looks too
>artificial.
>
>7. Replacing steps with intermediate # t by complex sql-query (without any #t)
> often leads to optimizer problem, cause optimizer often too weak and works
> fine in loop with join with couple tables and putting results in the next
>intermediate key #t.
>
>Upgrade to 15.x (where 7. suppose to be OK) ?
>
>Regards,
>Eugene
>
>
>
>

Is it possible to build your #temp tables outside of the transaction? Then
read/modify the #temp tables while inside the transaction?


----------------
"Eugene Korolkov" wrote in message
news:44A2E6FD.2060207@davidsohn.com...
Actually we are using approach 7. most of the time and trying not to use #t at
all,
but optimizer "weakness" includes fragile query plans for relatively
complex queries,
and surprisingly stable plans with couple of #t key-tables approach
(perhaps because sp with #t recompiles every time or/and simpler itself),
but without transaction support.

Eugene

Sherlock, Kevin wrote:

I'd still persue looking into #7 if I were you. Your assertion that the
optimizer is "too weak" is sometimes overused. I'll bet that there is a viable
solution to those kinds of queries more than half of the time (ie, my guess is
that #temp tables aren't necessary in those situations more often than not).

If catalog locking in tempdb is a major concern and you MUST have DDL IN TRAN in
your tempdb in order to do your work, then maybe this is a good reason to look
into multiple tempdbs.


"Eugene Korolkov" wrote in message
news:44a2ad6b$1@forums-2-dub...
Hi gurus,

Sybase ASE 12.x:

How to use temp tables #t in transaction if :
1. select into/bulkcopy usually is off on production db

2. create #t table, insert into works only when db_option
create 'ddl in tran' is on tempdb, but that option usually is off in prod.
too
(at least according to Sybase TS guide, err.2762)

3. Replacing #t by views often helps (worktables + no locks on system tables +
session copies like for t# tables),
but transitive closure for join between views based on views still does
not work properly.
=> table scan often + a lot of "internal" views

4. Replacing view by dynamic from clause still not supported.

5. Replacing #t by regular tables helps, but 1) a lot of intermediate tables,
(moreover 2) immediate locking/deadlocking pblm arises, cause still no
snapshot isolation)

6. Replacing #t by pseudotables (@@spid) again like 5.1 and looks too
artificial.

7. Replacing steps with intermediate # t by complex sql-query (without any #t)
often leads to optimizer problem, cause optimizer often too weak and works
fine in loop with join with couple tables and putting results in the next
intermediate key #t.

Upgrade to 15.x (where 7. suppose to be OK) ?

Regards,
Eugene

Eugene Korolkov wrote:

Can you make your posts in plain text in future? I keep seeing a cyan background
that makes them hard to read. Thanks.

> Sybase ASE 12.x:
>
> How to use temp tables #t in transaction if :
> 1. select into/bulkcopy usually is off on production db

It shouldn't be off for tempdb.

> 2. create #t table, insert into works only when db_option
> create 'ddl in tran' is on tempdb, but that option usually is off in prod. too
> (at least according to Sybase TS guide, err.2762)

You could turn it on for tempdb but I would oppose that.

> 3. Replacing #t by views often helps (worktables + no locks on system tables + session copies like for t# tables),
> but transitive closure for join between views based on views still does not work properly.
> => table scan often + a lot of "internal" views
>
> 4. Replacing view by dynamic from clause still not supported.
>
> 5. Replacing #t by regular tables helps, but 1) a lot of intermediate tables,
> (moreover 2) immediate locking/deadlocking pblm arises, cause still no snapshot isolation)
>
> 6. Replacing #t by pseudotables (@@spid) again like 5.1 and looks too artificial.
>
> 7. Replacing steps with intermediate # t by complex sql-query (without any #t)
> often leads to optimizer problem, cause optimizer often too weak and works
> fine in loop with join with couple tables and putting results in the next intermediate key #t.

8. Create your temp tables first outside the scope of the transaction.
That's how I would normally do it.

-am © MMVI

1. Sorry, of course select/into is on on tempdb, but producing the same
error,
cause no logging.
8. The problem with that approach is that #t is creating in some
low-level sp,
just for local purposes, but transaction sometimes is crossing
over low-level sps
beginning even on the client like VB.

Eugene

A.M. wrote:

>Eugene Korolkov wrote:
>
> Can you make your posts in plain text in future? I keep seeing a cyan background
> that makes them hard to read. Thanks.
>
>
>
>>Sybase ASE 12.x:
>>
>>How to use temp tables #t in transaction if :
>>1. select into/bulkcopy usually is off on production db
>>
>>
>
> It shouldn't be off for tempdb.
>
>
>
>>2. create #t table, insert into works only when db_option
>> create 'ddl in tran' is on tempdb, but that option usually is off in prod. too
>> (at least according to Sybase TS guide, err.2762)
>>
>>
>
> You could turn it on for tempdb but I would oppose that.
>
>
>
>>3. Replacing #t by views often helps (worktables + no locks on system tables + session copies like for t# tables),
>> but transitive closure for join between views based on views still does not work properly.
>> => table scan often + a lot of "internal" views
>>
>>4. Replacing view by dynamic from clause still not supported.
>>
>>5. Replacing #t by regular tables helps, but 1) a lot of intermediate tables,
>> (moreover 2) immediate locking/deadlocking pblm arises, cause still no snapshot isolation)
>>
>>6. Replacing #t by pseudotables (@@spid) again like 5.1 and looks too artificial.
>>
>>7. Replacing steps with intermediate # t by complex sql-query (without any #t)
>> often leads to optimizer problem, cause optimizer often too weak and works
>> fine in loop with join with couple tables and putting results in the next intermediate key #t.
>>
>>
>
>8. Create your temp tables first outside the scope of the transaction.
> That's how I would normally do it.
>
>-am © MMVI
>
>

Eugene Korolkov wrote:
>
> 1. Sorry, of course select/into is on on tempdb, but producing the same
> error,
> cause no logging.

OK.

> 8. The problem with that approach is that #t is creating in some
> low-level sp,
> just for local purposes, but transaction sometimes is crossing
> over low-level sps
> beginning even on the client like VB.

I'm not keep on initiating transactions from the client. I can
understand why but my approach has always been to keep them as
short as possible - which means do them in an sproc as needed.
The reason for this is obvious, if the client runs in chained
mode and goes to lunch before commiting a transaction, you have
a major problem brewing.

You only have two options then - allow ddl in tran in tempdb or
check the transaction nesting in the low level sproc and commit
if needed and then begin a new transaction after the temp table
is created. The latter is problematic so its probably not a good
idea - unless you understand exactly what you are doing.

-am © MMVI

Have you looked into abstract plans then? Multiple tempdb?


"Eugene Korolkov" wrote in message
news:44A2E6FD.2060207@davidsohn.com...
Actually we are using approach 7. most of the time and trying not to use #t
at all,
but optimizer "weakness" includes fragile query plans for relatively
complex queries,
and surprisingly stable plans with couple of #t key-tables approach
(perhaps because sp with #t recompiles every time or/and simpler itself),
but without transaction support.

Eugene

Sherlock, Kevin wrote:

I'd still persue looking into #7 if I were you. Your assertion that the
optimizer is "too weak" is sometimes overused. I'll bet that there is a
viable
solution to those kinds of queries more than half of the time (ie, my guess
is
that #temp tables aren't necessary in those situations more often than not).

If catalog locking in tempdb is a major concern and you MUST have DDL IN
TRAN in
your tempdb in order to do your work, then maybe this is a good reason to
look
into multiple tempdbs.


"Eugene Korolkov" wrote in message
news:44a2ad6b$1@forums-2-dub...
Hi gurus,

Sybase ASE 12.x:

How to use temp tables #t in transaction if :
1. select into/bulkcopy usually is off on production db

2. create #t table, insert into works only when db_option
create 'ddl in tran' is on tempdb, but that option usually is off in
prod.
too
(at least according to Sybase TS guide, err.2762)

3. Replacing #t by views often helps (worktables + no locks on system
tables +
session copies like for t# tables),
but transitive closure for join between views based on views still
does
not work properly.
=> table scan often + a lot of "internal" views

4. Replacing view by dynamic from clause still not supported.

5. Replacing #t by regular tables helps, but 1) a lot of intermediate
tables,
(moreover 2) immediate locking/deadlocking pblm arises, cause still no
snapshot isolation)

6. Replacing #t by pseudotables (@@spid) again like 5.1 and looks too
artificial.

7. Replacing steps with intermediate # t by complex sql-query (without any
#t)
often leads to optimizer problem, cause optimizer often too weak and
works
fine in loop with join with couple tables and putting results in the
next
intermediate key #t.

Upgrade to 15.x (where 7. suppose to be OK) ?

Regards,
Eugene

Keeping transaction as short as possible is the usual advice from
Sybase, but unfortunately this is business who dictate how long transaction
should be, not the dataserver restrictions and trans. becoming longer
and longer as
business stretches on the web and this is obvious cause more and more
information involved.

Eugene

A.M. wrote:

>Eugene Korolkov wrote:
>
>
>>1. Sorry, of course select/into is on on tempdb, but producing the same
>>error,
>> cause no logging.
>>
>>
>
> OK.
>
>
>
>>8. The problem with that approach is that #t is creating in some
>>low-level sp,
>> just for local purposes, but transaction sometimes is crossing
>>over low-level sps
>> beginning even on the client like VB.
>>
>>
>
> I'm not keep on initiating transactions from the client. I can
> understand why but my approach has always been to keep them as
> short as possible - which means do them in an sproc as needed.
> The reason for this is obvious, if the client runs in chained
> mode and goes to lunch before commiting a transaction, you have
> a major problem brewing.
>
> You only have two options then - allow ddl in tran in tempdb or
> check the transaction nesting in the low level sproc and commit
> if needed and then begin a new transaction after the temp table
> is created. The latter is problematic so its probably not a good
> idea - unless you understand exactly what you are doing.
>
>-am © MMVI
>
>

Eugene Korolkov wrote:
>
> Keeping transaction as short as possible is the usual advice from
> Sybase,

And very good advice too.

> but unfortunately this is business who dictate how long transaction
> should be,

Businesses aren't really in the business of dictating this.
Usually, its a business analyst who writes up specs for a
process without much thought to how a database may work.

It usually falls on the lowest people in the chain, the
developers, to implement the model. More often than not,
those developers aren't very experienced or perhaps they
just don't care.

> not the dataserver restrictions and trans. becoming longer and longer as
> business stretches on the web and this is obvious cause more and more
> information involved.

The size of the data is rather irrelevant and where it comes
from is totally irrelevant.

-am © MMVI