Creating an INDEX on output table

[JohnT]

Hi,

We are in the midst of migrating SAS code into DI Studio.

I have a job which creates some indexes at the end.

I have tried to do this in DI Studio, without using user written code.

When I run the job in DI Studio without indexes, it takes 20 seconds to run.  With indexes (it's still running but it's now over seven minutes).  I set the properties of the table to have indexes. 

   /*---- Create the indexes for a table  ----*/ 
   %put %str(NOTE: Creating indexes ...);
   proc datasets lib = NS nolist; 
      modify POLICY_HISTORY; 
         index create insurance_ref;
         index create insurance_file_cnt;
         index create p_key = (insurance_ref policy_version);
   quit; 
   
   %rcSet(&syserr); 
   
%end;  /* if table does not exist  */ 
/*---- Append  ----*/ 
%put %str(NOTE: Appending data ...);
proc append base = NS.POLICY_HISTORY 
            data = &etls_lastTable (&etls_tableOptions) force; 
run; 

^^
I think the reason it takes so long to run now, is because it applies the indexes to an empty dataset, and then appends the data to it.  I don't care about the append part, but I think the table loader transform does this by default.  I tried the SQL insert, and it still took ages.

I want the metadata to be meaningful, so in my head, the indexes need to be on the output table.

I guess, I could have some user written code at the end of the job which puts the indexes on, but then the metadata wouldn't tell people it's there.

So, what's the right way for me to apply these indexes.

I'm running DI Studio 4.21 on a Windows bases server.

Thanks.

[LinusH · Accepted solution]

In the Table Loader, you have the opportunity to specify whether you want the index to be built before or after table creation/update.

See "Index Condition", and refer to the Help section to chose the appropriate settings for your scenario.

Br

Linus

[JohnT]

Thanks for the suggestion LinusH, I think that's what I was after.

I would have expected to see this option on the output table, and not on the transform though?

I'll try it and see how it goes.

Thanks again.