У меня следующая ситуация. В базах данных я не специалист, но так случилось, что сейчас работаю над проектом с БД. Проект большой и не мой, мне поручают его поддерживать. Вобщем, заметил, на мой взгляд, серьезные ошибки в хранимых процедурах:
1. Есть хранимка (>500 строк) текст которой, это один большой Case. Не лучше ли было вынести каждую ветку кейса, в отдельную хранимку? Думаю, это прибавит производительности.
2. Есть хранимки, которые принимают > 10 параметров, и в коде часто параметрам передается null.. Не проще было бы опять несколько хранимок написать? Как это отражается на безопасности и производительности?
Ну, про всякого рода
"SELECT * FROM table WHERE field2="+param2+" .... "
, я уж вообще молчу.. Ладно хоть продукт предназначен для внутреннего использования, а не для продаж..
Есть какие-нибудь доки о том как правильно писать хранимки? Что можно почитать, кроме собственно документации к БД?
P.S. А то невольно начинает возникать мысль "Господи, куда я попал?! "
Здравствуйте, alexdev, Вы писали:
A>1. Есть хранимка (>500 строк) текст которой, это один большой Case. Не лучше ли было вынести каждую ветку кейса, в отдельную хранимку? Думаю, это прибавит производительности.
Производительности прибавит навряд ли, а вот логическое структурирование (декомпозиция) будет лучше. Поддерживать проще будет. Но чтобы точно ответить надо смотреть код хранимки.
A>2. Есть хранимки, которые принимают > 10 параметров, и в коде часто параметрам передается null.. Не проще было бы опять несколько хранимок написать? Как это отражается на безопасности и производительности?
Нужно смотреть конкретный случай. Очень возможно что не проще. Например если ХП делает выборку и если параметр не null, то он участвует в фильтрации, а если не null — то не участвует, т.е. что-то типа
... AND (SupplierID_ IS NULL OR SupplierID = SupplierID_)
то я бы предпочел как раз такую ХП, а не десять с разными вариациями where-фильтра вместо неё.
Здравствуйте, MozgC, Вы писали:
MC>Здравствуйте, alexdev, Вы писали:
A>>1. Есть хранимка (>500 строк) текст которой, это один большой Case. Не лучше ли было вынести каждую ветку кейса, в отдельную хранимку? Думаю, это прибавит производительности. MC>Производительности прибавит навряд ли, а вот логическое структурирование (декомпозиция) будет лучше. Поддерживать проще будет. Но чтобы точно ответить надо смотреть код хранимки.
код привести не могу, но постараюсь изложить суть
допустим эта процедура с 5 параметрами, код примерно следующий
BEGIN
CASE param1
WHERE 'Get1' THEN ... // 100-200 строк
WHERE 'Get2' THEN ... // 100-200 строк
WHERE 'Get3' THEN ... // 100-200 строк
END CASE;
END;
Я не знаю как хранимка выполняется на сервере, но могу предположить, что примерно также, как и исполняемые файлы. т.е. скомпилированная хранимка, проецируется в память (или она всегда там висит? ) Ну и собственно выполняется. Если она каждый раз проецируется в память, то думаю, что резоннее разбить на несколько..
A>>2. Есть хранимки, которые принимают > 10 параметров, и в коде часто параметрам передается null.. Не проще было бы опять несколько хранимок написать? Как это отражается на безопасности и производительности? MC>Нужно смотреть конкретный случай. Очень возможно что не проще. Например если ХП делает выборку и если параметр не null, то он участвует в фильтрации, а если не null — то не участвует, т.е. что-то типа MC>
... AND (SupplierID_ IS NULL OR SupplierID = SupplierID_)
то я бы предпочел как раз такую ХП, а не десять с разными вариациями where-фильтра вместо неё.
точно сказать не могу, но where-фильтров в коде достаточно. Ладно еще когда 10 входных параметров, но когда входных параметров >= 30 ...
Здравствуйте, alexdev, Вы писали:
A>допустим эта процедура с 5 параметрами, код примерно следующий
A>BEGIN A>CASE param1 A> WHERE 'Get1' THEN ... // 100-200 строк A> WHERE 'Get2' THEN ... // 100-200 строк A> WHERE 'Get3' THEN ... // 100-200 строк A>END CASE; A>END;
Здравствуйте, alexdev, Вы писали:
A>У меня следующая ситуация. В базах данных я не специалист, но так случилось, что сейчас работаю над проектом с БД. Проект большой и не мой, мне поручают его поддерживать. Вобщем, заметил, на мой взгляд, серьезные ошибки в хранимых процедурах:
БД на абстрактной СУБД или на конкретной? Работа с хранимками на разных серверах имеет свои особенности.
A>1. Есть хранимка (>500 строк) текст которой, это один большой Case. Не лучше ли было вынести каждую ветку кейса, в отдельную хранимку? Думаю, это прибавит производительности.
Производительность врядли.
Строк конечно многовато, но смотря что она делает, если этот case использован в стиле индийского программирования — то это очень плохо для программиста.
A>2. Есть хранимки, которые принимают > 10 параметров, и в коде часто параметрам передается null.. Не проще было бы опять несколько хранимок написать? Как это отражается на безопасности и производительности?
Страшного не вижу. Может даже часть зарезервировны.
A>Ну, про всякого рода
"SELECT * FROM table WHERE field2="+param2+" .... "
, я уж вообще молчу.. Ладно хоть продукт предназначен для внутреннего использования, а не для продаж..
Не очень хорошо. Хотя это смотря какой сервак...
A>Есть какие-нибудь доки о том как правильно писать хранимки? Что можно почитать, кроме собственно документации к БД?
гугл
A>1. Есть хранимка (>500 строк) текст которой, это один большой Case. Не лучше ли было вынести каждую ветку кейса, в отдельную хранимку? Думаю, это прибавит производительности.
Производительность вряд ли сильно возрастет, а вот читабельность и поддерживаемость возрастет в разы. Это если правильно разбить на мелкие. Сколько там процедур и что за сервер? на MSSQL при правильном именовании процедур можно поддерживать базу с несколькими тысячами хранимок, тут уже обсуждалось неоднократно.
A>2. Есть хранимки, которые принимают > 10 параметров, и в коде часто параметрам передается null.. Не проще было бы опять несколько хранимок написать? Как это отражается на безопасности и производительности?
На безопасность и производительность сам факт того, что параметру Null передается, никак не влияет. Но вот если внутри не все варианты параметров проверяются — тогда ой.
A>Ну, про всякого рода
"SELECT * FROM table WHERE field2="+param2+" .... "
, я уж вообще молчу.. Ладно хоть продукт предназначен для внутреннего использования, а не для продаж..
Drop table students, ага
A>Есть какие-нибудь доки о том как правильно писать хранимки? Что можно почитать, кроме собственно документации к БД? A>P.S. А то невольно начинает возникать мысль "Господи, куда я попал?! "
Надо читать что-то по общей культуре программирования, как-то разбивания кода на мелкие логически связанные действия, недопустимости спагетти итд.
Шурыгин Сергей
"Не следует преумножать сущности сверх необходимости" (с) Оккам
Здравствуйте, alexdev, Вы писали:
A>Доброго времени суток, коллеги!
A>У меня следующая ситуация. В базах данных я не специалист, но так случилось, что сейчас работаю над проектом с БД. Проект большой и не мой, мне поручают его поддерживать. Вобщем, заметил, на мой взгляд, серьезные ошибки в хранимых процедурах:
A>1. Есть хранимка (>500 строк) текст которой, это один большой Case. Не лучше ли было вынести каждую ветку кейса, в отдельную хранимку? Думаю, это прибавит производительности.
MySQL поддерживает IF/THEN и переменные, что очень часто гораздо «чище» выглядит, чем множество CASE. Не обязательно все писать одним монструозным запросом! Главное — чтоб код был простым.
A>2. Есть хранимки, которые принимают > 10 параметров, и в коде часто параметрам передается null.. Не проще было бы опять несколько хранимок написать? Как это отражается на безопасности и производительности? > 10 — это нормально... но в целом, надо призадуматься, а не передается ли лишних данных, например которые можно было бы выудить опосредованно, например передавать не (КЛЮЧДОКУМЕНТА,ДАТА,НОМЕР,СОТРУДНИК), а только КЛЮЧДОКУМЕНТА.
A>Ну, про всякого рода
"SELECT * FROM table WHERE field2="+param2+" .... "
, я уж вообще молчу.. Ладно хоть продукт предназначен для внутреннего использования, а не для продаж..
Вроде, с «PARAMETER BINDING»(не знаю как по-русски) у MySQL проблемы, хотя могу ошибаться, вроде как библиотеки есть, но далеко не идеальны. У Оракла все замечательно они двух зайцев убивают этим — исключает «SQL Injection» и позволяет оптимизатору кэшировать запросы. И это еще в книге Тома Кайта сказано, что оптимизатор понимает запросы с разными параметрами но использующие PB как одинаковые запросы, а не использующие считаются разными. Так в одной «программе» видел как излишне наворачивались запросы (типа, получения имени текущего табличного пространства, проверки модуля и т.п.), при том некоторые параметры именно «вшивались» в запрос, в результате запрос отрабатывал 15сек, когда делался «PARAMETER BINDING» запрос отрабатывал 0.2сек. Кстати, это одна из причин, по которой останавливать базу Оракла не очень хорошо — вроде как кэши сбрасываются и он заново начинает статистику по запросам собирать.
A>Есть какие-нибудь доки о том как правильно писать хранимки? Что можно почитать, кроме собственно документации к БД?
Тут надо подходить здраво, на надо все переносить на хранимки. Надо делать их как можно проще (как, в прочем, не только в SQL), тогда легче будет их поддерживать, а в SQL не все просто бывает с поддержкой, когда процедур свыше 2к, а еще есть проверка прав доступа и все такое.
Добавлю, что с хранимками для MySQL тут не недавно «встрял». На одном известном популярном хостере они просто НЕ РАБОТАЮТ. даже те, что содержат только "select 1 from dual;" Оказалось надо запустить какую-то консольную утилиту MySQL для исправления безобразия. Не говоря уже о встреченных багах. Очевидно, что мало кто пользуется процедурами в реальности и именно поэтому "SQL Injection" так распространен.
A>P.S. А то невольно начинает возникать мысль "Господи, куда я попал?! "
Это больше зависит от уровня программиста и уровня кода.
Здравствуйте, Plague, Вы писали:
P>MySQL поддерживает IF/THEN и переменные, что очень часто гораздо «чище» выглядит, чем множество CASE. Не обязательно все писать одним монструозным запросом! Главное — чтоб код был простым.
Тем не менее — выглядит устрашающе
P>Вроде, с «PARAMETER BINDING»(не знаю как по-русски) у MySQL проблемы....
Спасибо, обязательно почитаю!
P>Тут надо подходить здраво, на надо все переносить на хранимки. Надо делать их как можно проще (как, в прочем, не только в SQL), тогда легче будет их поддерживать, а в SQL не все просто бывает с поддержкой, когда процедур свыше 2к, а еще есть проверка прав доступа и все такое.
Заметил одну ХП из ~5000 строк. Предложил разбить на несколько, на что, получил ответ: "А зачем?". Отвечаю, что поддерживать легче и вообще, есть ли уверенность, что ~5000 строк написаны оптимальным для MySQL образом?..
Вообщем
P>Добавлю, что с хранимками для MySQL тут не недавно «встрял». На одном известном популярном хостере они просто НЕ РАБОТАЮТ. даже те, что содержат только "select 1 from dual;" Оказалось надо запустить какую-то консольную утилиту MySQL для исправления безобразия. Не говоря уже о встреченных багах. Очевидно, что мало кто пользуется процедурами в реальности и именно поэтому "SQL Injection" так распространен.
Я считаю, что исправить в коде уязвимость такого рода, чем менять ХП в базе (тут уже надо, какбэ сообща )
A>>P.S. А то невольно начинает возникать мысль "Господи, куда я попал?! " P>Это больше зависит от уровня программиста и уровня кода.
Удивительно то, что я не считаю себя программистом высокого уровня, да и годков мне поменьше чем тем, кто писал все это...
Здравствуйте, alexdev, Вы писали:
A>MySQL
Это несколько меняет дело.
В дополнение к посту от Plague добавлю.
Хранимки в MySQL появились уже довольно давно, но все равно остаются весьма глючными, к тому-же в них есть существенное кол-во ограничений.
Почитайте соответствующие вашей версии доки (http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html) перед любым рефакторингом.
Здравствуйте, alexdev, Вы писали:
P>>Добавлю, что с хранимками для MySQL тут не недавно «встрял». На одном известном популярном хостере они просто НЕ РАБОТАЮТ. даже те, что содержат только "select 1 from dual;" Оказалось надо запустить какую-то консольную утилиту MySQL для исправления безобразия. Не говоря уже о встреченных багах. Очевидно, что мало кто пользуется процедурами в реальности и именно поэтому "SQL Injection" так распространен.
A>Я считаю, что исправить в коде уязвимость такого рода, чем менять ХП в базе (тут уже надо, какбэ сообща )
Тут именно вопрос в том что легче: дописывать везде код для недопущения уязвимости (но в реальности, все допускают ошибки и где-то проверку забудете) или искоренить SQL Injection как класс используя Parameter binding и хорошо настроенный ограниченный доступ к базе? По правильному можно ограничить доступ к таблицам — запретить писать и читать из таблиц, а только из разрешенных вьюх, при том сами вьюхи будут отдавать данные только авторизированному пользователю. Соответственно разграничить и доступ к процедурам.
Что можно почитать, кроме собственно документации к БД?
"
) Ну и собственно выполняется. Если она каждый раз проецируется в память, то думаю, что резоннее разбить на несколько..
Оказалось надо запустить какую-то консольную утилиту MySQL для исправления безобразия. Не говоря уже о встреченных багах. Очевидно, что мало кто пользуется процедурами в реальности и именно поэтому "SQL Injection" так распространен.
)