BookmarkSubscribeRSS Feed
JasonNC
Quartz | Level 8

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.

14 REPLIES 14
BenConner
Pyrite | Level 9

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

PGStats
Opal | Level 21

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
JasonNC
Quartz | Level 8

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;

Hima
Obsidian | Level 7

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

JasonNC
Quartz | Level 8

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.

Hima
Obsidian | Level 7

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.

PGStats
Opal | Level 21

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
SASKiwi
PROC Star

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?

sassharp
Calcite | Level 5

use proc sql pass through

JasonNC
Quartz | Level 8

Hi,

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

sassharp
Calcite | Level 5

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

Hima
Obsidian | Level 7

Using DBkey might create incorrect results sometimes.

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

PGStats
Opal | Level 21

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
SASKiwi
PROC Star

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 2931 views
  • 0 likes
  • 6 in conversation