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

Откуда:
Сообщений: 1442
Здравствуйте.
Помогите с проектированием запроса/таблицы/индексов

В процедуре текстом формирую запрос, основанный на вьюшке. Если в предложении WHERE использую ИЛИ такое условие:keyCA=15 ИЛИ такое:[datToCA]<='05.06.2013', то запрос выполняется за 3 секунды.
Если использую оба условия вместе: keyCA=15 AND [datToCA]<='05.06.2013' запрос выполняется 50 секунд.

Проблемный запрос выглядит так:
INSERT INTO #T 
SELECT DogovorCred_View.*, tabColor.ColorR 
FROM #Flt INNER JOIN DogovorCred_View DogovorCred_View ON #Flt.keyDZ=DogovorCred_View.keyDZ 
    LEFT JOIN tabColor ON DogovorCred_View.HasFile = tabColor.keyColor  
WHERE keyCA=15 AND [datToCA]<='05.06.2013'


Поля keyCA и datToCA - в одной таблице.
Есть индекс на keyCA и на datToCA. Убивал индекс datToCA и делал составной keyCA, datToCA - не помогало.

Вьюшка DogovorCred_View построена на базе еще нескольких вьюх. Основная таблица в этой вьюхе содержит keyCA и datToCA

Планы выполнения прилагаю
Какие еще данные нужны, чтобы попытаться решить проблему?
27 сен 13, 16:05    [14893847]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Диам
Планы выполнения прилагаю

что-то не приложились
27 сен 13, 16:10    [14893892]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Диам
Member

Откуда:
Сообщений: 1442
видимо после предварительного просмотра
Дубль.

К сообщению приложен файл (Планы.zip - 63Kb) cкачать
27 сен 13, 16:17    [14893950]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Диам,

Надо же, всего 50 секунд на такой запрос, а сервер молодец!

Какая версия у вас? Если такие или старше чем, 2008 SP3 CU7, 2008R2 SP1 CU5, 2012 CU1 - то попробуйте взять ваш медленный запрос и дописать в конце:
OPTION (QUERYTRACEON 4137);

Если запрос станет выполняться быстро, думайте, как дать серверу знать о корреляции в ваших колонках, статистика там какая-нить фильтрованная или еще как...

Но по хорошему, нужно задать себе вопрос, а действительно ли все поля и все таблицы что есть во вьюхах нужни и без них никак? Если ответ отрицательный - то нужно переписать запрос делая выборку только из необходимых таблиц - забыв про вьюхи! Возможно, даже, разбить на несколько запросов при помощи временных таблиц, тогда, даже если будет незначительная ошибка в оценке, она не будет драматически влиять на последующие операторы в плане - как сейчас у вас в медленном плане.
27 сен 13, 16:55    [14894239]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Диам
Member

Откуда:
Сообщений: 1442
SomewhereSomehow,
Версия 2008 SP3 CU7.
Все поля в зависимости от накладываемых условий могут быть то нужны, то не нужны.

На данный момент я решил это таким способом. Сначала всю вьюху без условий копирую во временную таблицу. И потом уже при любых условиях результат получается за 3-5 секунд. Но т.к. это не правильно, хотелось бы разобраться с проблемой, а не делать такой костыль...
27 сен 13, 17:12    [14894312]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Диам
SomewhereSomehow,
Версия 2008 SP3 CU7.
Все поля в зависимости от накладываемых условий могут быть то нужны, то не нужны.

На данный момент я решил это таким способом. Сначала всю вьюху без условий копирую во временную таблицу. И потом уже при любых условиях результат получается за 3-5 секунд. Но т.к. это не правильно, хотелось бы разобраться с проблемой, а не делать такой костыль...

Мой здравый смысл говорит мне, что прежде чем разбираться с проблемой нужно понять в чем именно проблема. Вы выполнили запрос с флагом 4137? Если да и он стал быстрее - то понятно в чем проблема. Если нет - то прилагайте новый план выполнения с этим флагом.

На мой взгляд, проблема состоит в двух основных вещах:
1) слишком много таблиц в запросе (плюс - на многих нет покрывающих индексов, есть объемные таблицы)
2) есть ошибка в оценке из-за которой оптимизатор выбирает неправильный порядок соединения и неправильный тип соединения.

Обе эти проблемы накладываются и имеют эффект синергии.

Проблема номер 2, с ошибкой оценки - драйвер всего неправильного плана, и, имхо, она продиктована коррелированным предикатом. По этому, я попросил выполнить запрос с флагом трассировки, который меняет логику оценки селективности предикатов. Я так и не понял, зачем я писал предыдущее сообщение, если вы его полностью проигнорировали.

Насчет костыля в виде использования временных таблиц в сложных запросах - это вы зря. Даже SQLCAT рекомендует. К тому же, вы пишете "универсальный" запрос. "Универсальные запросы работают универсально плохо" - слышал я такую фразу от одного умного человека.

И в любом случае, я так понял, у вас сейчас - рабочее решение с временной таблицей. Тогда я вообще не понял зачем вы размещали вопрос. Или вы хотите понять причину? Но вы ведь даже не попытались проделать то, что описано выше, чтобы проверить догадку (во всяком случае не написали об этом сюда).
27 сен 13, 18:37    [14894623]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Диам
Member

Откуда:
Сообщений: 1442
SomewhereSomehow,
ВАУ!!! Что это было за чудо? С вот этим: OPTION (QUERYTRACEON 4137); запрос выполнился за 3 секунды!

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

После работы - семейные дела, не мог ответить.
Рабочее решение родилось до прочтения волшебной фразы. И все таки хотелось бы понять причину неправильного выполнения запроса...
27 сен 13, 19:51    [14894847]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Диам
После работы - семейные дела, не мог ответить.
Рабочее решение родилось до прочтения волшебной фразы. И все таки хотелось бы понять причину неправильного выполнения запроса...

Ок, теперь я вас понял. А то первоначально сложилось другое мнение, извините.
Судя по тому, что трэйс флаг сработал - драйвером проблем была действительно недооценка.
Это кстати документированный флаг от МС для решения подобных проблем: FIX:Низкая производительность при выполнении запроса, который содержит коррелированные предикаты и в SQL Server 2008 или SQL Server 2008 R2 или SQL Server 2012
Почитайте описание в документации, но если будет непонятно я приведу объяснение на плане в вашем случае. Только завтра или после завтра, а то сейчас уже меня ждут =)
В любом случае, раз TF сработал, все что я гговорил ранее о коррелированности и разбиении - применимо.
27 сен 13, 20:56    [14895007]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Диам
Member

Откуда:
Сообщений: 1442
SomewhereSomehow,
спасибо за ваш ответ.
Столкнулся с проблемой, что использовать флаг OPTION (QUERYTRACEON 4137) нельзя простым смертным пользователям.
У меня установлен:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4286.0 (Intel X86) May 29 2013 15:15:35 Copyright (c) Microsoft Corporation Enterprise Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

Т.е. у меня стоит SP2, а для 2008R2 фикс был в пакете 5 после SP1.
1 окт 13, 11:41    [14905640]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
aleks2
Guest
Диам
Здравствуйте.
Помогите с проектированием запроса/таблицы/индексов

В процедуре текстом формирую запрос, основанный на вьюшке. Если в предложении WHERE использую ИЛИ такое условие:keyCA=15 ИЛИ такое:[datToCA]<='05.06.2013', то запрос выполняется за 3 секунды.
Если использую оба условия вместе: keyCA=15 AND [datToCA]<='05.06.2013' запрос выполняется 50 секунд.

Проблемный запрос выглядит так:
INSERT INTO #T 
SELECT DogovorCred_View.*, tabColor.ColorR 
FROM #Flt INNER JOIN DogovorCred_View DogovorCred_View ON #Flt.keyDZ=DogovorCred_View.keyDZ 
    LEFT JOIN tabColor ON DogovorCred_View.HasFile = tabColor.keyColor  
WHERE keyCA=15 AND [datToCA]<='05.06.2013'



Ну чо тут париться?

1. В #T добавляешь поле keyCA. Да оно у тя там и так есть.
2.
INSERT INTO #T 
SELECT DogovorCred_View.*, tabColor.ColorR 
FROM #Flt INNER JOIN DogovorCred_View DogovorCred_View ON #Flt.keyDZ=DogovorCred_View.keyDZ 
    LEFT JOIN tabColor ON DogovorCred_View.HasFile = tabColor.keyColor  
WHERE [datToCA]<='05.06.2013'

3 сек
3.
DELETE #T  where keyCA<>15;

4. Профит.
5. Я б ишо для ускорения tabColor.ColorR заполнял бы после заполнения #T. Лишние JOIN-ы тока путают оптимизатор.
1 окт 13, 12:02    [14905840]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Диам
Member

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

Сейчас больше вопрос в том, как же установить обновление, если оно, по идее, и так должно было быть установлено. Фикс ведь был аж в SP1, а у меня стоит SP2....
SomewhereSomehow, можете это как-нибудь прокомментировать?
1 окт 13, 12:10    [14905895]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Диам
Фикс ведь был аж в SP1, а у меня стоит SP2....

Этот фикс лишь добавляет трейсфлаг 4137, не включая его.
Трейсфлаг 4137, будучи включён глобально, одни запросы "лечит", а другие "калечит".
Ибо заменяет одну эвристику подсчёта cardinality для AND-предикатов на другую эвристику.
Точный подсчёт cardinality по-прежнему невозможен, ибо гистограмма любой многоколоночной статистики по-прежнему строится только по первому столбцу, без учёта остальных столбцов.

Подробнее можно посмотреть тут: http://dba.stackexchange.com/questions/20597/how-does-sql-server-know-predicates-are-correlated , как всегда блестящее объяснение от Paul White'а.
1 окт 13, 12:38    [14906179]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Диам
Member

Откуда:
Сообщений: 1442
Гость333,
Спасибо за объяснение.
Только я не понял из объяснения - создание статистики на основе двух столбцов keyCA и datToCA меня не спасет?
1 окт 13, 13:11    [14906484]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Диам,

У вас неравенства в предикатах, в таком случае оптимизатор будет использовать гистограммы, а не плотность из многоколоночной статистики. К сожалению, как уже сказали, многомерных гистограмм в сиквеле нет, гистограммы строятся все-равно по первой колонке, так что боюсь многоколоночная статистика не поможет.

Если нет возможности применить TF на уровне запроса, то остается вариант, либо разбивать запрос через временную таблицу, что и я и другие вам уже советовали, да вы и сами так сделали, но почему-то вам не нравится этот вариант; либо можете фильрованную статистику сделать, например использовать keyCA=15 как условие, но тогда понятно, что такие же нужно будет делать и для 1,2,3...и сколько там их всего.

Если это тоже не вариант - можете попробовать обмануть оптимизатор, заменив "keyCA=15 AND [datToCA]<='05.06.2013'" на "keyCA=15 AND [datToCA]<=@datToCA", где @datToCA это локальная переменная (не параметр), в которую вы предварительно поместите значение '05.06.2013'. В таком случае, оптимизатор будет использовать догадку ~30% строк от таблицы, т.е. должно получиться явно больше строк, чем сейчас и план тоже должен другой построиться, возможно он окажется лучше (хотя может быть и нет). Еще можете выполнить запрос с трейсфлагом и при помощи plan guide-ов прибить план, но я так понял это не ваш вариант, т.к. у вас запрос шибко динамический.
1 окт 13, 15:26    [14907615]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Диам
Member

Откуда:
Сообщений: 1442
SomewhereSomehow,
спасибо за ответ. Остановлюсь на варианте создания временной таблицы с дальнейшим наложением условия.
1 окт 13, 15:43    [14907744]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Гость333
Member

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

А в случае равенства в предикатах будет использована плотность? Можно какой-нибудь пример, пусть даже без SQL-кода, "на пальцах"?
1 окт 13, 16:05    [14907922]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Гость333,

Да, пожалуйста.
use tempdb;
go
create table t1(a int, b int);
with n as
(
select top(10000) rn = row_number() over(order by (select null)) from master..spt_values v1, master..spt_values v2,master..spt_values v3
)
insert t1 (a,b)
select rn%20, rn%30 from n;
go
-- Actual: 167, Estimated: 16.7
select * from t1 where a = 1 and b = 1 option(recompile); --recompile, to remove simple parametrization
go
sp_helpstats 't1'
/*
_WA_Sys_00000001_21B6055D	a
_WA_Sys_00000002_21B6055D	b
*/
go
create statistics ab on t1(a,b);
go
-- Actual: 167, Estimated: 166.667
select * from t1 where a = 1 and b = 1 option(recompile);
go
sp_helpstats 't1'
/*
_WA_Sys_00000001_21B6055D	a
_WA_Sys_00000002_21B6055D	b
ab	a, b
*/
dbcc show_statistics(t1, ab) with density_vector;
/*
0.05			4		a
0.01666667		8		a, b

[complex density: 0.01666667] * [table cardinality: 10000] = 166.6667 Estimated rows
*/
go
-- Actual: 167, Estimated: 33.4 (sad face)
select * from t1 where a <= 1 and b = 1 option(recompile);
go
drop table t1;
1 окт 13, 16:43    [14908221]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить