Changeset 239

Show
Ignore:
Timestamp:
10/03/08 14:26:29
Author:
dan
Message:

Sub: QueryPlatformSensorReportCount?
Changes: reworked the query to get the sensor count to drastically improve the speed.
Sub: GetMTypeFromObsType
Changes: Added the subroutine. Given a platform and observation name, it returns the sensor type. Used in QueryPlatformSensorReportCount?
to help speed up the query. Elimates the need to LEFT JOIN the sensor table.

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • obskml/trunk/QAQC/CalcPlatformUptimePercentage.pl

    r231 r239  
    11####################################################################################################### 
    22#Revisions 
     3# Rev: 1.4.0.0 
     4# Author: DWR 
     5# Sub: QueryPlatformSensorReportCount 
     6# Changes: reworked the query to get the sensor count to drastically improve the speed. 
     7# Sub: GetMTypeFromObsType 
     8# Changes: Added the subroutine. Given a platform and observation name, it returns the sensor type. Used in QueryPlatformSensorReportCount 
     9# to help speed up the query. Elimates the need to LEFT JOIN the sensor table. 
     10 
    311# Rev: 1.3.0.0 
    412# Author: DWR 
    …… 
    389397{ 
    390398  my ( $DB, $strPlatformID, $strStart, $strEnd, $strSensorName, $iUpdateInterval, $PlatformIDs,$iTimeZoneShift ) = @_; 
    391       
     399  my $iCnt      = -1; 
     400   
    392401  #This query will return the number of entries of the sensor type given for the given platform and date range. 
    393402  #In other words how many times do we have an entry for that sensor for that platform during that date/time interval. 
    394403  # m_date >= '$strStart' AND m_date < '$strEnd'      AND  
    395  
    396   
    397   #DWR v1.2.0.0 
    398   #Added time zone adjustment into WHERE clause for start/end date range. 
    399   my $strSQL = "SELECT COUNT( sensor.m_type_id ) 
    400                 FROM multi_obs  
    401                 LEFT JOIN sensor on sensor.row_id=multi_obs.sensor_id 
    402                 WHERE   
    403                   platform_handle = '$strPlatformID'                                  AND  
    404                   sensor.short_name = '$strSensorName'                                AND 
    405                   datetime(m_date) >= datetime('$strStart','$iTimeZoneShift hours')   AND   
    406                   datetime(m_date) < datetime('$strEnd','$iTimeZoneShift hours');";       
    407  
    408   my $iCnt      = -1; 
    409    
    410   my $hSt       = $DB->prepare( $strSQL ); 
    411   if( defined $hSt ) 
    412   { 
    413     if( $hSt->execute( ) ) 
    414     { 
    415       $iCnt = $hSt->fetchrow_array();    
    416       my $strDay = substr( $strStart, 0, 10); 
    417       %$PlatformIDs->{Platform}{$strPlatformID}{Sensor}{$strSensorName}{Day}{$strDay}{Count} = $iCnt;   
    418       %$PlatformIDs->{Platform}{$strPlatformID}{Sensor}{$strSensorName}{UpdateInterval} = $iUpdateInterval; 
     404  #$strPlatformID = 'usf.C14.IMET'; 
     405 
     406  #DWR v1.3.0.0 
     407  #Query the m_type_id to alleviate the need below to JOIN on the sensor table. 
     408  my $iType = GetMTypeFromObsType ( $DB, $strSensorName, $strPlatformID, 1);#($dbh, $strObsName, $strPlatformHandle, $iSOrder ) 
     409   
     410  if( defined( $iType ) ) 
     411  { 
     412    #DWR v1.2.0.0 
     413    #Added time zone adjustment into WHERE clause for start/end date range. 
     414    #DWR v1.3.0.0 
     415    #Reworked the SQL query for speed. Dropped the join the the sensor table since we are now 
     416    #looking up the sensor m_type_id above. 
     417    my $strSQL = "SELECT COUNT( m_type_id ) 
     418                  FROM multi_obs  
     419                  WHERE   
     420                    multi_obs.m_type_id = $iType                              AND  
     421                    m_date >= strftime( '%Y-%m-%dT%H:00:00',datetime('$strStart','$iTimeZoneShift hours') )   AND   
     422                    m_date < strftime( '%Y-%m-%dT%H:00:00', datetime('$strEnd','$iTimeZoneShift hours') )     AND       
     423                    platform_handle = '$strPlatformID';";                 
     424  #  my $strSQL = "SELECT COUNT( sensor.m_type_id ) 
     425  #                FROM multi_obs  
     426  #                LEFT JOIN sensor on sensor.row_id=multi_obs.sensor_id 
     427  #                WHERE   
     428  #                  platform_handle = '$strPlatformID'                                  AND  
     429  #                  sensor.short_name = '$strSensorName'                                AND 
     430  #                  datetime(m_date) >= datetime('$strStart','$iTimeZoneShift hours')   AND   
     431  #                  datetime(m_date) < datetime('$strEnd','$iTimeZoneShift hours');";       
     432   
     433     
     434    my $hSt       = $DB->prepare( $strSQL ); 
     435    if( defined $hSt ) 
     436    { 
     437      if( $hSt->execute( ) ) 
     438      { 
     439        $iCnt = $hSt->fetchrow_array();    
     440        my $strDay = substr( $strStart, 0, 10); 
     441        %$PlatformIDs->{Platform}{$strPlatformID}{Sensor}{$strSensorName}{Day}{$strDay}{Count} = $iCnt;   
     442        %$PlatformIDs->{Platform}{$strPlatformID}{Sensor}{$strSensorName}{UpdateInterval} = $iUpdateInterval; 
     443      } 
     444      else 
     445      { 
     446        my $strErr = $hSt->errstr; 
     447        print( "QueryPlatformSensorReportCount::ERROR: Failed execute: $strErr\n SQLStatement: $strSQL\n");     
     448      } 
    419449    } 
    420450    else 
    421451    { 
    422       my $strErr = $hSt->errstr; 
    423       print( "QueryPlatformSensorReportCount::ERROR: Failed execute: $strErr\n SQLStatement: $strSQL\n");     
    424     } 
    425   } 
    426   else 
    427   { 
    428     print( "QueryPlatformSensorReportCount::ERROR: Unable to prepare SQL statement: $strSQL.\n"); 
    429   } 
    430      
     452      print( "QueryPlatformSensorReportCount::ERROR: Unable to prepare SQL statement: $strSQL.\n"); 
     453    } 
     454  } 
     455  #No m_type_id for the sensor on the platform. We'll create a placeholder and give a count of 0. 
     456  else 
     457  { 
     458    my $strDay = substr( $strStart, 0, 10); 
     459    %$PlatformIDs->{Platform}{$strPlatformID}{Sensor}{$strSensorName}{Day}{$strDay}{Count} = 0;   
     460    %$PlatformIDs->{Platform}{$strPlatformID}{Sensor}{$strSensorName}{UpdateInterval} = $iUpdateInterval;     
     461  }     
    431462  return( $iCnt ); 
    432463 
    …… 
    803834} 
    804835 
     836sub GetMTypeFromObsType 
     837{ 
     838  my ($dbh, $strObsName, $strPlatformHandle, $iSOrder ) = @_; 
     839   
     840  my $strSOrder = ''; 
     841  if( defined $iSOrder ) 
     842  { 
     843    $strSOrder = "sensor.s_order = $iSOrder AND"; 
     844  } 
     845  my $strSQL = "SELECT DISTINCT(sensor.m_type_id) FROM m_type, m_scalar_type, obs_type, sensor, platform 
     846                WHERE  sensor.m_type_id = m_type.row_id AND 
     847                m_scalar_type.row_id = m_type.m_scalar_type_id AND 
     848                obs_type.row_id = m_scalar_type.obs_type_id AND 
     849                platform.row_id = sensor.platform_id AND 
     850                $strSOrder 
     851                obs_type.standard_name = '$strObsName' AND 
     852                platform.platform_handle = '$strPlatformHandle';"; 
     853  my $iMType = -1; 
     854  my $sth = $dbh->prepare( $strSQL ); 
     855  if( defined $sth ) 
     856  { 
     857    if( $sth->execute() ) 
     858    {       
     859      $iMType = $sth->fetchrow(); 
     860    } 
     861    else 
     862    { 
     863      my $strErr = $sth->errstr; 
     864      print( "ERROR::$strErr\n"); 
     865    } 
     866  } 
     867  else 
     868  { 
     869    print( "ERROR::Unable to prepare SQL statement: $strSQL\n"); 
     870  }  
     871  $sth->finish(); 
     872  return( $iMType ); 
     873}