
-- 10/15/2018
-- Tony
-- Following changes are made to update SU calculations and improve queries efficiency. 

CREATE INDEX IX_JOB_STATS_01 ON job_stats (RESOURCE);
CREATE INDEX IX_JOB_STATS_02 ON job_stats (REMOTE_JOB_ID); 

CREATE INDEX IX_TGUSAGE_01 ON tgusage (RESOURCE); 
CREATE INDEX IX_TGUSAGE_02 ON tgusage (JOBID); 


-- Add a new column 'SPROPS' at job_stats table. 
ALTER TABLE `job_stats` 
  ADD COLUMN `SPROPS` VARCHAR(8000) NULL AFTER `APPNAME`
;


-- Update js.TG_CHARGE_NUMBER from tgusage table if it is null.
-- *** WARNING *** 
-- This update will be very very slow depending on the number of rows in 
-- job_stats and tgusage tables. 
UPDATE job_stats AS js  
    INNER JOIN resource_conversion AS rc ON js.RESRC_CONVRTN_ID = rc.ID 
    INNER JOIN tgusage AS tg ON js.REMOTE_JOB_ID = tg.JOBID AND tg.RESOURCE = rc.RESOURCE 
SET 
    js.TG_CHARGE_NUMBER = tg.CHARGE_NUMBER 
WHERE 
    js.TG_CHARGE_NUMBER IS NULL 
;


-- Update js.SU_CHARGED from tgusage table if it is null.
-- *** WARNING *** 
-- This update will be very very slow depending on the number of rows in 
-- job_stats and tgusage tables. 
UPDATE job_stats AS js  
    INNER JOIN resource_conversion AS rc ON js.RESRC_CONVRTN_ID = rc.ID 
    INNER JOIN tgusage AS tg ON js.REMOTE_JOB_ID = tg.JOBID AND tg.RESOURCE = rc.RESOURCE 
SET 
    js.SU_CHARGED = tg.SU 
WHERE 
    js.SU_CHARGED IS NULL AND 
    tg.SU IS NOT NULL 
;

