topic Re: PROC SQL JOINS PERFORMANCE in SAS Procedures

PROC SQL JOINS PERFORMANCE

JasonNC — Mon, 05 Mar 2012 15:17:51 GMT

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.

PROC SQL JOINS PERFORMANCE

BenConner — Mon, 05 Mar 2012 16:15:13 GMT

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

Re: PROC SQL JOINS PERFORMANCE

PGStats — Mon, 05 Mar 2012 16:17:33 GMT

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...

Re: PROC SQL JOINS PERFORMANCE

Hima — Mon, 05 Mar 2012 16:55:39 GMT

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

Re: PROC SQL JOINS PERFORMANCE

JasonNC — Mon, 05 Mar 2012 17:18:37 GMT

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;

Re: PROC SQL JOINS PERFORMANCE

JasonNC — Mon, 05 Mar 2012 17:21:56 GMT

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.

Re: PROC SQL JOINS PERFORMANCE

Hima — Mon, 05 Mar 2012 17:30:40 GMT

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.

Re: PROC SQL JOINS PERFORMANCE

PGStats — Mon, 05 Mar 2012 17:32:05 GMT

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.

Re: PROC SQL JOINS PERFORMANCE

SASKiwi — Mon, 05 Mar 2012 19:16:51 GMT

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?

PROC SQL JOINS PERFORMANCE

sassharp — Mon, 05 Mar 2012 19:40:54 GMT

use proc sql pass through

Re: PROC SQL JOINS PERFORMANCE

JasonNC — Mon, 05 Mar 2012 20:54:41 GMT

Hi,

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

Re: PROC SQL JOINS PERFORMANCE

PGStats — Mon, 05 Mar 2012 21:30:49 GMT

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;

PROC SQL JOINS PERFORMANCE

sassharp — Mon, 05 Mar 2012 21:36:33 GMT

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

PROC SQL JOINS PERFORMANCE

Hima — Mon, 05 Mar 2012 21:45:07 GMT

Using DBkey might create incorrect results sometimes.

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

PROC SQL JOINS PERFORMANCE

SASKiwi — Mon, 05 Mar 2012 23:52:29 GMT

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.