Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 выбор в where  [new]
Евгенич
Member

Откуда:
Сообщений: 274
Добрый день!
Прошу помощи!
Написал процедуру с двумя входными параметрами @date,@DealTransactID которые могут быть null

Необходимо заставить процедуру работать, когда на вход подан один из параметров,
в зависимости от параметра фильтровать запрос по @date или по @DealTransactID

Как вариант попробовал написать вот так - но оччень медленно!
select
...
where  Dt.DealTransactID=isnull(@DealTransactID,Dt.DealTransactID)
	 and
	 (Dt.Date=isnull(@date,Dt.Date))


Заранее благодарю за ответы!
24 сен 13, 12:09    [14876632]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
aleks2
Guest
Два параметра = 4 варианта => проще написать ЧЕТЫРЕ запроса в if-ах.
24 сен 13, 12:13    [14876661]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
prog882
Guest
Евгенич,
select ... where (@DealTransactID is null or Dt.DealTransactID = @DealTransactID)
and (@Date is null or Dt.Date=@Date)
24 сен 13, 12:20    [14876713]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
mike909
Member

Откуда:
Сообщений: 662
Евгенич,

Евгенич
Необходимо заставить процедуру работать, когда на вход подан один из параметров,
в зависимости от параметра фильтровать запрос по @date или по @DealTransactID

Т.е. два параметра одновременно не нужны ?
Может тогда так ?
create procedure dbo.BlaBlaBla(@DateOrTransactionID SQL_VARIANT)
begin
  if cast(SQL_VARIANT_PROPERTY(@DateOrTransactionID, 'BaseType') as sysname) in ('int','bigint','smalint')
    select .... where Dt.DealTransactID = cast(@DateOrTransactionID as int)
  else
    select .... where Dt.Date = cast(@DateOrTransactionID as datetime)
end;
24 сен 13, 13:58    [14877372]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
Евгенич
Member

Откуда:
Сообщений: 274
aleks2
Два параметра = 4 варианта => проще написать ЧЕТЫРЕ запроса в if-ах.


Текст запроса очень громоздкий, хоцца покомпактней
24 сен 13, 15:36    [14878052]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
Евгенич
Member

Откуда:
Сообщений: 274
prog882
Евгенич,
select ... where (@DealTransactID is null or Dt.DealTransactID = @DealTransactID)
and (@Date is null or Dt.Date=@Date)


Попробовал, когда фильтр по дате - выполнение 3 секунды, фильтр по ID - 13 секунд - не пойму почему? DealTransactID numeric (15,0) Date - datetime
24 сен 13, 15:38    [14878067]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
Евгенич
Member

Откуда:
Сообщений: 274
mike909
Евгенич,

Евгенич
Необходимо заставить процедуру работать, когда на вход подан один из параметров,
в зависимости от параметра фильтровать запрос по @date или по @DealTransactID

Т.е. два параметра одновременно не нужны ?
Может тогда так ?
create procedure dbo.BlaBlaBla(@DateOrTransactionID SQL_VARIANT)
begin
  if cast(SQL_VARIANT_PROPERTY(@DateOrTransactionID, 'BaseType') as sysname) in ('int','bigint','smalint')
    select .... where Dt.DealTransactID = cast(@DateOrTransactionID as int)
  else
    select .... where Dt.Date = cast(@DateOrTransactionID as datetime)
end;


Не знал таких выкрутасов! приму к сведению. Но текст запроса очень громоздкий - не пойдет.
24 сен 13, 15:40    [14878082]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
Евгенич
Member

Откуда:
Сообщений: 274
Вот так вроде работает и быстро:

where
((@DealTransactID is not null) and (Dt.DealTransactID=@DealTransactID))
	OR ((@Date is not null) and (Dt.Date=@date))
24 сен 13, 15:48    [14878162]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
option (recompile)
Guest
Евгенич
Вот так вроде работает и быстро:

where
((@DealTransactID is not null) and (Dt.DealTransactID=@DealTransactID))
	OR ((@Date is not null) and (Dt.Date=@date))

...
option (recompile)
24 сен 13, 15:55    [14878246]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
Гость333
Member

Откуда:
Сообщений: 3683
option (recompile)
Евгенич
Вот так вроде работает и быстро:

where
((@DealTransactID is not null) and (Dt.DealTransactID=@DealTransactID))
	OR ((@Date is not null) and (Dt.Date=@date))

...
option (recompile)

Рекомпайл не нужен.
Так же, как и условия "@DealTransactID is not null" и "@Date is not null".
24 сен 13, 16:06    [14878325]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
Евгенич
Member

Откуда:
Сообщений: 274
Гость333
option (recompile)
пропущено...

Рекомпайл не нужен.
Так же, как и условия "@DealTransactID is not null" и "@Date is not null".


Вы правы, а почему не нужен is not null?
24 сен 13, 16:17    [14878399]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Евгенич
а почему не нужен is not null?

Потому что условие "Dt.DealTransactID=@DealTransactID" не может выполниться, если @DealTransactID = NULL.
24 сен 13, 16:34    [14878529]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
option (recompile)
Guest
Гость333
option (recompile)
пропущено...

Рекомпайл не нужен.
Так же, как и условия "@DealTransactID is not null" и "@Date is not null".

ой-ли?


if object_id('dbo.tbl') is not null
	drop table dbo.tbl
create table dbo.tbl (id int identity not null primary key, id1 int not null, id2 int not null, c char(4000) not null)
create index id1 on dbo.tbl(id1) include (id2, c)
create index id2 on dbo.tbl(id2) include (id1, c)
go
if object_id('dbo.proc1') is not null
	drop proc dbo.proc1 
go
create proc dbo.proc1 
	@id1 int
	, @id2 int
as
	select id1, id2, c
	from dbo.tbl
	where
		id1 = @id1
		or id2 = @id2
go	
if object_id('dbo.proc2') is not null
	drop proc dbo.proc2 
go	
create proc dbo.proc2
	@id1 int
	, @id2 int
as

select id1, id2, c
from dbo.tbl
where
	(@id1 is not null and id1 = @id1)
	or (@id2 is not null and id2 = @id2)
option (recompile)	
go		

insert dbo.tbl(id1, id2, c)
select top 1000
	SIGN(checksum(newid()))
	, SIGN(checksum(newid()))
	, ''
from sys.objects	o1
cross join sys.objects	o2
go

set statistics time on
exec dbo.proc1
	1,
	null
exec dbo.proc1
	null
	, 1

exec dbo.proc2
	1,
	null
	
exec dbo.proc2
	null
	, 1
set statistics time off


запускал раз 20, у процедуры с option (recompile) и "...is not null and..." elapsed time лучше, да и актуальный план "красивее".
рекомпайл, конечно тоже требует время, но я бы все же не был столько категоричен
24 сен 13, 16:39    [14878562]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
Гость333
Member

Откуда:
Сообщений: 3683
option (recompile)
запускал раз 20, у процедуры с option (recompile) и "...is not null and..." elapsed time лучше, да и актуальный план "красивее".

Актуальный план красивее, факт.

Насчёт elapsed time не уверен. Я убрал из вашего примера вывод результатов в SSMS, заменив
select id1, id2, c
from dbo.tbl
на
declare @a int, @b int, @c char(4000);

select @a = id1, @b = id2, @c = c
from dbo.tbl

Также убрал set statistics time on, добавил set nocount on, а длительность отслеживал в профайлере по событию SP:Completed, колонка Duration.

В таких условиях оба вызова proc1 (которая без recompile) заняли по 1 мс, а вызовы proc2 (которая с recompile) заняли 2 мс и 3 мс.
24 сен 13, 17:31    [14878927]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Гость333,
всеж таки четыре if порвут и тот и другой способ .
24 сен 13, 17:40    [14878976]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Мистер Хенки
Гость333,
всеж таки четыре if порвут и тот и другой способ .

Не знаю насчёт "порвут", скорее скромно сэкономят энное количество миллисекунд :)
А учитывая, что "текст запроса очень громоздкий", такой вариант будет несколько печален в сопровождении ;)
24 сен 13, 17:52    [14879051]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
option (recompile)
Guest
Гость333
option (recompile)
запускал раз 20, у процедуры с option (recompile) и "...is not null and..." elapsed time лучше, да и актуальный план "красивее".

Актуальный план красивее, факт.

Насчёт elapsed time не уверен. Я убрал из вашего примера вывод результатов в SSMS, заменив
select id1, id2, c
from dbo.tbl
на
declare @a int, @b int, @c char(4000);

select @a = id1, @b = id2, @c = c
from dbo.tbl

Также убрал set statistics time on, добавил set nocount on, а длительность отслеживал в профайлере по событию SP:Completed, колонка Duration.

В таких условиях оба вызова proc1 (которая без recompile) заняли по 1 мс, а вызовы proc2 (которая с recompile) заняли 2 мс и 3 мс.



у меня при этом

declare @a int, @b int, @c char(4000);
select @a = id1, @b = id2, @c = c


"ломается" план запроса с рекомпайл (появляется два seek-а + фильтры + merge join),

переписал на
declare @t table (id1 int not null, id2 int not null, c char(4000) not null)
	
	insert @t
	select id1, id2, c


оценивая тоже событие в профайлере, получил идентичные(на восьми запусках суммарное время 37 vs 38 мс) показатели.
24 сен 13, 18:10    [14879139]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
Гость333
Member

Откуда:
Сообщений: 3683
option (recompile)
"ломается" план запроса с рекомпайл

Да, действительно, это я проморгал, извиняюсь.
Со вставкой во временную таблицу у меня получился какой-то дикий разброс времён выполнения процедур (от 10 до 70 мс при нескольких последовательных вызовах одной и той же процедуры с одними и теми же параметрами). Даже не знаю, чем это можно объяснить.
24 сен 13, 18:23    [14879209]     Ответить | Цитировать Сообщить модератору
 Re: выбор в where  [new]
option (recompile)
Guest
Гость333
option (recompile)
"ломается" план запроса с рекомпайл

Да, действительно, это я проморгал, извиняюсь.
Со вставкой во временную таблицу у меня получился какой-то дикий разброс времён выполнения процедур (от 10 до 70 мс при нескольких последовательных вызовах одной и той же процедуры с одними и теми же параметрами). Даже не знаю, чем это можно объяснить.

мое имхо, если запрос не очень сложный (читай план строится быстро), лучше с рекомпайл, ибо обращение к ненужным объектам, в моем примере к другому индексу, не есть хорошо, можно же и на какой SERIALIZABLE "нарваться".
24 сен 13, 18:32    [14879250]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить