SQL Server Performance Forum – Threads Archive

Bizzare query performance

Hi there,
The bad performance of the following query is realy ununderstood. select * from TABLE_A
WHERE TABLE_A.pin in
(
select top 2 TABLE_A.pin
FROM TABLE_B
INNER JOIN TABLE_A ON TABLE_B.PIN = TABLE_A.Pin
WHERE TABLE_A.Group_Id = 27290 AND
TABLE_B.postcode = ‘3671’
and TABLE_A.Group_Id = 27290
AND (TABLE_A.KeyCode IS NULL)
)
and TABLE_A.Group_Id = 27290 after 55 minutes of stucking the connection has broken, The problem is that
1. when I run the sub query (what in the IN()) seperatly I get the results immediatly: only 1 row returned as i expected. select top 2 TABLE_A.pin
FROM TABLE_B
INNER JOIN TABLE_A ON TABLE_B.PIN = TABLE_A.Pin
WHERE TABLE_A.Group_Id = 27290 AND
TABLE_B.postcode = ‘3671’
and TABLE_A.Group_Id = 27290
AND (TABLE_A.KeyCode IS NULL) results:
pin
———–
100214540 (1 row(s) affected) 2. when I take the result of step 1 and replace the IN with that result, running:
select * from TABLE_A
where pin = 100214540
and TABLE_A.Group_Id = 27290 the performance is excelent. If so, why the sub query which should take a second when run seperatly, take years when run a a sub query.
The %proccesor usage for the query in question query was enormous. What could be the reason for that behaviour? Some information:
TABLE_A: 18mil rows, Clusterd index on Group_Id, pin
TABLE_B: 12.5mil rows, Unique Clusterd Index on PIN Any help and insightful explanations will be appreciated Cheers. Aviel Iluz
Database Administrator
Pacific Micromarketing
Level 2, 616 St Kilda Road
Melbourne VIC 3004
Australia
Ph: +61 3 8517 3930
Fax: +61 3 9530 2350
www.pacmicro.com.au

---

Try this…. select * from TABLE_A
INNER JOIN (
select top 2 TABLE_A.pin
FROM TABLE_B
INNER JOIN TABLE_A ON TABLE_B.PIN = TABLE_A.Pin
WHERE TABLE_A.Group_Id = 27290 AND
TABLE_B.postcode = ‘3671’
and TABLE_A.Group_Id = 27290
AND (TABLE_A.KeyCode IS NULL)
) B
ON TABLE_A.pin = B.pin
and TABLE_A.Group_Id = 27290 Regards
Sanette

---

and TABLE_A.Group_Id = 27290
is not required thrice in the query. Try writing the query using EXISTS and see if it makes any difference.
select * from TABLE_A Outer
WHERE EXISTS
(
select top 2 TABLE_A.pin
FROM TABLE_B
INNER JOIN TABLE_A INNER ON TABLE_B.PIN = INNER.Pin
WHERE INNER.Group_Id = 27290 AND
TABLE_B.postcode = ‘3671’
AND INNER.KeyCode IS NULL
AND Outer.pin = INNER.pin
) Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

TABLE_A has an index on Group_Id, pin. So I think that you would want to use the Group_Id in the outer query or you will end up with table scan. Bambola

---

Thanks, Sanette, for your smart suggestion. It does work well and I have tried it before.
What is intersting me is why is there a differnece between the two cases, regarding the Optimizer plans. I have posted my original message with some more information and details in the "Performance Tuning for DBAs" forum. Pleaes tsee it there (same subject) Cheers,
Aviel Iluz
Database Administrator
Pacific Micromarketing
Level 2, 616 St Kilda Road
Melbourne VIC 3004
Australia
Ph: +61 3 8517 3930
Fax: +61 3 9530 2350
www.pacmicro.com.au

---

The tpoic has been moved to Performance Tuning for Developers under –http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=1395 Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

The problem doesn’t have anything to do with using table_a twice…? What happens if you use unique correlation names for the tables?

---

I’m locking this topic. There is an active one here
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1395 Bambola.

---

]]>