Каковы наиболее распространенные антипаттерны SQL?
Все мы, работающие с реляционными базами данных, узнали (или изучаем), что SQL отличается. Достижение желаемых результатов и их эффективное выполнение - это утомительный процесс, частично характеризующийся изучением незнакомых парадигм и обнаружением того, что некоторые из наших наиболее знакомых шаблонов программирования здесь не работают. Какие общие антипаттерны вы видели (или сами придерживались)?
@casperOne, разве нет пункта об "исторической значимости", который сделал бы этот вопрос приемлемым?
@DavidB Существует историческая блокировка, но вопросы не добавляются автоматически (на самом деле историческая блокировка используется для обработки старых вопросов, которые имеют значение, но не подходят для сайта, если бы все они были внесены в дедушку, это было бы быть бесполезным, потому что посты с дерьмом попадут автоматически). Тем не менее, вы можете сделать мета-сообщение с запросом обратной связи по исторической блокировке, и в зависимости от результата этот вопрос получит блокировку или останется закрытым. Обязательно пометьте его тегами specific-question, discussion и historical-lock.
Мне грустно, что один из самых полезных вопросов на всем сайте закрыт как неконструктивный.
@HLGEM Полностью согласен. Этот вопрос - прекрасный пример того, что не так со StackExchange.
Тема абсолютно важная и актуальная. Но вопрос слишком открытый, поэтому каждый ответ описывает личный багбир против паттернов отдельного инженера.
Просто чтобы поддержать Пункт 1 ответа Дэвида, проверьте этот пример ситуации, когда использование select * может привести к неожиданным результатам
Ответы 39
Не нужно копать для этого: не использовать подготовленные операторы.
Ага. Я внимательно следил за тем же контекстом, по моему опыту, с «ошибками не улавливания».
@stesch: это ничто по сравнению с использованием представлений и переменной датой отчетности. Представления - это антипаттерн, если у вас есть переменная дата отчета (я предполагаю, что у большинства приложений есть). Добавил бы это в отдельный ответ, но, к сожалению, он закрыт.
используйте SP в качестве префикса имени процедуры хранилища, потому что он сначала будет искать в расположении системных процедур, а не в пользовательских.
Также может быть расширен до использования любого другого общего префикса для всех хранимых процедур, что затрудняет выбор в отсортированном списке.
+1 за комментарий doofledorfer !! Я видел это много, я считаю это идиотским и действительно затрудняет поиск конкретного SP очень !!! Также расширен до "vw_" для представлений, "tbl_" для таблиц и т.п., как я их ненавижу!
Префиксы могут быть полезны, если вы создаете сценарии для объектов в файлах (например, для управления версиями, развертывания или миграции).
С какой стати было бы полезно ставить перед каждой хранимой процедурой префикс sp или же usp? Это просто затрудняет поиск в списке того, что вам нужно.
Вот моя тройка лучших.
Номер 1. Невозможность указать список полей. (Обновлено: во избежание путаницы: это правило производственного кода. Оно не применяется к одноразовым сценариям анализа - если я не являюсь автором.)
SELECT *
Insert Into blah SELECT *
должно быть
SELECT fieldlist
Insert Into blah (fieldlist) SELECT fieldlist
Номер 2. Использование курсора и цикла while, когда подойдет цикл while с переменной цикла.
DECLARE @LoopVar int
SET @LoopVar = (SELECT MIN(TheKey) FROM TheTable)
WHILE @LoopVar is not null
BEGIN
-- Do Stuff with current value of @LoopVar
...
--Ok, done, now get the next value
SET @LoopVar = (SELECT MIN(TheKey) FROM TheTable
WHERE @LoopVar < TheKey)
END
Номер 3. DateLogic через строковые типы.
--Trim the time
Convert(Convert(theDate, varchar(10), 121), datetime)
Должно быть
--Trim the time
DateAdd(dd, DateDiff(dd, 0, theDate), 0)
Я видел недавний всплеск «Один запрос лучше, чем два, правда?»
SELECT *
FROM blah
WHERE (blah.Name = @name OR @name is null)
AND (blah.Purpose = @Purpose OR @Purpose is null)
Для этого запроса требуются два или три разных плана выполнения в зависимости от значений параметров. Для этого текста SQL создается и остается в кеше только один план выполнения. Этот план будет использоваться независимо от значения параметров. Это приводит к периодическому снижению производительности. Гораздо лучше написать два запроса (по одному запросу на предполагаемый план выполнения).
хммм, я дам вам +1 только за пункты 2 и 3, но разработчики переигрывают правило 1. Иногда это имеет место.
В чем причина №1?
Когда вы используете select *, вы получаете все, что есть в таблице. Эти столбцы могут изменять имена и порядок. Клиентский код часто зависит от имен и порядка. Каждые 6 месяцев меня спрашивают, как сохранить порядок столбцов при изменении таблицы. Если бы правило соблюдалось, это не имело бы значения.
Иногда я использовал №2, в других случаях я шел по маршруту курсора (хотя затем я сначала сохраняю результаты запроса в таблице var, открываю на ней курсор). Мне всегда было интересно, проводил ли кто-нибудь тест на производительность обоих.
@Joe, курсор может допускать параллелизм, в то время как "цикл по ключам" или переменная таблицы - нет. В этом сценарии курсор выигрывает по производительности. Вот еще одна статья о курсорах в stackoverflow: stackoverflow.com/questions/172526/…
еще один побочный эффект использования подхода «select *», описанный в пункте 1: stackoverflow.com/questions/321468/…
@Cervo, конечно, (четко определенные) курсоры превосходят циклы. Многие разработчики sql плохо определяют курсоры. Вот мои мысли по этому поводу: stackoverflow.com/a/173216/8155
... но, конечно, курсоры почти всегда должны быть последним средством, если не удается понять, как выполнять работу с SQL на основе наборов. Однажды я потратил около 45 минут на тщательный анализ ужасающего гигантского курсора PL / SQL в хранимой процедуре (нарисовал диаграммы испорченной штуки), который заполнил большую временную таблицу, а затем выбрал содержимое временной таблицы обратно вызывающей стороне, чтобы отобразить отчет. На работу с мощным оборудованием потребовалось 8,5 минут. Изобразив все это на диаграмме, я смог заменить его одним запросом, который вернул те же результаты менее чем за 2 секунды. Курсоры, чувак ...
Использование @variables - это анти-шаблон, поскольку стандартный SQL не знает переменных.
Использование бессмысленных псевдонимов таблиц:
from employee t1,
department t2,
job t3,
...
Делает чтение большого оператора SQL намного сложнее, чем должно быть
псевдонимы? черт, я видел настоящие имена столбцов, такие как это
краткие псевдонимы - НУЖНО. Если вам нужно значимое имя, вообще не используйте псевдоним.
@Joel, вам нужен псевдоним для ваших подвыборок.
Он не сказал «кратко», он сказал «бессмысленно». В моей книге не было бы ничего плохого в использовании e, d и j в качестве псевдонимов в примере запроса.
Безусловно, Роберт - e, d и j меня устроит.
Я бы использовал emp для сотрудников, dep для отдела и job для работы (или, может быть, jb) :)
Псевдонимы полей / таблиц (для чего-то разумного) очень часто используются в больших и хорошо организованных хранилищах данных. он позволяет скопировать и вставить запрос, а затем просто изменить имя одной таблицы; полагаясь на псевдоним для всех ссылок.
@AndreiRinea, ваши правила создания псевдонимов кажутся немного непредсказуемыми (и поэтому их сложнее придумать / запомнить) по сравнению с правилами @ RobertRossney ;-).
@binki: Может быть, но когда у меня длинный SELECT с подзапросами и / или множеством объединений и я смотрю на T3.Id или T2.Name, какая таблица T2 без прокрутки вверх?
@AndreiRinea Я против t1, t2, t3. Я просто сказал, что j лучше, чем «job (или, может быть, jb)».
1) Я не знаю, что это «официальный» анти-шаблон, но мне не нравится и я стараюсь избегать строковых литералов как магических значений в столбце базы данных.
Пример из таблицы MediaWiki 'image':
img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO",
"MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
img_major_mime ENUM("unknown", "application", "audio", "image", "text",
"video", "message", "model", "multipart") NOT NULL default "unknown",
(Я просто замечаю другой корпус, другое, чего следует избегать)
Я разрабатываю такие случаи, как поиск int в таблицах ImageMediaType и ImageMajorMime с первичными ключами int.
2) преобразование даты / строки, которое зависит от определенных настроек NLS
CONVERT(NVARCHAR, GETDATE())
без идентификатора формата
И никаких синтаксических отступов. Argghh.
Почему это плохо? конечно, если вы пытаетесь выразить набор значений, это работает так же хорошо, как справочная таблица, и лучше подходит для кода, который ее вызывает. Я бы предпочел иметь перечисление в моем коде приложения, которое сопоставляется с ограничением перечисления в моей БД, чем перечисление в моем коде приложения, которое сопоставляется с конкретными строками таблицы поиска. Он просто кажется чище.
@JackRyan: Это плохо, потому что, когда вы позже измените список перечислений, вам нужно не забыть изменить его сейчас в двух местах. Это нарушает СУХОЙ. База данных должна быть единственным источником истины.
Чрезмерное использование временных таблиц и курсоров.
Хорошее доказательство того, что «все, что я знаю, - это процедурные языки».
Чрезмерное использование чего-либо по определению нежелательно. Был бы полезен конкретный пример того, где использование временных таблиц / курсоров не требуется.
В основном я вижу, что временные таблицы недостаточно используются. с SQL Server вы часто получаете прирост производительности, выполняя работу с кучей временных таблиц вместо одного монолитного запроса.
Удобочитаемые поля пароля, egad. Само собой разумеется.
Использование НРАВИТСЯ против индексированного столбцы, и я почти соблазняюсь просто скажи НРАВИТСЯ в целом.
Переработка значений PK, сгенерированных SQL.
Сюрприз, то стол бога пока никто не упомянул. Ничего не говорит "органический", например, 100 битных столбцов флаги, большие строки и целые числа.
Затем есть шаблон "Я скучаю по .ini файлы ": сохранение CSV, конвейер строки с разделителями или другой синтаксический анализ обязательные данные в больших текстовых полях.
А для сервера MS SQL использование курсоры вообще. Есть лучше способ выполнить любую заданную задачу курсора.
Отредактировано, потому что их так много!
Я не понимаю аргумента LIKE, я никогда не использовал LIKE, потому что мне было скучно, а только потому, что им нужен поиск по шаблонам. В моей текущей работе НРАВИТСЯ каждый столбец в поиске. Бьюсь об заклад, он будет оптимизирован, когда он будет слишком медленным, но я не получаю ненависти НРАВИТСЯ.
неправильно насчет курсоров, я бы не решился сказать, что делать что-то конкретное на 100% правильно или на 100% неправильно
По крайней мере, в SQL Server вы можете анализировать строки с разделителями быстрее, чем вы можете получать данные из столбца / объекта XML. Так что у них есть свое место, если вы заботитесь о производительности! И я не могу понять ваше отвращение к LIKE, я очень стараюсь и не могу придумать, почему держитесь подальше от LIKE.
Иногда нужно просто использовать курсор! Если вы не хотите выполнять обработку данных из базы данных, например, для конкретной цели, самодельной крошечной утилитой ... Подумайте о способе отправить письмо группе людей, например, без курсоров или курсора. внешнее приложение
Или создание набора результатов с промежуточными итогами.
До сих пор каждый пример защиты курсора, который я видел, использует неправильный инструмент для работы. Но если все, что вы знаете, это SQL, вы либо используете его ненадлежащим образом, либо учитесь писать другие виды программного обеспечения.
Я бы также защищал "анализируемые данные" внутри sql (в некоторых случаях). Иногда plob (разбираемый большой объект) - это тип, с которым вы не хотите иметь дело в sql (каждый имеет разные поля и т. д.) ... cf: writeonly.wordpress.com/2008/12/05/…
LIKE может использовать индекс (по крайней мере, в Oracle).
@tuinstoel: Как LIKE '% blah%' может использовать индекс? Индексирование зависит от порядка, и в этом примере выполняется поиск случайной средней позиции строки. (Порядок индексации по 1-му символу 1-й, поэтому, глядя на средние 4 символа, получается практически случайный порядок ...)
На большинстве серверов баз данных (по крайней мере, тех, которые я использовал), LIKE может использовать индексы .. пока это поиск по префиксу (LIKE 'xxx%'), то есть до тех пор, пока символы подстановки не на первом месте в строке поиска. Я думаю, вы здесь немного говорите о противоречиях.
Как будто тебе не нравится LIKE '%LIKE'.
Мне нужно найти один случай, когда курсор был хорошей идеей, за исключением некоторых редких одноразовых запросов.
@NicVerAZ - реальная потребность в курсорах находится в администраторе базы данных, где вам может потребоваться запускать системные процессы для нескольких таблиц. Это допустимое использование. Но обычно администраторы баз данных знают лучше, чем делать это с большим набором данных.
Используя хорошую СУБД, все LIKE могут использовать индекс. Даже запросы SIMILAR TO и regexp могут использовать индексы, если они созданы соответствующим образом.
Идентичные подзапросы в запросе.
К сожалению, иногда вы просто не можете этого избежать - в SQL 2000 не было ключевого слова "WITH", а использование UDF для инкапсуляции общих подзапросов иногда приводит к снижению производительности, вините в этом MS ...
Что ж, надеюсь, они дойдут до того, чтобы добавить его в ближайшее время.
В SQL 2000 вы можете использовать табличные переменные.
@recursive: у вас не может быть индексов для табличной переменной, что часто делает ее медленнее, чем подзапрос. Однако вы можете использовать временную таблицу с настраиваемыми индексами.
Круто, я работал с SQL годами и даже не знал, что существуют общие табличные выражения (хотя они мне бы понадобились). Теперь я знаю! Спасибо!
Меня больше всего беспокоят таблицы доступа с 450 столбцами, которые были собраны 8-летним сыном лучшего друга-грумера управляющего директора, и хитрая справочная таблица, которая существует только потому, что кто-то не знает, как правильно нормализовать структуру данных.
Обычно эта таблица поиска выглядит так:
ID INT, Name NVARCHAR(132), IntValue1 INT, IntValue2 INT, CharValue1 NVARCHAR(255), CharValue2 NVARCHAR(255), Date1 DATETIME, Date2 DATETIME
Я потерял счет количеству клиентов, которых я видел, у которых есть системы, которые полагаются на такие мерзости, как эта.
Что еще хуже, я читал, что в новейшей версии Access, которая фактически поддерживается автоматически, я боюсь, что поощрять будет больше от этого фетишизма столбца Value1, Value2, Value3 ...
Постойте - так 8-летний сын - сын грумера?
Я постоянно разочарован тенденцией большинства программистов смешивать свою UI-логику на уровне доступа к данным:
SELECT
FirstName + ' ' + LastName as "Full Name",
case UserRole
when 2 then "Admin"
when 1 then "Moderator"
else "User"
end as "User's Role",
case SignedIn
when 0 then "Logged in"
else "Logged out"
end as "User signed in?",
Convert(varchar(100), LastSignOn, 101) as "Last Sign On",
DateDiff('d', LastSignOn, getDate()) as "Days since last sign on",
AddrLine1 + ' ' + AddrLine2 + ' ' + AddrLine3 + ' ' +
City + ', ' + State + ' ' + Zip as "Address",
'XXX-XX-' + Substring(
Convert(varchar(9), SSN), 6, 4) as "Social Security #"
FROM Users
Обычно программисты делают это, потому что они намереваются привязать свой набор данных непосредственно к сетке, и удобнее иметь формат SQL Server на стороне сервера, чем формат на клиенте.
Запросы, подобные показанному выше, чрезвычайно хрупкие, потому что они тесно связывают уровень данных со слоем пользовательского интерфейса. Вдобавок ко всему, такой стиль программирования полностью предотвращает повторное использование хранимых процедур.
Хороший шаблон постер-потомок для максимальной связи между максимально возможным количеством уровней / уровней абстракции.
Это может быть не очень хорошо для разъединения, хотя по соображениям производительности я часто делал подобные вещи, итеративные изменения, выполняемые SQL Server, выполняются быстрее, чем выполняемые кодом на среднем уровне. Я не понимаю, что вам нужно повторно использовать - ничто не мешает вам запустить SP и переименовать cols, если хотите.
Мне больше всего нравится, когда люди встраивают HTML и javascript, например. ВЫБЕРИТЕ '<a href=... onclick = "">' + name '</a>'
С подобными запросами вы можете редактировать сетку на веб-сайте с помощью простого оператора alter. Или измените содержимое экспорта, или переформатируйте дату в отчете. Это делает клиентов счастливыми и экономит время. Так что спасибо, но нет, я буду придерживаться подобных запросов.
@ Мэтт Рогиш - Господи, кто-нибудь на самом деле это делает?
Да, кто-то действительно так делает. Я видел это обычно для приложений MSAccess, которые используют MSSQLServer в качестве бэкэнда, потому что вам нужно привязать запрос непосредственно к непрерывным формам.
select some_column, ...
from some_table
group by some_column
и предполагая, что результат будет отсортирован по some_column. Я видел это немного с Sybase, где предположение остается в силе (пока).
проголосовать за ВСЕГДА, предполагающий порядок сортировки, просто потому, что именно так он отображался в инструменте запросов, который однажды
Я даже не раз видел, как это сообщалось как ошибка.
в MySQL задокументирована сортировка. <dev.mysql.com/doc/refman/5.0/en/select.html>. Так что вините MySQL (снова).
В Oracle несортированные результаты (почти) всегда соответствовали группировке - до версии 10G. Много доработок для разработчиков, которые раньше не учитывали ORDER BY!
Я даже был на учебном курсе, где это было заявлено как факт для SQL Server. Мне пришлось очень громко протестовать. Чтобы просто сохранить для ввода 20 символов, вы полагаетесь на непонятное или недокументированное поведение.
Я бы не назвал это антипаттерном.
Использование SQL в качестве прославленного пакета ISAM (индексированный метод последовательного доступа). В частности, вложение курсоров вместо объединения операторов SQL в один, хотя и более крупный, оператор. Это также считается «злоупотреблением оптимизатором», поскольку на самом деле оптимизатор мало что может сделать. Это можно комбинировать с неподготовленными операторами для максимальной неэффективности:
DECLARE c1 CURSOR FOR SELECT Col1, Col2, Col3 FROM Table1
FOREACH c1 INTO a.col1, a.col2, a.col3
DECLARE c2 CURSOR FOR
SELECT Item1, Item2, Item3
FROM Table2
WHERE Table2.Item1 = a.col2
FOREACH c2 INTO b.item1, b.item2, b.item3
...process data from records a and b...
END FOREACH
END FOREACH
Правильное решение (почти всегда) - объединить два оператора SELECT в один:
DECLARE c1 CURSOR FOR
SELECT Col1, Col2, Col3, Item1, Item2, Item3
FROM Table1, Table2
WHERE Table2.Item1 = Table1.Col2
-- ORDER BY Table1.Col1, Table2.Item1
FOREACH c1 INTO a.col1, a.col2, a.col3, b.item1, b.item2, b.item3
...process data from records a and b...
END FOREACH
Единственным преимуществом версии с двойным циклом является то, что вы можете легко обнаружить разрывы между значениями в Table1, потому что внутренний цикл заканчивается. Это может быть фактором в отчетах о нарушении контроля.
Кроме того, сортировка в приложении обычно запрещена.
По моему опыту, этот стиль, хотя и не такой синтаксис, особенно широко используется в PHP.
На самом деле синтаксис - это IBM Informix-4GL, но он достаточно ясен, чтобы не требовать особых объяснений (я думаю). И этот стиль широко распространен во многих программах SQL - независимо от языка программирования.
За исключением того факта, что вы используете хорошо известный антипаттерн (неявные соединения), чтобы проиллюстрировать свой антипаттерн, это как бы опровергает суть дела.
И, конечно же, использование курсоров - это антипаттерн SQl. Практически все курсоры можно переписать как операции, основанные на наборах. Те немногие, которые не могут, - это те, кого должны писать только администраторы баз данных с многолетним опытом и понимающие, как работает внутренняя структура базы данных. Ни одному разработчику приложений никогда не потребуется писать курсор SQL.
Больше всего мне не нравятся
Использование пробелов при создании таблиц, sprocs и т. д. Меня устраивает CamelCase или under_scores, единственное или множественное число, а также ВЕРХНИЙ или строчный регистр, но мне приходится обращаться к таблице или столбцу [с пробелами], особенно если [он странно разнесен] (да, Я столкнулся с этим) меня очень раздражает.
Денормализованные данные. Таблица не должна быть полностью нормализована, но когда я сталкиваюсь с таблицей сотрудников, в которой есть информация об их текущих оценках или об их первичном балле, это говорит мне, что мне, вероятно, в какой-то момент понадобится создать отдельную таблицу и затем попытайтесь синхронизировать их. Сначала я нормализую данные, а затем, если я увижу место, где помогает денормализация, я рассмотрю это.
Чрезмерное использование представлений или курсоров. У представлений есть цель, но когда каждая таблица заключена в представление, этого становится слишком много. Мне несколько раз приходилось использовать курсоры, но обычно для этого можно использовать другие механизмы.
Доступ. Может ли программа быть антипаттерном? У меня на работе есть SQL Server, но некоторые люди пользуются доступом из-за его доступности, «простоты использования» и «удобства» для нетехнических пользователей. Здесь слишком много, чтобы вдаваться в подробности, но если вы были в подобной среде, вы знаете.
# 4 - есть еще один поток только для <a href='stackoverflow.com/questions/327199/…> :).
Доступ - это НЕ СУБД. Это среда RAD с очень простым менеджером баз данных. SQL Server, Oracle и др. заменит ли его никогда, если вы не добавите язык, подобный VB, и средство, подобное Crystal Reports.
@JoePineda Не уверен, что вы имеете в виду, цитируя VB и Crystal Reports, но я бы сказал, что 12 лет спустя C# и SSRS определенно превзошли их на этом этапе. Хотя, к сожалению, Access все еще используется в дикой природе людьми, не обладающими техническими знаниями, на данный момент он, безусловно, мертв. :)
Синтаксис
FROM TableA, TableB WHEREдля JOINS, а неFROM TableA INNER JOIN TableB ONДелать предположения, что запрос будет возвращен в отсортированном определенным образом без включения предложения ORDER BY, просто потому, что именно так он проявился во время тестирования в инструменте запросов.
Мои администраторы баз данных Oracle всегда жалуются, что я использую «соединения ANSI», то есть то, что вы представляете как правильный. Но я продолжаю это делать и подозреваю, что в глубине души они знают это лучше.
Я подозреваю, что Oracle хочет, чтобы стандартный SQL исчез. :-) Кроме того, вы не можете смешивать неявные и явные JOINS (также известные как ANSI JOIN) в MySQL 5 - это не работает. Это еще один аргумент в пользу явных JION.
Я бы сказал, что даже A INNER JOIN B ON - это антипаттерн. Я предпочитаю ИСПОЛЬЗОВАНИЕ ВНУТРЕННЕГО СОЕДИНЕНИЯ Б.
Oracle теперь поддерживает синтаксис ANSI, но раньше у них был этот действительно странный синтаксис для внешних соединений, и слишком много людей все еще его используют.
ну ... Oracle по-прежнему не позволит вам использовать соединения ANSI для Быстро обновляемые материализованные представления при фиксации
Использование слов INNER и OUTER является анти-шаблоном, поскольку они не меняют ни на йоту запроса и представляют собой просто шум.
@HolgerJakobs Согласитесь с "OUTER" (просто используйте "LEFT" или "RIGHT"), но я работал с БД, где "JOIN" само по себе было неоднозначным, поэтому теперь я всегда указываю "INNER" для ясности и никогда не просто JOIN сам по себе.
@JoelCoehoorn Интересно. Не могли бы вы сказать, какая это была СУБД? Конечно, JOIN требует NATURAL, ON или USING, но я никогда не сталкивался с СУБД, требующей INNER.
@HolgerJakobs Это уже вернулась. Некоторая система с предварительным графическим интерфейсом, работающая на Netware. И, как я вспоминаю, проблема заключалась не в том, что для этого требовалось «INNER», а в том, что JOIN само по себе по умолчанию было «LEFT OUTER». Поэтому мы всегда указали INNER как для этого, так и для (очень старого) сервера Sql Server 2000, который мы использовали для другой системы.
var query = "select COUNT(*) from Users where UserName = '"
+ tbUser.Text
+ "' and Password = '"
+ tbPassword.Text +"'";
- Слепое доверие к вводу пользователя
- Без использования параметризованные запросы
- Пароли в открытом виде
Со всем этим можно с пользой справиться, используя какой-либо (любой) уровень абстракции базы данных.
@doofledorfer: Согласитесь, средний уровень определенно был бы лучше в таком случае, плюс обеспечение кеширования результатов в качестве приятного побочного эффекта.
Классный пример. Если разработчик задумывается, как заменить это хорошим решением, он на полпути к тому, чтобы стать достойным разработчиком SQL.
Мне нужно поместить сюда свой текущий фаворит, просто чтобы список был полным. Мой любимый антипаттерн - не проверять ваши запросы.
Это применимо, когда:
- Ваш запрос включает более одной таблицы.
- Вы думаете, что у вас есть оптимальный план запроса, но не пытаетесь проверить свои предположения.
- Вы принимаете первый работающий запрос, не имея ни малейшего представления о том, близок ли он к оптимизированному.
И любые тесты, проведенные с нетипичными или недостаточными данными, не в счет. Если это хранимая процедура, поместите тестовый оператор в комментарий и сохраните его вместе с результатами. В противном случае поместите его в комментарий в коде с результатами.
Очень полезный метод для минимального теста T-SQL: в файле .SQL, где вы определяете свой SP, UDF и т. д., Сразу после него создайте блочный тест, например IF 1 = 2 BEGIN (примеры примеров для вашего кода с ожидаемыми результатами как комментарии) КОНЕЦ
SQL Server анализирует код в тестовом блоке, даже если он никогда не выполнялся. Поэтому, когда ваш объект изменяется и получает больше параметров или другого типа и т. д., Или когда объект, от которого он зависит, изменяется, вы получите сообщение об ошибке, просто запросив план выполнения!
Не всегда можно проверить на реальных данных. Часто dev-серверу / «тестовому» серверу недоплачивают и он получает лишь часть от реального сервера. Обычно тесты на реальном сервере не одобряются. В некоторых местах лучше и есть тестовый или промежуточный сервер с живыми данными.
Два, которые я считаю наиболее важными с точки зрения производительности, могут потребовать значительных затрат:
Использование курсоров вместо набора на основе выражение. Я предполагаю, что это часто случается, когда программист мыслит процедурно.
Использование коррелированных подзапросов, когда присоединиться к производной таблице может сделать работа.
Я согласен, если вы имеете в виду то, что я думаю; хотя коррелированный подзапрос является типом производной таблицы IIRC.
Производная таблица - это операция набора, тогда как коррелированный подзапрос выполняется для каждой строки внешнего запроса, что делает его менее эффективным (в 9 случаях из 10).
Пару лет назад я с удивлением обнаружил, что SQL S. каким-то образом оптимизирован для обработки коррелированных запросов: для простых запросов вы получаете тот же план выполнения, что и для логически эквивалентного запроса с использованием JOIN! Кроме того, коррелированные запросы, ставящие Oracle на колени, на SQL S выполняются очень медленно!
Вот почему я всегда проверяю оба варианта. И я <i> делаю </> обычно пробую оба варианта. На практике, в любом случае, для SQL Server я обычно обнаружил, что коррелированный sq не медленнее.
Я обманул и погуглил "коррелированную производную таблицу подзапроса". Здесь я обнаружил два источника, в которых говорится, что csq - это один из типов производной таблицы. (Он также упомянул распространенное заблуждение среди пользователей SQL Server. Я даже не знал достаточно, чтобы запутаться; так что Митч в курсе.)
Но на самом деле csq - это таблица, которая также является производной - просто несколько раз для каждой строки. (Если, конечно, не оптимизировать, что действительно произойдет.)
Я очень виноват во втором, но я над этим работаю.
ПОЖАЛУЙСТА, поймите, что коррелированный подзапрос и соединение ОДИНАКОВЫ (в большинстве случаев). Это даже не разные вещи, которые оптимизированы друг для друга, а просто разные текстовые представления одной и той же операции.
Связано с ответом Митча: в нашем приложении была одна операция, которая использовала CSQ в другом CSQ (динамический характер этой части приложения требует настройки. Это действительно так). Когда один клиент дошел до набора данных определенного размера, запрос взорвался до 4,9 МИЛЛИАРДА прочтений. Развернув внутренний запрос, мы сократили время выполнения этого конкретного бита на 99%.
Измененное представление - представление, которое изменяется слишком часто и без уведомления или причины. Изменение будет либо замечено в самый неподходящий момент, либо, что еще хуже, окажется неправильным и никогда не будет замечено. Возможно, ваше приложение сломается, потому что кто-то придумал для этого столбца лучшее имя. Как правило, представления должны расширять полезность базовых таблиц, сохраняя при этом договор с потребителями. Устраняйте проблемы, но не добавляйте функции или хуже меняйте поведение, для этого создайте новое представление. Для смягчения последствий не делитесь представлениями с другими проектами и используйте CTE, когда платформы позволяют. Если в вашем магазине есть администратор баз данных, вы, вероятно, не сможете изменить представления, но в этом случае все ваши представления будут устаревшими или бесполезными.
Параметр! Paramed - может ли запрос иметь более одной цели? Вероятно, но следующий человек, который прочитает это, не узнает до глубокой медитации. Даже если они вам сейчас не нужны, скорее всего, они вам понадобятся, даже если это «просто» для отладки. Добавление параметров сокращает время обслуживания и сохраняет СУХИЕ вещи. Если у вас есть предложение where, у вас должны быть параметры.
Случай для НЕ СЛУЧАЙ -
SELECT CASE @problem WHEN 'Need to replace column A with this medium to large collection of strings hanging out in my code.' THEN 'Create a table for lookup and add to your from clause.' WHEN 'Scrubbing values in the result set based on some business rules.' THEN 'Fix the data in the database' WHEN 'Formating dates or numbers.' THEN 'Apply formating in the presentation layer.' WHEN 'Createing a cross tab' THEN 'Good, but in reporting you should probably be using cross tab, matrix or pivot templates' ELSE 'You probably found another case for no CASE but now I have to edit my code instead of enriching the data...' END
Мне очень понравился третий. Уже локально пользуюсь ...
Спасибо за реквизит. :)
Противоположная точка зрения: чрезмерная одержимость нормализацией.
Большинство систем SQL / RBDB предоставляют множество функций (транзакции, репликация), которые весьма полезны даже для ненормализованных данных. Дисковое пространство дешевое, и иногда может быть проще (более простой код, более быстрое время разработки) манипулировать / фильтровать / искать полученные данные, чем писать схему 1NF и справляться со всеми возникающими в ней проблемами (сложные соединения, неприятные подвыборы). , так далее).
Я обнаружил, что чрезмерно нормализованные системы часто являются преждевременной оптимизацией, особенно на ранних стадиях разработки.
(еще мысли по этому поводу ... http://writeonly.wordpress.com/2008/12/05/simple-object-db-using-json-and-python-sqlite/)
Я думаю, что ненормализация часто является преждевременной оптимизацией.
Иногда это так, иногда нет. К счастью, часто это легко протестировать, и разные варианты работают с разными потребностями в БД.
Нормализация предназначена не только для экономии места на диске. Это также необходимо для создания авторитетного источника данных. Если данные хранятся только в одном месте, то согласованность - это не побочный продукт тщательного кодирования, а побочный продукт дизайна.
Хранение составных данных в формате JSON - это одно: их становится все больше и больше, и это сознательный компромисс. Использование значений, разделенных запятыми (или любых других), в попытке сохранить одно соединение - это глупо и глупо.
Решения noSQL демонстрируют повышение производительности за счет дублирования данных за счет исключения поиска по нескольким таблицам. Ставит все с ног на голову. В некоторых примерах данные собираются в нескольких местах, чтобы обеспечить максимально быстрое время отклика одного процесса. Конечно, в игру вступают вопросы об авторитетных источниках.
Возможно, это не антипаттерн, но меня раздражает, когда администраторы баз данных некоторых БД (хорошо, я говорю об Oracle здесь) пишут код SQL Server, используя стиль Oracle и соглашения о коде, и жалуются, когда он работает так плохо. Хватит курсоров оракулы! SQL предназначен для установки на основе.
Я думаю, что это больше связано с вашим администратором баз данных, чем с Oracle. Oracle советует людям думать и действовать на основе набора, а не построчно процедурного мышления с помощью курсоров.
Вы, наверное, правы, туинстоэль. Но в моей компании работает множество администраторов баз данных, и все они, кажется, любят курсоры.
Значит, они не очень хорошие администраторы баз данных ... Вы ведь не работаете в одном месте со мной, не так ли? ;)
Помещая данные во временные таблицы, особенно люди, которые переходят с SQL Server на Oracle, имеют привычку чрезмерно использовать временные таблицы. Просто используйте вложенные операторы выбора.
Объединение избыточных таблиц в такой запрос:
select emp.empno, dept.deptno
from emp
join dept on dept.deptno = emp.deptno;
Это действительно что-то делает: возвращает строки emp, только если соответствующая строка dept существует.
@Ariel: Дело в том, что это полусоединение, но этот факт не сразу очевиден. Вероятно, лучше писать с использованием emp.deptno (а не dept.deptno) в пункте SELECT, даже лучше использовать SELECT DISTINCT (а не SELECT ALL). Но, возможно, лучше использовать что-то, что более очевидно является полусоединением, например. SELECT empno, deptn FROM emp WHERE EXISTS (SELECT * FROM dept WHERE dept.deptno = emp.deptno);
Я просто собрал это вместе, основываясь на некоторых ответах SQL здесь, на SO.
Было бы серьезным антипаттерном думать, что триггеры относятся к базам данных, как обработчики событий относятся к ООП. Существует мнение, что в триггеры можно поместить любую старую логику, которая запускается, когда транзакция (событие) происходит в таблице.
Не правда. Одно из больших различий заключается в том, что триггеры синхронны - с удвоенной силой, потому что они синхронны для операции набора, а не операции строки. На стороне ООП с точностью до наоборот - события являются эффективным способом реализации асинхронных транзакций.
используя @@ IDENTITY вместо SCOPE_IDENTITY ()
Цитируется из этот ответ:
- @@ЛИЧНОСТЬ возвращает последнее значение идентификатора, созданное для любой таблицы в текущем сеансе во всех областях. Здесь нужно быть осторожным, так как это слишком сложно. Вы можете получить значение из триггера вместо текущего оператора.
- SCOPE_IDENTITY возвращает последнее значение идентификатора, созданное для любой таблицы в текущем сеансе и текущей области. Вообще то, что вы хотите использовать.
- IDENT_CURRENT возвращает последнее значение идентификатора, созданное для конкретной таблицы в любом сеансе и любой области. Это позволяет указать, из какой таблицы вы хотите получить значение, на случай, если два указанных выше не совсем то, что вам нужно (очень редко). Вы можете использовать это, если хотите получить текущее значение IDENTITY для таблицы, в которую вы не вставляли запись.
+1 очень верно, может вызвать ошибки, от которых будет сложно избавиться
Использование @@ в SQL уже является анти-шаблоном, потому что ANSI / ISO SQL не знает переменных.
SELECT FirstName + ' ' + LastName as "Full Name", case UserRole when 2 then "Admin" when 1 then "Moderator" else "User" end as "User's Role", case SignedIn when 0 then "Logged in" else "Logged out" end as "User signed in?", Convert(varchar(100), LastSignOn, 101) as "Last Sign On", DateDiff('d', LastSignOn, getDate()) as "Days since last sign on", AddrLine1 + ' ' + AddrLine2 + ' ' + AddrLine3 + ' ' + City + ', ' + State + ' ' + Zip as "Address", 'XXX-XX-' + Substring(Convert(varchar(9), SSN), 6, 4) as "Social Security #" FROM Users
Или втиснуть все в одну строчку.
Использовал запрос из предыдущего комментария только потому, что это был первый доступный мне SQL-оператор.
Злоупотребление временной таблицей.
Конкретно такие вещи:
SELECT personid, firstname, lastname, age
INTO #tmpPeople
FROM People
WHERE lastname like 's%'
DELETE FROM #tmpPeople
WHERE firstname = 'John'
DELETE FROM #tmpPeople
WHERE firstname = 'Jon'
DELETE FROM #tmpPeople
WHERE age > 35
UPDATE People
SET firstname = 'Fred'
WHERE personid IN (SELECT personid from #tmpPeople)
Не создавайте временную таблицу из запроса, только чтобы удалить ненужные строки.
И да, я видел страницы кода в этой форме в производственных БД.
+1, согласен. Хотя я обнаружил по крайней мере один или два случая, когда этот метод улучшил производительность - задействованные запросы были сложными, если не сказать больше.
Верно - им есть место, но не в каждом запросе :)
Иногда это приходится делать, если условия очень сложные. Правда, ею можно злоупотреблять до крайности. Но во многих случаях простое удаление намного проще, чем логика получения дела в начальном запросе. Также иногда, если предложение не является допустимым, начальный запрос будет замедляться. Но просто делать это на меньшей временной таблице более эффективно. А иногда вы продолжаете добавлять кейсы, которые деловые люди продолжают добавлять постфактум.
Для хранения значений времени следует использовать только часовой пояс UTC. Местное время использовать не следует.
Я до сих пор не нашел хорошего простого решения для преобразования из UTC в местное время для дат в прошлом, когда нужно учитывать переход на летнее время, с различными датами изменения по годам и странам, а также со всеми исключениями внутри стран. Так что UTC не избавляет вас от сложности конвертации. Однако важно иметь способ узнать часовой пояс каждого сохраненного datetime.
@CsongorHalmai Во многих местах используется летнее время, поэтому значения времени в пределах часа после смены времени могут быть неоднозначными.
Это, безусловно, верно для настоящего и прошлого, но для будущего, особенно для довольно далекого будущего, часто необходимы точные часовые пояса. Если у вас есть 30-летний опцион, срок действия которого истекает в 2049-09-27T17: 00: 00 по нью-йоркскому времени, то вы не можете слепо предполагать, что это будет 21: 00: 00Z. Конгресс США вполне может изменить правила перехода на летнее время. Вы должны разделять местное время и истинный часовой пояс (Америка / Нью-Йорк).
Имея 1 стол
code_1
value_1
code_2
value_2
...
code_10
value_10
Вместо трех таблиц
код, значение и code_value
Вы никогда не знаете, когда вам может понадобиться более 10 пар кода, значение.
Вы не тратите впустую дисковое пространство, если вам нужна всего одна пара.
Я не понимаю, зачем вам стол code_value? Разве это не просто дублирование данных?
Изучать SQL в первые шесть месяцев своей карьеры и никогда больше ничего не изучать в течение следующих 10 лет. В частности, отсутствие обучения или эффективного использования оконных / аналитических функций SQL. В частности, использование over () и partition by.
Window functions, like aggregate functions, perform an aggregation on a defined set (a group) of rows, but rather than returning one value per group, window functions can return multiple values for each group.
См. Поваренная книга O'Reilly по SQL, приложение A для хорошего обзора оконных функций.
Не использовать предложение With или правильное соединение и полагаться на подзапросы.
Анти-шаблон:
select
...
from data
where RECORD.STATE IN (
SELECT STATEID
FROM STATE
WHERE NAME IN
('Published to test',
'Approved for public',
'Published to public',
'Archived'
))
Лучше:
Мне нравится использовать предложение with, чтобы сделать мое намерение более читабельным.
with valid_states as (
SELECT STATEID
FROM STATE
WHERE NAME IN
('Published to test',
'Approved for public',
'Published to public',
'Archived'
)
select ... from data, valid_states
where data.state = valid_states.state
Лучший:
select
...
from data join states using (state)
where
states.state in ('Published to test',
'Approved for public',
'Published to public',
'Archived'
)
Почему? Не могли бы вы описать разницу в закулисной
Обычно называемые CTE или Common Table Expression, должны быть лучшие примеры, чем этот, поскольку ваш анти-шаблон и лучшие примеры более похожи, и ваш тезис заключается в том, что лучше должен быть шаблон
Повторное использование «мертвого» поля для чего-то, для чего оно не предназначалось (например, для хранения пользовательских данных в поле «Факс») - очень заманчиво в качестве быстрого решения!
re: использование @@ IDENTITY вместо SCOPE_IDENTITY ()
вы не должны использовать ни то, ни другое; вместо этого используйте вывод
ссылка не работает. Лучше включить соответствующий текст, чем ссылку
Использование первичных ключей в качестве суррогата для адресов записей и использование внешних ключей в качестве суррогата для указателей, встроенных в записи.
Мои любимые антипаттерны SQL:
JOIN для неуникальных столбцов и использование SELECT DISTINCT для обрезки результата.
Создание представления, объединяющего множество таблиц, просто чтобы выбрать несколько столбцов из одной таблицы.
CREATE VIEW my_view AS
SELECT * FROM table1
JOIN table2 ON (...)
JOIN table3 ON (...);
SELECT col1, col2 FROM my_view WHERE col3 = 123;
Я только что наткнулся на такое определение вида:
CREATE OR REPLACE FORCE VIEW PRICE (PART_NUMBER, PRICE_LIST, LIST_VERSION ...)
AS
SELECT sp.MKT_PART_NUMBER,
sp.PRICE_LIST,
sp.LIST_VERSION,
sp.MIN_PRICE,
sp.UNIT_PRICE,
sp.MAX_PRICE,
...
В представлении около 50 столбцов. Некоторые разработчики гордятся тем, что мучают других тем, что не предоставляют псевдонимы столбцов, поэтому нужно подсчитывать смещение столбца в обоих местах, чтобы иметь возможность выяснить, какой столбец в представлении соответствует.
Я видел слишком много людей, которые всю жизнь цеплялись за IN (...), совершенно не обращая внимания на EXISTS. Хороший пример - в Symfony Propel ORM.
Хранимые процедуры или функции без комментариев ...
И представления;) Функции истинные, кроме функций с табличным значением (= представления с параметрами).
Приложение присоединяется Не только проблема SQL, но, ища описания проблемы и находя этот вопрос, я был удивлен, что его не было в списке.
Как я слышал, соединение приложения - это когда вы извлекаете набор строк из каждой из двух или более таблиц, а затем соединяете их в своем (Java) коде с парой вложенных циклов for. Это обременяет систему (ваше приложение и базу данных) необходимостью идентифицировать весь кросс-продукт, извлекать его и отправлять в приложение. Предполагая, что приложение может фильтровать кросс-продукт так же быстро, как и база данных (сомнительно), простое сокращение набора результатов раньше означает меньшую передачу данных.
Разработчики, которые пишут запросы, не имея четкого представления о том, что делает приложения SQL (как отдельные запросы, так и многопользовательские системы) быстрыми или медленными. Это включает незнание:
- стратегии минимизации физического ввода-вывода, учитывая, что узким местом большинства запросов является ввод-вывод, а не ЦП
- perf влияние различных видов доступа к физическому хранилищу (например, множество последовательных операций ввода-вывода будет быстрее, чем множество небольших случайных операций ввода-вывода, хотя и меньше, если ваше физическое хранилище - SSD!)
- как настроить запрос вручную, если СУБД создает плохой план запроса
- как диагностировать низкую производительность базы данных, как «отлаживать» медленный запрос и как читать план запроса (или EXPLAIN, в зависимости от выбранной СУБД)
- стратегии блокировки для оптимизации пропускной способности и предотвращения взаимоблокировок в многопользовательских приложениях
- важность пакетной обработки и других приемов для обработки наборов данных
- дизайн таблиц и индексов для наилучшего баланса пространства и производительности (например, покрытие индексов, сохранение небольших индексов, где это возможно, сокращение типов данных до минимально необходимого размера и т. д.)
Это вопрос, который не соответствует новым стандартам относительно того, какой тип вопросов подходит для Stack Overflow. Когда его спросили, это могло быть неправдой.