Prev: Alternate row color in excl using dde
Next: Free Project Management Software Trial Demo
From: dc353 on 16 Jun 2010 17:25
Can someone explain why there is this extraordinary impact to
performance of the following proc sql statement:

%let mdt = 30DEC1986:00:00:00;
17 %LET NEXT_MDT = 31JAN1987:00:00:00;
18 proc sql;
19 create table Temp_crsp_dly as
20 select *
21 from hold.gold_crsp_19671231
22 where ncusip in (select cusip8 from temp_radds)
23 and dt >= "&MDT"DT
24 and dt LT "&NEXT_MDT"DT;
NOTE: Table WORK.TEMP_CRSP_DLY created, with 760 rows and 23 columns.

24 ! QUIT;
NOTE: PROCEDURE SQL used:
real time 1:33.64
cpu time 10.31 seconds
***
*** Notice the real time of 1:33.64, gold_crsp_19671231 has 66
million rows and is indexed on ncusip, dt


Now when I take the macro variables &mdt and &next_mdt out of the
statement it runs in under 10 seconds!! Can anyone explain what's
going on and how might I fix the problem. Thanks





32 proc sql;
33 create table Temp_crsp_dly as
34 select *
35 from hold.gold_crsp_19671231
36 where ncusip in (select cusip8 from temp_radds)
37 and dt >= '30DEC1986:00:00:00'DT
38 and dt LT '31JAN1987:00:00:00'DT;
NOTE: Table WORK.TEMP_CRSP_DLY created, with 760 rows and 23 columns.

38 ! QUIT;
NOTE: PROCEDURE SQL used:
real time 9.70 seconds
cpu time 9.68 seconds



From: dc353 on 16 Jun 2010 21:33
On Jun 16, 5:25 pm, "dc...(a)hotmail.com" <dc...(a)hotmail.com> wrote:
> Can someone explain why there is this extraordinary impact to
> performance of the following proc sql statement:
>
>         %let mdt = 30DEC1986:00:00:00;
> 17    %LET NEXT_MDT = 31JAN1987:00:00:00;
> 18   proc sql;
> 19   create table Temp_crsp_dly as
> 20   select *
> 21   from hold.gold_crsp_19671231
> 22   where ncusip in (select cusip8 from temp_radds)
> 23   and dt >= "&MDT"DT
> 24   and dt LT "&NEXT_MDT"DT;
> NOTE: Table WORK.TEMP_CRSP_DLY created, with 760 rows and 23 columns.
>
> 24 !                          QUIT;
> NOTE: PROCEDURE SQL used:
>       real time           1:33.64
>       cpu time            10.31 seconds
> ***
> ***  Notice the real time of 1:33.64, gold_crsp_19671231 has 66
> million rows and is indexed on    ncusip,   dt
>
> Now when I take the macro variables &mdt and &next_mdt out of the
> statement it runs in under 10 seconds!! Can anyone explain what's
> going on and how might I fix the problem.  Thanks
>
> 32   proc sql;
> 33   create table Temp_crsp_dly as
> 34   select *
> 35   from hold.gold_crsp_19671231
> 36   where ncusip in (select cusip8 from temp_radds)
> 37   and dt >= '30DEC1986:00:00:00'DT
> 38   and dt LT '31JAN1987:00:00:00'DT;
> NOTE: Table WORK.TEMP_CRSP_DLY created, with 760 rows and 23 columns.
>
> 38 !                                                           QUIT;
> NOTE: PROCEDURE SQL used:
>       real time           9.70 seconds
>       cpu time            9.68 seconds

I've discovered that the index is not being use in the first case.
I've tried the following to correct the problem, but still can't get
it to work. any suggestions??

%let mdt =&mdt;
%let next_mdt = &next_mdt;
%let mdt = %bquote('&mdt'dt);
%let next_mdt = %bquote('&next_mdt'dt);
%put &mdt &next_mdt;


proc sql;
create table Temp_crsp_dly as
select *
from hold.gold_crsp_19671231
where ncusip in (select cusip8 from temp_radds)
and dt >= &MDT
and dt < &NEXT_MDT; QUIT;



MLOGIC(RADD): %LET (variable name is MDT)
SYMBOLGEN: Macro variable MDT resolves to 27FEB1987:00:00:00
MLOGIC(RADD): %LET (variable name is NEXT_MDT)
SYMBOLGEN: Macro variable NEXT_MDT resolves to 31MAR1987:00:00:00
MLOGIC(RADD): %LET (variable name is MDT)
SYMBOLGEN: Macro variable MDT resolves to 27FEB1987:00:00:00
MLOGIC(RADD): %LET (variable name is NEXT_MDT)
SYMBOLGEN: Macro variable NEXT_MDT resolves to 31MAR1987:00:00:00
MLOGIC(RADD): %PUT &mdt &next_mdt
SYMBOLGEN: Macro variable MDT resolves to '27FEB1987:00:00:00'dt
SYMBOLGEN: Some characters in the above value which were subject to
macro quoting have been unquoted
for printing.
SYMBOLGEN: Macro variable NEXT_MDT resolves to '31MAR1987:00:00:00'dt
SYMBOLGEN: Some characters in the above value which were subject to
macro quoting have been unquoted
for printing.
'27FEB1987:00:00:00'dt '31MAR1987:00:00:00'dt
MPRINT(RADD): proc sql;
SYMBOLGEN: Macro variable MDT resolves to '27FEB1987:00:00:00'dt
SYMBOLGEN: Some characters in the above value which were subject to
macro quoting have been unquoted
for printing.
NOTE: Line generated by the macro variable "MDT".
1 '27FEB1987:00:00:00'dt
-
22
-
76
SYMBOLGEN: Macro variable NEXT_MDT resolves to '31MAR1987:00:00:00'dt
SYMBOLGEN: Some characters in the above value which were subject to
macro quoting have been unquoted
for printing.
MPRINT(RADD): create table Temp_crsp_dly as select * from
hold.gold_crsp_19671231 where ncusip in
(select cusip8 from temp_radds) and dt >= '27FEB1987:00:00:00'dt and
dt < '31MAR1987:00:00:00'dt;
ERROR 22-322: Syntax error, expecting one of the following: a name, a
quoted string,
a numeric constant, a datetime constant, a missing
value, (, +, -, ALL, ANY, BTRIM,
CALCULATED, CASE, INPUT, LOWER, PUT, SELECT, SOME,
SUBSTRING, TRANSLATE, UPPER, USER.

ERROR 76-322: Syntax error, statement will be ignored.

MPRINT(RADD): QUIT;
 | 
Pages: 1
Prev: Alternate row color in excl using dde
Next: Free Project Management Software Trial Demo