Date:         Tue, 24 Mar 2009 10:43:33 -0500
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: Reducing production time
Comments: To: murugesh <iammurugesh@GMAIL.COM>
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
              reply-type=original

Also, here's two examples of using pass-through SQL; you  would
put the code for SQL Server inside here.  Use the first if you just
want to execute code on SQL Server, and use the second if you
want to create a SAS dataset from a table or view on SQL Server.

This is for Oracle but the SQL server code is similar.

As Ken said, it is probably the time the data is passing though the
network that is the problem more than the connections, so the idea
of using pass-through SQL rather than SAS SQL is to try to prevent
the data from moving as much.

proc sql noprint;
connect to oracle (user=user password=password path=orcl);
execute(...) by oracle;
disconnect from oracle;
quit;
%put &SQLXMSG;
%put &SQLXRC;


proc sql;
connect to oracle (user=user password=password path=orcl);
create table newtable as
select * from connection to oracle(
select ...from ...);
disconnect from oracle;
quit;
%put &SQLXMSG;
%put &SQLXRC;


-Mary


----- Original Message -----
From: "murugesh" <iammurugesh@GMAIL.COM>
To: <SAS-L@LISTSERV.UGA.EDU>
Sent: Tuesday, March 24, 2009 9:53 AM
Subject: Reducing production time


> Hi All,
>
> I am running more than 25 sas programs (using #include) in one sas
> session. The maximum programs are pulling data from Sql Server
> database, Each programs making new connection to database and pulling
> the data.
>
> The SAS batch was running more than 6 Hours per day.
>
> I want to optimize the code and need to reduce the running time,
>
> Could you please let me know optimization methods which is helpful to
> reduce the running time.
>
> Regards,
> Murugesan P