topic Re: Sql Insert vs Data Step Efficiency Question in SAS Procedures

Sql Insert vs Data Step Efficiency Question

rileyd — Fri, 12 Dec 2014 16:47:22 GMT

Hey,

I have a Data step like follows:

Data Table3;

Set Table1

Table2;

Run;

I replaced it using a Sql Insert step. I decided not to use Proc Append because it would require me to use the force option. The Sql Insert option lets me defulat some fields to 0 that are not in Table2 but are in Table1. So my insert looks something like:

Proc Sql;

Insert Into Table1

Select Field1,

Field2,

0 as Field3 /* Defaulted field. */

From Table2

;

Quit;

I would have expected the Sql Insert to be more efficient because I'm inserting only the records from Table2 into Table1 versus the Data step which is reading all the records in Table1 and Table2 to create Table3. But when I checked the log the CPU time for the Data step was 38.61 and the CPU time for the Sql Insert was 2:00.35.

Just curious to know why the Sql Insert appears to be so much less efficient.

Thanks!

-Andrew

Re: Sql Insert vs Data Step Efficiency Question

blom0344 — Sat, 13 Dec 2014 16:54:19 GMT

What is the CPU time of:

proc sql;

create table3 as

select t.* from table1 t

union

select v.* from table2 v;

quit;

since this closely resembles building a table from scratch as the datastep does..

Re: Sql Insert vs Data Step Efficiency Question

Cynthia_sas — Sat, 13 Dec 2014 17:31:20 GMT

Hi:

In the Programming 3 class, we discuss these kinds of efficiencies and illustrate the proper way to do benchmarking. Here's the description of the Programming 3 class: SAS Training in the U.S. -- SAS Programming 3: Advanced Techniques and Efficiencies

cynthia

Re: Sql Insert vs Data Step Efficiency Question

PGStats — Sat, 13 Dec 2014 19:33:40 GMT

Good suggestion, but that should be:

proc sql;

create table table3 as

select * from table1

union all corresponding

select *, 0 as Field3 from table2;

quit;

Re: Sql Insert vs Data Step Efficiency Question

blom0344 — Sat, 13 Dec 2014 20:55:49 GMT

Yes, you are right. My idea was to point to the use of set operators. I wasn't sure SAS supports union all, so I sticked to the union

Re: Sql Insert vs Data Step Efficiency Question

rileyd — Mon, 15 Dec 2014 14:10:25 GMT

Thanks Cynthia. I have the Programming 3 book so I'll look into doing some benchmarking as outlined in it. I guess I was a little surprised in general that the Sql Insert wasn't less CPU intensive. In the past when I've replaced similar Data steps with Proc Append the CPU difference (improvement) is typically noticeable and significant (2-3 times faster). I assumed with the Sql Insert I would see similar results so when the CPU actually increased I was surprised.

Re: Sql Insert vs Data Step Efficiency Question

Kurt_Bremser — Mon, 15 Dec 2014 15:05:30 GMT

What were the real times of the data step and sql solutions?

Re: Sql Insert vs Data Step Efficiency Question

rileyd — Mon, 15 Dec 2014 15:45:20 GMT

The real time increased as well although I generally don't pay much attention to real time. They're not unreliable when it comes to determining the efficiency of a program.

Re: Sql Insert vs Data Step Efficiency Question

Kurt_Bremser — Mon, 15 Dec 2014 15:56:17 GMT

For me, efficiency means the time spent to achieve a result. 100 CPU seconds in 100 seconds real time is therefore more efficent than 5 seconds CPU time in 10 minutes real time.

Granted that real time depends very much on what else is going on in a certain system at a certain time, so you need more than one run to come to a valid conclusion.

SAS SQL is often a notorious I/O resource hog (which reflects mainly in real time), that's why I asked the question.

Watch which files (work directory, utility location) are created during the steps and to what size they grow.

Re: Sql Insert vs Data Step Efficiency Question

LinusH — Tue, 16 Dec 2014 15:56:58 GMT

PROC APPEND should be the most efficient technique.

Perhaps you could assign 0 to field3 when creating Table2?

Re: Sql Insert vs Data Step Efficiency Question

Howles — Wed, 17 Dec 2014 21:32:20 GMT

What I don't know is how the SQL statement is optimized by PROC SQL.

Maybe the SELECT is evaluated and the result set is spooled somewhere. Then the rows are batch-inserted into the target table. That seems to be the original poster's theory.

Maybe the SELECT is evaluated and as each row is generated it triggers a single-row insertion operation. That's my hunch.

The results will of course be the same either way, but the incurrence of overhead could differ.

Re: Sql Insert vs Data Step Efficiency Question

ballardw — Wed, 17 Dec 2014 21:50:46 GMT

A possibly obnoxious confounder in some of this is SAS will attempt to keep some of the data in memory. So sometimes a second test involving the same data set(s) may run quicker just because it isn't being read from disk.

Re: Sql Insert vs Data Step Efficiency Question

PGStats — Wed, 17 Dec 2014 23:02:51 GMT

I suspect that the default UNDO_POLICY=REQUIRED option forces SAS to do the inserts one at a time. Setting UNDO_POLICY=NONE may improve performance significantly.

Re: Sql Insert vs Data Step Efficiency Question

Kurt_Bremser — Thu, 18 Dec 2014 07:30:59 GMT

Any operating system that deserves to be called one will do caching on its own, so the second operation on the same data will usually be faster. A thorough test needs to clean the system cache out first and run the compared steps in reverse order to allow an educated guess about efficiency.

Re: Sql Insert vs Data Step Efficiency Question

rileyd — Fri, 16 Jan 2015 21:10:34 GMT

That's not an option in this situation. Thanks for the suggestion though Linus.

Re: Sql Insert vs Data Step Efficiency Question

rileyd — Fri, 16 Jan 2015 21:27:59 GMT

I experimented with the SQLUNDOPOLICY setting and with it set to NONE the CPU time did drop by 30 seconds but this setting generates warning messages for every Insert, Update, and Delete statement. That doesn't work for me and doesn't follow our programming guidelines of No Warning messages in the log. Plus, 30 seconds is still a full minute slower than the original Data step.

I then tried it set to Optional. This in a 9-10 second improvement so while faster it's not anywhere near the CPU speed of the Data step or a similar Proc Append (if that was an option in this situation).

I did crack open the Advanced Techniques and Efficiencies book as Cynthia suggested but haven't had a chance to go through the testing it suggests. Once I do I'll post what I find out.