пятница, 22 июля 2011 г.

MSSQL : Оптимизация запросов - убираем корреляцию в запросах.

Собственно нашел давно картинку, которая очень помогает в решении задач по оптимизации.
Например когда надо проверить (не)существование значений в другой таблице по заданным параметрам.

Самый простой вариант НЕ ПРАВИЛЬНЫХ запросов выглядит следующим образом
Вывести строки из t1, на которые есть ссылки в таблице t2
select * from t1
where exists(select * from t2 where t2.t1id = t1.id)

или на оборот,
select * from t1
where not exists(select * from t2 where t2.t1id = t1.id)


некоторые программисты начали пытаться ускорить код следующиими вариантами
select * from t1
where exists(select top 1 1 from t2 where t2.t1id = t1.id)

или на оборот,
select * from t1
where not exists(select top 1 1 from t2 where t2.t1id = t1.id)


Честно говоря я понимаю что есть ситуации когда без таких конструкций не обойтись, и в моей практике такое тоже встречалось. Но все же лучше пользоваться "умножения" таблиц
Получение строк, на которые есть ссылки в подчиненной таблице
select distinct t1.*
from t1
inner join t2 on t2.t1id = t1.id

И получение строк, на которые нет ссылок в подчиненной таблице
select distinct t1.*
from t1
left join t2 on t2.t1id = t1.id
where t2.id is null


Вобщем пользуйтесь на здоровье, и избегайте не нужной работы серверов

MSSQL : Оптимизация запросов - убираем TOP 1

Иногда бывает необходимо получить только некие "верхние" записи из подчиненной таблицы
Кто-то делает так:
select *
, (select top 1 t2.id from t2 where t1Id = t1.id)
, (select top 1 t2.someData from t2 where t1Id = t1.id)
from t1


Чем плох такой вариант? А плох он следующим.
Мы ориентируемся на некое "верхнее" значение, которое может не относиться в нужной нам последовательности в итоге.
Кроме этого, у нас может возникнуть ситуация когда вызовы "верхнего" значения будут относиться к разным строкам таблицы.
Что тогда будете делать?
Поэтому я предлагаю использовать агрегирующий подзапрос

select *
from t1
inner join (select max(id) id, t1Id from t2 group by t1Id) T2i on t1.id = T2i.t1Id
inner join t2 on t2.id = T2i.id

В большинстве своем мы получим более лучший план запроса, эффективность во времени, предсказуемость (точность) результата.

вторник, 5 июля 2011 г.

MSSQL : Уровни изоляции в MS SQL - READ UNCOMMITTED

READ UNCOMMITTED
Уровень изоляции READ UNCOMMITTED позволяет читать данные, измененные другими транзакциями, но еще не зафиксированными. Транзакции, запущенные с таким уровнем изоляции, не блокируют строки/объекты с которыми оперируют. Чтение в таком режиме также носит название "грязное чтение".
Так как READ UNCOMMITTED не блокирует доступ к своим объектам и может работать с заблокированными объектами, то может возникнуть ситуация, когда данные будут то появляться, то исчезать.

Откроем теперь 3 окна запроса
в 1-м окне введем:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
GO
BEGIN TRANSACTION

select * from t1

update t1
set textdata = 'thread 1'

select * from t1
GO
во 2-м окне
SET TRANSACTION ISOLATION LEVEL READ COMMITED
GO
BEGIN TRANSACTION

select * from t1

update t1
set textdata = 'thread 2'

select * from t1

COMMIT TRANSACTION
GO

в 3-м окне:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
GO
BEGIN TRANSACTION

select * from t1

update t1
set textdata = 'thread 3'

select * from t1

COMMIT TRANSACTION
GO
Запустите первый пакет, потом 2-й и 3-й.
Мы увидим, что если в 1-м пакете не закрыта транзакция, то 2-й и 3-й пакеты будут ожидать закрытия, но ожидать они будут в разных точках.
2-й пакет будет ожидать чтения (1-й select)
3-й пакет будет ожидать записи/обновления

MSSQL : Уровни изоляции в MS SQL - READ COMMITTED

Все существует 5 уровней изоляции транзакций
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SNAPSHOT
SERIALIZABLE

READ COMMITTED
В режиме READ COMMITTED инструкции sql запроса будут читать только те данные, которые были зафиксированы предыдущими/параллельными транзакциями. Если же на читаемы данные была наложена блокировка, то текущий пакет инструкций будет ожидать высвобождения ресурса. Например, есть два пакета инструкций, запущенных одновременно, рассмотрим пример, в Microsoft SQL Manegment Studio откроем 2 окна запроса, в первом запустите следующий код
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
SELECT * FROM t1

UPDATE t1
SET textdata = 'Thread_1'
WHERE row = 1

-- !!!Важно!!! - не фиксируем транзакцию
GO
во втором запускаем аналогичный код
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
SELECT * FROM t1

UPDATE t1
SET textdata = 'Thread_2'
WHERE row = 1

SELECT * FROM t1

COMMIT TRANSACTION
GO
В результате, если запустить 1-й код а следом запустить 2-й, мы получим во 2-м коде ожидание завершения транзакции в первом окне.
Добавьте в первое окно следующую строку:
COMMIT TRANSACTION

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


момент времени123456
запрос 1начало транзакциивнесение измененийфиксация изменений
запрос 1начало транзакцииожидание запроса 1внесение измененийфиксация транзакции

Базы Данных

Думаю придтся начать "цикл" статей по моему основному профилю - базы данных.
Причина - надоело постоянно искать ответы на уже не однократно заданные мною вопросы.
С одной стороны это будет handbook за моим авторством, с другой - BookOnLine, третей - перевод понравившихся статей.
Да!!! Я не люблю редмоновского гиганта, но в большинстве своем работаю именно с ним, таковы реалии.