DATA Step, Macro, Functions and more

Join vs sub query

Reply
Frequent Contributor
Posts: 79

Join vs sub query

hi guys,

 

I am a bit confused regarding using the join or the join statements, we don't know which is more efficient, option 1 or option 2. We are using SAS EG, 6.1 and the data is quite huge in the tables, almost 6Million records.

 

We have used the below option 1 to fetch the required datsets using the sub-query but there another approach which others as suggesting to use the option 2 to use the joins.

 

As per them, option 1 splits the query as 'where' and 'OR' clause,  and split of all the product ids,

like 'where product_id = 782656 or product_id =78555 or product_id = 55421268 and so on.....'

 

 

/* dataset having the required product_idd with price>100 */
proc sql;
create table all_items as select distinct(product_id) from catalogue where price >100;
quit;

/*Option1:*/
proc sql;
create table items as 
select product_id, item_id, price, description from catalogue where product_id in (select * from all_items);
quit;


/*Option2:*/
proc sql;
create table items as 
select a.product_id, a.item_id, a.price, a.description from catalogue as a
inner join all_items as b
on  a.product_id =  b.product_id;
quit;

 

However as per my understanding both queries are taking same time, does sas automatically optimises the proc sql queries and gives the same result and in same cpu and time usage?

 

Thanks for the help!

 


Cheers from India!

Manjeet
Super User
Posts: 5,922

Re: Join vs sub query

You can use the FEEDBACK to see how SAS normalizes the query.

You can use the undocumented option _method to see how SAS actually are treating the query.

From optimizing perspective, indexing can in some situations help.

You can set UBUFSIZE option to higher than the default 64K to make the likelihood of hash join to take place. 

Data never sleeps
Super User
Posts: 10,623

Re: Join vs sub query

How many observations does all_items have? This will determine if there are methods available that will outperform your SQL considerably.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 79

Re: Join vs sub query

Posted in reply to KurtBremser

all_items have almost 64 million rows.


Cheers from India!

Manjeet
Super User
Posts: 10,623

Re: Join vs sub query


@mnjtrana wrote:

all_items have almost 64 million rows.


Can't be. A subset of 6 million rows (as stated in your original post) can't have 64 million rows.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 79

Re: Join vs sub query

Posted in reply to KurtBremser
Opps, may bad, all_items have almost 4 million rows.

Cheers from India!

Manjeet
Super User
Posts: 10,623

Re: Join vs sub query


@mnjtrana wrote:
Opps, may bad, all_items have almost 4 million rows.

Ok.

Depending on the size of your index variable, you might be able to fit a format (or a hash table) based on this into memory, so you could solve your task with two sequential passes (one through all_items for the format, the other for the main dataset).

Alternatively, if your main dataset is already sorted along product_id you can do this:

data want;
merge
  catalogue (
    in=a
    keep=product_id price
    rename=(price=_price)
    where=(_price > 100)
  )
  catalogue
;
by product_id;
if a;
drop _price;
run;

Physically, this would be a single pass through the data, as most (if not all) would be present in cache for the seciond read.

 

I always like to keep my datasets sorted along the most important key.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,860

Re: Join vs sub query

JOIN is more efficient,

Ask a Question
Discussion stats
  • 7 replies
  • 185 views
  • 2 likes
  • 4 in conversation