SAS ENTERPRISE GUIDE PEFORMANCE

[vallsas]

Hi All,

I have experienced the performance issue while querying the data.

step1: Open the data in EG (from library (data from oracle schema)  and did query in query builder and it took 5 seconds to get the result.

step2: same query i have writtten proc sql query  in EG - program editor the query ran in 11 seconds.

Would like to know by architecture of sas the difference in time is accepted or any other factors should be considered?

thanks in advacnce.

[Kurt_Bremser]

Please copy/paste both complete codes into code boxes here.

[vallsas]

let ceral_id=2745

proc sql;

select gr into :gr from maingro.cerals where ceral_id=&ceral_id;

quit;

proc sql;

create table grocery2 as 

select  tgr1,gr2,gr3,gr4

from maingro.Grocery where gr =&gr;

quit.

2)from query of query builder selecting directly from maingro.Grocery table and filtering the passing value of gr.

this will run faster then the above code.

Thank you for your time

[Kurt_Bremser]

The query builder creates its own code, which is shown in a Code tab. Please post that.

Use a code box opened with the "little running man" button to post SAS code.

[vallsas]

%_eg_conditional_dropds(work.query_for_grocery);

PROC SQL;
 
     CREATE TABLE WORK.QUERY_FOR_GROCERY AS

    SELECT * FROM MAINGROCERY.GROCERY 

    WHERE GR ='XXXXXX';

QUIT;

Is there any difference between passing numeric value is faster than the character value in the query?

[Kurt_Bremser]

Create a large enough dataset with a numeric and a character variable containing the same spread (leading to identical subsets), then run your queries and compare the times. I would be mighty surprised if there was a significant difference.

[ChrisHemedinger]

The code you supplied is actually two queries -- one for the SELECT INTO and then one to CREATE TABLE.  The Query Builder will produce only a single query, so if you're comparing the two runs, they are not the same work.

You could optimize your user-written code with a nested query (sub-select) so that there is just one transaction to the database, which might save time.

[vallsas]

thank you .

first query runs one time only because macro variable will store. so that if you run again the second query it will be one query which we are comparing the time with query builder query.

i will check about the nested query which you mentioned.

thanks