LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (July 2009, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 7 Jul 2009 11:48:21 -0500
Reply-To:     Joe Matise <snoopy369@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Joe Matise <snoopy369@GMAIL.COM>
Subject:      Re: Running PROC SQL is very slow in SAS. Is there a way to
Comments: To: Shaheen <siqubal@gmail.com>
In-Reply-To:  <c8e0c8fd-ad4b-48f7-b8e7-9839b9d98a58@d32g2000yqh.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1

Any time you run a query on the server, and compare it to a query run off-server, there will be a dramatic time difference (6x is not unreasonable). I/O and network latency will cost you a lot... don't ever expect any off-server tool to compare to on-server tools.

You might consider having the server run a stored procedure, or using pass-through SQL to issue SQL statements to the server, instead of performing the query in libname SQL. Both can alleviate some of the bottlenecks there (in addition to any savings you can gain from increasing your statement's efficiency).

-Joe

On Tue, Jul 7, 2009 at 11:27 AM, Shaheen <siqubal@gmail.com> wrote:

> On Jul 6, 5:03 pm, HERMA...@WESTAT.COM (Sigurd Hermansen) wrote: > > Shaheen: > > You might try small changes that may decrease execution time a bit: > > > > PROC SQL; > > CREATE TABLE InvSys.Returns AS > > SELECT RD.ReturnDt, RD.Security_ID, RD.Currency_Code, > C.Currency_Return, ........ > > FROM (select ReturnDt, Security_ID, Currency_Code from > Return_Data > > where Security_ID IN (SELECT Security_ID FROM Ranking_Data) > > and ReturnDt IN (SELECT ReturnDate FROM Dates)) as RD > > LEFT JOIN (SELECT * FROM Currency_Return WHERE Currency_Code = > &RCurr) as C > > ON RD.ReturnDt=C.AsOfDt > > ; > > QUIT; > > > > The more serious bottleneck, so far as my testing goes (through V9.1.3), > has to do with the lack of optimization of the LEFT JOIN in SAS SQL. The > LEFT JOIN optimization plan insists on sorting both datasets. A SAS hash > object solution often performs much more quickly than the SAS SQL left join; > however, your query would be difficult to translate to a SAS Data step. > > > > In these situations I've taken the UNION of the INNER JOIN and the set > complement of the two datasets (the definition of the LEFT JOIN). The INNER > JOIN will likely construct and use a hash index. The set complement of LEFT > with respect to RIGHT takes more programming. > > S > > > > > > > > -----Original Message----- > > From: SAS(r) Discussion [mailto:SA...@LISTSERV.UGA.EDU] On Behalf Of > Shaheen > > Sent: Monday, July 06, 2009 4:34 PM > > To: SA...@LISTSERV.UGA.EDU > > Subject: Running PROC SQL is very slow in SAS. Is there a way to optimize > it > > > > Hi, > > I am running a query that takes about 5 minutes to return some 14000 > > rows. > > > > PROC SQL; > > CREATE TABLE InvSys.Returns > > AS > > ( > > SELECT ReturnDt, Security_ID, Sedol, &ReturnField, Mcap_USD, > > RD.Currency_Code AS Return_Currency '', C.Currency_Return > > FROM InvSys.Return_Data RD > > LEFT JOIN (SELECT * FROM InvSys.Currency_Return WHERE > Currency_Code = > > &RCurr) C > > ON RD.ReturnDt=C.AsOfDt > > WHERE Security_ID IN (SELECT DISTINCT Security_ID FROM > > InvSys.Ranking_Data) > > AND ReturnDt IN (SELECT ReturnDate FROM InvSys.Dates) > > ); > > > > Return_Data table has 3051106 rows and 28 columns > > Currency_Return table has 11743 rows and 4 columns > > Ranking_Data has 12465 rows and 9 columns > > and Dates has 5 rows and 2 columns. > > > > I am a new SAS user and disappointed in SAS data retrival. By the way > > all of these are SAS dataset stored on my local hard drive. > > > > Any help would be highly appreciated. If you can point to a document > > that would help me optimize my SQL queries, that would be great > > because I have other queries that takes more than 8 minutes to run. > > > > Regards, > > Shaheen- Hide quoted text - > > > > - Show quoted text - > > PROC SQL; > CREATE TABLE InvSys.Returns AS > ( > SELECT R.ReturnDt, R.Security_ID, R.Sedol, &ReturnField, R.Mcap_USD, > R.Currency_Code AS Return_Currency '', C.Currency_Return > FROM InvSys.Return_Data R > INNER JOIN InvSys.Dates D ON R.ReturnDt=D.ReturnDate > INNER JOIN (SELECT DISTINCT Security_ID FROM InvSys.Ranking_Data) RD > ON R.Security_ID = RD.Security_ID > LEFT JOIN (SELECT * FROM InvSys.Currency_Return WHERE Currency_Code > = > "USD") C ON R.ReturnDt=C.AsOfDt > ); > QUIT; > > Changed my query and now it takes 3 minutes..... still not happy... is > there a way to optimize this further. I have similar table sitting on > SQL server database and it takes about 30 sec to run this query > there. > > I tried creating an index on ReturnDt field in Return_Data table and > it did not help. Creating an index itself took over 4 minutes. >


Back to: Top of message | Previous page | Main SAS-L page