CODEWITHSUNDEEP
sqldatabasealgorithmoptimizationquery-optimization
james
james

Reputation: 1155

most efficient SQL query

I have 2 tables:

T1 {
  id: string;
  school: string
}

and 

T2 { 
  school_id: string; 
  student_id: string; 
  tuition_fee: double 
}

The 2 tables are associated by T1.id and T2.school_id. T1 contains information of each school. It's relatively small. T2 contains information of the tuition fee for each student. It can be huge (millions of records).

Edit:

Now, I want to find out which 3 schools collect most tuition fees from its students. Assume that it's impossible that 2 schools collect the same amount of total tuition fees from all its students. And my solution is as follow:

  1. Group records in T2 by school_id;
  2. Compute sum of tuition_fee in each group;
  3. Sort the groups by the sum in descending order;
  4. Get the top 3.

My questions are:

  1. Any better way to do this?
  2. What's the most efficient SQL to do this?
  3. Any other consideration to take into account to improve the speed? e.g. using indexing, view, etc.

Upvotes: 0

Views: 93

Answers (1)

AlvaroSanz
AlvaroSanz

Reputation: 49

If you only want the school_id and the sum of the fee one option is:

SELECT TOP 3
  SUM(tuition_fee) AS TOTAL_TUITION_FEE,
  school_id
FROM T2
GROUP BY school_id
ORDER BY TOTAL_TUITION_FEE DESC

If you´re using SQL server you can use the query analyzer (some info of where to find it) for seeing how to improve the performance, it suggest index or others tips.

Don't seem to be a pretty heavy query.

Upvotes: 2

Related Questions