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 09:27:18 -0700
Reply-To:     Shaheen <siqubal@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Shaheen <siqubal@GMAIL.COM>
Subject:      Re: Running PROC SQL is very slow in SAS. Is there a way to
Comments: To:
Content-Type: text/plain; charset=ISO-8859-1

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