Для начала скажу на ASA v7.0.4.3538 это все работало быстро, тормоз не замечалось, а после переезда на Adaptive Server Enterprise/12.5.3/EBF 12462 ESD#2/ все буквально остановилось. Запрос работает ооооочень долго, потому что Table scan по 15 млн записей.
Это нюансы оптимизатора, или глюки этой версии АСЕ?
Опишу ситуацию подробно: есть большие таблицы ( 15 млн ) Entry, Acccount ( 750 тыс ), Subscriber ( 350 тыс )

Есть индексы
Entry Index:
CREATE NONCLUSTERED INDEX XIF463Entry
ON dbo.Entry(AccountID,EntryDate)
Account, Subscriber -> Clustered по ID

1. Певичный запрос
select * from Entry e, Account a, Subscriber s
where e.AccountID = a.ID and a.SubscriberID = s.ID
and e.EntryDate between '2005.01.01' and '2005.02.01'

Так вот, при таком запросе по таблице получаем:
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE Entry e
Nested iteration.
Table Scan. Forward scan. Positioning at start of table.
FROM TABLE Account a
Nested iteration.
Using Clustered Index.
Index : Account_4236695262
Forward scan. Positioning by key.
Keys are: ID ASC
FROM TABLE Subscriber s
Nested iteration.
Using Clustered Index.
Index : Subscriber_17321981902
Forward scan. Positioning by key.
Keys are: ID ASC
И это работает безумно медленно, понятно почему.
Такой же план запроса получаем и при след. запросе ( убрали дату )
select * from Entry e, Account a, Subscriber s
where e.AccountID = a.ID and a.SubscriberID = s.ID
2. Если добавить точное значение
select * from Entry e, Account a, Subscriber s
where e.AccountID = a.ID and a.SubscriberID = s.ID
and e.EntryDate between '2005.01.01' and '2005.02.01'
and e.AccountID = 1234
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE Account a
Nested iteration.Using Clustered Index.
Index : Account_4236695262
Forward scan. Positioning by key.
Keys are: ID ASC
FROM TABLE Subscriber s
Nested iteration. Using Clustered Index.
Index : Subscriber_17321981902
Forward scan. Positioning by key.
Keys are: ID ASC
FROM TABLE Entry e
Nested iteration.
Index : XIF463Entry
Forward scan. Positioning by key.
Keys are: AccountID ASC EntryDate ASC
Видим, что в таком случае оптимизатор подхавтил идекс в Entry, причем по обоим ключам, собственно, чего мы и хотели добиться.
Но в нашем первичном запросе у нас нет возможности указать прямо конкретное значение, что и понятно.
3. Делаем подсказки
select *
from Entry e ( index XIF463Entry ) , Account a, Subscriber s
where e.AccountID = a.ID and a.SubscriberID = s.ID
and e.EntryDate between '2005.01.01' and '2005.02.01'
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE Entry e
Nested iteration.
Index : XIF463Entry
Forward scan. Positioning at index start.
FROM TABLE Account a
Nested iteration. Using Clustered Index.
Index : Account_4236695262
Forward scan. Positioning by key.
Keys are: ID ASC
FROM TABLE Subscriber s
Nested iteration. Using Clustered Index.
Index : Subscriber_17321981902
Forward scan. Positioning by key.
Keys are: ID ASC
Видим что при указании хинте индекса он берется, но ключи не подставляются, то есть получается все равно Table Scan почти что, только по индексу, что также не решает проблемы.
Направьте плз на путь истинный!!! Как обойти оптимизатор, чтобы он нормально брал индекс, и избежать Table Scan по самой большой табле?
Спасибо!

Может я не всего понимаю конечно, но ведь в индексе по Entry поле AccountID - первое, так почему же тогда при связке по этому полю индекс не подхватывается? Или индекс берется, только когда это поле стоит в условии напрямую, те AccountID = 1234? Те при его участии в джойне индекс что ли не берется?
Как то странно это...

А кто бы мне объяснил с каких пор для оптимизатора Table Scan лучше поиска по индексу, причем по 2м полям? Тем более с условием?

Для начала скажу, что проблему с предыдущим запросом мы таки решили, добавив принудительно ХИНТ по индексу:
запрос:
select *
from Subscriber as s (index XAK2Subscriber),
Account as a, Entry as e
where a.SubscriberID = s.ID
and e.AccountID = a.ID
and e.entryDate between '2005.06.01' and '2005.06.10'
DDL XAK2Subscriber:
CREATE NONCLUSTERED INDEX XAK2Subscriber
ON dbo.Subscriber(tmpCardID)
Query Plan:
QUERY PLAN FOR STATEMENT 4 (at line 87).
STEP 1 The type of query is SELECT.
FROM TABLE Subscriber s
Nested iteration.
Index : XAK2Subscriber
Forward scan.
Positioning at index start.
FROM TABLE Account a
Nested iteration.
Index : XIE2Account
Forward scan.
Positioning by key.
Keys are:
SubscriberID ASC
FROM TABLE Entry e
Nested iteration.
Index : XIF463Entry
Forward scan.
Positioning by key.
Keys are:
AccountID ASC
EntryDate ASC
Собственно выделенный индекс в Плане мы и хотели, чтобы Оптимизатор взял.
После этого запрос стал отрабатывать практически сразу, по крайней мере несарвнимо быстрее чем было до этого.

А теперь еще пара ситуаций, кт мы так и не смогли понять. Выбор оптимизатора остается полной загадкой!!!
К предыдущему запросу было добавлено условие из рабочего селекта, и встала новая проблема - ОПЯТЬ TABLE SCAN ( рассматриваемый запрос является частью основного, кт реально должен крутиться на базе, но остальные таблицы и условия не влияют на основную проблему, это проверено )
1.
запрос:
select *
from Subscriber as s (index XAK2Subscriber) ,
Account as a, Entry as e
where a.SubscriberID = s.ID
and e.AccountID = a.ID
and e.entryDate between '2005.06.01' and '2005.06.10'
and e.entryType = 10
Query plan:
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable1 created for REFORMATTING.
FROM TABLE Entry e
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 4 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
STEP 2
The type of query is SELECT.
FROM TABLE
Subscriber
s
Nested iteration.
Index : XAK2Subscriber
Forward scan.
Positioning at index start.
FROM TABLE
Account
a
Nested iteration.
Index : XIE2Account
Forward scan.
Positioning by key.
Keys are:
SubscriberID ASC
FROM TABLE
Worktable1.
Nested iteration.
Using Clustered Index.
Forward scan.
Positioning by key.
2. А теперь что мы сделали, чтобы обойти ЭТОТ непонятный оптимизатор!!!
Запрос:
select *
from Subscriber as s (index XAK2Subscriber) ,
Account as a, Entry as e
where a.SubscriberID = s.ID
and e.AccountID = a.ID
and e.entryDate between '2005.06.01' and '2005.06.10'
and Isnull(e.entryType,0 ) = 10
или можно аналогично
select *
from Subscriber as s (index XAK2Subscriber) ,
Account as a, Entry as e
where a.SubscriberID = s.ID
and e.AccountID = a.ID
and e.entryDate between '2005.06.01' and '2005.06.10'
and e.entryType not in ( 1, 2, 3 )
Query Plan:
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE Subscriber s
Nested iteration.
Index : XAK2Subscriber
Forward scan.
Positioning at index start.
FROM TABLE Account a
Nested iteration.
Index : XIE2Account
Forward scan.
Positioning by key.
Keys are:
SubscriberID ASC
FROM TABLE Entry e
Nested iteration.
Index : XIF463Entry
Forward scan.
Positioning by key.
Keys are:
AccountID ASC
EntryDate ASC
А теперь как это все объяснить?
Во всех учебниках написано:
Почти Цитата: " В целях оптимизации запроса избегайте использования отрицаний (not, "!=" ), а также применения функций"

MasterZiv
Не понятно, чего вы там добились, как был плохой план, так и остался.
Вместо table scan раньше - index scan теперь. Чем лучше ? Не понятно. Даже хуже, потому что index scan стоит по IO больше.
Вам надо по entryDate индекс сделать, это - единственный SARG в этом запросе.
Почему ничего не изменилось?
У нас TAbleScan по самой Маленькой ( 300к записей ) таблице Subscriber as s (index XAK2Subscriber) вместо Entry ( 15 млн записей ) Вот в чем разница!
А по самой большой Entry как раз взялся индекс по 2м ключам, причем по тому самому индексу составному. И почему Вы говорите, что там нету подходящего индекса, если запрос по сути тотже ( только Хинт принудительный по другой табле ), а Индекс он сам подхватил, потому что и ACcountId и entryDate фигурируют в условии и всвязках! Получается, что раз мы его ЗАСТАВИЛИ взять именно такой индекс, то он его бы и сам мог Догадаться взять.

MasterZivНапиши пожалуйста результаты выполнения этих запросов :

15385076
180593
15009070
176336

MasterZiv
И еще раз - не пытайся понять оптимизатор таким образом. Ты пытаешься его заставить выбирать между плохим и очень плохим планом запроса, да еще и не понимаешь, что TableScan- это не всегда плохо, и заставляещь читать еще и индекс зачем-то. Ну заставишь - толку -то ?

Повторюсь наверное, но скажу, что Table Scan по 15 млн не может быть лучше чем даже Index Scan с условием по дате, кт значительно ограничивает выборку. Я в курсе, что Table Scan это не всегда плохо, но не по 15 млн жеж, и индекс скан не всегда хорошо, это вроде тоже понятно... но в нашем варианте это не тот случай, когда Table Scan или Index Scan могут быть лучше выборки по индексу.
И еще в ASA все теже запросы, даже безо всяких изменений работают на ура быстро. А на ASE вот такие непонятки, у них различаются принципы выбора оптимизатора? или в чем разница?

Извините за пустой пост, случайно запостился.

MasterZiv, абстрактные планы нам уже предлагали. но без ста грамм там не разберешься, а у Вас есть опыт работы с ними?

А все-таки Вы бы могли прокомментировать мой пост от 28 июл 05, 19:28, когда после применения оператора отрицания и функции оптимизатор стал сам подхватывать индексы?

CripА статистики давно обновляли раз хинт помогает?
СТатистики обновляли кучу раз.. Тем более ХИНТ по сути не помогает ( см. там идет Forward Scan по индексу, это не лучше чем Table Scan, даже местами хуже)

MasterZiv
Записи с entryDate between '2005.06.01' and '2005.06.10' составляют порядка 1 процента от всех записей. Значит селективность по дате будет хорошей и для этого запроса индекс по entryDate был бы полезен. Вывод - хотите, чтобы этот запрос работал, создавайте индекс.
Предложение хорошее, но есть нюанс, БД, по кт мы делаем запросы - сторонний биллинг, поэтому влезать туда и строить индексы не очень хочется, потому что это может нарушить оптимизацию используемых в этом приложении запросов. Поэтому данный вариант нами рассматривается в последнюю очередь.

MasterZiv, спасибо огромное за качественные ответы по вопросам! Считаю что данный мой опыт по исследованию работы оптимизатора не был лишним!

Кстати в итоге оптимальным решением стало вот что:
1. Выгрузка только необходимых колонок и данных за нужный месяц в спец. таблицу ( или период - обычно не больше мес. )
2. На эту таблу вешаются все нужные индексы ( специально подбирались под запросы )
3. Генерится нужный отчет
Результат - все большие таблы идут по индексу, формирование идет порядка 5-10 мин ( до этого было за пару часов ) Причем на ASE по сравнению с ASA по одному периоду отчет формируется в несклько раз быстрее.