Date: Thu, 16 Jun 2005 12:30:46 -0500
Reply-To: Carol Bristow <Carol.Bristow@DPRA.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Carol Bristow <Carol.Bristow@DPRA.COM>
Subject: Re: PROC SQL takes too much time: can't optimize
Content-Type: text/plain; charset="us-ascii"
Run it on a faster computer? <VBG> Sorry, I couldn't resist.
Seriously, your SQL code is joining two tables together with no
definition of how to join them. As a result, you will get every record
joined with every record, and that means a whole lot of I/O. That will
be true in any database/language that you use, regardless of any indexes
that might exist.
Do your tables have any fields in common, or do you truly want the
Cartesian product?
Carol Bristow
DPRA Incorporated
1300 N 17th St Suite 950
Rosslyn, VA 22209
Work: 703-841-8025
Fax: 703-524-9415
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Igor Kurbeko
Sent: Thursday, June 16, 2005 1:20 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: PROC SQL takes too much time: can't optimize
Hello fellow SAS programmers.
I run proc sql in the middle of my program and it takes too much time.
I'm given a note that: NOTE: The execution of this query involves
performing one or more Cartesian product joins that can not be
optimized.
Any suggestions how to speed it up?
Thanks
proc sql noprint;
create table mine as
select distinct ' ' as ae , &bodysys., &texteng., rand.groupnum
from final,rand ;
quit;
NOTE: The execution of this query involves performing one or more
Cartesian product joins that can not be optimized.
NOTE: Table TAD_A42.MINE created, with 1312 rows and 4 columns.
MPRINT(AE_TE_TABLE_MACRO): quit;
NOTE: PROCEDURE SQL used:
real time 11:03.00
cpu time 2:45.73
Igor Kurbeko
Clinical Programmer Analyst
678 336 4328
ikurbeko@atherogenics.com <mailto:ikurbeko@atherogenics.com>
<mailto:ikurbeko@atherogenics.com>
If you torture data sufficiently, it will confess to almost anything.
