Help with SQL Query Optimization

Can someone please help with optimizing the below query. Initially I wanted to use the OR operator for the join since I want to join two columns in the one table with only 1 column in the other but my criteria is OR i.e., if any one of those match, go for the join. But that was causing the CPU to be at 100% so I now try the below code but that still is cuasing the CPU to reach 100%.

Please help with this query, perhaps there is a better way but I certainly cant seem to think of any.
---------------

select *
from (
select a.1a,
      a.2a,
      a.3a,
      a.4a,
      b.1b,
      b.2b,
      b.3b
from a (nolock)
right join b (nolock)
on a.id in (b.1b, b.2b)
where b.id not in ('DELETED', 'N/A')
) a
where a.id is null
bikieswimAsked:
Who is Participating?
 
Computer101Connect With a Mentor Commented:
PAQed with points refunded (125)

Computer101
Community Support Moderator
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:

select b.1b,
      b.2b,
      b.3b
from b (nolock)
left join a (nolock)
  on a.id in (b.1b, b.2b)
 and b.id not in ('DELETED', 'N/A')
 where a.id is null

now, what are the indexes on those tables?
0
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
You are referencing a.id in your final where clause.
Yet you are not returning an ID field in the intermediate table you creating from the select.
Also your reference to table a is confusing since you already have an inner table a.
Check how I have changed this and see if that is what you really want.

select c.*
from (
select a.1a,
      a.2a,
      a.3a,
      a.4a,
      b.1b,
      b.2b,
      b.3b,
      a.id
from a (nolock)
right join b (nolock)
on a.id in (b.1b, b.2b)
where b.id not in ('DELETED', 'N/A')
) c
where c.id is null
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
MikeTooleCommented:
Better to use two simple Selects and to UNION them together

select a.1a, a.2a, a.3a,  a.4a, b.1b,  b.2b, b.3b
from a  right join b on a.id = b.1b
where b.id not in ('DELETED', 'N/A') and a.id is null
Union
select a.1a, a.2a, a.3a,  a.4a, b.1b,  b.2b, b.3b
from a  right join b on a.id = b.2b
where b.id not in ('DELETED', 'N/A') and a.id is null

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
@MikeToole:
did you think about the results? actually, they will not be correct:

say a.id = 1
and you have 1 row in b with 1b = 1 and 2b = 2

the second part of you query will return a.id = 1, because it does not match 2b = 2, but that is a "wrong" match compared to the original query.

however, the idea of splitting the query into 2 is not bad, you just need to JOIN instead of UNION:

select b1.*
from (
select b.1b,
      b.2b,
      b.3b
from b (nolock)
left join a (nolock)
  on a.id = b.1b
 and b.id not in ('DELETED', 'N/A')
 where a.id is null
) b1
JOIN (
select b.1b,
      b.2b,
      b.3b
from b (nolock)
left join a (nolock)
  on a.id = b.2b
 and b.id not in ('DELETED', 'N/A')
 where a.id is null
) b2
ON b1.1b = b2.2b

0
 
bikieswimAuthor Commented:
- Thanks for the comments guys but I cannot use UNION since I have two columns that I have to match on the same select either one of them matches with the join. If I use join, then it will throw my results off.  For example I have an apple in table A but table B can have apple in either of the two columns. If I use UNION or split the code into two queries, it would work FINE FOR THE FIRST PART, where in it will give me the result in either the first query or the second. But the problem would be with the RIGHT JOIN. So for the first query when i do the right join, it will give me wrong results becuase they actually match with the second query condition and viceversa. Hope I have explained it ok here :)-
- Now, yes, it should have been C for the outer join, it is my mistake in creating this dummy code but believe me the actual code has different tables :)-
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>If I use join, then it will throw my results off.
can you explain that? I am "sure" that the JOIN (second suggestion) should work fine

the explanation of the UNION problem is the same I gave already...

also, did you check my first suggestion?
0
 
MikeTooleCommented:
<Thanks for the comments guys but I cannot use UNION since I have two columns that I have to match on the same select either one of them matches with the join>
The reason to use UNION is because you have two colums that you want to match on:
When a1 = 1, you want a result row if either b1 = 1 OR b2 = 1
The only downside is that you get two rows if b1=1 and b2 = 1 - and that could be handled
@AngelIII:
<say a.id = 1
and you have 1 row in b with 1b = 1 and 2b = 2
the second part of you query will return a.id = 1, because it does not match 2b = 2, but that is a "wrong" match compared to the original query.>
The second part of the query returns nothing  because a.id = b.2b is false


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
@MikeTools:
you overlooked that it is here a OUTER JOIN, not a INNER JOIN...
0
 
bikieswimAuthor Commented:
Well guys figured out myself, the key was to use the NOT operator. So when I match the first table apple with the second table orange on column1 like A1 = C1 I do A1 <> C2. Then I would do a UNION and do the same thing over in viceversa like A1<>C1 and A1 = C2. This would get me the records I actually want again, this is all right join. That did the trick. Thanks a lot for trying to help out my friends :)-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.