real time / user cpu time

Accepted Solution Solved
Reply
Valued Guide
Posts: 856
Accepted Solution

real time / user cpu time

Below is info from my log.  This is a simple query pulling from a large database.  Does the log tell you anything other than it is running slow?  Due to the time of the month and other jobs running I think the time is due to available resources and nothing else, I am wondering if there is any other information to be taken away from this.

Thank You,

55     create table mdj.current_cust_inst_base as
56     select distinct *
57     /*This dataset is valid wbdw data for testing*/
58     from jan15bs.cust_inst_base
59     where record_date > '31JAN2013'd
60     order by power_id,record_date;

NOTE: PROCEDURE SQL used (Total process time):

      real time           4:01:32.17

      user cpu time       15:26.71

      system cpu time     9:14.23

      Memory                            530145k

      OS Memory                         546216k


Accepted Solutions
Solution
‎02-07-2015 04:41 AM
Esteemed Advisor
Posts: 5,202

Re: real time / user cpu time

Since sorting data is memory intensive and due to the long running query,  I guess that you could benefit from making more RAM  available for your SAS session. See MEMSIZE  and SORTSIZE global options. Depending on the structure of your data, indexing on record_date might help. If your are using a SAS Data store, consider the SPDE engine.

Data never sleeps

View solution in original post


All Replies
Respected Advisor
Posts: 3,068

Re: real time / user cpu time

You don't say whether the large database you are reading from is in SAS or is an external database. If it is an external database then the performance you get reading it from SAS is primarily dependent on the database's performance and not SAS's.

If you are reading SAS data then the large difference you see between real time and user cpu time is most likely due to slow IO, which in turn means more demand for reading and writing to disk than there are resources available.

If you were to run the same job after hours what is the performance? If the performance is always slow then tuning the SQL query might be worth considering (index on record_date for example).

Grand Advisor
Posts: 9,596

Re: real time / user cpu time

Don't use ORDER statement if you can .

If you extract data from some DataBase Product . Add readbuff=100  option into libname .

Xia Keshan

Solution
‎02-07-2015 04:41 AM
Esteemed Advisor
Posts: 5,202

Re: real time / user cpu time

Since sorting data is memory intensive and due to the long running query,  I guess that you could benefit from making more RAM  available for your SAS session. See MEMSIZE  and SORTSIZE global options. Depending on the structure of your data, indexing on record_date might help. If your are using a SAS Data store, consider the SPDE engine.

Data never sleeps
Valued Guide
Posts: 3,206

Re: real time / user cpu time

This tiny part of information is just telling you it took 4 hours  15 minutes user cpu (your kind of calculations) 9 minutes of system cpu.

It is as  meaningless as a name like "steelers". Without knowing anything more machine environment history of job data network dbms etc may be the real reason of your question (is it a problem?). What could we say about that?    

---->-- ja karman --<-----
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 453 views
  • 3 likes
  • 5 in conversation