CODEWITHSUNDEEP
sqlsql-serverjoindb2ibm-cloud
danielo
danielo

Reputation: 780

Make SQL-query more efficient

So I am new to SQL and tried to build a query for joining and retrieving data from different tables in DB2 Warehouse on Cloud. I want to make it more efficient so I can retrieve the data faster. This my SQL-statement:

SELECT ML_ANOMALY_DETECTION.DATETIME, 
       ML_ANOMALY_DETECTION.TAG_NAME, 
       ML_ANOMALY_DETECTION.MLAD_VALUE AS INDEX, 
       PARENT.MLAD_VALUE AS SCORE, 
       ML_MEASURE.MLAD_VALUE AS VALUE, 
       DATA_CONFIG.TAG_DESCRIPTION AS TAG_DESCRIPTION, 
       DATA_CONFIG.UNITS AS UNITS 
  FROM ML_ANOMALY_DETECTION AS ML_ANOMALY_DETECTION 
  LEFT JOIN ML_ANOMALY_DETECTION AS PARENT 
    ON (ML_ANOMALY_DETECTION.DATETIME = PARENT.DATETIME AND ML_ANOMALY_DETECTION.TAG_NAME = PARENT.TAG_NAME) 
 INNER JOIN ML_MEASURE 
    ON (ML_ANOMALY_DETECTION.DATETIME = ML_MEASURE.DATETIME 
   AND ML_ANOMALY_DETECTION.TAG_NAME = ML_MEASURE.TAG_NAME) 
 INNER JOIN DATA_CONFIG 
    ON (ML_ANOMALY_DETECTION.TAG_NAME =DATA_CONFIG.TAG_NAME) 
 WHERE (ML_ANOMALY_DETECTION.DATETIME BETWEEN '2017-11-25 06:57:00' 
        AND '2017-11-25 07:36:00') 
   AND (ML_ANOMALY_DETECTION.MLAD_METRIC='ANOMALY_INDEX' 
        AND PARENT.MLAD_METRIC='ANOMALY_SCORE')
   AND (ML_ANOMALY_DETECTION.TAG_NAME='VAR1' 
        OR ML_ANOMALY_DETECTION.TAG_NAME='VAR2')
  1. So to explain it simple I have three tables. One containing variable names, date, type and value. In this table I have two different types (ANOMALY_INDEX/SCORE). I want to start by joining the value for the two rows that have the same DATETIME and TAG_NAME, but have different types.
  2. Then I want to join the above with a value from a row in a second table, where the DATETIME and TAG_NAME should be the same.
  3. After this I want to retrieve information regarding every tag (UNITS and TAG_DESCRIPTION) that I get so I need to join a row from a table containing the same TAG_NAME.
  4. The table above should be filtered between specific dates and use the tags I choose.

Hope I explained it good enough. Is is possible to make this more efficient?

Upvotes: 0

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

First, you want indexes on the columns used for joining.

Second, this is your where clause:

WHERE (ad.DATETIME BETWEEN '2017-11-25 06:57:00' AND '2017-11-25 07:36:00') AND 
      ad.MLAD_METRIC = 'ANOMALY_INDEX' AND
      p.MLAD_METRIC = 'ANOMALY_SCORE' AND 
      ad.TAG_NAME IN ('VAR1', 'VAR2')

(I added table aliases for readability and use IN rather than OR.)

For these conditions, I would recommend an additional index on ML_ANOMALY_DETECTION(mlad_metric, datetime, tag_name).

Upvotes: 3

Related Questions