topic Re: SAS ENTERPRISE GUIDE PEFORMANCE in SAS Enterprise Guide

SAS ENTERPRISE GUIDE PEFORMANCE

vallsas — Fri, 18 Sep 2020 05:35:17 GMT

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.

Re: SAS ENTERPRISE GUIDE PEFORMANCE

Kurt_Bremser — Fri, 18 Sep 2020 06:17:38 GMT

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

Re: SAS ENTERPRISE GUIDE PEFORMANCE

vallsas — Fri, 18 Sep 2020 09:58:00 GMT

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

Re: SAS ENTERPRISE GUIDE PEFORMANCE

Kurt_Bremser — Fri, 18 Sep 2020 11:00:57 GMT

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.

Re: SAS ENTERPRISE GUIDE PEFORMANCE

ChrisHemedinger — Fri, 18 Sep 2020 12:43:25 GMT

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.

Re: SAS ENTERPRISE GUIDE PEFORMANCE

vallsas — Sat, 19 Sep 2020 08:22:49 GMT

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

Re: SAS ENTERPRISE GUIDE PEFORMANCE

vallsas — Sun, 20 Sep 2020 06:50:17 GMT

%_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?

Re: SAS ENTERPRISE GUIDE PEFORMANCE

Kurt_Bremser — Sun, 20 Sep 2020 07:10:27 GMT

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.