Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Конструкция запроса для поиска / 22 сообщений из 22, страница 1 из 1
11.01.2022, 11:03
    #40125683
Kolu4ka
Kolu4ka
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
Всем привет!
Есть простая задача, искать данные по переменной, если переменная пустая, то возвращать все записи.
Работает вот так.
Код: sql
1.
2.
3.
4.
Declare @emailadr varchar(50)='email@mail.ru'
Select *
from dbo.UsersTable 
Where @emailadr is null or email=@emailadr


Но, по полю email есть индекс и при такой конструкции он не работает.
Можно как-то составить 1 запрос и без динамика, чтобы при заполненной переменной все таки начал использоваться индекс?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 11:21
    #40125688
komrad
komrad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
Kolu4ka,

уберите is null
если сиквел не использует индекс, то считает что дешевле просканировать таблицу (меньше I/O)

выполните и сравните результат

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
set statistics io, time on;

Declare @emailadr varchar(50)='email@mail.ru'

Select *
from dbo.UsersTable 
Where @emailadr is null or email=@emailadr

print '------------- INDEX IS FORCED'

Select *
from dbo.UsersTable  with (index=ВашИндекс)
Where @emailadr is null or email=@emailadr

set statistics io, time off;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 11:23
    #40125690
L_argo
L_argo
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Declare @emailadr varchar(50)='email@mail.ru'

if @emailadr is null
Select *
from dbo.UsersTable 

else 

Select *
from dbo.UsersTable 
Where email=@emailadr
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 11:28
    #40125691
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
Kolu4ka
Всем привет!
Есть простая задача, искать данные по переменной, если переменная пустая, то возвращать все записи.
Работает вот так.
Код: sql
1.
2.
3.
4.
5.
Declare @emailadr varchar(50)='email@mail.ru'
Select *
from dbo.UsersTable 
Where @emailadr is null or email=@emailadr
option (recompile)



Но, по полю email есть индекс и при такой конструкции он не работает.
Можно как-то составить 1 запрос и без динамика, чтобы при заполненной переменной все таки начал использоваться индекс?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 11:42
    #40125698
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
Kolu4ka,

@emailadr is null не очень хорошая идея, так как таблица может содержать большое количество строк.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 12:29
    #40125713
Kolu4ka
Kolu4ka
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
msLex, спасибо, все заработало как хотелось.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 13:31
    #40125731
DaniilSeryi
DaniilSeryi
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
Kolu4ka
Всем привет!
Есть простая задача, искать данные по переменной, если переменная пустая, то возвращать все записи.
Работает вот так.
Код: sql
1.
2.
3.
4.
Declare @emailadr varchar(50)='email@mail.ru'
Select *
from dbo.UsersTable 
Where @emailadr is null or email=@emailadr


Но, по полю email есть индекс и при такой конструкции он не работает.
Можно как-то составить 1 запрос и без динамика, чтобы при заполненной переменной все таки начал использоваться индекс?


Хотите прикол? Ваш запрос не будет возвращать ВСЕ ЗАПИСИ. Он будет возвращать записи, где поле email is null или где поле email равно значению переменной.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 13:37
    #40125734
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
DaniilSeryi
Kolu4ka
Всем привет!
Есть простая задача, искать данные по переменной, если переменная пустая, то возвращать все записи.
Работает вот так.
Код: sql
1.
2.
3.
4.
Declare @emailadr varchar(50)='email@mail.ru'
Select *
from dbo.UsersTable 
Where @emailadr is null or email=@emailadr



Но, по полю email есть индекс и при такой конструкции он не работает.
Можно как-то составить 1 запрос и без динамика, чтобы при заполненной переменной все таки начал использоваться индекс?


Хотите прикол? Ваш запрос не будет возвращать ВСЕ ЗАПИСИ. Он будет возвращать записи, где поле email is null или где поле email равно значению переменной.

брэхня !

там же переменная в запросе на НУЛЛ проверяется, а не поле
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 13:52
    #40125739
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
msLex,

по-моему, еще хинтом можно отключить parameter sniffing, но тогда оценка кардинальности будет происходить не по гистограмме, а по плотности, как для подсказки unknown.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 13:55
    #40125740
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
Владислав Колосов
msLex,

по-моему, еще хинтом можно отключить parameter sniffing


В данном случае нужно не отключать parameter sniffing, а явно использовать значение параметра при построении плана.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 14:43
    #40125766
komrad
komrad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
msLex
Владислав Колосов
msLex,

по-моему, еще хинтом можно отключить parameter sniffing


В данном случае нужно не отключать parameter sniffing, а явно использовать значение параметра при построении плана.

надо только упомянуть, что что каждая рекомпиляция (option (recompile)) - это расход ЦПУ и времени
стоит это учитывать при использовании, т.к. при частом вызове эффект будет значительным
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 14:56
    #40125776
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
komrad
msLex
пропущено...


В данном случае нужно не отключать parameter sniffing, а явно использовать значение параметра при построении плана.

надо только упомянуть, что что каждая рекомпиляция (option (recompile)) - это расход ЦПУ и времени
стоит это учитывать при использовании, т.к. при частом вызове эффект будет значительным

Все так, и есть альтернатива с if else, но при сколь-нибудь сложных запросах есть вероятность их рассинхронизации.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 15:45
    #40125802
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
Код: sql
1.
2.
3.
4.
5.
DECLARE @emailadr VARCHAR(50)='email@mail.ru'

SELECT * FROM dbo.UsersTable WHERE @emailadr IS NULL
UNION ALL
SELECT * FROM dbo.UsersTable WHERE email=@emailadr;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 15:56
    #40125807
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
Код: sql
1.
2.
3.
DECLARE @emailadr VARCHAR(50)='email@mail.ru';

SELECT * FROM dbo.UsersTable WHERE email=ALL(SELECT @emailadr WHERE @emailadr IS NOT NULL);

Но в этом варианте проверьте, устроит ли вас план!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 15:57
    #40125808
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
iap
Код: sql
1.
2.
3.
4.
5.
DECLARE @emailadr VARCHAR(50)='email@mail.ru'

SELECT * FROM dbo.UsersTable WHERE @emailadr IS NULL
UNION ALL
SELECT * FROM dbo.UsersTable WHERE email=@emailadr;



главное SET ANSI_NULLS OFF не делать
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 16:08
    #40125814
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
msLex
iap
Код: sql
1.
2.
3.
4.
5.
DECLARE @emailadr VARCHAR(50)='email@mail.ru'

SELECT * FROM dbo.UsersTable WHERE @emailadr IS NULL
UNION ALL
SELECT * FROM dbo.UsersTable WHERE email=@emailadr;




главное SET ANSI_NULLS OFF не делать
Истину глаголите!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 17:39
    #40125843
Ролг Хупин
Ролг Хупин
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
iap
Код: sql
1.
2.
3.
4.
5.
DECLARE @emailadr VARCHAR(50)='email@mail.ru'

SELECT * FROM dbo.UsersTable WHERE @emailadr IS NULL
UNION ALL
SELECT * FROM dbo.UsersTable WHERE email=@emailadr;



кто бы объяснил задумку
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 17:54
    #40125846
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
Ролг Хупин
iap
Код: sql
1.
2.
3.
4.
5.
DECLARE @emailadr VARCHAR(50)='email@mail.ru'

SELECT * FROM dbo.UsersTable WHERE @emailadr IS NULL
UNION ALL
SELECT * FROM dbo.UsersTable WHERE email=@emailadr;




кто бы объяснил задумку
Первый SELECT возвращает все строки, если параметр IS NULL, второй SELECT - записи, соответствующие параметру в случае, если параметр IS NOT NULL.
При этом в условии отсутствует OR, который в первоначальном варианте тормозит запрос.
Кстати, как получить записи, для которых email IS NULL, - это большой вопрос, поскольку значению параметра NULL придаётся особый смысл.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 18:00
    #40125851
Kolu4ka
Kolu4ka
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
iap, с юнионами точно не то.

Дело в том, что я привела задачу частично, более полно выглядит она примерно так, параметров сколько угодно и не все могут быть заполнены.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
Declare @emailadr varchar(50)='email@mail.ru'
, @name varchar(50)='Victor'
, @fam varchar(50)='Ivanov'

Select *
from dbo.UsersTable 
Where (@emailadr is null or email=@emailadr)
and (@name is null or uname=@name )
and (@fam is null or famil=@fam)


На некоторых полях есть индексы, но при таком запросе всегда возникали только сканы, при добавлении option (recompile) начали подхватываться индексы и запрос начал работать быстро.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 04:42
    #40125932
Ennor Tiegael
Ennor Tiegael
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
Kolu4ka,

В таком случае единственный вариант - это динамическая генерация запроса, либо на клиенте, либо в хранимке. Второе не рекомендую, я так делал однажды, задолбался код отлаживать. Если же клиентское приложение использует Entity Framework или какой другой ORM, то там это будет тривиально.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 17:22
    #40126073
Ролг Хупин
Ролг Хупин
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
iap
Ролг Хупин
пропущено...


кто бы объяснил задумку
Первый SELECT возвращает все строки, если параметр IS NULL, второй SELECT - записи, соответствующие параметру в случае, если параметр IS NOT NULL.
При этом в условии отсутствует OR, который в первоначальном варианте тормозит запрос.
Кстати, как получить записи, для которых email IS NULL, - это большой вопрос, поскольку значению параметра NULL придаётся особый смысл.


Разве Union all- это не OR в даном случае?
почемуне сделать одним запросом что-то типа:

Код: sql
1.
SELECT * FROM dbo.UsersTable WHERE (@emailadr IS NULL OR (@emailadr IS NOT NULL AND email=@emailadr))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 17:39
    #40126077
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Конструкция запроса для поиска
Ролг Хупин
iap
пропущено...
Первый SELECT возвращает все строки, если параметр IS NULL, второй SELECT - записи, соответствующие параметру в случае, если параметр IS NOT NULL.
При этом в условии отсутствует OR, который в первоначальном варианте тормозит запрос.
Кстати, как получить записи, для которых email IS NULL, - это большой вопрос, поскольку значению параметра NULL придаётся особый смысл.


Разве Union all- это не OR в даном случае?
почемуне сделать одним запросом что-то типа:

Код: sql
1.
SELECT * FROM dbo.UsersTable WHERE (@emailadr IS NULL OR (@emailadr IS NOT NULL AND email=@emailadr))

Ну не любит сервер OR. Тяжело ему оптимизировать. А логически-то, конечно, не поспоришь.
Переделывание OR на UNION - это хорошо известный способ оптимизации запроса.
Но тут говорят: таких условий слишком много... А первоначальный вопрос - это совсем не то, что подразумевалось. Во как!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Конструкция запроса для поиска / 22 сообщений из 22, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 579ms
Закрыть
start [/forum/topic.php?fid=46&tablet=1&tid=1683940]:
 0ms
get settings:
 11ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 217ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 60ms
get tp. blocked users:
 2ms
others: 252ms
total:  579ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]