Comments 19
Еще индекс с отфильтрацией "левых" значений надо бы упомянуть.
да, вот помнил что чтото забыл. Сейчас допишу
добавил, но может ктото знает решения проблемы с WHERE индексом, которая там описана?
Не за компом. Вот это разве не сработает?
select *
from Unlucky with (index=val)
where val<>'n/a'
and val=@valНет, не настолько умный оптимизатор пока
Все нормально работает. Просто нужно указывать точный фильтр в запросе, такой же как и в фильтрованном индексе. Будет работать и для сложных условий по нескольким столбцам тоже.
create index IDX_VAL on Unlucky (VAL) where VAL <> 'n/a'
go
create or alter procedure SmartGetVal
@val varchar(32)
as
if @val = 'n/a'
select *
from dbo.Unlucky
where VAL = 'n/a' -- not @val!!!
else
select *
from dbo.Unlucky with ( index = IDX_VAL )
where VAL = @val
and VAL <> 'n/a'
go
exec dbo.SmartGetVal @val = '1val'
go
print @@version
Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64)
Nov 2 2020 19:19:59
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19044: )
Если половина одинаковых значений, то сама собой напрашивается нормализация базы данных.
К примеру в таблице 15 столбцов, один из столбцов (DataStatus) содержит 50% одинаковых значений (значение Completed), и что вы предлагаете, как нормализовать такую таблцу?
Перенести Completed записи в другую таблицу? А потом мучиться с отчетами, и с процессами по переносу данных и одной таблиы в другую. Может кто-то захочит перевести статус в Active? А если нужно обновить данные другого столбца то придется обновлять две таблицы, делать два запроса, писать "умный" DataAccess. В общем это проблема из ничего.
Нормализация хороша в теории, на практике выше 3-й нормальной формы заморачиватся не стоит.
Очень мало кто делает оптимизацию путём изменения схемы. Все предпочитают фокусы с инексацией, хинты и т.п.
Секционирование, кстати, можно ещё применять. Но тогда несбалансированное поле надо в первичный ключ загонять (если конечно СУБД не умеет делать партиционирование по произвольному уникальному индексу, а только по первичному ключу), а это почит всегда невозможно и всегда неудобно.
Второй подход заключается в использовании динамического SQL:
А почему не использовать sp_executesql? Проблему с SQL Injection он по крайней мере решит.
И тут вы опять попадете в Parameter Sniffing. Тут надо или инлайнить параметры или добавлять к запросу OPTION(...).
Есть вариант перекидывать входящие переменные в локальные. Кстати в 2022й эту беду обещают победить и сделать хранение несколько планов для процедур и автоматически выбирать самый подходящий.
Можно ставить "метку" на запрос.
В данном примере для n/a значения будет другой текст запроса, а значит и другой план (даже коммент в текте запроса меняет его хеш).
create or alter procedure dbo.SmartGetValDyn
@val varchar(32)
as
declare @Sql nvarchar(max) = concat(
iif(@val='n/a', '-- n/a query', ''),
'
select *
from dbo.Unlucky
where VAL = @val
')
print @Sql
exec sys.sp_executesql @Sql, N'@val varchar(32)', @val = @val
go
exec dbo.SmartGetValDyn @val = 'n/a'
exec dbo.SmartGetValDyn @val = '1val'
goКак видите, тут два разных плана. Причем их будет только 2, а не миллион как с инлайном где также присутствует проблема sql injection.
Чем плох набор фильтров:
and (@userid IS NULL or userid=@userid)и на что можно заменить?
Статью прочёл, но так и не понял как оптимизировать запросы %)
тем что если userid известен, то SQL не может использовать индекс по нему
либо писать явные варианты (при 3 таких параметров их 8, при 4 - 16), либо динамический SQL
тут ещё может спасти OPTION(RECOMPILE) для отдельного запроса:
where ...
and (@userid IS NULL or userid=@userid)
and (@companyid IS NULL or companyid=@companyid)
and (@deptid IS NULL or deptid=@deptid)
OPTION(RECOMPILE)
когда значения переменных уже известны, то план меняется в лучшую сторону. хотя конечно платим временем за рекомпиляцию запроса.
оптимизатор MySQL умеет такое кушать, но тоже оч ограниченно
С чем кушать Irregular Selectivity в MSSQL и не только