BookmarkSubscribeRSS Feed
Sulthan
Calcite | Level 5

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 ?

9 REPLIES 9
LinusH
Tourmaline | Level 20

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
Sulthan
Calcite | Level 5

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
Calcite | Level 5

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

TomKari
Onyx | Level 15

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
Calcite | Level 5

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

LinusH
Tourmaline | Level 20
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
Sulthan
Calcite | Level 5

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
Tourmaline | Level 20
Oh, so the million dollar question is: what has changed?
Data never sleeps
LinusH
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 2046 views
  • 0 likes
  • 3 in conversation