Table Loader slow when updating SAS physical data set

[Sulthan]

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 ?

[LinusH]

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.

[Sulthan]

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

[Sulthan]

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

[TomKari]

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

[Sulthan]

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

[LinusH]

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.

[Sulthan]

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

[LinusH]

Oh, so the million dollar question is: what has changed?

[LinusH]

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.