Может быть я сейчас опишу известный факт, но для меня он стал открытием только в последние полгода. И его до сих пор не исправили.(Причем такая байда и во всех АСА6.0х)
ASA плохо работает с индексом по полю с типом данных date. Оптимизатор то и дело игнорирует индекс, и вообще выдает крайне не выгодные планы запросов по такому полю. (Может дело конечно в величине таблицы?!)
Пример:
Таблица branch_cash (12 млн)
Запрос1:
select branch_id,sum(costrozn*qty) as sum_rozn,sum(qty) as sum_qty
from branch_cash
where date_cash between '2005.05.08' and '2005.05.09'
group by branch_id;
План1:
( Plan [ Total Cost Estimate: 131.138582 ]
( WorkTable
( HashGroupBy
( TableScan branch_cash[ ( 2005-05-08 <= branch_cash.date_cash <= 2005-05-09 ) : .1812673785% Statistics ] )
)
)
)
НО!!!:
Запрос2:
select branch_id,sum(costrozn*qty) as sum_rozn,sum(qty) as sum_qty
from branch_cash
where date_cash between '2005.05.10' and '2005.05.13'
group by branch_id;
План2:
( Plan [ Total Cost Estimate: 60.62579409 ]
( WorkTable
( HashGroupBy
( IndexScan branch_cash index_date_cash )
)
)
)
Уверяю, что в выборке с 10 по 13 используется больше записей чем с 8 по 9, но АСА почему-то решила, что при выборке с 8 по 9 нужно сканить таблицу.
Сечас не могу повторить, но была еще такая ситуация: при выборке с "d between d1 and d2" индекс использовался, а в "d between d1 and d1" - нет, а использовалось сканирование таблицы(!!!!!).
При чем такое поведение сервера не наблюдалось при выборках по проиндексированым полям других типов.
Я конечно понимаю, что видимо в конкретном случае у сервера нет статистики по данному полю со значением ранее чем 10.09.05. Но странно то, что даже написав в первом запросе подсказку INDEX FORCE и он, выполнив запрос за миллисекунды, не запоминает, что такой путь короче!!! Т.е. стираем подсказку, и он опять нам выдает план со сканированием...
Запарили меня эти поля с датами. Буду переделывать все на integer, и буду считать дни от некой базы.
Может я не знаю какой-то общеизвестный факт??????????
P.S.:Если попробывать все-таки выполнить первый запрос (ну мало ли что в плане написано))), то я прождал полчаса, дальше не стал. И что поразительно, АСА не откорректировал свою статистику и упорно лезет в сканирование.

Хорошо, помогло, но надолго ли?
Три дня назад запрос с 8 по 9 выполнялся, но не выполнялся за более ранние периоды. Где гарантия что это не повторится? Или мне надо всегда ее(create statistics) вызывать после заливки данных?
P.S.:А почему тогда с integer такого никогда не было?

А для тех баз, которые на АСА6? Там нет создания статистики.
P.S.: Статистика помогла, но не для всех запросов. Вот на такой запрос:
select *
from branch_cash
where date_cash between dateadd(day,-1,today()) and today() ;
Вот такой план:
( Plan [ Total Cost Estimate: 30.738638 ]
( TableScan branch_cash[ ( expr( branch_cash.date_cash ) >= expr( 3, -1 ) : 25% Guess ) AND ( branch_cash.date_cash <= expr() : 100% Statistics ) ] )
)
Так что увы и ах. С датами что-то не то...

Вот, пересоздал статистику, по таблице и по полю. С таблицей никто кроме меня не работает.
Смотрите:
Запрос1:
select *
from branch_cash
where date_cash in ( dateadd(day,-1,today()) ,today());
План1:
( Plan [ Total Cost Estimate: 30.738638 ]
( TableScan branch_cash[ ( expr( branch_cash.date_cash ) = expr( 3, -1 ) : .1953125% Column ) OR ( branch_cash.date_cash = expr() : .0001% Statistics ) : .1954125% Combined ] )
)
Запрос2:
select *
from branch_cash
where date_cash=dateadd(day,-1,today());
План2:
( Plan [ Total Cost Estimate: .0602513906 ]
( IndexScan branch_cash index_date_cash[ expr( branch_cash.date_cash ) = expr( 3, -1 ) : .1953125% Column ] )
)
Запрос3:
select *
from branch_cash
where date_cash=today();
План3:
( Plan [ Total Cost Estimate: .0002457386 ]
( IndexScan branch_cash index_date_cash )
)
P.S.: А вот в таком случае статистика вообще не причем, это чистая работа оптимизатора: делаем выборку "d between d1 and d2" - чтение таблицы, делаем "d in (d1,...,d2)" - как не удивительно, через индекс. Складывается впечатление, что дату, а точнее индекс по ней АСА хранит в каком-то "неюзабельном" виде.

Александр Гoлдун
О, вот оно! Заливка данных.
Заливка Insert'ами, а не load'ом. Это я так выразился просто.

Ладно с АСА6, а тут-то как? Перестроил статистику, так ведь не помогло! (см.выше)
Что с этим-то делать?

Между прочем, IN, BETWEEN, >=,<= - это не тоже самое, что OR, статью о предикатах я читал. А то, что оптимизатор решил перевести IN в OR - его проблемы.
Во всяком случае, на запрос
select *
from branch_cash
where date_cash >= dateadd(day,-1,today());
идет чтение таблицы. А если бы поле было числовым, то сервер схватил бы индекс.
А индекс составной, (date_cash desc,branch_id asc), а что в таком случае меняется? Ну пусть ход мыслей оптимизатора будет другой, меня-то это не должно трогать, главное чтобы план был нормальный. Ведь с подсказкой-то делает через индекс, зараза, и за доли секунды.
Хотя я пробовал и простой индекс делать и больше никаких индексов на тестовой таблице. И все равно теже проблемы.

P.S.:Я же писал про случай, когда в запросе были перечислены все даты диапазона через IN и АСА делал такой запрос через индекс . А когда тот же диапазон написал через between или >=, то использовал чтение таблицы.
Неужели ни у кого нет проблем выборок по дате?

Лучше заведу-ка я поле smallint, вычисляемое от даты, как i=date_cash - convert(date,'2000.01.01'). И по нему индекс.
Все-таки smallint по-лучше будет чем varchar. Попробую так.

Блин, вот зараза!!!!!!! Остается только ругаться....
Сделал computed field day_id, с его участием составной индекс(day_id,branch_id)
И вот какие результаты:
Запрос1:
select *
from branch_cash
where day_id >= convert(date,'2005.05.14') - convert(date,'2000.01.01') - 10 ;
План1:
( Plan [ Total Cost Estimate: 30.738638 ]
( TableScan branch_cash[ expr( branch_cash.day_id ) >= expr( '2005.05.14', -1, '2000.01.01', -1, 10 ) : 25% Guess ] )
)
Запрос2:
select *
from branch_cash
where day_id >= 1950;
План2:
( Plan [ Total Cost Estimate: .919294 ]
( IndexScan branch_cash day_branch_key[ branch_cash.day_id >= 1950 : 2.98998009% Statistics ] )
)
Запросы-то одинаковые!!! Я так понял, по выделенному синим, оптимизатор считает что над полем совершается какая-то функция и ее результат используется для отбора, поэтому и индекс не использует. А на самом-то деле никакой функции нет.
Ладно хоть можно по константе отбирать с использованием индекса.

И еще одна сопутствующая проблема при выборках по дате:
Запрос1:
select *
from branch_cash
where branch_cash.date_cash>=dateadd(day,-60,convert(date,'2005.05.04')) and
branch_cash.date_cash<=current date;
План1:
( Plan [ Total Cost Estimate: 30.738638 ]
( TableScan branch_cash[ ( expr( branch_cash.date_cash ) >= expr( 3, -60, '2005.05.04', -1 ) : 25% Guess ) AND ( branch_cash.date_cash <= expr() : 100% Statistics ) ] )
)
Запрос2:
select *
from branch_cash
where branch_cash.date_cash>=dateadd(day,-60,convert(date,'2005.05.04')) and
branch_cash.date_cash<=convert(date,'2005.05.14');
План2:
( Plan [ Total Cost Estimate: 7.684873 ]
( IndexScan branch_cash index_date_cash[ expr( branch_cash.date_cash ) >= expr( 3, -60, '2005.05.04', -1 ) : 25% Guess ] )
)
Замена переменной на константу влияет на план?!
Может быть "convert(date,'2005.05.14')" и "current date" и не одно и тоже, но почему тогда нигде нет упоминания о таких различиях в использовании.?..(
Замучил я наверно тут всех своими датами. Но я долго не хотел признавать проблемы в работе оптимизатора АСА, пока не стал его сильно ковырять, ибо достала не постоянность его(оптимизатора) "взглядов" на запросы. И возможно тут дело не только в датах.

ASCRUSДык я рекомендовал сделать индекс для того, чтобы искать по его равенству, а не затем, чтобы опять на него операции сравнения навешивать.
Ну так вот
select *
from branch_cash
where day_id >= 1950;
так-то работает нормально. Значит дело не в виде индекса.
Вы предлагаете мне смоделировать работу составного индекса.
Но в этой ситуации какие различия? Кроме синтаксических - никаких. Значит надо править им оптимизатор.
А сделаю так, убью доп.поле(day_id), вообщем вернуть все обратно как было. Просто в запросе уберу все ссылки на today(). На клиента перенесу вычисление дат.

И, завершающий аккорд(branch_day_rest 86 млн, PK - составной (day_rest,...)):
Запрос1:
select *
from branch_day_rest
where day_rest between '2005.03.14' and '2005.05.14';
План1:
( Plan [ Total Cost Estimate: 829.306464 ]
( TableScan branch_day_rest[ ( 2005-03-14 <= branch_day_rest.day_id <= 2005-05-14 ) : 54.07456114% Statistics ] )
)
Запрос2:
select *
from branch_day_rest
where day_rest between convert(date,'2005.03.14') and convert(date,'2005.05.14');
План2:
( Plan [ Total Cost Estimate: 220.810038 ]
( IndexScan branch_day_rest branch_day_rest )
)
Ну тут я вообще понял, что мне пора отдыхать...)
P.S.: Честное слово, больше писать не буду. Уважаемый ASCRUS, если Вы сочтете необходимым, то передайте привет разработчикам АСА, а то, я инглиша разговорного не знаю.

А-а-а, я понял. По крайней мере часть проблем можно объснить следующим: когда мы указываем в where выражение типа "date between '...' and '...' " или "date>= '...' ", то АСА(!!!!), никому ничего не сказав, конвертирует date в строку, т.е. ищет такие записи, для которых строка полученная из date удовлетворяет условию!!!! А не строки конвертируются в дату! Вот. Почему АСА так делает по умолчанию - видимо ему так видней. (Кстати когда пишем равенство, АСА сообржает что можно заюзать индекс, не смотря на конвертацию из даты в строку, т.к. функция дата<->строка обратима...!)
Но этим объясняется только часть проблем.
Мне, например, все равно остается непонятным, почему
where day_id >= convert(date,'2005.05.14') - convert(date,'2000.01.01') - 10 и where day_id >= 1950 не одно и тоже. А ведь это уже выборка по целочисленному полю. Видимо оптимизатор тут тоже хочет day_id во что-то конвертить.
А также, сохраняется неустойчивое поведение оптимизатора при использовании функций (даже с константными аргументами!!!) в условиях запросов, таких как today(),current date,dateadd().
Во всяком случае, все "завихи" оптимизатора поборол, в чем-то виноват он, в чем-то я. Но результат есть. Так что думаю, что разработчикам АСА это можно не показывать...
P.S.:Хотя тот факт, что он молча преобразует дату в строку, и ничего не говорит - напрягает. Хоть бы выругался о несоответствии типов....