?
Solved

Help with SQL Query Optimization

Posted on 2007-10-03
11
Medium Priority
?
242 Views
Last Modified: 2010-03-20
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
0
Comment
Question by:bikieswim
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20009004
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
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 20009013
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
 
LVL 27

Expert Comment

by:MikeToole
ID: 20009034
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20009074
@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
 

Author Comment

by:bikieswim
ID: 20009231
- 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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20009259
>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
 
LVL 27

Expert Comment

by:MikeToole
ID: 20010329
<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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20010394
@MikeTools:
you overlooked that it is here a OUTER JOIN, not a INNER JOIN...
0
 

Author Comment

by:bikieswim
ID: 20018310
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
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 20943196
PAQed with points refunded (125)

Computer101
Community Support Moderator
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Viewers will learn how the fundamental information of how to create a table.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question