CODEWITHSUNDEEP
sqlperformancepostgresql
SFernando
SFernando

Reputation: 1124

sql performance when executing huge table

i found this sql file at my workplace

DECLARE

 M_DEPATURE_TIME    varchar;

BEGIN

  SELECT min(cast(to_char(i.departuredate,'DD-Mon-YYYY') as varchar) ||' '|| i.departuretime) as deptime
  INTO M_DEPATURE_TIME
  FROM myschema.flightinfosv i

  WHERE upper(i.inout) = upper(m_inout)
  AND  i.r_id= m_resconfirmid;

  RETURN M_DEPATURE_TIME;

END;

The problem is why this script will take more time(more than 15 mins) to execute in thousands of records table.The table(resflightinfosv) consist of at least 50,000 records.Although i use indexes for both 'inout' and 'r_id' and when execute with even 1000 limit it will take that time.Where are the places I need to change this script?

Thanks in advance!

Upvotes: 0

Views: 90

Answers (1)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

You can add a multi-column index on departuredate and departuretime and try something like this; extract the minimum date first and then, for this date, select the smallest hour. I don't think that the following code will work "as-is" cause I'm not able to test it but you get the main idea :)

DECLARE

 M_DEPATURE_DATE    date,
 M_DEPATURE_TIME    varchar;

BEGIN

  SELECT min(i.departuredate) as depdate
  INTO M_DEPATURE_DATE
  FROM myschema.flightinfosv i
  WHERE upper(i.inout) = upper(m_inout)
  AND  i.r_id= m_resconfirmid;

  SELECT to_char(i.departuredate,'DD-Mon-YYYY') ||' '|| min(i.departuretime) as deptime
  INTO M_DEPATURE_TIME
  FROM myschema.flightinfosv i

  WHERE upper(i.inout) = upper(m_inout)
  AND  i.r_id= m_resconfirmid
  AND  i.departuredate = M_DEPATURE_DATE

  RETURN M_DEPATURE_TIME;

END;

Upvotes: 1

Related Questions