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

Откуда:
Сообщений: 2
Приветствую.
Исходные данные: есть таблица

CREATE TABLE [dbo].[CL](
	[clNumber] [varchar](20) NOT NULL, 
	[clDate] [datetime] NOT NULL,
	[clStation] [int] NULL,
 CONSTRAINT [PK_CL] PRIMARY KEY CLUSTERED 
(
	[clDate] ASC,
	[clNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


Суть - информация о нахождении вагонов. clNumber - номер вагона, clDate - дата, clStation - ссылка на станцию нахождения.

Данные имеют вид примерно такой

INSERT INTO CL (clNumber, clDate, clStation) VALUES ('12345','20110101',1)
INSERT INTO CL (clNumber, clDate, clStation) VALUES ('12345','20110102',2)
INSERT INTO CL (clNumber, clDate, clStation) VALUES ('12345','20110103',2)
INSERT INTO CL (clNumber, clDate, clStation) VALUES ('12345','20110104',2)
INSERT INTO CL (clNumber, clDate, clStation) VALUES ('12345','20110105',3)
INSERT INTO CL (clNumber, clDate, clStation) VALUES ('54321','20110101',1)
INSERT INTO CL (clNumber, clDate, clStation) VALUES ('54321','20110102',2)
INSERT INTO CL (clNumber, clDate, clStation) VALUES ('54321','20110103',3)
INSERT INTO CL (clNumber, clDate, clStation) VALUES ('54321','20110104',4)
INSERT INTO CL (clNumber, clDate, clStation) VALUES ('54321','20110105',5)


Пытаюсь построить view, который бы выводил - сколько времени вагон находится на станции в днях.

select *,
convert(integer,CL.clDate - (select top 1 clDate from CL AS CLT
where  CLT.clNumber = CL.clNumber and CLT.clDate < CL.clDate and CL.clStation <> CLT.clStation
order by CLT.clDate DESC) ) AS DysOnStation
from CL


Результат запроса должен иметь вид
clNumber clDate clStationDysOnStation
1234502.01.201121
5432102.01.201121
1234503.01.201122
5432103.01.201131
1234504.01.201123
5432104.01.201141
1234505.01.201131
5432105.01.201151

Получается плохо, т.е. выполняется очень медленно. как можно сделать этот запрос быстрее?

PS.
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
12 дек 11, 17:47    [11751613]     Ответить | Цитировать Сообщить модератору
 Re: Помогите SQL запрос.  [new]
Наталь
Guest
индексы добавить
12 дек 11, 22:53    [11753437]     Ответить | Цитировать Сообщить модератору
 Re: Помогите SQL запрос.  [new]
ChA
Member

Откуда: Москва
Сообщений: 11316
MaksZzn
Навскидку.
Можно попробовать так
SELECT
	CL.*
	, (
		SELECT DATEDIFF(d, MAX(clDate), CL.clDate)
		FROM CL1 AS CLT
		WHERE
			CLT.clNumber = CL.clNumber
			AND CLT.clDate < CL.clDate
			AND CL.clStation <> CLT.clStation
		 
	) AS DysOnStation
FROM CL1 AS cl

И заменить
 CONSTRAINT [PK_CL] PRIMARY KEY CLUSTERED 
(
	[clDate] ASC,
	[clNumber] ASC
)

на
 CONSTRAINT [PK_CL] PRIMARY KEY CLUSTERED 
(
	[clNumber] ASC,
	[clDate] ASC
)

или даже
(
	[clNumber],
	[clDate],
	[clStation]
)
Если [clStation] сделать NOT NULL, либо вместо PRIMARY KEY CLUSTERED - UNIQUE CLUSTERED
На большом количестве данных может оказаться быстрее.
13 дек 11, 04:36    [11753971]     Ответить | Цитировать Сообщить модератору
 Re: Помогите SQL запрос.  [new]
ChA
Member

Откуда: Москва
Сообщений: 11316
Ещё, как вариант, можно попробовать так
SELECT
	CL.[clNumber]
	, CL.[clDate]
	, CL.[clStation]
	, DATEDIFF(d, MAX(clt.clDate), CL.clDate)
FROM CL1 AS cl
LEFT JOIN CL1 AS CLT ON (
	CLT.clNumber = CL.clNumber
	AND CLT.clDate < CL.clDate
	AND CL.clStation <> CLT.clStation
)
GROUP BY
	cl.[clNumber],
	cl.[clDate],
	cl.[clStation]
Рекомендуется с последним вариантом ограничения.
13 дек 11, 05:02    [11753980]     Ответить | Цитировать Сообщить модератору
 Re: Помогите SQL запрос.  [new]
aleks2
Guest
;with
Y as (
select clNumber, min(clDate)-1 clDate, cast(null as int) as clStation, 0 as days  FROM @CL GROUP BY clNumber
UNION ALL
select Y.clNumber, Z.clDate, Z.clStation, CASE Z.clStation WHEN Y.clStation THEN Y.days+1 ELSE 1 END as days
from Y inner join @CL Z ON Y.clNumber=Z.clNumber AND Y.clDate+1=Z.clDate
)
select * FROM Y WHERE clStation is not null
order by clNumber, clDate
13 дек 11, 07:16    [11754011]     Ответить | Цитировать Сообщить модератору
 Re: Помогите SQL запрос.  [new]
MaksZzn
Member

Откуда:
Сообщений: 2
ChA,

Спасибо, за советы.

Забыл, наверное, дописать. Объем таблицы ~ 600 000 Записей. Стандартная выборка имеет вид:
select * from CLView where clDate = :pclDate

То есть выбираются данные за день, порядка 4000 записей за раз. По этому и кластерный индекс так построил (сначала по дате), ранее он у имел вид как вы и предложили. Зачем включать код станции в индекс я, если честно, совсем не понял.

Поэкспериментировал с вашими вариантами, скорость если и увеличилась, то не значительно, было примерно 60 сек. стало 45 - 50 что все равно очень долго.

Сейчас переписал приложение клиентское и сделал запросы по вычислению времени нахождения прямо в цикле с клиента. Стало как ни странно быстрее (~14 секунд). Но этот вариант так же не очень нравится.
13 дек 11, 10:55    [11754736]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить