How to find index details on Temporary tables in SYBASE

duplicationindexsybasetemporary-tables

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.

Best Answer

Assuming this is Sybase ASE ...

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?

Related Question