Всем привет
Я надеялся, что кто-нибудь поможет мне улучшить запрос, который мне приходится периодически запускать. На данный момент на выполнение уходит более 40 минут. В это время он использует всю выделенную память, но загрузка ЦП в основном колеблется на уровне 2–5%, время от времени подскакивая до 40% на несколько секунд.
У меня есть эта таблица (упрощенный пример):
CREATE TABLE [dbo].[dataTable]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[dteEffectiveDate] [date] NULL,
[dtePrevious] [date] NULL,
[dteNext] [date] NULL,
[Age] [int] NULL,
[Count] [int] NULL
) ON [PRIMARY]
GO
Вот некоторые входные значения:
INSERT INTO [YourDB].[dbo].[dataTable]
([dteEffectiveDate]
,[dtePrevious]
,[dteNext]
,[Age]
,[Count])
VALUES
('2009-01-01',NULL,'2010-01-01',40,300),
('2010-01-01','2009-01-01', NULL,40,200),
('2009-01-01',NULL, '2010-01-01',20,100),
('2010-01-01','2009-01-01', NULL,20,50),
('2009-01-01',NULL,'2010-01-01',30,10)
GO
Каждая запись имеет поле dteEffectiveDate. Кроме того, у каждого есть dtePrevious и dteNext, которые отражают даты ближайшей предыдущей/следующей даты вступления в силу. Теперь мне нужен запрос, который будет вычислять среднее значение в полях Count между последовательными периодами в пределах определенного возраста.
Так, например, в приведенных выше данных для возраста 40 у нас есть 300 на 01.01.2009 и 200 на 01.01.2010, поэтому запрос должен дать 250.
Обратите внимание, что возраст 30 имеет только одну запись, 10. Это 01.01.2009. На 01.01.2010 нет записи, но мы знаем, что данные были собраны в этот момент, поэтому тот факт, что ничего нет, означает, что 30 равно 0 на эту дату. Следовательно, запрос должен выдать 5.
Для этого я использую ПОЛНОЕ СОЕДИНЕНИЕ таблицы с самой собой и использую ISNULL для выбора значений. Вот мой код:
SELECT
ISNULL(T1.dteEffectiveDate,T2.dtePrevious) as [Start Date]
,ISNULL(T1.dteNext,T2.dteEffectiveDate) as [End Date]
,ISNULL(T1.Age,T2.Age) as Age
,ISNULL(T1.[Count],0) as [Count Start]
,ISNULL(T2.[Count],0) as [Count End]
,(ISNULL(T1.[Count],0)+ISNULL(T2.[Count],0))/2 as [Mid Count]
FROM
[ExpDBClient].[dbo].[dataTable] as T1
FULL JOIN [ExpDBClient].[dbo].[dataTable] as T2
ON
T2.dteEffectiveDate = T1.dteNext
AND T2.Age = T1.Age
WHERE ISNULL(T1.dteEffectiveDate,T2.dtePrevious) is not null
AND ISNULL(T1.dteNext,T2.dteEffectiveDate) is not null
GO
который выводит:
Start Date End Date Age Count Start Count End Mid Lives
2009-01-01 2010-01-01 40 300 200 250
2009-01-01 2010-01-01 20 100 50 75
2009-01-01 2010-01-01 30 10 0 5
Это работает отлично, но когда я запускаю это на реальных данных, а это около 7 миллионов записей, выполнение занимает мучительно много времени.
У кого-нибудь есть предложения?
Спасибо
Карл