Index on temporary table inside sp

[John Flynn]

I'm on ASE 15.0.2 ESD#6. Suppose I create a temporary table, and then I call
a stored procedure which uses that temporary table. Then, suppose I
determine that the query in the sp will benefit by there being an index on
the temporary table. I know I can solve this by creating the index *outside*
of the sp (like right after I create the temp table). But what if I'd prefer
to create the index INSIDE the sp? How do I do that?

I have tried the following things:

1. I have tried simply adding the CREATE INDEX command in the sp. That works
the first time I call the sp. But if I then call the sp again, I get an
error like "cannot create the index because it already exists".

2. I tried adding a DROP INDEX to the end of the sp. That doesn't work;
whenever I call the sp I get "Schema for database object
'#access00000890016714047' has changed since compilation of this query.
Please re-execute query." Adding WITH RECOMPILE makes no difference. I have
no idea what this means or why ASE would complain about it. If it lets me
create an index why won't it let me drop an index?

3. I tried to detect whether the index already exists, and create it only if
it doesn't yet exist. Sorta like:

if not exists (select * from tempdb..sysindexes where
id=object_id('#access') and name='ind1')
create index ind1 on #access(col)

This works perfectly. It creates the index the first time, and doesn't the
subsequent times. (And as usual, my sp queries *use* the index like I want.)
But I don't like it because it hardcodes the name tempdb, and what if there
are multiple tempdbs? Also it depends on a particular design of the ASE
system tables, and I don't like putting that in production code.

4. I tried to take into account the multiple tempdbs, with some dynamic SQL,
like this:

declare @cmd varchar(255)
select @cmd = "if not exists ( select * from " + db_name(tempdb_id()) +
"..sysindexes where id=object_id('#access') and name='ind1') " +
"create index ind1 on #access(col)"
execute ( @cmd )

That also works perfectly. I think it solves the problem of the multiple
tempdbs. But I'd rather not use dynamic SQL. But mainly, it still depends on
a particular ASE implementation that could theoretically break in a future
ASE release. That is sufficiently unclean that I tend to reject this option.

5. I see a function called index_col(), which I thought could help me
determine cleanly whether the index already exists. But I couldn't get it to
work, it returned null no matter what I did. Maybe I just don't know how to
use it.

How can I do this? If I could *cleanly* determine whether the index exists,
then I think I'm home free. Alternatively, if I can figure out how to get
the DROP INDEX to work inside the sp, that would also be a clean solution.
Any hints?

(BTW this is not a question about whether ASE will *use* the index. I have
already tested it and ASE does appear to use the index when I want it to.)

Thanks.
- John.

[Mark A. Parsons]

Grant mon_role to your login.

Query master..monOpenObjectActivity for the existence of the index.

Assumes index descriptor has not been flushed from monOpenObjectActivity (ie, this would imply 'number of open indexes'
is too small).

-----------------------

Along the same lines as using index_col() ... data_pages(), reserved_pages(), used_pages(); if they return 0 then index
does not exist.

'course, this assumes you know the index's id; if you're *assuming* the index id will always be a specific number (eg,
2) ... you're now relying on an ASE 'feature' which could be changed in the future. This could also be a problem if you
ever get to the point where different procs could create different indexes on the same #table, ie, the index id's could
differ each time your run your process depending on the firing order of the procs.

-----------------------

Use ASE's Application Context Functions (ACFs) to keep track of the existence of an index.

NOTE: This would require that right after creating/dropping an index that the associated ACF tuple is updated accordingly.

For example:

=======================================
create index a_b_c on #t1(a, b, c)
select set_appcontext('#t1','a_b_c','yes')
=======================================

Later, when you want to check for the existence of the index:

=======================================
if isnull(get_appcontext('#t1','a_b_c'),'no') != 'yes'
... create index
=======================================

-----------------------

If you're going to limit yourself by ignoring certain (but not all) ASE features then you (obviously) start to
complicate your life. To be really safe, and to insure you never run into any issues with a change in the ASE codeline,
your best bet would be to not use ASE at all, right?

Personally, I would opt for the K.I.S.S. principle ... add the index on the #temp table after the table is created
(preferably after the table has been populated else you may find yourself having to run 'update [index] stats').

Next best (?) option ... encapsulate the necessary ASE-specific logic into a stored proc, then invoke this proc any time
you need to a) determine the existence of an index and/or b) dynamically create an index on a given table. The general
idea would be to keep all the logic in one place where it can be easily updated/modified if/when a change in the ASE
codeline dictates a change in your coding.

Post by John Flynn
I'm on ASE 15.0.2 ESD#6. Suppose I create a temporary table, and then I call
a stored procedure which uses that temporary table. Then, suppose I
determine that the query in the sp will benefit by there being an index on
the temporary table. I know I can solve this by creating the index *outside*
of the sp (like right after I create the temp table). But what if I'd prefer
to create the index INSIDE the sp? How do I do that?
1. I have tried simply adding the CREATE INDEX command in the sp. That works
the first time I call the sp. But if I then call the sp again, I get an
error like "cannot create the index because it already exists".
2. I tried adding a DROP INDEX to the end of the sp. That doesn't work;
whenever I call the sp I get "Schema for database object
'#access00000890016714047' has changed since compilation of this query.
Please re-execute query." Adding WITH RECOMPILE makes no difference. I have
no idea what this means or why ASE would complain about it. If it lets me
create an index why won't it let me drop an index?
3. I tried to detect whether the index already exists, and create it only if
if not exists (select * from tempdb..sysindexes where
id=object_id('#access') and name='ind1')
create index ind1 on #access(col)
This works perfectly. It creates the index the first time, and doesn't the
subsequent times. (And as usual, my sp queries *use* the index like I want.)
But I don't like it because it hardcodes the name tempdb, and what if there
are multiple tempdbs? Also it depends on a particular design of the ASE
system tables, and I don't like putting that in production code.
4. I tried to take into account the multiple tempdbs, with some dynamic SQL,
"..sysindexes where id=object_id('#access') and name='ind1') " +
"create index ind1 on #access(col)"
That also works perfectly. I think it solves the problem of the multiple
tempdbs. But I'd rather not use dynamic SQL. But mainly, it still depends on
a particular ASE implementation that could theoretically break in a future
ASE release. That is sufficiently unclean that I tend to reject this option.
5. I see a function called index_col(), which I thought could help me
determine cleanly whether the index already exists. But I couldn't get it to
work, it returned null no matter what I did. Maybe I just don't know how to
use it.
How can I do this? If I could *cleanly* determine whether the index exists,
then I think I'm home free. Alternatively, if I can figure out how to get
the DROP INDEX to work inside the sp, that would also be a clean solution.
Any hints?
(BTW this is not a question about whether ASE will *use* the index. I have
already tested it and ASE does appear to use the index when I want it to.)
Thanks.
- John.

[John Flynn]

Post by Mark A. Parsons
Personally, I would opt for the K.I.S.S. principle ... add the index
on the #temp table after the table is created (preferably after the
table has been populated else you may find yourself having to run
'update [index] stats').

Thanks, that's pretty much what I've decided to do. Thanks for all your
suggestions; I had never heard of some of those. Your posts are always
educational.

Unless there's some kind of magical does_index_exist() function (which there
obviously isn't), I'm going to stick with the obvious solution.

- John.