Free cookie consent management tool by TermsFeed Policy Generator

Changeset 9557


Ignore:
Timestamp:
05/29/13 15:17:03 (11 years ago)
Author:
pfleck
Message:

#2063:
Added DataSize column in TaskData (calculated column).
Created queries for fetching required data for generating task facts.

Location:
branches/HiveStatistics/sources
Files:
5 edited

Legend:

Unmodified
Added
Removed

  • branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/HiveDataContext.dbml

    r9546 r9557  
    116116      <Column Name="Data" Type="System.Byte[]" DbType="VarBinary(MAX)" CanBeNull="false" UpdateCheck="Never" IsDelayLoaded="true" />
    117117      <Column Name="LastUpdate" Type="System.DateTime" DbType="DateTime" CanBeNull="false" />
     118      <Column Name="DataSize" Type="System.Int64" DbType="BigInt NOT NULL" IsReadOnly="true" IsDbGenerated="true" CanBeNull="false" />
    118119      <Association Name="Task_TaskData" Member="Task" Storage="_Job" ThisKey="TaskId" OtherKey="TaskId" Type="Task" IsForeignKey="true" />
    119120    </Type>
    … …  
    212213  <Table Name="[statistics].DimClient" Member="DimClients">
    213214    <Type Name="DimClient">
    214       <Column Name="Id" AutoSync="Never" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
     215      <Column Name="Id" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
    215216      <Column Name="Name" Type="System.String" DbType="VarChar(MAX) NOT NULL" CanBeNull="false" />
    216217      <Column Name="ResourceId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" CanBeNull="false" />
    … …  
    224225  <Table Name="[statistics].FactTask" Member="FactTasks">
    225226    <Type Name="FactTask">
    226       <Column Name="TaskId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" CanBeNull="false" />
     227      <Column Name="TaskId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
    227228      <Column Name="TotalRuntime" Type="System.Double" DbType="Float NOT NULL" CanBeNull="false" />
    228229      <Column Name="TotalWaitingTime" Type="System.Double" DbType="Float NOT NULL" CanBeNull="false" />
    … …  
    232233      <Column Name="CoresRequired" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
    233234      <Column Name="MemoryRequired" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
    234       <Column Name="TaskSize" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
    235       <Column Name="ResultSize" Type="System.Int32" DbType="Int" CanBeNull="true" />
     235      <Column Name="TaskSize" Type="System.Int64" DbType="BigInt NOT NULL" CanBeNull="false" />
     236      <Column Name="ResultSize" Type="System.Int64" DbType="BigInt" CanBeNull="true" />
    236237      <Column Name="Priority" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
    237238      <Column Name="LastClientId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
    … …  
    241242      <Association Name="DimClient_FactTask" Member="DimClient" ThisKey="LastClientId" OtherKey="Id" Type="DimClient" IsForeignKey="true" />
    242243      <Association Name="DimJob_FactTask" Member="DimJob" ThisKey="JobId" OtherKey="JobId" Type="DimJob" IsForeignKey="true" />
    243       <Association Name="DimTime_FactTask" Member="DimTime" ThisKey="EndTime" OtherKey="Time" Type="DimTime" IsForeignKey="true" />
    244       <Association Name="DimTime_FactTask1" Member="DimTime1" ThisKey="StartTime" OtherKey="Time" Type="DimTime" IsForeignKey="true" />
     244      <Association Name="DimTime_FactTask" Member="DimTimeEnd" Storage="_DimTime" ThisKey="EndTime" OtherKey="Time" Type="DimTime" IsForeignKey="true" />
     245      <Association Name="DimTime_FactTask1" Member="DimTimeStart" Storage="_DimTime1" ThisKey="StartTime" OtherKey="Time" Type="DimTime" IsForeignKey="true" />
    245246    </Type>
    246247  </Table>

  • branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/HiveDataContext.dbml.layout

    r9541 r9557  
    7676      </nodes>
    7777    </associationConnector>
    78     <classShape Id="6bc13f26-f9a8-4597-b054-35be34190d12" absoluteBounds="4.125, 1, 2, 1.26050048828125">
     78    <classShape Id="6bc13f26-f9a8-4597-b054-35be34190d12" absoluteBounds="4.125, 1, 2, 1.424847819010417">
    7979      <DataClassMoniker Name="/HiveDataContext/TaskData" />
    8080      <nestedChildShapes>
    81         <elementListCompartment Id="a068522c-7974-4679-b356-e33c941c465b" absoluteBounds="4.14, 1.46, 1.9700000000000002, 0.70050048828125" name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
     81        <elementListCompartment Id="a068522c-7974-4679-b356-e33c941c465b" absoluteBounds="4.14, 1.46, 1.9700000000000002, 0.86484781901041663" name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
    8282      </nestedChildShapes>
    8383    </classShape>
    … …  
    121121      </nestedChildShapes>
    122122    </classShape>
    123     <associationConnector edgePoints="[(11.25 : 1.44547136382195); (11.0260416666667 : 1.44547136382195); (11.0260416666667 : 0.5625); (2.75 : 0.5625); (2.75 : 1)]" manuallyRouted="true" fixedFrom="Caller" fixedTo="Algorithm">
     123    <associationConnector edgePoints="[(11.25 : 1.44547136382195); (11.0260416666667 : 1.44547136382195); (11.0260416666667 : 0.5625); (2.75 : 0.5625); (2.75 : 1)]" manuallyRouted="true" fixedFrom="NotFixed" fixedTo="NotFixed">
    124124      <AssociationMoniker Name="/HiveDataContext/Resource/Resource_StateLog" />
    125125      <nodes>
    … …  
    179179      </nodes>
    180180    </associationConnector>
    181     <associationConnector edgePoints="[(8.0192961430407 : 3.90397379557292); (8.0192961430407 : 5.04807657877604); (8.875 : 5.04807657877604)]" fixedFrom="Caller" fixedTo="Algorithm">
     181    <associationConnector edgePoints="[(8.0192961430407 : 3.90397379557292); (8.0192961430407 : 5.04807657877604); (8.875 : 5.04807657877604)]" fixedFrom="NotFixed" fixedTo="NotFixed">
    182182      <AssociationMoniker Name="/HiveDataContext/Task/Task_AssignedResource" />
    183183      <nodes>
    … …  
    186186      </nodes>
    187187    </associationConnector>
    188     <associationConnector edgePoints="[(7.20145120722822 : 3.90397379557292); (7.20145120722822 : 5.875)]" fixedFrom="Caller" fixedTo="Algorithm">
     188    <associationConnector edgePoints="[(7.20145120722822 : 3.90397379557292); (7.20145120722822 : 5.875)]" fixedFrom="NotFixed" fixedTo="NotFixed">
    189189      <AssociationMoniker Name="/HiveDataContext/Task/Task_RequiredPlugin" />
    190190      <nodes>
    … …  
    193193      </nodes>
    194194    </associationConnector>
    195     <associationConnector edgePoints="[(7.20923076923077 : 1); (7.20923076923077 : 0.770833333333333); (7.80461538461539 : 0.770833333333333); (7.80461538461539 : 1)]" manuallyRouted="true" fixedFrom="Algorithm" fixedTo="Algorithm">
     195    <associationConnector edgePoints="[(7.20923076923077 : 1); (7.20923076923077 : 0.770833333333333); (7.80461538461539 : 0.770833333333333); (7.80461538461539 : 1)]" manuallyRouted="true" fixedFrom="NotFixed" fixedTo="NotFixed">
    196196      <AssociationMoniker Name="/HiveDataContext/Task/Task_Task" />
    197197      <nodes>
    … …  
    200200      </nodes>
    201201    </associationConnector>
    202     <associationConnector edgePoints="[(6.5 : 1.69314697265625); (6.125 : 1.69314697265625)]" fixedFrom="NotFixed" fixedTo="NotFixed">
     202    <associationConnector edgePoints="[(6.5 : 1.71242390950521); (6.125 : 1.71242390950521)]" fixedFrom="NotFixed" fixedTo="NotFixed">
    203203      <AssociationMoniker Name="/HiveDataContext/Task/Task_TaskData" />
    204204      <nodes>
    … …  
    207207      </nodes>
    208208    </associationConnector>
    209     <associationConnector edgePoints="[(6.5 : 2.50564697265625); (3.75 : 2.50564697265625)]" fixedFrom="NotFixed" fixedTo="NotFixed">
     209    <associationConnector edgePoints="[(6.5 : 2.52492390950521); (3.75 : 2.52492390950521)]" fixedFrom="NotFixed" fixedTo="NotFixed">
    210210      <AssociationMoniker Name="/HiveDataContext/Task/Task_StateLog" />
    211211      <nodes>

  • branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/HiveDataContext.designer.cs

    r9546 r9557  
    27312731    private System.DateTime _LastUpdate;
    27322732   
     2733    private long _DataSize = default(long);
     2734   
    27332735    private EntityRef<Task> _Job;
    27342736   
    … …  
    28122814          this.OnLastUpdateChanged();
    28132815        }
     2816      }
     2817    }
     2818   
     2819    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_DataSize", AutoSync=AutoSync.Always, DbType="BigInt NOT NULL", IsDbGenerated=true, UpdateCheck=UpdateCheck.Never)]
     2820    public long DataSize
     2821    {
     2822      get
     2823      {
     2824        return this._DataSize;
    28142825      }
    28152826    }
    … …  
    46484659    }
    46494660   
    4650     [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Id", DbType="UniqueIdentifier NOT NULL", IsPrimaryKey=true, IsDbGenerated=true)]
     4661    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Id", AutoSync=AutoSync.OnInsert, DbType="UniqueIdentifier NOT NULL", IsPrimaryKey=true, IsDbGenerated=true)]
    46514662    public System.Guid Id
    46524663    {
    … …  
    48614872    private int _MemoryRequired;
    48624873   
    4863     private int _TaskSize;
    4864    
    4865     private System.Nullable<int> _ResultSize;
     4874    private long _TaskSize;
     4875   
     4876    private System.Nullable<long> _ResultSize;
    48664877   
    48674878    private int _Priority;
    … …  
    49034914    partial void OnMemoryRequiredChanging(int value);
    49044915    partial void OnMemoryRequiredChanged();
    4905     partial void OnTaskSizeChanging(int value);
     4916    partial void OnTaskSizeChanging(long value);
    49064917    partial void OnTaskSizeChanged();
    4907     partial void OnResultSizeChanging(System.Nullable<int> value);
     4918    partial void OnResultSizeChanging(System.Nullable<long> value);
    49084919    partial void OnResultSizeChanged();
    49094920    partial void OnPriorityChanging(int value);
    … …  
    49284939    }
    49294940   
    4930     [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TaskId", DbType="UniqueIdentifier NOT NULL")]
     4941    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TaskId", DbType="UniqueIdentifier NOT NULL", IsPrimaryKey=true)]
    49314942    public System.Guid TaskId
    49324943    {
    … …  
    50885099    }
    50895100   
    5090     [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TaskSize", DbType="Int NOT NULL")]
    5091     public int TaskSize
     5101    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TaskSize", DbType="BigInt NOT NULL")]
     5102    public long TaskSize
    50925103    {
    50935104      get
    … …  
    51085119    }
    51095120   
    5110     [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ResultSize", DbType="Int")]
    5111     public System.Nullable<int> ResultSize
     5121    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ResultSize", DbType="BigInt")]
     5122    public System.Nullable<long> ResultSize
    51125123    {
    51135124      get
    … …  
    53135324   
    53145325    [global::System.Data.Linq.Mapping.AssociationAttribute(Name="DimTime_FactTask", Storage="_DimTime", ThisKey="EndTime", OtherKey="Time", IsForeignKey=true)]
    5315     public DimTime DimTime
     5326    public DimTime DimTimeEnd
    53165327    {
    53175328      get
    … …  
    53415352            this._EndTime = default(System.DateTime);
    53425353          }
    5343           this.SendPropertyChanged("DimTime");
     5354          this.SendPropertyChanged("DimTimeEnd");
    53445355        }
    53455356      }
    … …  
    53475358   
    53485359    [global::System.Data.Linq.Mapping.AssociationAttribute(Name="DimTime_FactTask1", Storage="_DimTime1", ThisKey="StartTime", OtherKey="Time", IsForeignKey=true)]
    5349     public DimTime DimTime1
     5360    public DimTime DimTimeStart
    53505361    {
    53515362      get
    … …  
    53755386            this._StartTime = default(System.DateTime);
    53765387          }
    5377           this.SendPropertyChanged("DimTime1");
     5388          this.SendPropertyChanged("DimTimeStart");
    53785389        }
    53795390      }
    … …  
    57715782    {
    57725783      this.SendPropertyChanging();
    5773       entity.DimTime = this;
     5784      entity.DimTimeEnd = this;
    57745785    }
    57755786   
    … …  
    57775788    {
    57785789      this.SendPropertyChanging();
    5779       entity.DimTime = null;
     5790      entity.DimTimeEnd = null;
    57805791    }
    57815792   
    … …  
    57835794    {
    57845795      this.SendPropertyChanging();
    5785       entity.DimTime1 = this;
     5796      entity.DimTimeStart = this;
    57865797    }
    57875798   
    … …  
    57895800    {
    57905801      this.SendPropertyChanging();
    5791       entity.DimTime1 = null;
     5802      entity.DimTimeStart = null;
    57925803    }
    57935804   

  • branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql

    r9546 r9557  
    9696  [Data] VarBinary(MAX) Filestream NOT NULL,
    9797  [LastUpdate] DateTime NOT NULL,
     98  [DataSize]  AS (datalength([Data])),
    9899  CONSTRAINT [PK_dbo.TaskData] PRIMARY KEY ([TaskId])
    99100  )
    … …  
    260261    [CoresRequired]      INT              NOT NULL,
    261262    [MemoryRequired]     INT              NOT NULL,
    262     [TaskSize]           INT              NOT NULL,
    263     [ResultSize]         INT              NULL,
     263    [TaskSize]           BIGINT           NOT NULL,
     264    [ResultSize]         BIGINT           NULL,
    264265    [Priority]           INT              NOT NULL,
    265266    [LastClientId]       UNIQUEIDENTIFIER NOT NULL,
    … …  
    267268    [StartTime]          DATETIME         NOT NULL,
    268269    [EndTime]            DATETIME         NOT NULL,
    269     CONSTRAINT [PK_FactTask] PRIMARY KEY CLUSTERED ([LastClientId] ASC, [JobId] ASC, [StartTime] ASC, [EndTime] ASC),
     270    CONSTRAINT [PK_FactTask] PRIMARY KEY CLUSTERED ([TaskId] ASC, [LastClientId] ASC, [JobId] ASC, [StartTime] ASC, [EndTime] ASC),
    270271    CONSTRAINT [FK_FactTask_DimClient] FOREIGN KEY ([LastClientId]) REFERENCES [statistics].[DimClient] ([Id]),
    271272    CONSTRAINT [FK_FactTask_DimJob] FOREIGN KEY ([JobId]) REFERENCES [statistics].[DimJob] ([JobId]),
    … …  
    273274    CONSTRAINT [FK_FactTask_DimTimeEnd] FOREIGN KEY ([EndTime]) REFERENCES [statistics].[DimTime] ([Time])
    274275);
     276
     277INSERT INTO [HeuristicLab.Hive-3.3].[statistics].[DimClient] ([Id] ,[Name] ,[ResourceId])
     278VALUES ('00000000-0000-0000-0000-000000000000' ,'NULL' ,'00000000-0000-0000-0000-000000000000')

  • branches/HiveStatistics/sources/HeuristicLab.Services.Hive/3.3/HiveStatisticsGenerator.cs

    r9546 r9557  
    1414    public void GenerateStatistics() {
    1515      using (var db = new HiveDataContext(Settings.Default.HeuristicLab_Hive_LinqConnectionString))
    16       using (var transaction = new TransactionScope()) {
     16      using (var transaction = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted })) {
     17
    1718        var newTime = UpdateDimensionTables(db);
    1819        db.SubmitChanges();
    … …  
    3031      var newTime = UpdateTime(db);
    3132      // Update other tables out of sync with time dimension?
     33      UpdateUsers(db);
    3234      UpdateJobs(db);
    33       UpdateUsers(db);
    3435      UpdateClients(db);
    3536
    … …  
    3839
    3940    private DimTime UpdateTime(HiveDataContext db) {
    40       var lastUpdate =
     41      var lastUpdateTime =
    4142        (from t in db.DimTimes
    4243         orderby t.Time descending
    … …  
    4647      var now = DateTime.Now;
    4748      DimTime newTime = null;
    48       if (lastUpdate == default(DateTime) || lastUpdate + SmallestTimeSpan < now) {
     49
     50      if (lastUpdateTime == default(DateTime) || lastUpdateTime + SmallestTimeSpan < now) {
    4951        newTime = new DimTime {
    5052          Time = new DateTime(now.Year, now.Month, now.Day, now.Hour, now.Minute - now.Minute % SmallestTimeSpan.Minutes, 0),
    … …  
    5860
    5961      return newTime;
     62    }
     63
     64    private void UpdateUsers(HiveDataContext db) {
     65      var newUsers =
     66        from u in db.Resources.Where(x => x.OwnerUserId != null).Select(x => x.OwnerUserId.Value).Union(db.Jobs.Select(x => x.OwnerUserId))
     67        where !db.DimUsers.Select(x => x.UserId).Contains(u)
     68        select u;
     69
     70      var newDimUsers =
     71        from u in newUsers.ToList()
     72        select new DimUser {
     73          UserId = u,
     74          Name = userManager.GetUserById(u).UserName
     75        };
     76
     77      db.DimUsers.InsertAllOnSubmit(newDimUsers);
     78
     79      // insert NULL-User
     80      if (!db.DimUsers.Any(x => x.UserId == Guid.Empty)) {
     81        db.DimUsers.InsertOnSubmit(new DimUser { UserId = Guid.Empty, Name = "NULL" });
     82      }
    6083    }
    6184
    … …  
    7699
    77100      db.DimJobs.InsertAllOnSubmit(newDimJobs);
    78     }
    79 
    80     private void UpdateUsers(HiveDataContext db) {
    81       var newUsers =
    82         from u in db.Resources.Where(x => x.OwnerUserId != null).Select(x => x.OwnerUserId.Value).Union(db.Jobs.Select(x => x.OwnerUserId))
    83         where !db.DimUsers.Select(x => x.UserId).Contains(u)
    84         select u;
    85 
    86       var newDimUsers =
    87         from u in newUsers.ToList()
    88         select new DimUser {
    89           UserId = u,
    90           Name = userManager.GetUserById(u).UserName
    91         };
    92 
    93       db.DimUsers.InsertAllOnSubmit(newDimUsers);
    94 
    95       // insert NULL-User
    96       if (!db.DimUsers.Any(x => x.UserId == Guid.Empty)) {
    97         db.DimUsers.InsertOnSubmit(new DimUser { UserId = Guid.Empty, Name = "NULL" });
     101
     102      // insert NULL-Job
     103      if (!db.DimJobs.Any(x => x.JobId == Guid.Empty)) {
     104        db.DimJobs.InsertOnSubmit(new DimJob { JobId = Guid.Empty, JobName = "NULL", UserId = Guid.Empty, UserName = "NULL" });
    98105      }
    99106    }
    … …  
    143150    private void UpdateFactTables(DimTime newTime, HiveDataContext db) {
    144151      UpdateClientInfoFacts(newTime, db);
    145       //UpdateTaskFacts(newTime, db);
     152      UpdateTaskFacts(newTime, db);
    146153    }
    147154
    … …  
    213220               : selector(lastFact);
    214221    }
     222
     223    private void UpdateTaskFacts(DimTime newTime, HiveDataContext db) {
     224      var lastFacts =
     225        from f in db.FactTasks
     226        group f by f.TaskId into grpFacts
     227        select grpFacts.OrderByDescending(x => x.EndTime).First();
     228
     229      var tasksWithLastFact =
     230        from t in db.Tasks
     231        join lf in lastFacts on t.TaskId equals lf.TaskId into tLfJoin
     232        from lWithLf in tLfJoin.DefaultIfEmpty()
     233        where !t.IsParentTask
     234        select new { Task = t, LastFact = tLfJoin.FirstOrDefault() };
     235
     236      var tasksWithNewStateLogs =
     237        from sl in db.StateLogs
     238        join twlf in tasksWithLastFact on sl.TaskId equals twlf.Task.TaskId
     239        where twlf.LastFact == null || (sl.DateTime > twlf.LastFact.EndTime && sl.DateTime < newTime.Time)
     240        group sl by twlf.Task into factGroup
     241        select new { Task = factGroup.Key, StateLogs = factGroup.OrderBy(x => x.DateTime) };
     242
     243      var tasksWithLastFactsAndStateLogs =
     244        from twsl in tasksWithNewStateLogs
     245        join twlf in tasksWithLastFact on twsl.Task equals twlf.Task
     246        select new { twlf.Task, twlf.LastFact, twsl.StateLogs };
     247
     248      var newTaskFacts =
     249        from t in tasksWithLastFactsAndStateLogs.ToList()
     250        select new FactTask {
     251          TaskId = t.Task.TaskId,
     252          JobId = t.Task.JobId,
     253          DimTimeStart = t.LastFact.DimTimeStart,
     254          DimTimeEnd = newTime,
     255          // ...
     256        };
     257
     258      db.FactTasks.InsertAllOnSubmit(newTaskFacts);
     259    }
    215260  }
    216261}
Note: See TracChangeset for help on using the changeset viewer.