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

Откуда: канализация
Сообщений: 6615
чего то совсем голова перестала соображать, помогите пожалуйста переделать
AND (@BDate IS NULL OR FLOOR(CAST(AddDate AS FLOAT))>=@BDate)
          AND (@EDate IS NULL OR FLOOR(CAST(AddDate AS FLOAT))<=@EDate)

во что-нибудь позволяющее использовать поиск по индексу по AddDate
7 апр 14, 18:58    [15845663]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
AddDate between isnull(@BDate, '17530101') and isnull(@EDate, '99991231')
7 апр 14, 19:31    [15845760]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Или OPTION(Recompile) на крайняк

Да, да, грёбанный оптимизатор, мог бы и догадаться сам поставить Merge Interval.
А тем временем вышла очередная версия скуля ...
8 апр 14, 03:42    [15846969]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
спасибо
8 апр 14, 10:22    [15847534]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Всеж таки какая то ошибка есть
declare @AddDate datetime = '19820323 12:00:00'
		,@BDate datetime =  '19820323 00:00:01'
		,@Edate datetime = '19820325 00:00:00'

select 1 where @AddDate between isnull(@BDate, '17530101') and isnull(@EDate, '99991231')
select 1 where  (@BDate IS NULL OR FLOOR(CAST(@AddDate AS FLOAT))>=@BDate)
          AND (@EDate IS NULL OR FLOOR(CAST(@AddDate AS FLOAT))<=@EDate)
8 апр 14, 10:37    [15847641]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Мистер Хенки
select 1 where @AddDate between isnull(@BDate, '17530101') and isnull(@EDate, '99991231')
Не, не получится.
Сделать вычисляемое поле, обнуляющее время поля AddDate,
и для него сделать индекс. Условие накладывать на него.
8 апр 14, 10:46    [15847721]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
Мистер Хенки,

cast(AddDate as date) between isnull(@BDate, '17530101') and isnull(@EDate, '99991231')
Тоже sargable.
8 апр 14, 11:01    [15847820]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
invm,

как же тут используется индекс по AddDate?
Интересно просто.
Всё-таки, в индексе нет значений функции cast.
8 апр 14, 11:06    [15847862]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
iap,

От версии к версии оптимизатор становится умнее:
+
create table #t (dt datetime primary key);

insert into #t
values
 ('20140101 10:00'), ('20140101 11:00'), ('20140101 12:00'),
 ('20140102 10:00'), ('20140102 11:00'), ('20140102 12:00'),
 ('20140103 10:00'), ('20140103 11:00'), ('20140103 12:00'),
 ('20140104 10:00'), ('20140104 11:00'), ('20140104 12:00'),
 ('20140105 10:00'), ('20140105 11:00'), ('20140105 12:00'),
 ('20140106 10:00'), ('20140106 11:00'), ('20140106 12:00'),
 ('20140107 10:00'), ('20140107 11:00'), ('20140107 12:00'),
 ('20140108 10:00'), ('20140108 11:00'), ('20140108 12:00');
go

set statistics profile on;
go

select * from #t where cast(dt as date) between '20140102' and '20140104';
go

set statistics profile off;
go

drop table #t;
go
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4276.0 (X64) 
Feb 8 2013 10:37:00
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

select * from #t where cast(dt as date) between '20140102' and '20140104';
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1011], [Expr1012]))
|--Merge Interval
| |--Concatenation
| |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert('2014-01-02',NULL,(22))))
| | |--Constant Scan
| |--Compute Scalar(DEFINE:(([Expr1008],[Expr1009],[Expr1007])=GetRangeThroughConvert(NULL,'2014-01-04',(42))))
| |--Constant Scan
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#t]), SEEK:([tempdb].[dbo].[#t].[dt] > [Expr1010] AND [tempdb].[dbo].[#t].[dt] < [Expr1011]), WHERE:(CONVERT(date,[tempdb].[dbo].[#t].[dt],0)>='2014-01-02' AND CONVERT(date,[tempdb].[dbo].[#t].[dt],0)<='2014-01-04') ORDERED FORWARD)
8 апр 14, 11:20    [15847969]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
о, это видимо по типу like 'abc%' . Что же, очень интересно
8 апр 14, 11:25    [15847999]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
o-o
Guest
извиняюсь за вторжение в обсуждение предикатов :)
отвечу ТС-у на его вопрос:
Мистер Хенки
Всеж таки какая то ошибка есть
declare @AddDate datetime = '19820323 12:00:00'
		,@BDate datetime =  '19820323 00:00:01'
		,@Edate datetime = '19820325 00:00:00'

select 1 where @AddDate between isnull(@BDate, '17530101') and isnull(@EDate, '99991231')
select 1 where  (@BDate IS NULL OR FLOOR(CAST(@AddDate AS FLOAT))>=@BDate)
          AND (@EDate IS NULL OR FLOOR(CAST(@AddDate AS FLOAT))<=@EDate)

ошибка уже вот тут:
declare @AddDate datetime = '19820323 12:00:00'
		,@BDate datetime =  '19820323 00:00:01'
		,@Edate datetime = '19820325 00:00:00'

select 1 where  FLOOR(CAST(@AddDate AS FLOAT))>= @BDate 
-- смотрим, что сравнивается:        
select cast(FLOOR(CAST(@AddDate AS FLOAT)) as datetime), @BDate  
-------------------
1982-03-23 00:00:00.000	1982-03-23 00:00:01.000      
8 апр 14, 11:28    [15848021]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Интересно. Но результат неправильный:
create table #t (dt datetime primary key);

insert into #t
values
 ('20140101 10:00'), ('20140101 11:00'), ('20140101 12:00'),
 ('20140102 10:00'), ('20140102 11:00'), ('20140102 12:00'),
 ('20140103 10:00'), ('20140103 11:00'), ('20140103 12:00'),
 ('20140104 10:00'), ('20140104 11:00'), ('20140104 12:00'),
 ('20140105 10:00'), ('20140105 11:00'), ('20140105 12:00'),
 ('20140106 10:00'), ('20140106 11:00'), ('20140106 12:00'),
 ('20140107 10:00'), ('20140107 11:00'), ('20140107 12:00'),
 ('20140108 10:00'), ('20140108 11:00'), ('20140108 12:00');
go

set statistics profile on;
go

select * from #t where cast(dt as date) between '20140102 11:13:05.333' and '20140104 06:02:11.150';
go

set statistics profile off;
go

drop table #t;
Надо cast(dt as date) кастить к datetime, а тогда будет index scan
8 апр 14, 11:32    [15848049]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
o-o
Guest
iap
Интересно. Но результат неправильный


а почему результат "неправильный"?
вы же указали явно, что тип date ( cast(dt as date) ),
вот он и сравнивает только даты, не?

больше же нигде тип не указан, а '20140102 11:13:05.333' подходит и для задания значений типа date:

declare @dt date;
set @dt = '20140102 11:13:05.333';
select @dt
--------------------
2014-01-02


iap
Надо cast(dt as date) кастить к datetime

вот-вот.
с чем желаете работать, то и надо явно указать.
т.е. вы отбирали только по дате, заказав date, а я, например, отбираю только по времени,
заказав time (явно указав cast(dt as time)):

insert into #t
values
 ('20140101 10:00'), ('20140101 11:00'), ('20140101 12:00'),
 ('20140102 10:00'), ('20140102 11:00'), ('20140102 12:00'),
 ('20140103 10:00'), ('20140103 11:00'), ('20140103 12:00'),
 ('20140104 10:00'), ('20140104 11:00'), ('20140104 12:00'),
 ('20140105 10:00'), ('20140105 11:00'), ('20140105 12:00'),
 ('20140106 10:00'), ('20140106 11:00'), ('20140106 12:00'),
 ('20140107 10:00'), ('20140107 11:00'), ('20140107 12:00'),
 ('20140108 10:00'), ('20140108 11:00'), ('20140108 12:00');
go

select * from #t where cast(dt as time) between '20140102 10:13:05.333' and '20140104 11:15:11.150';
go

drop table #t;
----------------------------------
dt
2014-01-01 11:00:00.000
2014-01-02 11:00:00.000
2014-01-03 11:00:00.000
2014-01-04 11:00:00.000
2014-01-05 11:00:00.000
2014-01-06 11:00:00.000
2014-01-07 11:00:00.000
2014-01-08 11:00:00.000
8 апр 14, 12:31    [15848597]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
o-o,

сравниваю с первичным запросом просто.
Поэтому и неправильный.
Время у ограничивающих литералов обнуляется, ибо строка приводится к типу DATE.
А оно должно влиять на результат, судя по 15847641
8 апр 14, 12:55    [15848811]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
o-o
Guest
iap,
ну я про то и говорю.
сами приводите к типу date, он и интерпретирует следующие далeе константы как date.
привели бы к time, он бы дату обнулил.
просто это называется не "неправильный", а не тот результат, что хотелось :)
8 апр 14, 13:01    [15848877]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
iap
А оно должно влиять на результат, судя по 15847641
Как раз если там написать
select 1 where cast(@AddDate as date) between isnull(@BDate, '17530101') and isnull(@EDate, '99991231')
Запросы дадут одинаковый результат.
8 апр 14, 13:02    [15848893]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
o-o
iap,
ну я про то и говорю.
сами приводите к типу date, он и интерпретирует следующие далeе константы как date.
привели бы к time, он бы дату обнулил.
просто это называется не "неправильный", а не тот результат, что хотелось :)
Справедливости ради: это не я приводил
Хотя я очень благодарен invm за интересный пример.
8 апр 14, 13:04    [15848913]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
invm
iap
А оно должно влиять на результат, судя по 15847641
Как раз если там написать
select 1 where cast(@AddDate as date) between isnull(@BDate, '17530101') and isnull(@EDate, '99991231')

Запросы дадут одинаковый результат.
Это потому, что константы типа DATETIME.
Значит, cast(@AddDate as date) будет приводиться к DATETIME неявно.
Что там при этом будет с использованием индекса?
Не не INDEX SEEK, наверно?
8 апр 14, 13:07    [15848938]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
o-o
Guest
iap
Это потому, что константы типа DATETIME.


вот я что пытаюсь донести, а вы упорно игнорите.
константа, заданная как '20140102 10:13:05.333', она никакого не типа datetime.
т.е. если отдельно от всего рассмотреть '20140102 10:13:05.333'
select SQL_VARIANT_PROPERTY('20140102 11:13:05.333', 'basetype')
выдаст varchar.

сам способ задания константы со временем это никакая не гарантия задания именно datetime.
так что в выражении
cast(dt as date) between '20140102 10:13:05.333' and '20140104 11:15:11.150'

нету никакого datetime,
есть только date.

равно как и в выражении
cast(dt as time) between '20140102 10:13:05.333' and '20140104 11:15:11.150'

нету никакого datetime,
есть только time.
8 апр 14, 13:18    [15849029]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
o-o,

я вот это самое и утверждаю!
Строка приводится к DATE, если слева от "=" стоит CAST(AS DATE),
и CAST(AS DATE) неявно приводится к DATETIME, если справа стоят выражения для переменных типа DATETIME.
В первом случа имеем INDEX SEEK, но неправильный результат, а во втором обламывается INDEX SEEK.

Это всё, что я сказал invm по поводу предложенного им решения.
Про TIME я ничего ещё не говорил.
8 апр 14, 13:25    [15849100]     Ответить | Цитировать Сообщить модератору
 Re: surgable предикаты  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
iap
а во втором обламывается INDEX SEEK.
+
create table #t (dt datetime primary key, dummy char(8000)/*, dtc as cast(dt as date)*/);

insert into #t (dt)
values
 ('20140101 10:00'), ('20140101 11:00'), ('20140101 12:00'),
 ('20140102 10:00'), ('20140102 11:00'), ('20140102 12:00'),
 ('20140103 10:00'), ('20140103 11:00'), ('20140103 12:00'),
 ('20140104 10:00'), ('20140104 11:00'), ('20140104 12:00'),
 ('20140105 10:00'), ('20140105 11:00'), ('20140105 12:00'),
 ('20140106 10:00'), ('20140106 11:00'), ('20140106 12:00'),
 ('20140107 10:00'), ('20140107 11:00'), ('20140107 12:00'),
 ('20140108 10:00'), ('20140108 11:00'), ('20140108 12:00');
go

set statistics profile, io on;
go

declare @s datetime = '20140102 10:13:05.333', @e datetime = '20140104 11:15:11.150';

select dt from #t where cast(dt as date) between '20140102 10:13:05.333' and '20140104 11:15:11.150';
select dt from #t where cast(dt as date) between @s and @e;
go

set statistics profile, io off;
go

drop table #t;
go
dt
2014-01-02 10:00:00.000
2014-01-02 11:00:00.000
2014-01-02 12:00:00.000
2014-01-03 10:00:00.000
2014-01-03 11:00:00.000
2014-01-03 12:00:00.000
2014-01-04 10:00:00.000
2014-01-04 11:00:00.000
2014-01-04 12:00:00.000

RowsExecutesStmtText
91select dt from #t where cast(dt as date) between '20140102 10:13:05.333' and '20140104 11:15:11.150'
91 |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1011], [Expr1012]))
11 |--Merge Interval
21 | |--Concatenation
11 | |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert('2014-01-02',NULL,(22))))
11 | | |--Constant Scan
11 | |--Compute Scalar(DEFINE:(([Expr1008],[Expr1009],[Expr1007])=GetRangeThroughConvert(NULL,'2014-01-04',(42))))
11 | |--Constant Scan
91 |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#t]), SEEK:([tempdb].[dbo].[#t].[dt] > [Expr1010] AND [tempdb].[dbo].[#t].[dt] < [Expr1011]), WHERE:(CONVERT(date,[tempdb].[dbo].[#t].[dt],0)>='2014-01-02' AND CONVERT(date,[tempdb].[dbo].[#t].[dt],0)<='2014-01-04') ORDERED FORWARD)


dt
2014-01-03 10:00:00.000
2014-01-03 11:00:00.000
2014-01-03 12:00:00.000
2014-01-04 10:00:00.000
2014-01-04 11:00:00.000
2014-01-04 12:00:00.000

RowsExecutesStmtText
61select dt from #t where cast(dt as date) between @s and @e
61 |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1011], [Expr1012]))
11 |--Merge Interval
21 | |--Concatenation
11 | |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert(GetRangeWithMismatchedTypes([@s],NULL,(22)))))
11 | | |--Constant Scan
11 | |--Compute Scalar(DEFINE:(([Expr1008],[Expr1009],[Expr1007])=GetRangeThroughConvert(GetRangeWithMismatchedTypes(NULL,[@e],(42)))))
11 | |--Constant Scan
61 |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#t]), SEEK:([tempdb].[dbo].[#t].[dt] > [Expr1010] AND [tempdb].[dbo].[#t].[dt] < [Expr1011]), WHERE:(CONVERT(date,[tempdb].[dbo].[#t].[dt],0)>=[@s] AND CONVERT(date,[tempdb].[dbo].[#t].[dt],0)<=[@e]) ORDERED FORWARD)
8 апр 14, 14:02    [15849441]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить