topic Re: Table Loader slow when updating SAS physical data set in SAS Data Management

Table Loader slow when updating SAS physical data set

Sulthan — Mon, 06 Feb 2017 17:16:54 GMT

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 ?

Re: TABLELOADER SLOWNESS WHILE UPLOADING DATA TO SAS PHYSICAL DATASET

LinusH — Mon, 06 Feb 2017 16:20:44 GMT

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.

Re: TABLELOADER SLOWNESS WHILE UPLOADING DATA TO SAS PHYSICAL DATASET

Sulthan — Tue, 07 Feb 2017 02:01:34 GMT

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

Re: TABLELOADER SLOWNESS WHILE UPLOADING DATA TO SAS PHYSICAL DATASET

Sulthan — Tue, 07 Feb 2017 02:30:49 GMT

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

Re: TABLELOADER SLOWNESS WHILE UPLOADING DATA TO SAS PHYSICAL DATASET

TomKari — Tue, 07 Feb 2017 04:27:12 GMT

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

Re: Table Loader slow when updating SAS physical data set

LinusH — Tue, 07 Feb 2017 07:34:26 GMT

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.

Re: TABLELOADER SLOWNESS WHILE UPLOADING DATA TO SAS PHYSICAL DATASET

Sulthan — Wed, 08 Feb 2017 07:42:32 GMT

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

Re: Table Loader slow when updating SAS physical data set

LinusH — Wed, 08 Feb 2017 16:04:11 GMT

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

Re: Table Loader slow when updating SAS physical data set

Sulthan — Wed, 15 Feb 2017 03:15:30 GMT

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

Re: Table Loader slow when updating SAS physical data set

LinusH — Wed, 15 Feb 2017 07:02:42 GMT

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.