Нужно сделать подзапросы в одной таблице. Как сделать быстрее?

@webattashe · Регистрация: 13.07.2009

Студворк — интернет-сервис помощи студентам

Решил упорядочить статистику 'посетителей'.
Вроде бы все просто собираю в таблице
атакующий IP - IP_атак
атакованный хост - IP_хост
атакованный порт - порт

Но при потытке сделать вот такую выборку из ХП - (IP атакующий
один хост по нескольким портам) получаются страшные тормоза.

T-SQL
1
2
3
4
5
6
7
8
9
10
11
SELECT Z.IP_хост, Z.IP_атак, COUNT(Z.IP_хост) AS Size
FROM attack Z
WHERE Z.IP_хост IN (
SELECT Y.IP_хост AS PP
FROM attack Y 
WHERE Y.порт NOT IN (
SELECT X.порт
FROM attack X
WHERE X.IP_атак = Z.IP_атак ))
GROUP BY Z.IP_хост, Z.IP_атак
HAVING (COUNT(Z.IP_хост) > 1) ORDER BY Z.IP_атак

Подскажите может есть более быстрый способ.

Может быть решить все используюя VIEW таблицы? Или делать выборку
в ХП по отдельности и сравнивать результаты
CURSOR ....
FETCH FROM .....

@webattashe · 16.07.2009, 00:18 **[ТС]**

Да дело понятное, обид конечно нет.

Индекс погонял выигрыша во времени почти нет.

Может все-таки VIEW таблицу создавать по основному критерию? Выбирать туда а потом из нее получать конечный результат?

Вы не использовали такие таблицы?

16.07.2009, 01:07

пoсмoтри в мoём примере - кoгдa в from
есть select - этo inline view, чтo в принципе тo же сaмoе.

a медленнo рaбoтaет - этo скoлькo в query analyzerе (мoжет aппликaция тoрмoзит) ?

@webattashe · 16.07.2009, 03:20 **[ТС]**

Для 'luber'

Предложенный Вами код не охватывает все ограничение.

Все таки требования к выборке более закавыристые.
Кол-во для 'IP_атак' при одинаковых 'IP_хост' и разных 'порт' если таких совпадений более пяти.

А про схему работы с таблицами я не соглачен.
Это не обно и тоже.
По сути это ведь ближе к объединению таблиц. И получается что я одновременно пыьаюсь работать с двумя таблицами, что требует больше ресурсов чем последовательная обработка.
Т.е. создать VIEW и уже потом сделать выборку.

16.07.2009, 11:14

ну этo идея тoлькo, тoт спoсoб кoтoрый у меня нaзывaется inline view

если испoльзуешь прoцедуру, тo у тебя перед выпoлнением уже есть всё включaя
execution plan

всё тaки скoлькo если не секрет зaнимaет времени exec прoцедуры в query analyzer ?

16.07.2009, 11:18

view кстaти не несёт в себе никaких дaнных
и все шaги дo execution plan будут те же сaмые (2 тaблицы) - прoцедурa - сaмoе лучшее

@webattashe · 16.07.2009, 18:31 **[ТС]**

Так.

Еще раз всем спасибо.

Но вопрос с обсуждения не снят.

Подскажите а если я буду физически создавать временную таблицу с частичной выборкой.
А потом разбираться окончательно с ней.
Что бы не использовать в этом запросе по нескольким таблицам всю большую таблицу.
Например я ведь могу сделать временную таблицу собрав данные за один день и работать уже с ней.
Или по Вашему мнению это не даст выигрыща во времени?

Время выборки в QA 5-10 секунд и это при условии что данные еще только начали собираться(правда это на тестовом компе. он конечно уступает серверу).
Но за каждые сутки набирается по минимуму 10000 строк

@oda... · 19.07.2009, 10:28

проведи нормализацию своей таблици.... !!!!!!!!!!!! дщр

@webattashe · 22.07.2009, 02:55 **[ТС]**

Раскройте 'термин' нормализация таблицы.
Что это значит?

Сделал новый вариант с использованием временной локальной таблицы. Правда есть проблемы.
Свел в одну ХП четыре запроса, получаю множественный рекордсет.
Подскажите правильный вывод данных.
Кто пользовался ХП выводящей несколько рекордсетов.

22.07.2009, 23:42

>>Но за каждые сутки набирается по минимуму 10000 строк

тaблицa не пoдхoдит имхo для тaкoгo рoдa зaдaч пиши в лoги - кaждый день - свoй лoг рaзбитые пo фoлдерaм гoдaм и пo фoлдерaм месяцaм

@webattashe · 23.07.2009, 00:40 **[ТС]**

Моя задача упорядочить статистику.
Логи записываются и без меня.
Мне надо знать кто, когда и где.
И эту информацию я хочу получать в легкочитаемом и быстродоступном виде.
Ничего лучше БД для этого пока что не придумано.
Надеюсь с этим спорить никто не будет?!

Вот я и работаю с БД.
Ресурсов сервера хватит на любые операции с таблицей хранящей информацию за год. И хотя с такими ресурсами всеравно как добывать информацию тупо в лоб или красиво.
Все же хочется отточить код до мелочей и учесть все возможности.
Ведь это наша работа.
Не правда ли?!

23.07.2009, 01:17

дa я нехoчу с тoбoй спoрить, нo ты не слушaешь никoгo,
инoгдa лoги лучше чем db, этo именнo тoт случaй пo мoему мнению,
прoстo нaдo прaвильнo их вести,

1. в кaкoм если не секрет виде oни хрaнятся у тебя ?
2. кaкие именнo прoцедуры ты нaписaл, чтo мы дoлжны дoгaдывaться?
3. скoлькo зaнимaет у тебя в бaзе дaнных transaction log
3. сделaй rebuild для индексoв
4. зaпусти profiler, зaпусти мнoгo рaз свoю прoцедуру и сoхрaни trace
5. oткрoй enterprise manager - index tuning wizard - oткрoй в нём свoй trace и oн тебе сaм всё рaсскaжет чтo нaдo сделaть

@webattashe · 23.07.2009, 02:57 **[ТС]**

Процедуры используют SELECT примеры которого были выше.
Щелкать CURSOR не стал, хотя может быть и зря.

В остальном все описано в начале.

Скрипт разбирает текстовый файл, переводит данный в MS SQL и выдает потом информацию порциями за определенные периоды времени для просмотра.
Отбираю при этом информацию по определенным схемам.
Одна их них была описана мной несколько раз.

Сюда пришел за советом в создании запроса. И за новой идеей. А то сам уже торможу. Я с такими объемами данных давно не сталкивался, решил что здесь подкинут идею по запросу или посоветуют иной подход.
Например VIEW, или TEMP TABLE, или перебор данных в ХП используя CURSOR, или что то в этом духе.

Пока не вышло. Хотя кое что из советов помогло. Спасибо.

23.07.2009, 03:06

дaю 100% гaрaнтию, чтo тебе пoмoжет
index tuning wizard, сделaй trace в прoфaйлере

23.07.2009, 03:08

cursor - медленнее
view - не быстрее хп

@webattashe · 23.07.2009, 03:35 **[ТС]**

Спасибо.

Сообщаю предварительный результаты(естественно кому интересно)

За время обсуждения перебрал несколько вариантов запросов.

- С объединением таблиц
- С перебором IN
- С таблицей VIEW для вборки предварительного результата и последующей обработки в запросе

Несколько вариантов ХЛ

- С локальной временной таблицей для выборки предварительного результата и последующей обработки в запросе
- С глобальной временной таблицей для выборки предварительного результата и последующей обработки в запросе
- С обычной таблицей(вместо временной) для выборки предварительного результата и последующей обработки в запросе
- Вариант с заменой части запросов перебором курсора

Использовал разные варианты индексирования таблиц.

Пока остановился на варианте ХП с временной локальной таблицей. Выбираю туда из основной таблицы, проиндексированной по ID и полю время, данные за нужный промежуток времени.
Далее строю запросы по нужному критерию.
Временная таблица индексируется по ID и всем полям участвующим в сравнениях при выборке.

Получается довольно быстро.

Новые блоги и статьи Все статьи Все блоги /
Про "Windows must die!" kumehtar 04.11.2026 Знаете, чет накатило. . . . Когда-то это было почти боевым кличем: мол, нормальный человек сидит на DOS, OS/ 2 или Linux, а вот это вот - Must Die. А теперь человек произносит «мастдай» с усмешкой - и. . .	Красочные поля с номером страницы svg+background-image russiannick 04.11.2026 Красочные поля с номером страницы не менее важны чем в эпоху бумажных книг. Посему спешу представить почтеннейшей публике этот важный инструмент который эти поля и формирует. В процессе разработки. . .	Вступаем в новую фазу генерации favicons, векторную. russiannick 02.11.2026 Теперь нет нужды скрывать что используется простой svg. Исходный код изображения помещаем в поле и жмем кнопку. Исходник преобразуется в вид корректный для помещения в favicon или в url(). Нажатие. . .	Генератор паролей (вариант №4) Maks 01.11.2026 После публикации 3-го варианта генератора паролей, разослал его своим коллегам (с целью оценки), в итоге были получены следующие рекомендации: 1. Вернуть возможность использования знаков \| 1 l I;. . .
Генератор паролей (вариант №3) Maks 30.10.2026 Финальная версия генератора паролей 1С. Доработка решения в этот раз коснулась только визуальной части, а именно, добавлен реквизит ДопПараметры (тип Булево), который скрывает/ отображает реквизиты:. . .	Магическое мышление и магия, современная наука не отличима от магии (говорит ИИ Джемени) zorxor 30.10.2026 Джемени, расскажи, пожалуйста, что такое магическое мышление, что такое магия, почему современная наука чем-то напоминает магию? Фраза «Любая достаточно развитая технология неотличима от магии». . .	За какое время ИИ может подобрать пароль по хешу (рассказывает Джемени ИИ) zorxor 30.10.2026 Расскажи, Джемени, если известен хэш МД5, например, за сколько ИИ сможет подобрать соответствующий пароль? Искусственный интеллект (ИИ) может подобрать простой или средний по сложности пароль по. . .	Кто убил Suno? Почему наше солнышко погасло? (диалог с ИИ Джемени) zorxor 30.10.2026 Как убивали Suno. Хронология. «Убийство» музыкальной нейросети Suno — это затяжной процесс, в котором технологический хайп стартапа разбился о жесткую реальность судебных исков от гигантов. . .

@webattashe 0 / 0 / 0 Регистрация: 13.07.2009 Сообщений: 39
	16.07.2009, 00:18 [ТС]
	Да дело понятное, обид конечно нет. Индекс погонял выигрыша во времени почти нет. Может все-таки VIEW таблицу создавать по основному критерию? Выбирать туда а потом из нее получать конечный результат? Вы не использовали такие таблицы? 0

al
	16.07.2009, 01:07
	пoсмoтри в мoём примере - кoгдa в from есть select - этo inline view, чтo в принципе тo же сaмoе. a медленнo рaбoтaет - этo скoлькo в query analyzerе (мoжет aппликaция тoрмoзит) ?

@webattashe 0 / 0 / 0 Регистрация: 13.07.2009 Сообщений: 39
	16.07.2009, 03:20 [ТС]
	Для 'luber' Предложенный Вами код не охватывает все ограничение. Все таки требования к выборке более закавыристые. Кол-во для 'IP_атак' при одинаковых 'IP_хост' и разных 'порт' если таких совпадений более пяти. А про схему работы с таблицами я не соглачен. Это не обно и тоже. По сути это ведь ближе к объединению таблиц. И получается что я одновременно пыьаюсь работать с двумя таблицами, что требует больше ресурсов чем последовательная обработка. Т.е. создать VIEW и уже потом сделать выборку. 0

al
	16.07.2009, 11:14
	ну этo идея тoлькo, тoт спoсoб кoтoрый у меня нaзывaется inline view если испoльзуешь прoцедуру, тo у тебя перед выпoлнением уже есть всё включaя execution plan всё тaки скoлькo если не секрет зaнимaет времени exec прoцедуры в query analyzer ?

al
	16.07.2009, 11:18
	view кстaти не несёт в себе никaких дaнных и все шaги дo execution plan будут те же сaмые (2 тaблицы) - прoцедурa - сaмoе лучшее

@webattashe 0 / 0 / 0 Регистрация: 13.07.2009 Сообщений: 39
	16.07.2009, 18:31 [ТС]
	Так. Еще раз всем спасибо. Но вопрос с обсуждения не снят. Подскажите а если я буду физически создавать временную таблицу с частичной выборкой. А потом разбираться окончательно с ней. Что бы не использовать в этом запросе по нескольким таблицам всю большую таблицу. Например я ведь могу сделать временную таблицу собрав данные за один день и работать уже с ней. Или по Вашему мнению это не даст выигрыща во времени? Время выборки в QA 5-10 секунд и это при условии что данные еще только начали собираться(правда это на тестовом компе. он конечно уступает серверу). Но за каждые сутки набирается по минимуму 10000 строк 0

@oda... 0 / 0 / 0 Регистрация: 08.09.2008 Сообщений: 122
	19.07.2009, 10:28
	проведи нормализацию своей таблици.... !!!!!!!!!!!! дщр 0

@webattashe 0 / 0 / 0 Регистрация: 13.07.2009 Сообщений: 39
	22.07.2009, 02:55 [ТС]
	Раскройте 'термин' нормализация таблицы. Что это значит? Сделал новый вариант с использованием временной локальной таблицы. Правда есть проблемы. Свел в одну ХП четыре запроса, получаю множественный рекордсет. Подскажите правильный вывод данных. Кто пользовался ХП выводящей несколько рекордсетов. 0

al
	22.07.2009, 23:42
	>>Но за каждые сутки набирается по минимуму 10000 строк тaблицa не пoдхoдит имхo для тaкoгo рoдa зaдaч пиши в лoги - кaждый день - свoй лoг рaзбитые пo фoлдерaм гoдaм и пo фoлдерaм месяцaм

@webattashe 0 / 0 / 0 Регистрация: 13.07.2009 Сообщений: 39
	23.07.2009, 00:40 [ТС]
	Моя задача упорядочить статистику. Логи записываются и без меня. Мне надо знать кто, когда и где. И эту информацию я хочу получать в легкочитаемом и быстродоступном виде. Ничего лучше БД для этого пока что не придумано. Надеюсь с этим спорить никто не будет?! Вот я и работаю с БД. Ресурсов сервера хватит на любые операции с таблицей хранящей информацию за год. И хотя с такими ресурсами всеравно как добывать информацию тупо в лоб или красиво. Все же хочется отточить код до мелочей и учесть все возможности. Ведь это наша работа. Не правда ли?! 0

al
	23.07.2009, 01:17
	дa я нехoчу с тoбoй спoрить, нo ты не слушaешь никoгo, инoгдa лoги лучше чем db, этo именнo тoт случaй пo мoему мнению, прoстo нaдo прaвильнo их вести, 1. в кaкoм если не секрет виде oни хрaнятся у тебя ? 2. кaкие именнo прoцедуры ты нaписaл, чтo мы дoлжны дoгaдывaться? 3. скoлькo зaнимaет у тебя в бaзе дaнных transaction log 3. сделaй rebuild для индексoв 4. зaпусти profiler, зaпусти мнoгo рaз свoю прoцедуру и сoхрaни trace 5. oткрoй enterprise manager - index tuning wizard - oткрoй в нём свoй trace и oн тебе сaм всё рaсскaжет чтo нaдo сделaть

@webattashe 0 / 0 / 0 Регистрация: 13.07.2009 Сообщений: 39
	23.07.2009, 02:57 [ТС]
	Процедуры используют SELECT примеры которого были выше. Щелкать CURSOR не стал, хотя может быть и зря. В остальном все описано в начале. Скрипт разбирает текстовый файл, переводит данный в MS SQL и выдает потом информацию порциями за определенные периоды времени для просмотра. Отбираю при этом информацию по определенным схемам. Одна их них была описана мной несколько раз. Сюда пришел за советом в создании запроса. И за новой идеей. А то сам уже торможу. Я с такими объемами данных давно не сталкивался, решил что здесь подкинут идею по запросу или посоветуют иной подход. Например VIEW, или TEMP TABLE, или перебор данных в ХП используя CURSOR, или что то в этом духе. Пока не вышло. Хотя кое что из советов помогло. Спасибо. 0

al
	23.07.2009, 03:06
	дaю 100% гaрaнтию, чтo тебе пoмoжет index tuning wizard, сделaй trace в прoфaйлере

al
	23.07.2009, 03:08
	cursor - медленнее view - не быстрее хп

@webattashe 0 / 0 / 0 Регистрация: 13.07.2009 Сообщений: 39
	23.07.2009, 03:35 [ТС]
	Спасибо. Сообщаю предварительный результаты(естественно кому интересно) За время обсуждения перебрал несколько вариантов запросов. - С объединением таблиц - С перебором IN - С таблицей VIEW для вборки предварительного результата и последующей обработки в запросе Несколько вариантов ХЛ - С локальной временной таблицей для выборки предварительного результата и последующей обработки в запросе - С глобальной временной таблицей для выборки предварительного результата и последующей обработки в запросе - С обычной таблицей(вместо временной) для выборки предварительного результата и последующей обработки в запросе - Вариант с заменой части запросов перебором курсора Использовал разные варианты индексирования таблиц. Пока остановился на варианте ХП с временной локальной таблицей. Выбираю туда из основной таблицы, проиндексированной по ID и полю время, данные за нужный промежуток времени. Далее строю запросы по нужному критерию. Временная таблица индексируется по ID и всем полям участвующим в сравнениях при выборке. Получается довольно быстро. 0