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

Откуда:
Сообщений: 99
Добрый день

Время выполнения запроса
SELECT
	DislocationTable.*, Distance 
FROM 
	(
	SELECT 
		Wagon_Dislocation.ID as DislocationID,
		DislocationDate,
		OperationDate,
		OperationName,
		TrainIndex,
		Number,
		WagonTypes.Description,
		st1.FullName as CurStation,
		st1.Code as CurCode,
		st1.Latitude,
		st1.Longitude,
		rr1.RRName as CurRoad,
		r1.RegionName as CurRegion,
		st2.FullName as FromStation,
		st2.Code as FromCode,
		rr2.RRName as FromRoad,
		r2.RegionName as FromRegion,
		st3.FullName as ToStation,
		st3.Code as ToCode,
		rr3.RRName as ToRoad,
		r3.RegionName as ToRegion,
		GNGName,
		GNG,
		Class,
		CargoWeight,
		WagonID,
		StationFrom,
		CurrentStationID,
		StationTo,
		SenderOKPO,
		SenderOrg,
		RecepientOKPO,
		RecipientOrg,
		DocDate,
		DocNumber,
		TripPrice,
		TripNotes
	FROM 
		Wagons,
		Stations as st1,
		Stations as st2,
		Stations as st3,
		RailRoads as rr1,
		RailRoads as rr2,
		RailRoads as rr3,
		Regions as r1,
		Regions as r2,
		Regions as r3,
		GNGTable,
		WagonTypes,
		WagonOperations,
		Wagon_Trips,
		Wagon_Dislocation
	WHERE 
		Wagon_Dislocation.ID IN (SELECT TOP 1 WITH TIES Wagon_Dislocation.ID FROM Wagon_Trips,Wagon_Dislocation,Wagons WHERE Wagon_Trips.ID=TripID AND Wagons.ID=WagonID AND WagonID IN (SELECT WagonID FROM WagonList WHERE GroupID=43) ORDER BY row_number() OVER (PARTITION BY WagonID ORDER BY DislocationDate DESC)) AND
		st1.ID=CurrentStationID AND 
		st1.RailRoads_ID=rr1.ID AND 
		st1.Regions_ID=r1.ID AND 
		st2.ID=StationFrom AND 
		st2.RailRoads_ID=rr2.ID AND 
		st2.Regions_ID=r2.ID AND 
		st3.ID=StationTo AND 
		st3.RailRoads_ID=rr3.ID AND 
		st3.Regions_ID=r3.ID AND 
		Wagon_Trips.ID=TripID AND
		Wagons.ID=WagonID AND 
		WagonTypes.ID=WagonTypes_ID AND
		GNGTable.ID=CargoType AND
		WagonOperations.ID=OperationID
	) as DislocationTable,
	ConstantDistance
WHERE
	(s1=CurrentStationID AND s2=StationTo) OR (s2=CurrentStationID AND s1=StationTo)

составляет 16-17 секунд. Если пробовать отдельно
WHERE
	(s1=CurrentStationID AND s2=StationTo)
и
WHERE
	(s2=CurrentStationID AND s1=StationTo)

то время выполнение составляет 0-1 секунды. Но во второй таблице у нас данные могут храниться в таком порядке
s1s2
АВ

, а могут в таком
s1s2
ВА

Заранее спасибо, за Ваше потраченное время!
4 янв 14, 22:00    [15377545]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
sdet
Member

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

И где сформулированный вопрос? Если разница в быстродействии то это из-за OR
4 янв 14, 22:14    [15377588]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Замените OR на UNION ALL двух запросов с каждым из условиев.

ЗЫ. На дворе 2014 год и уже давно пора перейти на ANSI style join syntax. Читать же невозможно!
4 янв 14, 22:19    [15377602]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Arik
Member

Откуда:
Сообщений: 99
Собственно за счет чего можно сократить время выполнения? Что изменить в запросе, как оптимизировать? В какую сторону копать?
4 янв 14, 22:20    [15377603]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
sdet
Member

Откуда:
Сообщений: 463
Arik
Собственно за счет чего можно сократить время выполнения? Что изменить в запросе, как оптимизировать? В какую сторону копать?

Как уже сказали, начинайте с синтаксиса
и устранение конструкции
WHERE 
		Wagon_Dislocation.ID IN (SELECT TOP 1 WITH TIES Wagon_Dislocation.ID FROM Wagon_Trips,Wagon_Dislocation,Wagons WHERE Wagon_Trips.ID=TripID AND Wagons.ID=WagonID AND WagonID IN (SELECT WagonID FROM WagonList WHERE GroupID=43) ORDER BY row_number() OVER (PARTITION BY WagonID ORDER BY DislocationDate DESC)) AND

а также используйте union all вместо OR
4 янв 14, 22:27    [15377621]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Добрый Э - Эх
Guest
Смущает многократное использование одних и тех же таблиц, наличие предикатов с IN-подзапросами, соседство старообрядных джойнов с ROW_NUMBER-ром.
Ну а про оптимизацию OR через UNION ALL уже сказали. Осталось добавить, что для сокращения записи запроса в этом случае имеет смысл запихнуть портянку в СТЕ, чтобы дважды код не повторять.
4 янв 14, 22:31    [15377629]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Arik
Member

Откуда:
Сообщений: 99
sdet,pkarklin,

Спасибо. Используя UNION время сократилось до 1 секунды. UNION ALL нам не подходит, потому, как в таблице могут быть записи
s1s2
AA
BB
AB
BA


Чем заменить или как избавиться, не знаю
WHERE 
       Wagon_Dislocation.ID IN (
              SELECT TOP 1 WITH TIES Wagon_Dislocation.ID 
              FROM Wagon_Trips,Wagon_Dislocation,Wagons 
              WHERE Wagon_Trips.ID=TripID AND Wagons.ID=WagonID AND WagonID IN (
                     SELECT WagonID 
                     FROM WagonList WHERE GroupID=43
                     ) 
              ORDER BY row_number() OVER (PARTITION BY WagonID ORDER BY DislocationDate DESC))

, подскажите в какую сторону копать. Нам необходим именно последний набор данных для каждой записи. Код обязательно перепишу.
Спасибо за подсказку про ANSI style join syntax первый раз прочитал (редко пользуюсь SQL)
4 янв 14, 22:39    [15377647]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Arik,

автор
Чем заменить или как избавиться


и

автор
Используя UNION время сократилось до 1 секунды.


Этого недостаточно? Если нет, то, боюсь, Вам придется привести слишком много информации о структуре Вашей бд, образцов данных и того, какой нужен результат, чтобы получить ответ на свой вопрос.

Сообщение было отредактировано: 4 янв 14, 22:47
4 янв 14, 22:46    [15377667]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
sdet
Member

Откуда:
Сообщений: 463
Arik
sdet,pkarklin,

Спасибо. Используя UNION время сократилось до 1 секунды. UNION ALL нам не подходит, потому, как в таблице могут быть записи
s1s2
AA
BB
AB
BA


Чем заменить или как избавиться, не знаю
WHERE 
       Wagon_Dislocation.ID IN (
              SELECT TOP 1 WITH TIES Wagon_Dislocation.ID 
              FROM Wagon_Trips,Wagon_Dislocation,Wagons 
              WHERE Wagon_Trips.ID=TripID AND Wagons.ID=WagonID AND WagonID IN (
                     SELECT WagonID 
                     FROM WagonList WHERE GroupID=43
                     ) 
              ORDER BY row_number() OVER (PARTITION BY WagonID ORDER BY DislocationDate DESC))

, подскажите в какую сторону копать. Нам необходим именно последний набор данных для каждой записи. Код обязательно перепишу.
Спасибо за подсказку про ANSI style join syntax первый раз прочитал (редко пользуюсь SQL)

Используйте CTE,JOIN а также устраните hardcode WHERE GroupID=43
4 янв 14, 22:57    [15377700]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Arik
Member

Откуда:
Сообщений: 99
pkarklin
Этого недостаточно? Если нет, то, боюсь, Вам придется привести слишком много информации о структуре Вашей бд, образцов данных и того, какой нужен результат, чтобы получить ответ на свой вопрос.

Вполне, но когда я писал этот запрос, я понимал, что его можно сделать более оптимальным. К сожалению за недостатком знаний, пользуешься тем, что есть. Могу привести структуру БД. Хотелось бы узнать, чем можно заменить
WHERE 
       Wagon_Dislocation.ID IN (
              SELECT TOP 1 WITH TIES Wagon_Dislocation.ID 
              FROM Wagon_Trips,Wagon_Dislocation,Wagons 
              WHERE Wagon_Trips.ID=TripID AND Wagons.ID=WagonID AND WagonID IN (
                     SELECT WagonID 
                     FROM WagonList WHERE GroupID=43
                     ) 
              ORDER BY row_number() OVER (PARTITION BY WagonID ORDER BY DislocationDate DESC))
Добрый Э - Эх
Смущает ..., наличие предикатов с IN-подзапросами, соседство старообрядных джойнов с ROW_NUMBER-ром.

Как сделать правильно? Про СТЕ прочитал, добавил, спасибо.
4 янв 14, 23:15    [15377764]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Arik
Member

Откуда:
Сообщений: 99
sdet
..., а также устраните hardcode WHERE GroupID=43


да, там у меня параметр в хранимой процедуре WHERE GroupID=@GroupID
4 янв 14, 23:18    [15377774]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Arik
Member

Откуда:
Сообщений: 99
WITH DislocationTable AS 
(
	SELECT 
		Wagon_Dislocation.ID as DislocationID,
		DislocationDate,
		OperationDate,
		OperationName,
		TrainIndex,
		Number,
		WagonTypes.Description,
		st1.FullName as CurStation,
		st1.Code as CurCode,
		st1.Latitude,
		st1.Longitude,
		rr1.RRName as CurRoad,
		r1.RegionName as CurRegion,
		st2.FullName as FromStation,
		st2.Code as FromCode,
		rr2.RRName as FromRoad,
		r2.RegionName as FromRegion,
		st3.FullName as ToStation,
		st3.Code as ToCode,
		rr3.RRName as ToRoad,
		r3.RegionName as ToRegion,
		GNGName,
		GNG,
		Class,
		CargoWeight,
		WagonID,
		StationFrom,
		CurrentStationID,
		StationTo,
		SenderOKPO,
		SenderOrg,
		RecepientOKPO,
		RecipientOrg,
		DocDate,
		DocNumber,
		TripPrice,
		TripNotes
	FROM 
		Wagons,
		Stations as st1,
		Stations as st2,
		Stations as st3,
		RailRoads as rr1,
		RailRoads as rr2,
		RailRoads as rr3,
		Regions as r1,
		Regions as r2,
		Regions as r3,
		GNGTable,
		WagonTypes,
		WagonOperations,
		Wagon_Trips,
		Wagon_Dislocation
	WHERE 
		Wagon_Dislocation.ID IN (SELECT TOP 1 WITH TIES Wagon_Dislocation.ID FROM Wagon_Trips,Wagon_Dislocation,Wagons WHERE Wagon_Trips.ID=TripID AND Wagons.ID=WagonID AND WagonID IN (SELECT WagonID FROM WagonList WHERE GroupID=@GroupID) ORDER BY row_number() OVER (PARTITION BY WagonID ORDER BY DislocationDate DESC)) AND
		st1.ID=CurrentStationID AND 
		st1.RailRoads_ID=rr1.ID AND 
		st1.Regions_ID=r1.ID AND 
		st2.ID=StationFrom AND 
		st2.RailRoads_ID=rr2.ID AND 
		st2.Regions_ID=r2.ID AND 
		st3.ID=StationTo AND 
		st3.RailRoads_ID=rr3.ID AND 
		st3.Regions_ID=r3.ID AND 
		Wagon_Trips.ID=TripID AND
		Wagons.ID=WagonID AND 
		WagonTypes.ID=WagonTypes_ID AND
		GNGTable.ID=CargoType AND
		WagonOperations.ID=OperationID
)


SELECT
	DislocationTable.*, Distance 
FROM 
	DislocationTable,ConstantDistance
WHERE 
	s1=CurrentStationID AND s2=StationTo
UNION
SELECT
	DislocationTable.*, Distance 
FROM 
	DislocationTable,ConstantDistance
WHERE 
	s2=CurrentStationID AND s1=StationTo
ORDER BY DislocationTable.Number

Время выполнения меньше 1 секунды! Всем большое спасибо, если будут советы по оптимизации либо избавлении от старообрядных методов, приму со всем вниманием.
Еще раз спасибо!
4 янв 14, 23:29    [15377809]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Добрый Э - Эх
Guest
Arik,

Как правило, in-подзапросы стараются заменить на join-ы. Поиск первой/последней записи в группе можно сделать как минимум пятью способами, в том числе и на join-нах. Что будет быстрее/оптимальнее на твоих данных - нужно пробовать
5 янв 14, 06:25    [15378455]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Добрый Э - Эх
Guest
Arik,

Поиск последней/первой записи в группе :8947782
5 янв 14, 06:32    [15378456]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить