Free cookie consent management tool by TermsFeed Policy Generator

Ignore:
Timestamp:
08/13/15 14:51:54 (9 years ago)
Author:
ascheibe
Message:

#2388

  • cleaned up sql scripts
  • cleaned up hive service
  • made performance logger optional
  • removed unused web service methods
File:
1 edited

Legend:

Unmodified
Added
Removed

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

    r12768 r12857  
     1/* HeuristicLab
     2 * Copyright (C) 2002-2015 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
     3 *
     4 * This file is part of HeuristicLab.
     5 *
     6 * HeuristicLab is free software: you can redistribute it and/or modify
     7 * it under the terms of the GNU General Public License as published by
     8 * the Free Software Foundation, either version 3 of the License, or
     9 * (at your option) any later version.
     10 *
     11 * HeuristicLab is distributed in the hope that it will be useful,
     12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
     13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
     14 * GNU General Public License for more details.
     15 *
     16 * You should have received a copy of the GNU General Public License
     17 * along with HeuristicLab. If not, see <http://www.gnu.org/licenses/>.
     18 */
     19
     20/* this script is supposed to be executed after the plain DB is generated by the linq-to-sql schema */
    121USE [HeuristicLab.Hive-3.3]
    2 /* this script is supposed to be executed after the plain DB is generated by the linq-to-sql schema */
    3 /* adds default values */
    4 /* creates delete and update cascades */
    5 /* creates indices */
    6 /* creates views */
    7 /* creates triggers */
    8 
    922
    1023ALTER TABLE [dbo].[AssignedResources]  DROP  CONSTRAINT [Task_AssignedResource]
    … …  
    97110GO
    98111
    99 /* views */
    100 -- =============================================
    101 -- Author:    cneumuel
    102 -- Description: Returns the first StateLog entry for each job
    103 -- =============================================
    104 CREATE VIEW [dbo].[view_FirstState]
    105 AS
    106 SELECT     sl.TaskId, sl.DateTime, sl.State
    107 FROM         dbo.StateLog AS sl INNER JOIN
    108                           (SELECT     TaskId, MIN(DateTime) AS DateTime
    109                             FROM          dbo.StateLog
    110                             GROUP BY TaskId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.TaskId = minDate.TaskId
    111 
     112-- speed up joins between Job and Task
     113CREATE NONCLUSTERED INDEX [TaskJobIdIndex]
     114ON [dbo].[Task] ([JobId])
     115INCLUDE ([TaskId],[TaskState],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[CoresNeeded],[MemoryNeeded],[IsParentTask],[FinishWhenChildJobsFinished],[Command],[IsPrivileged])
    112116GO
    113117
    114 -- =============================================
    115 -- Author:    cneumuel
    116 -- Description: Returns the last StateLog entry for each job
    117 -- =============================================
    118 CREATE VIEW [dbo].[view_LastState]
    119 AS
    120 SELECT     sl.TaskId, sl.DateTime, sl.State
    121 FROM         dbo.StateLog AS sl INNER JOIN
    122                           (SELECT     TaskId, MAX(DateTime) AS DateTime
    123                             FROM          dbo.StateLog
    124                             GROUP BY TaskId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.TaskId = minDate.TaskId
     118-- this is an index to speed up the GetWaitingTasks() method
     119CREATE NONCLUSTERED INDEX [TaskGetWaitingTasksIndex]
     120ON [dbo].[Task] ([TaskState],[IsParentTask],[FinishWhenChildJobsFinished],[CoresNeeded],[MemoryNeeded])
     121INCLUDE ([TaskId],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[Command],[JobId],[IsPrivileged])
    125122GO
     123
    126124
    127125/****** Object:  Trigger [dbo].[tr_JobDeleteCascade]    Script Date: 04/19/2011 16:31:53 ******/
    … …  
    152150CREATE TRIGGER [dbo].[tr_TaskDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS
    153151BEGIN
    154     SELECT
    155         he.OwnerUserId AS UserId,
    156         ROUND(SUM(j.ExecutionTimeMs) / 1000, 0) AS ExecutionTimeS, 
    157         ROUND(ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN j.ExecutionTimeMs END), 0) / 1000, 0) AS ExecutionTimeSFinishedJobs,
    158         ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN DATEDIFF(s, fs.DateTime, ls.DateTime) ELSE 0 END), 0) AS StartToEndTimeS
    159     FROM
    160         deleted j,
    161         Job he,
    162         view_FirstState fs,
    163         view_LastState ls
    164     WHERE
    165         he.JobId = j.JobId AND
    166         fs.TaskId = j.TaskId AND
    167         ls.TaskId = j.TaskId
    168     GROUP BY he.OwnerUserId
    169 
    170152    -- recursively delete jobs
    171153    CREATE TABLE #Table(
    … …  
    194176GO
    195177
    196 -- ============================================================
    197 -- Description: Create indices to speed up execution of queries
    198 -- ============================================================
    199178
    200 -- speed up joins between Job and Task
    201 CREATE NONCLUSTERED INDEX [TaskJobIdIndex]
    202 ON [dbo].[Task] ([JobId])
    203 INCLUDE ([TaskId],[TaskState],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[CoresNeeded],[MemoryNeeded],[IsParentTask],[FinishWhenChildJobsFinished],[Command],[IsPrivileged])
    204 GO
    205 
    206 -- this is an index to speed up the GetWaitingTasks() method
    207 CREATE NONCLUSTERED INDEX [TaskGetWaitingTasksIndex]
    208 ON [dbo].[Task] ([TaskState],[IsParentTask],[FinishWhenChildJobsFinished],[CoresNeeded],[MemoryNeeded])
    209 INCLUDE ([TaskId],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[Command],[JobId],[IsPrivileged])
    210 GO
Note: See TracChangeset for help on using the changeset viewer.