Check a value against multiple columns in diff tab

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Check a value against multiple columns in diff tab

Author

Topic

gems
Starting Member

19 Posts

Posted - 2014-11-25 : 10:28:46

Hi,

I have two tables table1 and table2. I want to check a value from table1 against 4 different columns in table 2. What would be the most optimized way to do this. I came up with this SQL but it runs forever.

select * from table1 a
where
(a.id in (select orig_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select benef_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select send_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select rcv_id from table2 where exctn_dt >= '01-OCT-14'));

Thanks a lot!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-25 : 10:50:54

It runs forever since the way you've written the subqueries make it an O(n^2) runtime, where n is the largest count of the main and sub queries.

Do you have columns you can join on? Can you rewrite like this:


select * from table1 a
LEFT join (select orig_id from table2 where exctn_dt >= '01-OCT-14') b
  ON a.___ = b.___
left join table3 (...) c
  ON a.___ = c.___

...etc...

where b.orig_id is not null or
      c. benef_id is not null or
      ... etc. ...

Then, make sure the join columns and where predicates are indexed.

gems
Starting Member

19 Posts

Posted - 2014-11-25 : 11:18:45

Thanks! You are right. Will try your suggestion.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-25 : 12:48:42


SELECT *
FROM table1 T1
WHERE EXISTS
(
	SELECT 1
	FROM table2 T2
	WHERE T2.exctn_dt >= '20141001'
		AND
		(
			T1.id = T2.orig_id
			OR T1.acct_intrl_id = T2.benef_id
			OR T1.acct_intrl_id = T2.send_id
			OR T1.acct_intrl_id = T2.rcv_id
		)
);

gems
Starting Member

19 Posts

Posted - 2014-11-25 : 14:41:34

Thanks guys. Both the queries were far more efficient and ran much faster. Had one more question. I also want to see the fields from table2. How can I change the query to include fields from table2?

Thanks again.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-26 : 09:39:13

just include them in the select list from my sample code. For Ifor's code, you'll need to do a bit more work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources