Is null access - Новости с мира ПК
Vvmebel.com

Новости с мира ПК
4 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Is null access

Is null access

А втор Allen Browne, ноябрь 1999 г.
http://allenbrowne.com/casu-12.html
Перевод: А. Артамонов, октябрь 2011г.

Типичные ошибки с Null-ами

Вот некоторые распространенные ошибки обращения с Null -ами, которые допускают новички. Если у вас нет ясности насчет Null’ов, сначала прочитайте Nulls: Do I need them?

Ошибка № 1: Null-ы в критериях

Если вы вводите условии отбора под полем в конструкторе запросов, он возвращает только совпадающие с образцом записи. Null-ы при этом исключаются из отбора.
Например, скажем, у вас есть таблица компаний и адресов. Вам нужны два запроса: один, который выдает местные компании, другой ― все остальные . В строке условий отбора в первом запросе под полем Город вы вписываете:
«Бобруйск»
и во втором запросе :
Not «Бобруйск»
Неправильно! Ни один запрос не включит записи, у которых город Null.

Используйте Is Null . Во втором запросе, чтобы достичь желаемого результата, условия отбора должны выглядеть как:
Is Null Or Not «Бобруйск»
Примечание: Запросы DDL (языка определения данных) обращаются с Null-ами иначе. Например, Null-ы учитываются в таком типе запроса:
ALTER TABLE Table1 ADD CONSTRAINT chk1 CHECK (99 ‘TX’));

Ошибка № 2: Null-ы в выражениях

Результатом вычислений, включающих Null, обычно является Null. Например, новички иногда вводят выражение в источник данных текстового поля, чтобы вывести остаток к оплате:
=[СуммаКОплате] — [СуммаУплаченная]
Проблема в том, что если не было оплачено ничего, СуммаУплаченная является Null-ом, и в текстовом поле ничего не отображается.

Используйте функцию Nz(), чтобы указать значение для Null-а:
= Nz([СуммаКОплате], 0) — Nz([СуммаУплаченная], 0)

Ошибка № 3: Null-ы во внешних ключах

В то время, как Аксесс запрещает Null-ы в первичных ключах, он разрешает Null-ы во внешних. В большинстве случаев стоит явно запрещать эту возможность, ч тобы избежать висящих ссылок .
В типичной таблице для накладных, строки накладной хранятся в таблице НакладнаяСтроки, соединенной с таблицей Накладные по НакладнаяКод. Вы создаете связь между Накладные.НакладнаяКод и НакладнаяСтроки.НакладнаяКод с поддержкой ссылочной целостности . Этого недостаточно!
Если вы не установили свойство Обязательное поля НакладнаяКод в таблице НакладнаяСтроки на Да , Аксесс разрешает Null-ы. Чаще всего это случается, когда пользователь начинает добавлять строки в подформе, не создав сперва саму накладную в основной форме. Так как у этих записей нет соответствующей им записи в основной форме, эти висящие записи больше нигде не показываются снова. Пользователь уверен, что программа их удалила, хотя на самом деле они находятся в таблице.

Всегда выставляйте значение свойства Обязательное на Да в конструкторе таблиц, если только вам не нужна возможность иметь Null-ы во внешних ключах.

Ошибка № 4: Null-ы и типы, отличные от Variant.

В Visual Basic единственным типом данных, который может содержать Null является Variant. Когда бы вы ни присваивали значение поля в переменную с типом не-Variant, всегда нужно учитывать возможность, что в поле может содержаться Null. Посмотрите, что в этом коде модуля формы может пойти не так:
Dim strName as String
Dim lngID As Long
strName = Me.Отчество
lngID = Me.КодКлиента
Когда поле Отчество содержит Null, попытка присвоить Null переменной строкового типа породит ошибку .
Аналогично, присвоение значения КодКлиента числовой переменной может вызвать ошибку. Даже если КодКлиента является первичным ключом, код небезопасен: первичный ключ содержит Null в новой записи.

(a) Используйте тип Variant, если вам необходимо работать с Null-ами.
(b) Используйте функцию Nz() , чтобы указать значение вместо Null. Например::
strName = Nz(Me.Отчество, «»)
lngID = Nz(Me.КодКлиента, 0)

Error 5: Сравнение с Null

Выражение:
If [Фамилия] = Null Then
будет бессмысленно, так как никогда не может быть истинно. Даже если фамилия является Null-ом, VBA полагает, что вы спросили::
Является ли Неизвестное равным Неизвестному?
и всегда отвечает: “Откуда мне знать, равны ли ваши неизвестные друг другу? Здесь мы опять наблюдаем распространение Null : результат не является ни истинным ни ложным.

Используйте функцию IsNull() :
If IsNull([Фамилия]) Then

Ошибка № 6: Забыли, что Null ни Истина ни Ложь.

Выполняют ли эти две конструкции одну и ту же работу?
(a) If [Фамилия] = «Иванов» Then
MsgBox «Это Иванов”
Else
MsgBox «Это не Иванов»
End If

(b) If [Фамилия] <> «Иванов» Then
MsgBox «Это не Иванов»
Else
MsgBox «Это Иванов”
End If
Когда фамилия является Null-ом, эти два куска кода противоречат друг другу. В обоих случаях, первая часть If не срабатывает, и начинается выполнение Else, что приводит к противоречащим друг другу сообщениям.

(a) Учитывайте все три возможных результата сравнения — True, False, и Null :
If [Фамилия] = «Иванов» Then
MsgBox «Это Иванов”
ElseIf [Фамилия] <> «Иванов» Then
MsgBox «Это не Иванов”
Else
MsgBox «Мы не знаем, Иванов это или нет»
End If
(b) В некоторых случаях, функция Nz() позволит обработать два случая разом. Например, рассматривать Null и нулевую строку одинаково:
If Len(Nz([Фамилия],»»)) = 0 Then 

Oracle mechanics

Производительность СУБД и смежные вопросы

Операция filter(NULL IS NOT NULL) и запросы со связанными переменными

При выполнении запроса с NULL-евыми значениями связанных переменных условия типа COLUMN1 = :VAR1 превращаются в COLUMN1 = NULL и по определению становятся невыполнимыми (в том смысле, что запрос с таким условием в WHERE не возвращает строк), но при построении плана / выполнении запроса оптимизатор не всегда использует эту возможность сэкономить ресурсы

Например, при выполнении практическоого запроса (судя по тексту, выполняющему проверку на совпадение введённого в поле формы текста с ID либо логином клиента) с пустым значением переменной:

— запрос выполняется так же, как и для не NULL-евых значений переменных, с тем же значительным количеством излишних в этом случае чтений блоков бд

Тот же запрос без использования связанных переменных отрабатывает намного эффективнее:

Читать еще:  Как переустановить офис 2020

— общая стоимость выполнения запроса оказывается меньше стоимости слагаемых: при заведомо невыполнимых условиях ненужные операции доступа к бд исключаются оператором filter(NULL IS NOT NULL)

Или на тестовом примере:

— и опять несмотря на ненулевую стоимость сканирования T1, стоимость всего запроса = 0, что логично — запросу получения результата не нужны данные бд

Поскольку таблица в этом примере «свежая» (без статистики) в соответсвии со значением по умолчанию параметра:

дополнительно выполняется избыточная операция dynamic sampling после получения значений связанных переменных (что важно для запросов со связанными переменными), уже на этапе выбора метода доступа к таблице:

— что добавляет небольшой overhead при разборе (hard parse) во время первого выполнения запроса

Последующие выпонения уже разобранного запроса уже не читают блоки бд и не делают никаких сортировок за ненадобностью, как и ожидается:

К сожалению, замечательная операция filter(NULL IS NOT NULL) в явном виде не используется Oracle для того же запроса со связанными переменными:

— даже если при первом выполнении/разборе курсора (hard parse) в качестве значения связанной переменной использовался NULL — bind peeking в этом случае не помогает

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

— т.е., отображая в плане обычную операцию INDEX RANGE SCAN, предполагающую доступ к блокам индекса, Oracle в зависимости от значения связанной переменной может полностью исключать обращения к данным бд. Очень похоже на неявное использование аналога операции типа filter(NULL IS NOT NULL)

При не NULL-вом значении переменной индекс честно читается:

Важно отметить, что при выборе способа выполнения запроса (читать или не читать блоки) Oracle не использует статистику таблицы:

— т.о. в этом месте поведение Oracle основано на анализе значений связанных переменных (bind peeking) при каждом выполнении курсора на этапе soft parse:

Для первоначального практического запроса добиться того же эффекта можно с помощью пары дополнительных индексов + эквивалентно переформулировав текст запроса — заменив LEFT OUTER JOIN на UNION, таким образом приводя сложный запрос к объединению двух запросов с простыми одиночными предикатами:

— т.о. для NULL-евых значений связанных переменных Oracle может исключать логически избыточные чтения, заметно улучшая время выполнения запросов: с 3 секунд до 100 мс, например

Common Errors with Null

Here are some common mistakes newbies make with Nulls. If you are unclear about Nulls, first read Nulls: Do I need them?.

Error 1: Nulls in Criteria

If you enter criteria under a field in a query, it returns only matching records. Nulls are excluded when you enter criteria.

For example, say you have a table of company names and addresses. You want two queries: one that gives you the local companies, and the other that gives you all the rest. In the Criteria row under the City field of the first query, you type:

and in the second query:

Wrong! Neither query includes the records where City is Null.

Solution

Specify Is Null. For the second query above to meet your design goal of «all the rest», the criteria needs to be:

Note: Data Definition Language (DDL) queries treat nulls differently. For example, the nulls are counted in this kind of query:
ALTER TABLE Table1 ADD CONSTRAINT chk1 CHECK (99 ‘TX’));

Error 2: Nulls in expressions

Maths involving a Null usually results in Null. For example, newbies sometimes enter an expression such as this in the ControlSource property of a text box, to display the amount still payable:

The trouble is that if nothing has been paid, AmountPaid is Null, and so this text box displays nothing at all.

Solution

Use the Nz() function to specify a value for Null:

Error 3: Nulls in Foreign Keys

While Access blocks nulls in primary keys, it permits nulls in foreign keys. In most cases, you should explicitly block this possibility to prevent orphaned records.

For a typical Invoice table, the line items of the invoice are stored in an InvoiceDetail table, joined to the Invoice table by an InvoiceID. You create a relationship between Invoice.InvoiceID and InvoiceDetail.InvoiceID, with Referential Integrity enforced. It’s not enough!

Unless you set the Required property of the InvoiceID field to Yes in the InvoiceDetail table, Access permits Nulls. Most often this happens when a user begins adding line items to the subform without first creating the invoice itself in the main form. Since these records don’t match any record in the main form, these orphaned records are never displayed again. The user is convinced your program lost them, though they are still there in the table.

Solution

Always set the Required property of foreign key fields to Yes in table design view, unless you expressly want Nulls in the foreign key.

Error 4: Nulls and non-Variants

In Visual Basic, the only data type that can contain Null is the Variant. Whenever you assign the value of a field to a non-variant, you must consider the possibility that the field may be null. Can you see what could go wrong with this code in a form’s module?

When the MiddleName field contains Null, the attempt to assign the Null to a string generates an error.

Similarly the assignment of the ClientID value to a numeric variable may cause an error. Even if ClientID is the primary key, the code is not safe: the primary key contains Null at a new record.

Solutions

(a) Use a Variant data type if you need to work with nulls.

Читать еще:  Powershell web access

(b) Use the Nz() function to specify a value to use for Null. For example:

Error 5: Comparing something to Null

is a nonsense that will never be True. Even if the surname is Null, VBA thinks you asked:

and always responds «How do I know whether your unknowns are equal?» This is Null propagation again: the result is neither True nor False, but Null.

Solution

Use the IsNull() function:

Error 6: Forgetting Null is neither True nor False.

Do these two constructs do the same job?

When the Surname is Null, these 2 pieces of code contradict each other. In both cases, the If fails, so the Else executes, resulting in contradictory messages.

Solutions

(a) Handle all three outcomes of a comparison — True, False, and Null:

(b) In some cases, the Nz() function lets you to handle two cases together. For example, to treat a Null and a zero-length string in the same way:

Типичные тормоза запросов

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

Мы предполагаем, что ваши таблицы содержат первичные ключи, внешние ключи и индексы на полях, по которым совершается поиск и сортировка.

Используйте SQL, а не VBA

JET/ACE (движок запросов в Аксессе) использует Structured Query Language (SQL), как и многие базы данных. JET также способен вызывать код Visual Basic for Applications (VBA). Это радикально расширяет возможности JET-а, но вызов VBA теряет смысл, если работу может выполнить SQL.

Is Null, не IsNull()

Is Null является родным выражением SQL.

IsNull() — вызов VBA функции.

Не бывает веских причин вызывать IsNull() в запросе, так как SQL может самостоятельно оценить смысл выражения.

IIf(), не Nz()

Функция Nz() заменяет Null другим значением (для чисел обычно нулем, для текста — пустой строкой.) Новое значение является типом данных Variant, а VBA помечает его в свою очередь подтипом: String, Long, Double, Date и т.д.

В VBA это просто замечательно: функция может возвращать разные подтипы в разных ситуациях. Но в запросе столбец может быть только ОДНОГО типа данных. JET, следовательно, воспринимает значения типа Variant как текстовые, так как что угодно (числа, даты, символы, . ) являются валидными в текстовом столбце.

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

Если вы ожидали числовую или колонку с датами, у вас серьезные проблемы. Текстовые поля оцениваются посимвольно. Т.е. 2 больше 19, потому что первый символ (2) больше, чем первый символ в другом тексте (1 in 19.) Подобным образом, 4/1/2009 идет после 1/1/2010 в текстовом столбца, так как 4 идет после 1.

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

Можно было бы типизировать выражение еще вызовом еще одной функции VBA, но лучшим решением было бы позволить JET выполнить работы, не вызывая VBA вообще.

Вместо:
Nz(MyField,0)
используйте:
IIf(MyField Is Null, 0, MyField)

Да, придется чуть больше напечатать, но есть свои плюсы:

  • Вы избегаете вызова функции Nz().
  • Вы сохраняете желаемый тип данных.
  • Критерии применяются корректно.
  • Столбец сортируется корректно.

Этот принцип относится не только к Nz(), но и к любой функции VBA, возвращающей Variant. Просто Nz() — наиболее распространенный случай.

(Обратите внимание: функция JET IIf() намного более эффективна, чем одноименная функция в VBA. Функция VBA тратит время на оценку и истинной и ложной части и генерирует ошибки, если какая-нибудь из частей не срабатывает (даже если эта часть не нужна.) У JET-овской функции IIf() подобных проблем нет.)

Доменные агрегатные функции

DLookup(), DSum() и т.д. — медленные по выполнению функции. Они требуют вызова VBA, вызова службы выражений (Expression Service) и расходуют ресурсы (открывая дополнительные подключения к файлу данных). Особенно все затягивается, если JET должен выполнить операцию на каждой строке запроса.

Подзапросы будут значительно быстрее, чем доменные агрегатные функции. В большинстве случаев, вложенный запрос будет еще быстрее (т.е. еще один сохраненный запрос, который включается как «таблица» в первый запрос.)

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

Составляйте выражения, чтобы использовать индексы

Запрос будет намного быстрее, если база данных может использовать индекс, чтобы выбирать или сортировать записи. Вот два примера.

Оптимизируйте групповые запросы

Оптимизатор JET-а весьма неплох, так что вам может показаться, что простые запросы будут выполняться быстро и без советов из этого раздела. Все равно стоит потраченных усилий сделать запрос как можно лучше, чтобы он не стал внезапно тормозить, когда вы его измените.

WHERE против HAVING

Итоговые запросы (те, что с предложением GROUP BY) могут иметь как предложение WHERE, так и предложение HAVING. Сначала выполняется WHERE — перед группировкой; затем следует HAVING — когда высчитываются итоги. Итак, имеет смысл поместить критерии в предложение WHERE и использовать HAVING только, когда нужно примерить критерии на итоги по группам.

В конструкторе запросов Аксесса это не очевидно. Когда вы добавляете поле в строке конструктора, Аксесс устанавливает строку Групповая операция на Группировка (Group By), и хочется добавить критерии прямо под ним. Если вы это сделаете, критерии отбора окажутся в предложении HAVING. Чтобы использовать предложение WHERE clause, добавьте поле в грид конструктора еще раз и выберите Where в строке Групповая операция.

Читать еще:  Как установить майкрософт офис 10

FIRST против GROUP BY

Когда вы добавляете поле в групповой запрос, Аксесс предлагает Группировку/Group By в строке Групповая операция. Следовательно, по умолчанию Аксесс будет группировать по всем этим полям.

Первичный ключ уникален. Так, если вы группируете по полю первичного ключа, нет никакой необходимости группировать по другим полям в этой таблице. Вы можете оптимизировать запрос, выбрав First вместо Group By в строке Групповая операция под другими полями. First позволяет JET вернуть значение из первой совпадающей записи, без необходимости группировать по этому полю.

Это сильно меняет дело в ситуации с полями типа Memo. Если вы делаете GROUP BY по МЕМО-полю (Notes в примере), Аксесс сравнивает только первые 255 символов, а остальные просто отсекаются! Выбирая First вместо Группировка/Group By, JET может вернуть поле Memo полностью из первого же совпадения. Так что это не только более эффективно; это реально решает проблему усечения полей Memo.

(Минусом использования First является получение полем псевдонима, напр. FirstOfNotes.)

Прочие приемы оптимизации

Прочие предложения общего характера для оптимизации запросов в JET:

  • С многотабличными запросами по возможности используйте JOIN-ы. JET выполнит такой запрос быстрее, чем с предложением WHERE по внешнему ключу.
  • Возвращайте по возможности меньше полей. Это оптимизирует использование памяти и может сократить количество обращений к диску. Но включайте ключевые поля, чтобы дать JET-у быстрый способ идентифицировать записи.
  • Стройте строки запроса динамически,как показано здесь форма поиска. Особенно там, где пользователь будет вводить только несколько из возможных критериев, которые вы ему предоставляете. Это радикально упрощает критерии. Аксесс применяет фильтру интеллектуально. т.е. Filter или WhereCondition обычно применяются перед тем, как он запрашивает записи из файла с данными.
  • Избегайте множества таблиц на внешней стороне JOIN-а, так как JET может их неправильно интерпретировать.
  • Чтобы контролировать порядок выполнения, сохраните один запрос и используйте его как таблицу для другого запроса (вложенные запросы). Это важно, так как JET не считается со скобками в предложении FROM, когда он составляет план выполнения.
  • Подзапросы в общем менее эффективны, чем другие приемы (такие как JOIN-ы или вложенные сохраненные запросы), но более эффективны, чем доменные агрегатные функции.
  • Советы по перекрестным запросам см. Техника построений перекрестных запросов.
  • Используйте ShowPlan для JET для более подробной информации по поводу того, как JET планирует выполнение запроса.

Оптимизация запросов — огромная тема. Для дальнейшего чтения см. SQL Queries for Mere Mortals авторов Michael Hernandez и John Viescas.

Oracle mechanics

Производительность СУБД и смежные вопросы

Операция filter(NULL IS NOT NULL) и запросы со связанными переменными

При выполнении запроса с NULL-евыми значениями связанных переменных условия типа COLUMN1 = :VAR1 превращаются в COLUMN1 = NULL и по определению становятся невыполнимыми (в том смысле, что запрос с таким условием в WHERE не возвращает строк), но при построении плана / выполнении запроса оптимизатор не всегда использует эту возможность сэкономить ресурсы

Например, при выполнении практическоого запроса (судя по тексту, выполняющему проверку на совпадение введённого в поле формы текста с ID либо логином клиента) с пустым значением переменной:

— запрос выполняется так же, как и для не NULL-евых значений переменных, с тем же значительным количеством излишних в этом случае чтений блоков бд

Тот же запрос без использования связанных переменных отрабатывает намного эффективнее:

— общая стоимость выполнения запроса оказывается меньше стоимости слагаемых: при заведомо невыполнимых условиях ненужные операции доступа к бд исключаются оператором filter(NULL IS NOT NULL)

Или на тестовом примере:

— и опять несмотря на ненулевую стоимость сканирования T1, стоимость всего запроса = 0, что логично — запросу получения результата не нужны данные бд

Поскольку таблица в этом примере «свежая» (без статистики) в соответсвии со значением по умолчанию параметра:

дополнительно выполняется избыточная операция dynamic sampling после получения значений связанных переменных (что важно для запросов со связанными переменными), уже на этапе выбора метода доступа к таблице:

— что добавляет небольшой overhead при разборе (hard parse) во время первого выполнения запроса

Последующие выпонения уже разобранного запроса уже не читают блоки бд и не делают никаких сортировок за ненадобностью, как и ожидается:

К сожалению, замечательная операция filter(NULL IS NOT NULL) в явном виде не используется Oracle для того же запроса со связанными переменными:

— даже если при первом выполнении/разборе курсора (hard parse) в качестве значения связанной переменной использовался NULL — bind peeking в этом случае не помогает

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

— т.е., отображая в плане обычную операцию INDEX RANGE SCAN, предполагающую доступ к блокам индекса, Oracle в зависимости от значения связанной переменной может полностью исключать обращения к данным бд. Очень похоже на неявное использование аналога операции типа filter(NULL IS NOT NULL)

При не NULL-вом значении переменной индекс честно читается:

Важно отметить, что при выборе способа выполнения запроса (читать или не читать блоки) Oracle не использует статистику таблицы:

— т.о. в этом месте поведение Oracle основано на анализе значений связанных переменных (bind peeking) при каждом выполнении курсора на этапе soft parse:

Для первоначального практического запроса добиться того же эффекта можно с помощью пары дополнительных индексов + эквивалентно переформулировав текст запроса — заменив LEFT OUTER JOIN на UNION, таким образом приводя сложный запрос к объединению двух запросов с простыми одиночными предикатами:

— т.о. для NULL-евых значений связанных переменных Oracle может исключать логически избыточные чтения, заметно улучшая время выполнения запросов: с 3 секунд до 100 мс, например

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector