SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Creating an INDEX on output table

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

Creating an INDEX on output table

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.


Accepted Solutions
Solution
‎10-13-2011 08:44 AM
Super User
Posts: 5,256

Creating an INDEX on output table

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

Data never sleeps

View solution in original post


All Replies
Solution
‎10-13-2011 08:44 AM
Super User
Posts: 5,256

Creating an INDEX on output table

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

Data never sleeps
Frequent Contributor
Posts: 75

Re: Creating an INDEX on output table

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 662 views
  • 0 likes
  • 2 in conversation