Разделы
Публикации
Популярные
Новые
Главная » Оптимизация производительности transact

1 2 3 4 5 6 ... 55

фильтрация данных 39

Когда значение Amount проверяется в п|)едикаге >600, оно еще не равно NULL, так что оно ошибочно включается в результирующее лнюжество. К тому времени, когда оно становится равным NULL, оно уже включено в результирующее \п10жество, фактически отрицая предикат >600.

Хотя выбор синтаксиса внутреннего соединения - в значительной степени вопрос предпочтения, используйте синтаксис SQL-92 везде, где это возможно. Конечно, достаточно трудно придерживаться единообразия при соединении таблиц, особенно если существует два разных способа сделать это. И, как мы только что увидели, существуют реальные проблемы с устаревшим синтаксисо.м внешних соединений. Более того, компания Microsoft настоятельно рекомендует использовать ANSI-синтаксис, и публично заявила, что устаревший синтаксис внешних соединений не будет поддерживаться в дальнейших версиях. Применение ANSI/ISO-синтаксиса также имеет смысл и с точки зрения функциональной совмести.мости. Так как мир СУБД - как и настоящий мир - сжимается, не является необьип1ЫМ тот факт, что приложение \южет взаимодействовать с СУБД более че.м одного производителя или использовать их. Гетерогенные соединения, транзитные запросы, репликация с гетерогенных источников - обыкновенное дело в наше время. Зная это, имеет смысл отказаться от частных синтаксических элементов в пользу повсеместно используемых.

Другие типы соединений

к настоящему времени мы исследовали только левые соединения - внутренние и внешние. Есть еще несколько других, о которых стоит упомянуть. Transact-SQL также поддерживает соедииення RIGHT OUTER JOIN (правое внешнее соединение), CROSS JOIN (перекрестное соединение) и FULL OUTER JOIN (полное внешнее соединение).

RIGHT OUTER JOIN на самом деле не отличается от LEFT OUTER JOIN. Это тот же LEFT OUTER JOIN с из.менеиным порядком таблиц. Очень просто изменить LEFT OUTER JOIN на RIGHT OUTER JOIN. Вот переделанный запрос с LEFT OUTER JOIN:

SELECT customers.CustomerNumber+2, orders.Amount FROM orders RIGHT OUTER JOIN customers ON (customers .CustomerNumber-b2=orders. CustomerNumber)

CustomerNumber Amount

3 86753.09

4 NULL

5 NULL

RIGHT JOIN возвращает столбцы первой таблицы как NULL, когда не выно;н1я-ется условие соединения. После того как вы решите, какая из таблиц является первой, а какая - второй, выбор LEFT JOIN или RIGHT JOIN в значительной степени зависит от ваших предпочтений.

В отличие от них, CROSS JOIN возвращает декартово произведение. Размер декартова произведения - произведенне количества записей одной таблицы и количества записей другой. Так что для двух таблиц с тремя записями в каждой их CROSS JOIN, или декартово произведение, будет содержать девять записей.



По определению для CROSS JOIN не требуечся и не поддерживается использование предложения ON, как требуется для других соединений. Вот CROSS JOIN таблиц customers и orders:

SELECT customers.CustomerNumber. orders.Amount FROM orders CROSS JOIN customers

Amount

1 123.45

1 678.90

1 86753.09

1 678.90

2 123.45 2 678.90

2 86753.09

2 678.90

3 123.45 3 678.90

3 86753,09

3 678.90

(12 row(s) affected)

FULL OUTER JOIN возвраигает записи из обеих таблиц, независимо от выполнения условия соединения. Когда для соединяемого столбца из первой таблицы не удается найти соответствие во второй, значения из второй таблицы возвращаются как NULL, так же как в случае LEFT OUTER JOIN. Когда для соединяемого столбца из второй таблицы не удается найти соответствие в первой, столбцы первой таблицы возвращаются как NULL, как и с RIGHT OUTER JOIN. Вы можете считать, что FULL OUTER JOIN - это комбинация LEFT JOIN и RIGHT JOIN.

Вот пример с LEFT OUTER JOIN, переделаиньп ! с использованием FULL OUTER JOIN;

SELECT customers.CustomerNumber+2. orders.Amount FROM customers FULL OUTER JOIN orders ON (customers.CustomerNumber+2=orders.CustomerNumber)

Amount

3 86753.09

4 NULL

5 NULL NULL 123.45 NULL 678.90 NULL 678.90

Подзапросы

Оператор SELECT, заключенный в скобки и включенный в другой запрос (обычно в его предложение WHERE), называется подзапросом. Подзапрос обычно служит для получения списка значений, которые затем сравниваются со столбцо.\1 главного запроса. Вот пример; SELECT * FROM customers

WHERE CustomerNumber IN (SELECT CustomerNumber FROM orders)



Конечно, вы могли бы сделать то же самое с помощью внутреннего соединения. На самом деле оптимизатор SQL Server превратит этот запрос во внутреннее соединение. Однако вы должны понять - подзапрос возвращает значение или множество значений, которые зате.м .можно использовать для фильтрации запроса или получения значения столбца.

Группировка данных

Так как SQL - это язык запросов, ориентированный на работу с множества.ми, операторы группировки и сум.мировання данных - его стихия. С помощью этих операторов и агрегатных функций работают SQL-заиросы. Разработчики, знакомые с СУБД, ориентированными на работу по одной записи, находят этот подход довольно специфичны.м, потому что они приучены к работе одновременно только с одьюй записью. Получение суммарной информации с помощью цикла по таблице - обычный подход в старых продуктах для работы с базами данных, но не в SQL Server. Один оператор SQL .\южет вьиюлиить работу, для выполнения которой потребовалась бы целая nporpaM.via на COBOL. Это волшебство реализуется с помощью предложения GROUP BY оператора SELECT и агрегатных функций Transact-SQL. Вот пример:

SELECT customers.CustomerNumber. SUMCorders.Amount) AS TotalOrders

FROM customers JOIN orders ON customers.CustomerNumber-orders.CustomerNumber

GROUP BY customers.CustomerNumber

Этот запрос возвращает список всех клиентов и сумму их заказов. Как узнать, какие ноля включать в пред;н)жение GROUP BY? Вы должны включать все элементы, которые используются в списке столбцов оператора SELECT и не являются агрегатными функциями или абсолютиыли! зиачения.ми. Рассмотрим следующий оператор SELECT:

Плохой код - не делайте так

SELECT customers.CustomerNumber. customers.LastName. SUMCorders.Amount) AS TotalOrders

FROM customers JOIN orders ON customers.CustomerNumber=orders.CustomerNumber

GROUP BY customers.CustomerNumber

Данный запрос не будет вьиюлнен, потому что в его предложении GROUP BY пропущен столбец. Вместо этого он должен выглядеть так: GROUP BY customers,CustomerNumber, customers.LastName

Обратите внимание, что добавление столбца LastName на самом деле не затрагивает результаты, так как CustomerNumber - уникальный ключ. То есть включение LastName в GROUP BY не добавит дополнительных уровней группировки, поскольку существует только один LastName для каждого CustomerNumber.

HAVING

Предложение HAVING используется для ограничения занисен, возвращаемых опе-раторо.м SELECT с GROUP BY. Его отношение к GROUP BY похоже на отноше1и1е предложения WHERE и самого оператора SELECT. Так же как предложение WHERE, HAVING ограничивает записи, возвращаемые оператором SELECT.



В отличие от WHERE, HAVING работает с заиисими 1з результирующем множестве, а не в таблицах запроса. Вот измененный предыдущий запрос, включаю-щий предложение HAVING:

SELECT ciiStomers.CustomerNumber. customers.LastName. SUMCorciers.Amo.nt) AS TolaOrders

FROM custoaiers JOIN orders ON customers.CustomerNumber=orders,Custo,7ie,-Nu,4iber

GROUP BY customers.CustomerNumber. customers.LastName

HAVING SUM(orders.Amount) > 700

CustomerNa*er LastName Total Orders

3 Citizen 86753.09

I Dee 802,35

Часто для фильтрации запроса в.место предложения HAVING лучше выбрать другой способ. В общем, HAVING менее эффективно, чем WHERE, потому что оно обрабатывает результирующее м1Южество после того, как оно группируется; WHERE делает это заранее. Вот пример неправильного нpн.vleнeния предложения HAVING:

-- Плохой код - не делайте так

SELECT customers.LastName, COUNT(*) AS NumberWithName

FROM custodiers

GROUP BY customers.LastName

HAVING customers.LastNameoCitizen

При правильной sainicn параметры фильтрации запроса должны быть записаны в предложении WHERE, как сделано ниже: SELECT customers.LastName. COUNT(*) AS NumberWithName FROM customers

WHERE customers.LastNameo Xttuen GROUP BY customers.LastName

Ha самом деле SQL Server распознает этот тип ненравп.чьного исполь.зова-ния HAVING и преобразует HAVING в WHERE во врс.угя выполнения запроса. Независимо от того, сумеет ли SQL Server распознать ошибки, подобные этой, лучиш с самого начала писать оптимальный код.

Упорядочение данных

Предложение ORDER BY служит для у.юрядочения записей, возвращасмььх запросом. Оно следует за предложениями WHERE и GROUP BY (если они есть) и сг,п тирует результирующее множество прямо перед его возвращением Вот ппимео

SELECT LastName, State Ч^имер.

FROM customers

ORDER BY State

Вот другой пример:

SELECT FirstName. LastName

FROM customers

ORDER BY LastName OESC

Обратите онимание на использование ключевого слова DESC для соотиоот.и записей в юрядке убывания. Ьсли не о/,ределено иначе, ORDER BY всегла спптГ рует .записи по возрастанию. cupiw-



Псевдонимы столбцов

Вы, возможно, заметили, что в некоторых предыдущих запросах в этой главе применялись логические названия столбцов для агрегатных функций, таких как COUNTO и SUM(). Подобные названия известны как псевдопшш столбцов, они помогают сделать запросы и результирующие множества более понятнылиг Как и в случае соединений, Transact-SQL предоставляет два отдельных синтаксиса для создания псевдонимов столбцов: устаревший, или классический, и ANSL В классическом синтаксисе псевдонимы столбцов предшествуют названию столбца и отделяются знаком равенства, как здесь: SELECT TodaysDate=GETDATE()

В ANSLcHHTaKCHCe псевдоним столбца раснола1-ается справа от его названия и опционально отделяется ключевым словом AS, подобно этому: SELECT GETOATEO AS TodaysDate

или

SELECT GETDATEO TodaysDate

В отличие от соединений, выбор си1ггаксиса для псевдонимов столбцов не повлияет на результирующее кигожество. Это в значительной степени вопрос предпочтения, хотя всегда желательно применять ANSLcnHxaKCHc, когда это возможно, единственная причина, но которой можно поступить иначе - совместимость с другими пpoдyктaш.

Вы можете использовать псевдонимы столбцов для любых элементов результирующего множества, не только для ai-регатных функций. Напри.мер, в следующем примере для столбца LastName в результирующее множество подставляется псевдоним LName:

SELECT customers.LastName AS LName, CCUNK*) AS NumberWithName

FROM customers

GROUP BY customers,LastName

Обратите внимание, однако, что вы не можете задействовать псевдонимы столбцов в других частях запроса, кроме предложения ORDER BY. В предложениях WHERE, GROUP BY и HAVING вы должны применить фактические названия столбцов или значений. В донолнение к иоддержке псевдони.\юв столбцов, в ORDER BY вы можете указать столбец сортировки, используя его порядковый номер в списке столбцов оператора SELECT, подобно этому:

SELECT FirstName. LastName FROM customers ORDER BY 2

Этот синтаксис подвергается всеобщему осуждению и менее прозрачен, чем просто указание названия столбца или его псевдонима.

Псевдонимы таблиц

Подобно псевдоьш.мам столбцов, вы .\южете исгюльзовать псевдопшш таблиц, чтобы не указьшать nojnioe иазва1И1е таблтнды. Псевдонимы таблиц указьншются.



в предложении FROM запроса. Они номещаются справа от ()а1С1Ического названия таблицы (псевдони.м опционально отделяется ключевым слово.м AS), как показано ниже:

SELECT c.LastNacie, CObNT(*) AS NunberWiinNaT.e FROM customers AS с GROUP BY cLasLName

Обратите BHH.Mamie, что исевлоиим .чюжет быть использован в списке полей оператора SELECT, до того как он с1П1таксическн определен. Это возможно, потому что ссылки на объекты базы дашплх разрешаются перед вьтолпением запроса.

Управление транзакциями

Управление транзакция.ми на ca.vio.vi де.че выходит за ра.ун<и введения в Transact-SQL. Однако транзакции - важнейшая часть разработки приложений баз данных, и иоии.мание их основ - ключ к }шписапию оптимального SQL (более подробную информацию см. в главе 14, Транзакции ).

Термин транзакция соответствует ipynne из.меиений в базе данных. Транзакции обеспечивают атомарность изменештй - это означает, что вносятся либо все измене11ия, либо ни одно из них. Приложения SQL Server исггользуют транзакции для поддержания целостности данных и нсключеиия снтуацин, когда база даттых остается в промежуточном состояшт, нанри.мер в случае неудачного завершения операции.

Команда COMMIT запись{вает чранзакцню на диск (если существуют вложенные транзакции, это истинно только для са.мой внешней команды COMMIT, но это уже более с;южная тема). Считайте, что ;-jto ко.мапда Сохранить (Save) для базы данных. ROLLBACK, с другой стороны, отклоняет изменения, сделанные транзакцией; она работает как кома{1да Отменить (Undo). Обе эти ко.манды воздействуют только па из.менеиия, сделан1Н)1е после последнег! оиерацтш COMMIT; вы не можете откатить уже зафтн<сированные нз.мспения (coiuiuitled).

Если только неремешктя сессии IMPLICIT TRANSACTIONS не была разрешена, вы должны явно начать транзакцию, чтобы завернтпть ее или откатить. Транзакции могут быть вложенными, можно npoiiepHTb текуши!) уровень вложенности с помощью автоматической переменной @@TRANCOUNT:

SELECT WRANCOUNT AS TranNestingLevei

Вот пример кода Transact-SQL, в котором используются транзакции для отмены изменений, сделашшьч в базе данных;

BEGIN TRAN DELETE customers GO

ROLLBACK

SELECT * FROM customers

CustomerNumber LastName FirstName StreetAdcress City State Zip

1 Doe John 123 Josnua Tree Piano TX 75025

2 Doe Jane ;23 Joshua Tree Piano TX 75025

3 Citizen John 57 Riverside Reo CA 90120



Заключение

Как вы видите, ROLLBACK отменяет удале1И1е записей, совершенное оператором DELETE.

ВНИМАНИЕ -

Убедитесь, что каждой команде BEGIN TRAN соответствует COMMIT или ROLLBACK. Повисшие транзакции могут привести к серьезным нарушениям работы сервера, а также к снижению производительности.

Заключение

На этом мы закончим знакомство с Transact-SQL. Теперь вы должны уметь создавать базы данных и таблицы, а также заполнять таблицы данными. Вы также должны были изучить основные синтаксические конструкции, необходимые для создания запросов к таблица.м и внесения в них элементарных изменений. Убедитесь, что вы хорошо поняли основы Transact-SQL, прежде чем продолжить чтение.



Особенности типов данных Transact-SQL

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

Л'. В. Кентоп

SQL Server включает множество разнообразных встроенных типов данных - фактически больше, чем в других основных СУБД. Он поддерживает символьные, числовые, datetime, BLOB-типы данных н .множество других. Он предлагает узкие типы для небольших данных и широкие для больших. Символьные строки в SQL Server могут иметь размер до 8000 байт, в то время как BLOB-типы могут хранить до 2 Гбайт. Числовые значения варьируются от однобайтовых беззнаковых целых, до знаковых значений с плавающей запятой с точностью до 53 BiiaKOB. Все эти типы, кроме одного (курсорного типа данных), представляют собой скалярные типы - они представляют одновременно только одно значение. Существует масса нюансов, подводных камней и ловушек, которые поджидают вас при использова1нш большинства этих типов. В этой главе мы более подробно изучим некоторые из них.

в SQL Server даты бывают двух типов: datetime и smalldatetime. Отдельного типа данных для времени не существует - дата и время в SQL Server всегда хранятся вместе. Столбцы типа datetime занимают восемь байтов и могут хранить даты с 1 января 1753 г, но 31 декабря 9999 г. Столбцы тина smalldatetime за1Н1мают четыре байта и могут хранить даты с I января 1900 г. но 6 июня 2079 v. Столбцы типа datetime хранят даты и время, округленные к ближайшим трем сотым секунды (3,33 .мс), в то время как столбцы тина smalldatetime - к ближайшей минуте: они вообще не хранят секунды пли миллисекунды.

Если вы хотите хранить дату без времени, просто опустите временную часть столбца или переменной - по умолчанию она будет равна 00:00:00.000 (полночь).



Если вгсм необходимо время бев даты, опустите часть, представляющую дату, - она будет равна 1 января 1900 года. Дата по умолчанию - 1 января 1900 года, потому что это дата начала отсчета SQL Server (reference date); все даты в SQL Server хранятся как количество дней до или после 1 января 1900 года.

Часть переменной типа datetime, представляющая дату, занимает первые четыре байта, а часть, представляющая время, - последние четыре. Временная часть столбцов типа datetime или smalldatetime - количество .миллисекунд после полуночи. Вот почему она равна полуночи по умолчанию, если ее опустить.

Одна особенность типа datetime, о которой вы должны знать, это способ хранения миллисекунд. Так как точность ограничена 3,33 мс, миллисекунды всегда округляются к ближайщей трехсотой доли секунды. Это означает, что часть типа datetime, представляющая миллисекунды, всегда оканчивается иа О, 3, или 7. Так что 1900 01 01 12:00:00.564 округляется до 1900 01 01 12:00:00..563 и 1900 01 01 12:00:00.565 округляется до 1900 01 01 12:00:00.567 .

Проблема 2000 года и другие проблемы с датами

я считаю, что здесь будет уместно обсудить влияние проблемы Y2K на приложения SQL Server и способы ее рещения. Проблема 2000 года вызывала панику у людей, как и.меющих, так и не и.меющих отнощения к технике, так что целесообразно рассказать, как проблема 2000 года могла бы затронуть SQL Server и приложения, основанные на ие.м.

Во-первых, вследствие того что в SQL Server имеется тин данных datetime, множество проблем, свойственных старым 1гриложепиям и СУБД, здесь отсутствуют. Даты хранятся как числовые величины, а не как символьные строки, так что не нул<но гадать о веке данной нере.менной типа datetime или столбца.

Во-вторых, учитьит, что дал<:е тип smalldatetime .\южет хранить даты до 2079 года, проблемы с вмести.\10стью тоже нет. Так как четыре байта столбца этого типа зарезервированы под дату, он .может хран1-ггь до 2147483647 дней (включая знаковый бит) даже при том, что между 1 января 1753 года н 31 декабря 9999 года всего 3012 153 дня.

Несмотря иа все это, есть еще несколько тонких моментов, касающихся проблемы 2000 года и других проблем с датами, которые могут повлиять на приложения SQL Server. Большинство из них связаны с предположениями о формате даты в коде Transact-SQL. Рассмотрим следующий код:

SELECT CAST(01-01-39 AS datetime) AS DadsBirthDate

Какая дата будет возвращена? Хотя это не очевидно из кода, ответ - 1 января 2039 года. Почему? SQL Server и.меет виутреппий параметр, который контролирует, как интерпретировать года, представленные двумя цифрами. Вы можете сконфигурировать его, используя Enterprise Manager (щелчок правой клавишей на сервере, выберите Properties > Server Settings (Свойства ► Настройки сервера)) >щи при помощи sp configure (с помощью настройки two-digit year cutoff). По y.vюлчa-нию годы, представленные двумя цифрами, SQL Server интерпретирует как даты из интервала 1950 и 2049. Так что приведенный выше код Transact-SQL, который использует SELECT, и предполагает ссылку на 1939 год, может работать неправильно. (Если за год рождения отца принять 2039 год, это будет означать, что он вообще еще не родился!)



Конечно, самое нросгое решение - применять годы, иредсташюниые четырьмя цифрами. Это устраняет иеодно.зиачносгь н опасность, что изменение настроГгки two-digit year cutoff повредит суитсствуюшин код. Обратите внимание, я не утверждаю, что вы Д0ЛЖ1НЛ использовать толы, представленные четырьмя цифрами в пользовательском И1тгерфейсе, - я говорю только о коде Tran.sact-SQL, который вы пишете. Что вы требуете от нользователен - это уже другой вопрос.

Другой тонкий момент, связанньп] с проблемой 2000 года, который может повлиять на пр!1Ложсния SQL Server, - идентификаторы, основанные на дате. Подход с использованием года внутри столетия для нумерации тюследовательных элементов не является необычным для старых систем (и некоторых новых). Нанри.мер, в системе обработки заказов, которую я нереписал в восьмидесятых, применялся формат YY-SequenceNumber для ун1и<:альной идентифтисацин заказов. Эти числа использовались в качестве уникальных идентификаторов в реляционной базе данных. Каждьн-! раз, когда добавлялся новый заказ, процедура в клиентском приложении искала в таблице макси.мальный SequenceNumber и увеличива.та его на единицу. Примерно за пять лет до того, как я стал работать над этим проектом, компания объединилась с другой компанией, имеющей такую же схему ну.мерации. Чтобы избежать появления дублируютцихся ключей, программисты, объединяювше данные двух этих компантгй, просто добавили 10 к префиксам годов заказов второй ко.мпанитт Это стало бомбой замедленного действия, котора;:! сработала бы через десять лет, когда новые ключи, генерируемые для данных nepBoii ко.мпанин, начали бы конфликтовать с первоначальными ключа.ми второй ко.мпанин. К счастью, мы ирсдвидети эту ситуацию и вовремя устранили проблему. Мы заново объедтитили две базы данных, на этот раз добавив к SequenceNuniber часть но.мсра заказа, а не префикс его гола. Мы добавили число к гюрядковым 1юмера.м второй ко.мпанин, которого было достаточно для раз.мешения всех их после номеров первой компании, предотвратив таки.м образом воз.можность кон()ликтов ключей в будущем.

Эта ситуация лишь отчасти связана с проблемой 2000 года, она больше зависит от неосторожного использовантш к.ночей, основатн!ых на датах. Представим ситуацию, в которой ключи начтнта.тись бы с 1999 года. Схема с применением двух цифр не смогла бы обработать переход к 2000 году, потому что оказалась бы неспособной извлекать .максимальное значе;птс порядкового номера из базы даниых и увеличивать его.

Обитая суть всех этих спенариев такова: исюиочение из даты части, соответствующей столетию, может привести к проблемам. Не делайте так, если только вы не любите проблемы.

Функции для работы с датами

SQL Server включает множество ([пункций для управления и работы со столбна-ми типа datetime. Эти функции позвол>иот 1тзвлекать части дат, добавлять части дат к существующтьм дата.м, получать значение текуще1т даты и времени, и так далее. Давайте исследуем несколько из них, рассмот1)ев некоторые интересные проблемы, связанные с датами.

Рассмотрим классическую проблему ои1)еделенти1 годовщин приема на работу сотрудников компании, которые вьнтдают па с;1елуюитие тридцать диен. Эта



1 2 3 4 5 6 ... 55
© 2004-2024 AVTK.RU. Поддержка сайта: +7 495 7950139 в тональном режиме 271761
Копирование материалов разрешено при условии активной ссылки.
Яндекс.Метрика