topic Re: Copying an existing table taking very long in SAS Data Management

Copying an existing table taking very long

jim_toby — Mon, 05 Nov 2018 12:48:14 GMT

So my main objective is to load up a sas table into netezza. However, I am getting the error: Error terminating Netezza load: Communication link failure.

I was told here: https://communities.sas.com/t5/SAS-Data-Management/Loading-SAS-To-Netezza-Communication-Link-Failure/m-p/509940#M15814 that this error could be that my table may be too big. I tried distributing on certain column names and on random but I still get the error.

The next plan is to split the table into different smaller tables and than inserting each table into a single table in netezza. The problem is that copying an existing table (even if it is just one record) takes around 30 minutes. Here is my code:

LIBNAME ECLIB000 "\path\to\SASTable";
proc sql;
create table ECLIB000.TestTable as
select * from ECLIB000.MySasTable
where ID = 'R988664335710II';
quit;

So there is only one row with the specified ID. The table I need to copy from has over 34 million rows and 50 columns. I'm worried that if copying one records takes this long, how long would it take to copy half of the table? Would it take the same amount of time since it needs to iterate over each row? Is there a better way in accomplishing what I am trying to accomplish? For instance in the link I sent above, it was suggested that I use SAS views? Is that better in order to load the data into Netezza?

Thanks in advance!

Re: Copying an existing table taking very long

VDD — Mon, 05 Nov 2018 14:08:36 GMT

Is ID indexed?

Re: Copying an existing table taking very long

andreas_lds — Mon, 05 Nov 2018 14:23:56 GMT

If ID is not indexed all rows have to be read, which is most likely the reason for the amount of time required to extract the observation. Creating an index on a 34 million row dataset is time-consuming, too, but could speed up extracting rows.

You should have a look at the log, too. Check real-time and cpu-time of that proc sql step: if real-time is much higher than cpu-time, the sas jobs is waiting to get the data, this could be caused by slow hard-disks.

Re: Copying an existing table taking very long

jim_toby — Mon, 05 Nov 2018 15:34:19 GMT

@andreas_lds I don't think ID is indexed. Can you show me how to index it? Would it be like this:

proc sql;
   create index ID
      on MySasTable(ID);

Also, does it only make sense to index columns with unique values, or can I index a value that may split the table in half like Identifier column that stores M for male of F for female?

Re: Copying an existing table taking very long

andreas_lds — Mon, 05 Nov 2018 15:39:25 GMT

I don't use proc sql for such tasks, here is an example using proc datasets:

data work.classclone;
   set sashelp.class;
run;

proc datasets library=work noprint;
   modify classclone;
      index create Age;
quit;