Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 пытаюсь заменить clr скриптом  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
SQL2005 the must

автор
CREATE TABLE [dbo].[device_status_lifecycle](
[dev_status_lifecycle_id] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[dev_id] [int] NOT NULL,
[dev_status_lifecycle_status] [bigint] NOT NULL,
[dev_status_first_date] [datetime] NOT NULL,
[dev_status_last_date] [datetime] NULL,
[dev_status_lifecycle_cleared] [datetime] NULL,
[dev_status_servicecode] [char](5) NULL
)

CREATE INDEX idx_dsl ON device_status_lifecycle ([dev_id]);
CREATE INDEX idx_dsl1 ON device_status_lifecycle ([dev_status_first_date]);
CREATE INDEX idx_dsl2 ON device_status_lifecycle ([dev_status_last_date]);




заполнение приааттачено

select * from [dbo].ufn_DeviceStatusTimes(1, '2014/01/01', '2014/02/28') 


такой запрос CLR выполняет мгновенно на любом рабочем об'eме данных.

Никак не могy замутить скрип что бы хотя бы близко был по производительности.

Скрип сливает интервалы если они пересекаются для каждого принтера и каждого типа сообшения на заданном интервале

куда копать?

К сообщению приложен файл (testCLR.zip - 129Kb) cкачать
22 сен 14, 23:28    [16607505]     Ответить | Цитировать Сообщить модератору
 Re: пытаюсь заменить clr скриптом  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
IF OBJECT_ID('dbo.ufn_DeviceStatusTimes') IS NOT NULL DROP FUNCTION dbo.ufn_DeviceStatusTimes;
GO

CREATE function dbo.ufn_DeviceStatusTimes( @deviceID int, @StartDate datetime, @EndDate datetime)
 RETURNS @rst TABLE ( dev_id int
                    , report_aggregate_period_start datetime
					, report_aggregate_period_end datetime
					, statusCode int, iter bigint not null identity(1,1) )
AS
BEGIN
  if @deviceID Is Null OR @StartDate Is Null OR @EndDate Is Null
        return;
  ;WITH T AS
  (
    SELECT @deviceID as deviceId, 
	  CASE WHEN dev_status_first_date < @StartDate THEN @StartDate ELSE dev_status_first_date END AS StartDate,
	  CASE WHEN dev_status_last_date IS NULL OR dev_status_last_date < @StartDate THEN @EndDate ELSE dev_status_last_date END AS EndDate,
	  CASE 
	    WHEN dev_status_lifecycle_status = 137438953472 THEN 0                                      -- Unreachable
		WHEN dev_status_lifecycle_status = 34359738368  THEN 1                                      -- TonerError
		WHEN dev_status_lifecycle_status IN (17179869184, 8589934592, 4294967296)  THEN 2           -- PaperError
		WHEN (dev_status_lifecycle_status  & CAST(274307481856 AS bigint)) > 0  THEN 3              -- OtherError
		WHEN (dev_status_lifecycle_status  & CAST(29361814 AS bigint)) > 0  THEN 4                  -- Warning
		ELSE 5                                                                                      -- available 
	  END AS statusCode
		FROM device_status_lifecycle
		WHERE dev_id = @deviceID AND 
		((dev_status_first_date <= @StartDate AND @EndDate < dev_status_last_date) OR
			(@StartDate <= dev_status_first_date AND @EndDate < dev_status_last_date AND dev_status_first_date <= @EndDate) OR
			(dev_status_first_date <= @StartDate AND dev_status_last_date < @EndDate AND @StartDate <= dev_status_last_date) OR 
			(@StartDate <= dev_status_first_date AND dev_status_last_date < @EndDate))
   )
   INSERT INTO @rst (dev_id, report_aggregate_period_start, report_aggregate_period_end, statusCode) 
     SELECT @deviceID, s1.StartDate AS stDt, MIN(t1.EndDate) AS enDt, s1.statusCode
	  FROM T s1                                                                               -- here we just merge time intervals
		INNER JOIN T t1 ON s1.StartDate <= t1.EndDate AND s1.statusCode=t1.statusCode
		  AND NOT EXISTS(SELECT * FROM T t2
						 WHERE t1.EndDate >= t2.StartDate AND t1.EndDate < t2.EndDate AND t1.statusCode=t2.statusCode) 
 	  WHERE NOT EXISTS(SELECT * FROM T s2 
						 WHERE s1.StartDate > s2.StartDate AND s1.StartDate <= s2.EndDate AND s1.statusCode=s2.statusCode) 
      GROUP BY s1.StartDate, s1.statusCode

  RETURN
END
GO	
22 сен 14, 23:38    [16607537]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить