Optimizing SQL queries

Question

I have created some MSSQL queries, all of them work well, but I think it could be done in a faster way. Can you help me to optimize them?

That's the database:

Create table Teachers
    (TNO char(3) Primary key,
     TNAME char(20),
     TITLE char(6) check (TITLE in('Prof','PhD','MSc')),
     CITY char(12),
     SUPNO char(3) REFERENCES Teachers);

Create table Students
    (SNO char(3) Primary key,
     SNAME char(20),
     SYEAR int,
     CITY char(20));

Create table Courses
    (CNO char(3) Primary key,
     CNAME char(20),
     STUDYEAR int);

Create table TSC
    (TNO char(3) REFERENCES Teachers,
     SNO char(3) REFERENCES Students,
     CNO char(3) REFERENCES Courses,
     HOURS int,
     GRADE float,
     PRIMARY KEY(TNO,SNO,CNO));

1: On which study year there are most courses?

Problem: it looks like the result is being sorted while I only need the max element.

select
  top 1 STUDYEAR
from
  Courses
group by
  STUDYEAR
order by COUNT(*) DESC

2: Show the TNOs of those teachers who do NOT have courses with the 1st studyear

Problem: I'm using a subquery only to negate a select query

select
  TNO
from
  Teachers
where
  TNO not in (
    select distinct
      tno
    from
      Courses, TSC
    where tsc.CNO=Courses.CNO and STUDYEAR = 1)

I've fixed it, even added the homework tag if you say it's relevant.. The homework was to create a query, optimizing it is my own effort. — Andrzej Gis
– Andrzej Gis, Commented Jan 15, 2012 at 22:26

Lucero · Accepted Answer · 2012-01-15 21:52:33Z

3

Some ordering needs to be done to find the max or min value; maybe using ranking functions instead of a group by would be better but I frankly expect the query analyzer to be smart enough to find a good query plan for this specific query.
The subquery is performing well as long as it isn't using columns from the outer query (which may cause it to be performed for every row in many cases). However, I'd leave away the distinct, as it has no benefit. Also, I'd always use the explicit join syntax, but that's mostly a matter of personal preference (for inner joins - outer joins should always be done with the explicit syntax).

So all in all I think that these queries are simple and clear enough to be handled well in the query analyzer, thereby yielding good performance. Do you have a specific performance issue for asking this question? If yes, give us more info (query plan etc.), if no, just leave them - don't to premature optimization.

answered Jan 15, 2012 at 21:52

Lucero

60.4k9 gold badges127 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrzej Gis Over a year ago

'Some ordering needs to be done to find the max or min value' - I don't now how dbms handles it but it looks like it's sorting O(nlogn) while I only need the max value O(n) it makes some difference unless it's automatically handled.

Andrzej Gis Over a year ago

I don't have any specific problem with that - it's just for educational reasons. :)

Lucero Over a year ago

@gisek, the query analyzer creates a query plan which may do something completely different from what you wrote in your code. SQL is not an imperative language where you decide what to do step by step; instead you define what you want to get and have the SQL engine figure out how to do this in an optimal way.

Collectives™ on Stack Overflow

Optimizing SQL queries

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related