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

Table Loader slow when updating SAS physical data set

Reply
Occasional Contributor
Posts: 10

Table Loader slow when updating SAS physical data set

[ Edited ]

Dear All,

 

When trying to upload a data to sas physical dataset via TABLELOADER we are facing very slow in writing the data to the final dataset , for ex . bytes per minutes of data getting written to the dataset

LOADTECHNIQUE used is REPLACE 

NEWROWS IS append(proc(append))

 

We have increased the tmp memory to 250 GB, anyone have faced the same ?

Super User
Posts: 5,257

Re: TABLELOADER SLOWNESS WHILE UPLOADING DATA TO SAS PHYSICAL DATASET

Just to make sure: you are using Data Integration Studio, right?

 

And Table Loader, is that in fact "Table Loader", and not any other like SCD Type 2 Loader, correct?

Since tmp space should have nothing to do with append performance.

 

Your target table, is it in a Base SAS libref?

Do you have any indexes defined for the target table?

What about topology: source data, compute server, data server, networking....

Setting:

options fullstimer msglevel=i;

might give you some hints on processing bottlenecks.'

Can you give an example of the amount bytes written in a certain amount of time? Compare that with a simple write operation in the OS to the same location, i.e. copying a text file.

 

Data never sleeps
Occasional Contributor
Posts: 10

Re: TABLELOADER SLOWNESS WHILE UPLOADING DATA TO SAS PHYSICAL DATASET

Yes we are using TableLoader transformation from SAS DI studio,
Registered the SAS table in BASE SAS libref,
Tried after by defining index(Still no use)
Source data is an extraction from MSSQL tables.

The speed of transfer as far tested is 3mb/10min

Occasional Contributor
Posts: 10

Re: TABLELOADER SLOWNESS WHILE UPLOADING DATA TO SAS PHYSICAL DATASET

I have loaded 1000 obs it was loading fast and i am sharing the log so that can find anything.

PROC Star
Posts: 1,094

Re: TABLELOADER SLOWNESS WHILE UPLOADING DATA TO SAS PHYSICAL DATASET

Yes, the PROC APPEND step jumps right out. For 1,000 records, that's not fast...it's painfully slow.

 

For comparison, I'm showing two log entries on my 4 year old home computer, one with 1,000 records and the next with 1,000,000.

 

Since it's such a simple step, the only things I can think of are i) the compress option is slowing it down horribly, or ii) something's badly wrong with your processor.

 

Tom

 

NOTE: Appending WORK.D1 to WORK.D2.

NOTE: There were 1000 observations read from the data set WORK.D1.

NOTE: 1000 observations added.

NOTE: The data set WORK.D2 has 1000 observations and 124 variables.

NOTE: PROCEDURE APPEND used (Total process time):

     real time           0.16 seconds

     cpu time           0.01 seconds

 

===============================================================

NOTE: Appending WORK.D1 to WORK.D2.

NOTE: There were 1000000 observations read from the data set WORK.D1.

NOTE: 1000000 observations added.

NOTE: The data set WORK.D2 has 1000000 observations and 124 variables.

NOTE: PROCEDURE APPEND used (Total process time):

     real time           1:13.01

     cpu time           8.75 seconds

 

Occasional Contributor
Posts: 10

Re: TABLELOADER SLOWNESS WHILE UPLOADING DATA TO SAS PHYSICAL DATASET

Yes its very slow, but this was not happened few weeks back.

Super User
Posts: 5,257

Re: Table Loader slow when updating SAS physical data set

Are you sure that it's the writing that takes time, not the reading from external source?
Do a data _null_ that reads the same table.
Again, look a the file system outside SAS.
I don't think that compress would slow things down, since compress bottleneck is CPU and the CPU is barely working in your case.
Data never sleeps
Occasional Contributor
Posts: 10

Re: Table Loader slow when updating SAS physical data set

data _null_;

set sqlref.SBR_PNR_DATAELEMENTS;

run;

 

I tried the below , but it was taking longer time to respond , but the same table using extract transformation have completed in 0.18s

Super User
Posts: 5,257

Re: Table Loader slow when updating SAS physical data set

Oh, so the million dollar question is: what has changed?
Data never sleeps
Super User
Posts: 5,257

Re: Table Loader slow when updating SAS physical data set

Are you sure about the extract? It by default creates a view. As i recall your was just an extract and a load. Compare the data _null_ with the whole job.
Data never sleeps
Ask a Question
Discussion stats
  • 9 replies
  • 262 views
  • 0 likes
  • 3 in conversation