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

Откуда:
Сообщений: 12310
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Есть несколько таблиц такого вида:
create table t1(
    objectGUID uniqueidentifier not null unique default newid(),
    parentObjectGUID uniqueidentifier null,
--    ...
    primary key nonclustered (objectGUID),
    foreign key (parentObjectGUID) references t1 (objectGUID)
)
go
create nonclustered index ind_t1_parentObjectGUID on t1 (parentObjectGUID)
go

create table t2(
    objectGUID uniqueidentifier not null unique default newid(),
    parentObjectGUID uniqueidentifier null,
--    ...
    primary key nonclustered (objectGUID),
    foreign key (parentObjectGUID) references t2 (objectGUID)
)
go
create nonclustered index ind_t2_parentObjectGUID on t2 (parentObjectGUID)
go
...


Есть сборная вьюха:
create view v
as
    select
        objectGUID,
        parentObjectGUID
        ...
    from
        t1
    union all
    select
        objectGUID,
        parentObjectGUID
        ...
    from
        t2


В запросах типа такого
select
    ...
from
    v
where
    parentObjectGUID = 'A325551C-395C-4F85-BF0D-C7594E1129B4'
в некоторых случаях execution plan показывает, что используется index seek, в некоторых - table scan. В обоих ситуациях количество данных примерно одинаково - по нескольку сотен записей. Селективность не слишком хорошая -

select count(*) from t1
select count(distinct(cast(parentObjectGUID as varchar(36)))) from t1

----------- 
270
            
----------- 
12

Скорость выборки из вьюхи при использовании индекса существенно выше. Выборка из вьюхи не обязательно происходит по условию, которое задействовало бы данный индекс.

Если добавить во вьюху принудительное использование индекса, то для запросов по другому условию получится нехорошо, только замедлится все. Если не добавлять, то сервер не всегда использует его, даже если индекс там помогает.

Как бы получше решить эту проблему?

Из знания предметной области можно сказать, что выборки делаются по одному из условий (where f1 = '<value>', where f2 = '<value>', where f3 like '<value>%'). Все три поля проиндексированы. Плюс еще некоторые условия по неиндексированным полям, их скорость уже неважна.

Соответственно есть идея сделать вместо одной вьюхи три и с клиента вызывать нужную, поскольку этой информацией он обладает.

Что посоветуете?
10 авг 04, 19:24    [872487]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
В.Петренко
Guest
Я бы вместо View использовал два запроса

Select ...
from t1 with (index(ind_t1_parentObjectGUID ))
left join t2 with (index(ind_t2_parentObjectGUID ))
on t1.parentObjectGUID=t2.parentObjectGUID
where t1.parentObjectGUID = 'A325551C-395C-4F85-BF0D-C7594E1129B4'

Union

Select ...
from t2 with (index(ind_t2_parentObjectGUID ))
left join t1 with (index(ind_t1_parentObjectGUID ))
on t1.parentObjectGUID=t2.parentObjectGUID
where t2.parentObjectGUID = 'A325551C-395C-4F85-BF0D-C7594E1129B4'
10 авг 04, 19:55    [872520]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
aleks2
Guest
А я бы таблицы t1, t2 и т.д. объединил бы в одну, ну и добавочный столбец
TableID = 't1' или 't2' или т.д.
11 авг 04, 07:19    [872902]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Хотелось бы оригинальные данные для тестирования поиметь. А то у меня на 3 таблицах по 1000 сгененрированных записей Index Seek везде.
11 авг 04, 09:06    [872997]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
2В.Петренко: это тоже самое, что я предлагаю - вместо одной вьюхи сделать несколько, каждую с принудительным использованием определенного индекса.

2aleks2: что вы имеете в виду?

2pkarklin: да на тестовых данных у меня тоже все зашибись... Потому и не могу толком поиграться. Проблемы-то в реальной базе :-/
11 авг 04, 19:58    [875631]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104760
В обоих ситуациях количество данных примерно одинаково - по нескольку сотен записей.
- Если таблица занимает не больше одного экстента то по идеи индекс вообще не нужен.

в некоторых случаях execution plan показывает, что используется index seek, в некоторых - table scan. В обоих ситуациях количество данных примерно одинаково - по нескольку сотен записей.
Статистика ? Дефрагментация ? Список столбцов в select ?

ЗЫ
А кластерного индекса по каким причинам нет ?
12 авг 04, 10:22    [876191]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
а существенно выше ето сколько?


для спящего время бодрствования равносильно сну
12 авг 04, 10:26    [876215]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
У меня воспроизвелась эта байда, теперь могу ответить на вопросы.

Glory
Если таблица занимает не больше одного экстента то по идеи индекс вообще не нужен

Таблицы маленькие, но по 6-8 экстентов они занимают.

Glory
Статистика ? Дефрагментация ?

Это результатом какого запроса должно быть? DBCC SHOWCONTIG, DBCC SHOW_STATISTICS?

Glory
Список столбцов в select ?

Никаких '*' нет. Список из 5-8 полей из общего количества несколько десятков. Уменьшение списка до 1 поля не помогает - план тот же. Наличие либо отсутствие в списке поля, индекс на который хотелось бы задействовать, тоже не влияет.

Glory
А кластерного индекса по каким причинам нет ?

Поле не уникальное, да и селективность не очень высокая. Это не PK.

Алексей2003
а существенно выше ето сколько?

Сейчас при воспроизведении такого поведения выяснилось, что при table scan время выполнения запроса ~ 1.7сек. Если принудительно указать хинт с индексом, то выполняется ДОЛЬШЕ :-( ~ 2.2сек

Воспроизвести так, чтобы index seek был, пока не могу, так что точных цифр не назову. Но было меньше полсекунды, это точно.

О попытках обновления и дефрагментации: UPDATE STATISTICS не помогает - план тот же. DBCC DBREINDEX тоже.

Ну вот, вроде ответы все. Подскажите, куда копать?
25 авг 04, 19:00    [908399]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
snake
Member

Откуда: Russia, Penza
Сообщений: 2290
Предлагаю primary key сделать кластерным.
26 авг 04, 09:50    [908956]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
snake
Предлагаю primary key сделать кластерным.



Не нарвемся ли на тормоза при изменении данных?
26 авг 04, 10:04    [908998]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Если принудительно указывать хинт - использование индекса, то в плане стоит index scan -> тормоза до тех самых 2.2сек.

Изначально PK некластерный потому что тип поля uniqueidentifier. Спустя довольно долгое время здесь на форуме было обсуждение, вызывает ли вставка данных с кластерным ключом uniqueidentifier физические перемещения записей (это же не нарастающий identity). Вроде доказали, что нет. Следовательно, вроде и тормозов быть не должно. Но поскольку серьезных проблем с производительностью не было, замена на кластерный не обдумывалась и не тестировалась.
26 авг 04, 10:24    [909063]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Вот план запроса:

StmtText                                                                                                                 
------------------------------------------------------------------------------------------------------------------------ 
select distinguishedName from EDMConfigurationView where ParentObjectGUID = N'79ea6a67-9c0a-485f-ba8f-71f8635daab3'

(1 row(s) affected)

StmtText                                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------------- 
  |--Concatenation
       |--Filter(WHERE:([Expr1208]=Convert([@1])))
       |    |--Compute Scalar(DEFINE:([Expr1208]=isnull([AccessTemplates].[ParentObjectGUID], 79EA6A67-9C0A-485F-BA8F-71F8635DAAB3)))
       |         |--Table Scan(OBJECT:([edm52].[dbo].[AccessTemplates]))
       |--Filter(WHERE:([Expr1428]=Convert([@1])))
       |    |--Compute Scalar(DEFINE:([Expr1428]=isnull([ACEs].[ParentObjectGUID], 79EA6A67-9C0A-485F-BA8F-71F8635DAAB3)))
       |         |--Table Scan(OBJECT:([edm52].[dbo].[ACEs]))
       |--Filter(WHERE:([Expr1886]=Convert([@1])))
       |    |--Compute Scalar(DEFINE:([Expr1886]=isnull([APOLinks].[ParentObjectGUID], 79EA6A67-9C0A-485F-BA8F-71F8635DAAB3)))
       |         |--Table Scan(OBJECT:([edm52].[dbo].[APOLinks]))
       |--Filter(WHERE:([Expr2350]=Convert([@1])))
       |    |--Compute Scalar(DEFINE:([Expr2350]=isnull([APOs].[ParentObjectGUID], 79EA6A67-9C0A-485F-BA8F-71F8635DAAB3)))
       |         |--Table Scan(OBJECT:([edm52].[dbo].[APOs]))
       |--Filter(WHERE:([Expr4060]=Convert([@1])))
       |    |--Compute Scalar(DEFINE:([Expr4060]=isnull([Union3899], 79EA6A67-9C0A-485F-BA8F-71F8635DAAB3)))
       |         |--Concatenation
       |              |--Table Scan(OBJECT:([edm52].[dbo].[ApplicationConfiguration]))
       |              |--Compute Scalar(DEFINE:([Expr2754]=isnull([AppSettings].[ParentObjectGUID], E8379D4E-DEFC-4BE1-A944-0A00BAE87D7F)))
       |              |    |--Table Scan(OBJECT:([edm52].[dbo].[AppSettings]))
       |              |--Compute Scalar(DEFINE:([Expr2925]=isnull([DispSpec].[ParentObjectGUID], E8379D4E-DEFC-4BE1-A944-0A00BAE87D7F)))
       |              |    |--Table Scan(OBJECT:([edm52].[dbo].[DispSpec]))
       |              |--Compute Scalar(DEFINE:([Expr3096]=isnull([EDMDispSpec].[ParentObjectGUID], E8379D4E-DEFC-4BE1-A944-0A00BAE87D7F)))
       |              |    |--Table Scan(OBJECT:([edm52].[dbo].[EDMDispSpec]))
       |              |--Compute Scalar(DEFINE:([Expr3280]=isnull([ExtendedRights].[ParentObjectGUID], E8379D4E-DEFC-4BE1-A944-0A00BAE87D7F)))
       |              |    |--Table Scan(OBJECT:([edm52].[dbo].[ExtendedRights]))
       |              |--Compute Scalar(DEFINE:([Expr3478]=isnull(E8379D4E-DEFC-4BE1-A944-0A00BAE87D7F, E8379D4E-DEFC-4BE1-A944-0A00BAE87D7F)))
       |              |    |--Compute Scalar(DEFINE:([Expr3397]=[dbo].[MakeDN_OtherObjects]('SecuritySyncJobs', 'Security Synchronization Jobs')))
       |              |         |--Constant Scan
       |              |--Compute Scalar(DEFINE:([Expr3641]=isnull([tblSchema].[ParentObjectGUID], E8379D4E-DEFC-4BE1-A944-0A00BAE87D7F)))
       |              |    |--Table Scan(OBJECT:([edm52].[dbo].[tblSchema]))
       |              |--Compute Scalar(DEFINE:([Expr3804]=isnull([WebInterface].[ParentObjectGUID], E8379D4E-DEFC-4BE1-A944-0A00BAE87D7F)))
       |                   |--Table Scan(OBJECT:([edm52].[dbo].[WebInterface]))
       |--Filter(WHERE:([Expr5981]=Convert([@1])))
       |    |--Compute Scalar(DEFINE:([Expr5981]=isnull([Union5885], 79EA6A67-9C0A-485F-BA8F-71F8635DAAB3)))
       |         |--Concatenation
       |              |--Compute Scalar(DEFINE:([Expr4402]=isnull([Applications].[ParentObjectGUID], 047590C7-F38D-4AA2-8F06-0084B0BF3B47)))
       |              |    |--Table Scan(OBJECT:([edm52].[dbo].[Applications]))
       |              |--Compute Scalar(DEFINE:([Expr4521]=isnull([ClientSessions].[ParentObjectGUID], 047590C7-F38D-4AA2-8F06-0084B0BF3B47)))
       |              |    |--Table Scan(OBJECT:([edm52].[dbo].[ClientSessions]))
       |              |--Table Scan(OBJECT:([edm52].[dbo].[Configuration]))
       |              |--Compute Scalar(DEFINE:([Expr5011]=isnull([License].[ParentObjectGUID], 047590C7-F38D-4AA2-8F06-0084B0BF3B47)))
       |              |    |--Table Scan(OBJECT:([edm52].[dbo].[License]))
       |              |--Compute Scalar(DEFINE:([Expr5245]=isnull([tblReplication].[ParentObjectGUID], 047590C7-F38D-4AA2-8F06-0084B0BF3B47)))
       |              |    |--Nested Loops(Left Outer Join, WHERE:([tblReplication].[edsaServerGUID]=[Settings].[objectGUID]))
       |              |         |--Nested Loops(Left Outer Join, WHERE:([tblReplication].[objectGUID]=[objectGUID]))
       |              |         |    |--Table Scan(OBJECT:([edm52].[dbo].[tblReplication]))
       |              |         |    |--Table Scan(OBJECT:(dbo.ReplGetAllData))
       |              |         |--Table Scan(OBJECT:([edm52].[dbo].[Settings]))
       |              |--Compute Scalar(DEFINE:([Expr5501]=isnull([ScheduledTasks].[ParentObjectGUID], 047590C7-F38D-4AA2-8F06-0084B0BF3B47)))
       |              |    |--Table Scan(OBJECT:([edm52].[dbo].[ScheduledTasks]))
       |              |--Compute Scalar(DEFINE:([Expr5752]=isnull([VirtualSchema].[ParentObjectGUID], 047590C7-F38D-4AA2-8F06-0084B0BF3B47)))
       |                   |--Table Scan(OBJECT:([edm52].[dbo].[VirtualSchema]))
       |--Filter(WHERE:(0455AC68-29BE-4354-A127-E26560BE77FF=Convert([@1])))
       |    |--Compute Scalar(DEFINE:([Expr6256]=[dbo].[MakeDN_OtherObjects]('EDMConfiguration', 'EDM Configuration')))
       |         |--Table Scan(OBJECT:(dbo._GetBackupData))
       |--Filter(WHERE:([Expr6909]=Convert([@1])))
       |    |--Compute Scalar(DEFINE:([Expr6909]=isnull([ManagedUnits].[ParentObjectGUID], 79EA6A67-9C0A-485F-BA8F-71F8635DAAB3)))
       |         |--Table Scan(OBJECT:([edm52].[dbo].[ManagedUnits]))
       |--Filter(WHERE:([Expr7369]=Convert([@1])))
            |--Compute Scalar(DEFINE:([Expr7369]=isnull([ScriptModules].[ParentObjectGUID], 79EA6A67-9C0A-485F-BA8F-71F8635DAAB3)))
                 |--Table Scan(OBJECT:([edm52].[dbo].[ScriptModules]))

(61 row(s) affected)
26 авг 04, 10:49    [909171]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
пока только можно посоветовать искать узкое место...
каждый запросик в отдельности... =\


для спящего время бодрствования равносильно сну
26 авг 04, 10:57    [909218]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Каждый запросик в отдельности как раз вызывает index seek и шустрит пулей. В том-то и дело! Сканирование начинается только при использовании самой вьюхи.
26 авг 04, 11:38    [909430]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
тогда может попробовать взять весь запрос, вставить в QA, и там по одному запросику исключать...
я понимаю мартышкин труд... но помочь может...


для спящего время бодрствования равносильно сну
26 авг 04, 13:54    [910365]     Ответить | Цитировать Сообщить модератору
 Re: View. Использование индексов на участвующие в нем таблицы  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
По наводке Crimean'а вроде выяснилась причина слетания оптимизатора. Во вьюхе кое-где на то поле, которое индексированное, и по которому существует индекс, делается выборка через isnull. Если это убрать и оставить поле напрямую, то индекс сик без разговоров. С COALESCE старый table scan.

Осталось только попытаться избавиться от null'ов, если это возможно по структуре системы... Заменить на какие-то суррогатные значения, к примеру... Или еще как-нибудь. Но это уже вне данного топика однозначно.

Спасибо всем за участие.
26 авг 04, 14:22    [910547]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить