Help using Base SAS procedures

PROC SQL JOINS PERFORMANCE

Reply
Regular Contributor
Posts: 157

PROC SQL JOINS PERFORMANCE

Hi,

Does using left joins on Non-Key fields when we have huge data creates a performance issue.If it creates then what is the best way to over come that.

test is my data set in SAS and the remaining tables are from data base.

ID AND ID1 are two different Id's and so i called the data base table again in the same query.

For example

Proc Sql;

  create table test as

select t1.*,t2.Name,t3.Address

From test AS t1 LEFT JOIN DB2_CON.. Name_table AS t2 ON (t1.Id=t2.Id)

LEFT JOIN DB2_CON.. Address_table AS t3 ON (t2.state_id=t3.State_id)

LEFT JOIN DB2_CON.. Name_table AS t4 ON (t1.id1=t2.Id1)

LEFT JOIN DB2_CON..Address_table AS t5 ON (t2.state_id=t5.State_id);

QUIT;

I hope it is clear and i appreciate your help.

Regular Contributor
Posts: 179

PROC SQL JOINS PERFORMANCE

Hi,

Do you have indexes created on the keys you are comparing against?

That should help, especially if the # of rows in each table is significantly different.

--Ben

Respected Advisor
Posts: 4,920

Re: PROC SQL JOINS PERFORMANCE

I don't understand why you wouldn't use :

Proc Sql;
create table test as
select t1.*, t2.Name, t3.Address
From test AS t1
LEFT JOIN DB2_CON.. Name_table AS t2 ON ((t1.Id=t2.Id) or (t1.id1=t2.id1))
LEFT JOIN DB2_CON.. Address_table AS t3 ON (t2.state_id=t3.State_id);
QUIT;

and cut the number of joins by half. I don't understand the syntax in red either...

PG

PG
Regular Contributor
Posts: 157

Re: PROC SQL JOINS PERFORMANCE

Hi,

The things in red are the connectors to the Data base.As far as the jons i can't use OR as there are different id's and they both will be there .I forgot to keep Name1 and Name 2. Address 1 and Address 2.It's my bad

Proc Sql;

  create table test as

select t1.*,t2.Name1 t2.Name2,t3.Address1 and t3.Address2

From test AS t1 LEFT JOIN DB2_CON.. Name_table AS t2 ON (t1.Id=t2.Id)

LEFT JOIN DB2_CON.. Address_table AS t3 ON (t2.state_id=t3.State_id)

LEFT JOIN DB2_CON.. Name_table AS t4 ON (t1.id1=t2.Id1)

LEFT JOIN DB2_CON..Address_table AS t5 ON (t2.state_id=t5.State_id);

QUIT;

Regular Contributor
Posts: 233

Re: PROC SQL JOINS PERFORMANCE

I was having the same issue with performance. It takes hours and hours for my code to run. When I started creating hash tables in SAS, the performance has been increased. Normally my left joins takes 3 - 4 hours to run that has about 50 millions of data but now with hash tables it takes about half hour to run.

Hash tables are best way to perform joins and I love it. It has some delimitations as well.

http://www2.sas.com/proceedings/forum2008/029-2008.pdf

Regular Contributor
Posts: 157

Re: PROC SQL JOINS PERFORMANCE

Hi Hima,

Thx fo ryou reply.I have one questions do  you use hashing in every scenario or when one data set is small and the other one is large.

Regular Contributor
Posts: 233

Re: PROC SQL JOINS PERFORMANCE

I have created hash tables in every scenario and it worked fine for me. The results matched with the proc sql left join results. Over all the run time has reduced.

Respected Advisor
Posts: 4,920

Re: PROC SQL JOINS PERFORMANCE

One option to consider is pushing test onto the server in a temporary table as explained in :

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002677163.htm

and have the DB2 server do the joins.

PG

PG
Super User
Posts: 3,252

Re: PROC SQL JOINS PERFORMANCE

The reason for the slow performance is SAS has to read all of your selected data out of DB2 into the SAS environment BEFORE it can compare it with your TEST dataset. In extreme examples you could be reading millions of records out of DB2 to join to a handful of rows in SAS.

There are a number of strategies for dealing with this that have been covered in previous posts. PGStat's method is one of these. How many distinct IDs are in your TEST dataset?

Frequent Contributor
Posts: 110

PROC SQL JOINS PERFORMANCE

use proc sql pass through

Regular Contributor
Posts: 157

Re: PROC SQL JOINS PERFORMANCE

Hi,

i am having 100 records and duplicates are possible in my test data set.Can you refer to me a best strategy

Frequent Contributor
Posts: 110

PROC SQL JOINS PERFORMANCE

try to use dbkey option in your code. it definitely imroves performance when you are joining sas data set and dbms table.

Regular Contributor
Posts: 233

PROC SQL JOINS PERFORMANCE

Using DBkey might create incorrect results sometimes.

http://support.sas.com/kb/42/979.html

Respected Advisor
Posts: 4,920

Re: PROC SQL JOINS PERFORMANCE

Only 100 records, well then, how about never joining test with the DB2 tables? Very low tech :

Proc Sql;

select id into :id seperated by ","
from test where id is not missing;

select id1 into :id1 seperated by ","
from test where id1 is not missing;

create table ids as
select T2.*, T3.Address1 from DB2_CON.Name_table AS t2
LEFT JOIN DB2_CON.Address_table AS t3 ON (t2.state_id=t3.State_id)
where T2.id in (&id.);

create table id1s ...

you get the idea. And then join local tables test, ids and id1s...


QUIT;

PG

PG
Super User
Posts: 3,252

PROC SQL JOINS PERFORMANCE

With only 100 records/values PGSTAT's approach is a popular strategy - building a text string of the ID values to be selected. If ID is a character column then you may have to wrap and values in quotes. It should give you much improved performance as all of the data selection happens in DB2 before SAS reads it. 

Ask a Question
Discussion stats
  • 14 replies
  • 641 views
  • 0 likes
  • 6 in conversation