CODEWITHSUNDEEP
sql
Alberto Montellano
Alberto Montellano

Reputation: 6246

Optimize WHERE clause in query

I have the following query:

SELECT table2.serialcode,
       p.name,
       date,
       power,
       behind,
       direction,
       length,
       centerlongitude,
       centerlatitude,
       currentlongitude,
       currentlatitude
FROM table1 as table2
  JOIN pivots p ON p.serialcode = table2.serial
WHERE table2.serialcode = '49257' 
and date = (select max(a.date) from table1 a where a.serialcode ='49257');

It seems it is retrieving the select max subquery for each join. It takes a lot of time. Is there a way to optimize it? Any help will be appreciated.

Upvotes: 0

Views: 148

Answers (4)

spioter
spioter

Reputation: 1870

Sub selects that end up being evaluated "per row of the main query" can cause tremendous performance problems once you try to scale to larger number of rows.

Sub selects can almost always be eliminated with a data model tweak.

Here's one approach: add a new is_latest to the table to track if it's the max value (and for ties, use other fields like created time stamp or the row ID). Set it to 1 if true, else 0.

Then you can add where is_latest = 1 to your query and this will radically improve performance.

You can schedule the update to happen or add a trigger etc. if you need an automated way of keeping is_latest up to date.

Other approaches involve 2 tables - one where you keep only the latest record and another table where you keep the history.

Upvotes: 2

Gaurav
Gaurav

Reputation: 227

Since you haven't mentioned which DB you are using, I would answer if it was for Oracle.

You can use WITH clause to take out the subquery and make it perform just once.

WITH d AS (
  SELECT max(a.date) max_date from TABLE1 a WHERE a.serialcode ='49257'
)
SELECT table2.serialcode,
       p.name,
       date,
       power,
       behind,
       direction,
       length,
       centerlongitude,
       centerlatitude,
       currentlongitude,
       currentlatitude
FROM table1 as table2
  JOIN pivots p ON p.serialcode = table2.serial
  JOIN d on (table2.date = d.max_date)
WHERE table2.serialcode = '49257'

Please note that you haven't qualified date column, so I just assumed it belonged to table1 and not pivots. You can change it. An advise on the same note - always qualify your columns by using table.column format.

Upvotes: 0

halit
halit

Reputation: 1128

declare @maxDate datetime;
select @maxDate = max(a.date) from table1 a where a.serialcode ='49257';


SELECT table2.serialcode,
           p.name,
           date,
           power,
           behind,
           direction,
           length,
           centerlongitude,
           centerlatitude,
           currentlongitude,
           currentlatitude
    FROM table1 as table2
      JOIN pivots p ON p.serialcode = table2.serial
    WHERE table2.serialcode = '49257' 
    and date =@maxDate;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can optimize this query using indexes. Here are somethat should help: table1(serialcode, serial, date), table1(serialcode, date), and pivots(serialcode).

Note: I find it very strange that you have columns called serial and serialcode in the same table, and the join is on serial.

Upvotes: 0

Related Questions