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.
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
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
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;
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.
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.
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.
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
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?
use proc sql pass through
Hi,
i am having 100 records and duplicates are possible in my test data set.Can you refer to me a best strategy
try to use dbkey option in your code. it definitely imroves performance when you are joining sas data set and dbms table.
Using DBkey might create incorrect results sometimes.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.