CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Vignesh
Vignesh

Reputation: 215

SQL Server query perfomance tuning with group by and join clause

We have been experiencing performance concerns over job and I could fortunately find the query causing the slowness.. 

select   name from Student a, Student_Temp b
where a.id = b.id and
a.name in (select name from Student
group by name having count(*) = @sno)
group by a.name having count(*) = @sno
OPTION (MERGE JOIN, LOOP JOIN)

This particular query is iteratively called many times slowing down the performance..

Student table has 8 Million records and Student_temp receives 5-20 records in the iteration process each time. Student table has composite primary key on ( id and name) and sno = No of records in Student_Temp.

My questions are below, 1) why does this query show performance issues. 2) could you guys give a more efficient way of writing this piece ?

Thanks in Advance !

Upvotes: 0

Views: 817

Answers (3)

Abdul Rehman Zafar
Abdul Rehman Zafar

Reputation: 11

Here you go

select   name 
from Student a
inner join Student_Temp b
on a.id = b.id
group by a.name 
HAVING COUNT(*) = @sno

Upvotes: 0

Twinkles
Twinkles

Reputation: 1994

Your query returns the following result: Give me all names that are @sno times in the table Student and exactly once in Student_temp.

You can rewrite the query like this:

SELECT a.name
FROM Student a
INNER JOIN Student_temp b
ON a.id = b.id
GROUP BY a.name
HAVING COUNT(*) = @sno

You should omit the query hint unless you are absolutely sure that the query optimizer screws up.

EDIT: There is of course a difference between the queries: if for instance @sno=2 then a name that shows up once in Student but twice in Student_temp would be included in my query but not in the original. I depends on what you really want to achieve whether that needs to be adressed or not.

Upvotes: 1

richard
richard

Reputation: 12498

It's repeating the same logic unnecessarily. You really just want:

  • Of the Student(s) who also exist in Student_temp
  • what names exist @sno times?

Try this:

SELECT
    name
FROM
    Student a JOIN
    Student_Temp b ON a.id = b.id
GROUP BY
    name
HAVING
    count(*) = @sno

Upvotes: 1

Related Questions