CODEWITHSUNDEEP
sql-server-2008optimization
Pavi
Pavi

Reputation: 345

optimizing sql query which involves 3 tables and joins

i am having state schools result data. i normalized database and created three tables i.e. marks table, schools table and student table. i want to write the query to get pass of each schools. here i pasted query for getting total students appeared for exams and in the same way i ll get passed students. 

     SELECT a.Year_id
     , school_code
     , count(a.Marks_id) AS TotalAppeared
FROM
  Marks_Table a
  JOIN Master_Student ms
    ON ms.Student_id = a.Student_id
  JOIN Master_School mss
    ON mss.school_id = ms.School_code_int
WHERE
  CANDIDATE_TYPE = 'RF'
  AND TOTAL_MARKS != 0
  AND school_code = 'AS0180'
  AND a.Year_id BETWEEN 5 AND 9
GROUP BY
  a.Year_id
, SCHOOL_CODE

how to optimize this query

Upvotes: 0

Views: 1414

Answers (3)

Stoleg
Stoleg

Reputation: 9310

Your query is fine itself. Create indexes on all tables you are using Clustered, if possible on JOINing column and nonclustered on other columns.

Upvotes: 1

u07ch
u07ch

Reputation: 13702

I can't see your data and some of the table names arent in the where clause which doesnt help. Your query is likely upside down; smallest number of rows come from schools and then pupils (im guessing) before marks.

My basic philosophy for a fast query is filter early and often so you always heave the least rows. Taking my guess on your data; this may be quicker on that principle. If it isnt then running the execution plan and analysing the tables/ indices should help you move further.

 SELECT a.Year_id
 , school_code
 , count(a.Marks_id) AS TotalAppeared
FROM 
    (
    Select
        *
    Master_School mss
    Where
        school_code = 'AS0180'
    ) mss
Join
    Master_Student ms
    ON mss.school_id = ms.School_code_int
    and CANDIDATE_TYPE = 'RF'
Join
    Marks_Table a
    ON ms.Student_id = a.Student_id
    AND TOTAL_MARKS != 0
    and a.Year_id BETWEEN 5 AND 9
GROUP BY
    a.Year_id
    , SCHOOL_CODE

Upvotes: 1

Mez
Mez

Reputation: 4726

You can check the execution plan from the SSMS, and see if you have any missing indexes that could speed up the result. Other than that, if you do not require the last data you can add with(nolock) to the joins. Check this link for other tips http://web.synametrics.com/top10performancetips.htm.

Upvotes: 0

Related Questions