Есть простая табличка:


Primary Key - REGIM_ID. На эту таблицу по Foreign Key ссылаются еще 18 таблиц (каскадное удаление не назначено).
В таблице около 50 000 записей. Сначала удаляю записи из зависимых таблиц по REGIM_ID - выполняется быстро. Но последний шаг:


выполняется около 4 минут. В чем может быть дело?

Что-то с трудом верится ..

Верится - не верится, а факт. Меня "приставили к стенке" и требуют срочно исправить ситуацию, которая возникла недавно, до этого все работало с удовлетворительной скоростью.

Только с этой таблицей, или с любой?
Лог, случаем, за какой-нибудь большой размер не вылез?

Проблемы только с этой таблицей (отличается от других большим кол-вом FK). Сама база довольно большая 6Гб, под T-Log выделено 150 Мб, занято 18 Мб.

это очистка таблиц? транкейт не работает? (если внешний ключ с удалением из зависимой)
отключить или удалить его перед очисткой? (с последующим восстановлением конечно)

Транкейтить нельзя, т.к. удаляется выборочная информация. Каскадное удаление убрала умышленно, т.к. MSSQL не справляется с таким кол-вом (больше каскадных 5 ключей - и вешается)

Триггер на delete есть?

> Каскадное удаление убрала умышленно
как?
проверка очевидно остается иначе не было бы так долго. вот проверь, скопируй таблицу с данными в независимую от ключей и удали тоже самое из нее (индексы по полям где условию не забудь в ней воссоздать те же что в оригинале). и как? быстро?
если быстро то значит проверки есть и их надо отключать (анчек констраинтам делать)...
в принципе 18 проверок в 18 таблицах к удалению каждой записи... понятно что долго будет.

подожди,
> Primary Key - REGIM_ID.
> ... REGIM_ID=2265
это одна запись удаляется 4 минуты? ....

Асе-таки похоже на "тормоза" при исполнении тела триггера на удаление ..

> это одна запись удаляется 4 минуты? ....


Похоже на то. Что удивительно.

Триггеров нет и галочек на констраинтах нет (зуб даю, это первое, что я проверила), а работает так, как будто они есть.
Что-то с самой таблицей неладно - даже SELECT выполняется медленно.
Изменила незначительно структуру таблицы (чтобы прошел ALTER TABLE), удаление стало работать 1.5 минуты. Но это тоже не годится, нужно около 10 сек.

Похоже что поле первичного ключа перстало быть индексированным.
Либо индекс выключен либо его там вообще никогда не было.

> либо его там вообще никогда не было.
->
> Есть простая табличка:
> ...
его и нет. :)  если по скрипту
разве что в заявлении
> Primary Key - REGIM_ID

да на сервере тупо идет проверка по всей куче ссылающихся таблиц

> sniknik ©   (12.01.09 12:17) [14]



> его и нет


А как тогда автор умудрилась организовать связь с подчиненными таблицами и каскадные операции ?
Или эта СУБД позволяет подобные вольности ? Я просто не в курсе..

не было бы пк, форин констрейнты на REGIMS не создались бы

Ну что вы, господа, я понимаю праздники... но я уж не до такой степени...
PK есть и 18 FK, соответственно, есть. В скрипт не включила, чтобы место сэкономить.

> Или эта СУБД позволяет подобные вольности ? Я просто не в курсе..
не пробовал, но вроде не должен.

+
ну, форейн я лично видел тут тоже только в заявлениях... может он считает что если в селектах джойн к этой таблице делаеш то это и есть внешний ключ...

> В скрипт не включила, чтобы место сэкономить.
утаивание инфы обычно приводит к обратному... даже к многостраничному флуду.

и кстати скрипт похож (очень похож) на автосгенеренный в QA.

тормоза из за проверок 18 таблиц на возможные нарушение fk после удаления ключа.

ну ежу же понятно.

в
> ну ежу же понятно.

А мне не очень, что вчера проверок не было? Что произошло сегодня? Возможно, конечно, что база сегодня уже потежелее...

А мне не очень, что вчера проверок не было? Что произошло сегодня?

о! моя любимая жалоба пользователей.
"почему сломалось, ведь вчера же работало?!"

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

и так далее.

итого: в "калинке" продают вечные чайники.

Временно удалила все FK - удаление работает за 1 сек. Причину пока так и не определила.
Все, пошла "на ковер", сдаваться.

> Что произошло сегодня?
вопрос конечно интересный, но как быть без достоверной инфы???

зайди в QA, найди в браузере объектов свою таблицу и сделай скрипт на ее создание (реальный скрипт, реальной таблицы!), и приведи его (получившийся скрипт) весь as is не экономя место (переживем как нибудь дополнительную сотню другую байт)...

и то же самое для таблицы с fk ссылающейся на эту, любой из 18 если они однотипны.

> Временно удалила все FK - удаление работает за 1 сек.
а я предлагал... но только анчек сделать, а после удаления восстановить. но вообще даже 18 проверок для удаления одной записи это слишком (что 4 что 1.5 мин)

> удаление работает за 1 сек.
из 50 тыс? долго. у меня из миллиона одна запись по ключу удаляется 0,01 сек.

> Временно удалила все FK - удаление работает за 1 сек. Причину
> пока так и не определила.
> Все, пошла "на ковер", сдаваться.



> На эту таблицу по Foreign Key ссылаются еще 18 таблиц


В тех таблицах, который ссылаются на "проблемную", нужно ещё и индексы по полю связи иметь, ибо без них происходит полный скан тех самых таблиц для обеспечения Foreign Key - отсюда и тормоза.

DBCC INDEXDEFRAG сделай
почисти лог транзакций
посмотри, что вызывает такие тормоза
http://msdn.microsoft.com/ru-ru/magazine/cc135978.aspx

> В тех таблицах, который ссылаются на "проблемную", нужно
> ещё и индексы по полю связи иметь, ибо без них происходит
> полный скан тех самых таблиц для обеспечения Foreign Key
> - отсюда и тормоза.

Как только прочитала этот пост, сразу поняла, что это и есть решение проблемы. Восстановила все FK на таблицу REGIMS, для каждой из 18 зависимых таблиц создала индекс по REGIM_ID, до кучи выполнила DBCC INDEXDEFRAG на REGIMS. Теперь все летает!
Почему это решение не пришло мне самой в голову? Видимо, потому, что замедление началось не постепенно, а сразу. Пока в зависимых таблицах было по 6-7 млн. записей, все было благополучно. Как только в одной из таблиц перевалило за 8 млн. (достигло критической массы) - все резко (без объявления войны) затормозилось.
Большое спасибо всем за помощь.

> Как только в одной из таблиц перевалило за 8 млн. (достигло
> критической массы) - все резко (без объявления войны) затормозилось.
>

теперь один вопрос остался - почему критическая масса 8, а не 6.5 и не 12? :) Как ее предсказать, есть какая-нить методика?
P.S. спрашиваю из праздного любопытства ;)

> ля каждой из 18 зависимых таблиц создала индекс по REGIM_ID


У Вас его не было? Да на таких-то объёмах???


> почему критическая масса 8, а не 6.5 и не 12? :) Как ее
> предсказать, есть какая-нить методика?


На досуге поковыряюсь, на следующей неделе обсудим в стандартном месте...  :)

> обеспечения Foreign Key - отсюда и тормоза.
если у индекса отключена проверка (uncheck у fk) то "обеспечение" как таковое не производится.

> если у индекса отключена проверка

индеса нет. есть только Constraint


> то "обеспечение" как таковое не производится

И в чем же тогда Constraint состоит? :)
Ибо сказано The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail when the deleted or changed primary key value corresponds to a value in the FOREIGN KEY constraint of another table. To successfully change or delete a row in a FOREIGN KEY constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.


> (uncheck у fk)

Имхо, не для тех целей. Forcing a FOREIGN KEY Constraint by Using WITH NOCHECK относится к непроверке уже существующих до создания Constraint-а данных.

> У Вас его не было? Да на таких-то объёмах???

Да, не было. На таблицах были только PK (из 3-5 полей, одно из которых REGIM_ID). Я считала, что этого индекса достаточно. Да и работало все нормально (6 млн. зап., согласитесь, тоже не мало), я и не задумывалась.
Конечно, хотелось бы понять, где в MSSQL лежат грабли (для которых 7 и 8 млн. зап. - большая разница), чтобы при проектировании следующей БД либо не наступать на них, либо вообще ликвидировать.


> на следующей неделе обсудим в стандартном месте...  

А это где? Я бы с удовольствием подключилась к всемирному разуму...

> Конечно, хотелось бы понять, где в MSSQL лежат грабли (для
> которых 7 и 8 млн. зап. - большая разница), чтобы при проектировании
> следующей БД либо не наступать на них, либо вообще ликвидировать.


Надо эксперементировать. Я сейчас точно не скажу (а врать неохота..), была некая рекомендация по построению индексов.
И время-от-времени надо индексы перестраивать. Когда данные изменились  ~на 20% от первоначальных - надо перестраивать, ибо может оказаться, что с индексами даже хуже.


> А это где? Я бы с удовольствием подключилась к всемирному
> разуму...


э-э-э-э.... Дело в том, что с Абрамовым мы регулярно обсуждаем всякое... Под пиво.... На Савёловском вокзале есть 2 чудных места - "Салун Бочка" и "Золотая Вобла". Ну а поскольку мне как раз оттуда домой ехать (в Дубну) - то сам Аллах велел.
Так что ежели тебе из Ебурга до Москвы недалеко - милости просим, клуб не закрытый... :) Мыло давай.. :)

Эх, пивко, да с рыбкой, да в хорошей компании... Но, конечно, далековато, потому остается только виртуальное общение: roeva@tsgrp.ru
Круг моих интересов, в общем, должен быть уже понятен - построение оптимальных БД промышленного масштаба (то бишь куча данных, ежесекундно в БД что-нибудь пишется и что-нибудь удаляется). Буду рада любой полезной информации на эту тему.

> Ольга ©   (14.01.09 09:44) [36]


> Да, не было. На таблицах были только PK (из 3-5 полей, одно
> из которых REGIM_ID). Я считала, что этого индекса достаточно.
>  

его достаточно, если REGIM_ID первым полем в PK.

> его достаточно, если REGIM_ID первым полем в PK.


Ага.
Вот наглядный пример преимущества суррогатного ключа.

> Ega23 ©   (14.01.09 20:02) [40]

причем тут суррогатный ключ???
если по логике какая-то комбинация уникальная, она и должна быть объявлена unique или pk. Если никто дальше на таблицу не ссылается, то нафих еще и суррогатный ключ городить - поле, которое никогда не будет не будет ни в select <field list>, ни в цруку

> ни в цруку

yb d where

> yb d where

блин,
ни в where
;)

> Если никто дальше на таблицу не ссылается ...
18 таблиц в fk.

> Petr V. Abramov  (15.01.2009 0:14:43)  [43]

Взял да испортил, второе как раз дополняло первое и было прекрасно.

> sniknik ©   (15.01.09 00:38) [44]
> > Если никто дальше на таблицу не ссылается ...
> 18 таблиц в fk.

имеется в виду "ссылающиеся" таблицы, у которых pk большой.
P.S. из постов вроде следует, что к чему

Удалено модератором

Удалено модератором