How to find index details on Temporary tables in SYBASE

Question

I am a layman in SYBASE world.

In order to improve performance, I am creating indexes after data load of temporary tables in many stored procedures.

Say, one proc A has an insert into temporary table X. so I have created index IDX after insert statement.

Proc B is invoking Proc A in a loop. In this case, index IDX on temporary table X will try to get created at each call. That’s where I get duplicate index has already create on table X with index name IDX.

To avoid that, I developed code piece(below) to check if the above IDX on temporary table A created or not. If not exists, I create index IDX on temporary table A. This will avoid duplicate index error when invoked proc A in a loop. It's not helping me.

I need to understand how to find if an indexes created on temp table pro-grammatically to restrict duplicate index error.

OPEN CUR_VAR_1
FETCH CUR_VAR_1 INTO @VARIABLE
WHILE (@@sqlstatus=0)
BEGIN
INSERT INTO #TABLE_X
SELECT * FROM TABLE Y

IF NOT EXISTS(SELECT 1 FROM sysindexes where  id=OBJECT_ID('#TABLE_X') and 
name ='IDX')
BEGIN
CREATE INDEX IDX on #TABLE_X(COL_1)
END
End
CLOSE CUR_VAR_1

Error message:

Msg 1913, Level 16, State 1: Server 'ABCDE', Procedure 'PROC_A', Line 1155: There is already an index on table '#TABLE_X' named 'IDX'

A quick help is really appreciated as I have to complete this no later than 04/16/2018.

Answer 1

Assuming this is Sybase ASE ...

---

The error (index already exists on table) is likely due to the conditional ...

IF NOT EXISTS(SELECT 1 FROM sysindexes where  id=OBJECT_ID('#TABLE_X') and name ='IDX')

... being run against sysindexes in the wrong database.

If I'm sitting in database userdb and I create a table #TABLE_X, the above query is going to check for the existence of an index on #TABLE_X in the current database named userdb, which will always return 'true' (ie, no index exists for this table in the userdb database), which in turn means the create index will be run each time the conditional is run; first time conditional is run => create index succeeds; second time conditional is run => create index generates the error.

The conditional needs to be run against the sysindexes table in the temporary database where the #TABLE_X table resides.

For older versions of ASE, or ASE instances with a single temporary database, this means running the conditional against tempdb..sysindexes however ...

In ASE's with multiple temporary databases it's first necessary to first figure out which temporary database the #TABLE_X table resides in and then query the associated sysindexes table.

While this outlines the appropriate method of determining the existence of the index, there's the bigger question of why check for the existence of the index after each insert? Ideally the create index should be tested for (and created) just once ... either before or after the insert (depending on overall code design and when in the process the index is to be used).

---

I would probably pull the create index out to a different place in your code, eg:

• create/select-into <table>, create index, looping/insert, update statistics <table>, run queries

or

• create/select-into <table>, looping/insert, create index, (optionally update statistics <table> - depends on your query requirements), run queries

---

If you insist on throwing the create index into the middle of your looping/insert construct (I wouldn't recommend this), consider ...

• to query the system tables you need to know which tempdb your session is assigned to (assuming your ASE is configured with multiple tempdb's), and then query the system tables in that particular tempdb (eg, build a dynamic query)

or

• you can use some of the built-in system functions to run some tests

Your current tempdb's database id can be found in @@tempdbid (or you can call tempdb_id()). You can then call db_name(@@tempdbid) (or db_name(tempdb_id())) to get your tempdb name. From here you can build your dynamic query against sysindexes. [Plenty of posts on dynamic ASE queries so I'm not going to cover that here.]

Instead of building a dynamic query I'd probably opt for a (relatively) simple function-based test, eg:

NOTE: Assuming no other indexes on the table ... the index id should be 1 or 2 ... depending on the table's locking scheme and the type of index (clustered/non-clustered).

if  index_name(@@tempdbid,object_id(#TABLE_X),1) is NULL
and index_name(@@tempdbid,object_id(#TABLE_X),2) is NULL
begin
    create index ...
end

Regardless of whether you build/run a dynamic query, or use the system functions to test for the existence of the index, I would highly recommend you re-think your code design. Do you really, Really, REALLY want to add the extra overhead for a dynamic-query/function-call to every pass through your looping/insert construct?