Медленно работает MSSQL сервер

← →
ch_victor (2004-02-02 10:45) [0]

Добрый день всем!!!
Я слышал что MSSQL сервер медленно работает с user-defined function, но не думал, что настолько медленно

обьясняю суть задачи:
имееется 2 таблицы
1 таблица частолетающих пассажиров
2 таблица полетов этих пассажиров
по правилам частолетающим пассажиром является тот кто совершает полеты не реже раз в год
поэтому нужно связать эти две таблицы и найти таких пассажиров, у которых промежуток между двумя полетами более года

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

так вот эта задача отрабатывает очень долго (около часа), хотя количество записей в таблицах не очень большое в первой 10000 записей, а во второй 60000

можно ли обойтись без курсора в функции, потому что я так думаю тормозит из-за него, какие будут предложения?

← →
Ega23 © (2004-02-02 10:53) [1]

Это всё на уровне Select можно сделать.
where (Date1-Date2)<=365

← →
Nikolay M. © (2004-02-02 10:53) [2]

> можно ли обойтись без курсора в функции

Почти наверняка можно. Что мешает сделать селект, в котором связать таблицу саму на себя, условие соединения - естественно по пассажиру, а в HAVING поставить условие, что дата из одной таблицы больше всех дат из второй таблицы как минимум на год?
Час на таких объемах - просто несерьезно.

← →
sniknik © (2004-02-02 10:56) [3]

в функции наверное специально задержки ставил? показать важность и обьемность задачи. ;о))

думаю можно обойтись совсем без функции, обьеденением. давай структуру таблиц подробнее.

← →
Nikolay M. © (2004-02-02 10:56) [4]

Да, кстати, я что-то не совсем въехал в условие. С одной стороны, есть таблица
> 1 таблица частолетающих пассажиров
> по правилам частолетающим пассажиром является тот кто совершает
> полеты не реже раз в год

, а с другой стороны, нужно найти тех, у кого промежуток между полетами больше года. Какая-то нестыковочка.

← →
ch_victor (2004-02-02 11:03) [5]

> , а с другой стороны, нужно найти тех, у кого промежуток
> между полетами больше года. Какая-то нестыковочка.

операторы забивают любые полеты, поэтому и стоит задача найти неправильно введенные

а структура в простом виде такая
(убираю поля которые сейчас неважные)
такая
таблица пассажиров(client)
client_id
lastname,
firstname

таблица полетов(transaction_)
transaction_id
client_id
servicedate (дата полета)

← →
Nikolay M. © (2004-02-02 11:11) [6]

Имхо, можно вот так, но могут быть ошибки, да и не самое оптимальное это, потому что подзапрос есть:
SELECT DISTINCT(client_id)
FROM transaction tr1
WHERE DATEDIFF(Day, tr1.servicedate, (SELECT ISNULL(MAX(service), tr1.servicedate) FROM transaction tr2 WHERE tr2.servicedate < tr1.servicedate)) > 365

← →
Nikolay M. © (2004-02-02 11:15) [7]

Еще как вариант - сделать курсор по запросу
SELECT client_id, servicedate
FROM transaction
ORDER BY client_id, servicedate
и пошагово сравнивать в рамках каждого клиента, что последующая дата больше предыдущей на год и, если больше, то такого клиента пихать во временную таблицу, а потом делать селект из нее.

← →
sniknik © (2004-02-02 11:29) [8]

еще к примеру можно

SELECT T1.client_id, T1.lastname, T1.firstname, Max(T2.servicedate) AS MaxServicedate, Min(T3.servicedate) AS MinServicedate
FROM (client T1 LEFT JOIN transaction T2 ON T1.client_id = T2.client_id) LEFT JOIN transaction T3 ON T1.client_id = T3.client_id
GROUP BY T1.client_id, T1.lastname, T1.firstname;

это на access (не могу сейчас на MSSQL проверить) так что придется тебе синтаксис поправить.
но смысл ясен (?) остается только сравнить MaxServicedate и MinServicedate.

← →
ch_victor (2004-02-02 11:31) [9]

Nikolay M. © (02.02.04 11:15) [7]

не работает запрос

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

и не совсем понятно в чем преимущества использования временной таблицы перед view c функцией внутри?

← →
sniknik © (2004-02-02 11:34) [10]

хотя нет не все, тебе ведь "дырки" между соседними нужны а не минимальный и максимальный. :( еще надо подумать.

← →
ch_victor (2004-02-02 11:42) [11]

> sniknik © (02.02.04 11:34) [10]
> хотя нет не все, тебе ведь "дырки" между соседними нужны
> а не минимальный и максимальный. :( еще надо подумать.

вот именно почему я курсор и использовал

← →
sniknik © (2004-02-02 11:48) [12]

хотя чего думать то, прыгать нужно. ;о))
обьеденение нужно не с таблицей делать а с вложенным подзапросом в котором по самообьеденению разницу дат высчитывать, вот и получится в результирующем запросе с максимальной и минимальной "дыркой".

← →
Anatoly Podgoretsky © (2004-02-02 11:53) [13]

Нестыковки этим не кончаются
Возьмем три полета
P1 - 0
P2 - 364
P3 - 380

по условию P3-P1 > юольше 365, такие полеты найдены, хотя P3-P2 < 365

Второй вариант
P1 - 1.01.2000
P2 - 5.01.2000

опять же по условию разница между полетами < 365, а сейчас 2004 год, условие получается вечное. Можете надо разница между текущей датой и датой полета <= 365 дней. Тогда сработает (Max(D)-Now) <= 365 GROUP BY client_id другие условия

Нужна четкая формулировка задачи.

← →
Nikolay M. © (2004-02-02 11:56) [14]

У меня работает:

SELECT DISTINCT(client_id)
FROM transaction_ tr1
WHERE DATEDIFF(Day, (SELECT ISNULL(MAX(tr2.servicedate), tr1.servicedate) FROM transaction_ tr2 WHERE (tr1.client_id = tr2.client_id) AND (tr2.servicedate < tr1.servicedate)), tr1.servicedate) > 365

← →
ch_victor (2004-02-02 12:05) [15]

последний вариант
> Nikolay M. © (02.02.04 11:56) [14]
работает, и не очень долго около 13 секунд, спасибо за помощь

← →
Fay © (2004-02-02 12:07) [16]

MSSQL - очень быстрый. Но может тормозить в режиме совместимости с CurveHands.

← →
Nikolay M. © (2004-02-02 12:26) [17]

13 секунд - тоже немало. Все-таки попробуй еще с курсором.

← →
ch_victor (2004-02-02 12:43) [18]

> MSSQL - очень быстрый. Но может тормозить в режиме совместимости
> с CurveHands.

а как посмотреть не в этом ли режиме он работает?

← →
Nikolay M. © (2004-02-02 12:49) [19]

> > MSSQL - очень быстрый. Но может тормозить в режиме совместимости
> > с CurveHands.
> а как посмотреть не в этом ли режиме он работает?

:)))))
Если задача, которая должна отрабатывать за пару секунд, выполняется порядка часа, значит имеет быть место несовместимость :))

← →
ch_victor (2004-02-02 12:52) [20]

таже самая задача, на том же сервере но под ораклом отрабатывает действительно за пару секунд
так что кто виноват кривые руки или действительно MSSQL не самый быстрый

Код (+DDL) в студию

← →
SergSuper (2004-02-02 14:28) [22]

А не имеет смысл взять какой-нибудь промежуток и считать сколько раз какой клиент летал?

Конечно, смотреть тех, у которых есть Дата_полёта>=GetDate()-365

Эти тебе нужны?
select distinct q.client_id
from
(select
client_id
from [таблица полётов]
group by client_id, datepart(y, servicedate)
having count(*) = 0
) q

Я бы просто сгруппировал полеты по пассажирам и годам:
select extract(year from date1), passanger_id, count(*)
from mytable
group by extract(year from date1), count(*), passanger_id

(это правда синтаксис IB, но уверен в MSSQL есть подобная возможность)

Это покажет по годам, как народ летает. Я уверен, что заказчика устроит такой отчет. Совсем не обязательно ловить промежутки дат. Из того, что чел 2 раза в жизни полетел (один раз 22 декабря, а второй раз - обратно 5 января следующего года) еще никак не следует, что он часто летает. Лучший отчет - погодовой. Так сказать хит-парад часто летающих пассажиров. И работать это будет за 1 сек.

Пардон, агрегатное поле в группировке не нужно (скопировал, забыл стереть):
select extract(year from date1), passanger_id, count(*)
from mytable
group by extract(year from date1), passanger_id

Рекомендую хотя бы попробовать такой запрос.

Медленно работает MSSQL сервер Найти похожие ветки