Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Странное поведение подчиненного запроса.  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
Здравствуйте,

в моей базе (Access 2003 + MS SQL 2008 проект ADP) имеется форма заполняемая следующим запросом к серверу:
Select a.iNumsk, a.iNum, strNumstr, dtDateb, c.strName, bitScan, strNote 
FROM Base..tblInvoices_Buh a Inner Join Base..tblContractor_Buh c 
ON a.iKF = c.iKF Left Join Income..tblIncomeOrder b 
ON a.iNumsk = b.iNumsk AND a.iNum =b.iNum WHERE
c.iKF <> 468 AND iInvoice in (Select iInvoice From BASE.dbo.qryCostFullWithNorm Where IDZAK in ('выборка ID через контролы формы"))


Все работает нормально и достаточно быстро до тех пор пока в выборке ID имеется несколько ID. Но если туда попадает только один ID - резко теряется скорость расчёта. С долей секунд до десятков минут и даже часов.
Любопытно, почему такое происходит? Подумал что из за того что оператор in изначально предполагает выбор из списка состоящего минимум из двух позиций. Для проверки заменил строку
Where IDZAK in ('выборка ID через контролы формы")

на
Where IDZAK = ID

расчёт не ускорился, всё так же медленно.
В принципе проблему для себя решил добавлением в условие отбора по in фиктивного (не существующего) ID - все тут же быстренько заработало как и должно бы.
Тем не менее любопытно было бы узнать в чем здесь может быть проблема.
20 июн 13, 05:37    [14457251]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
Если что - акцесс здесь ни при чём. Проверял этот запрос непосредственно из менеджмент студии. Всё то же самое - быстрая отработка запроса если в списке более одного ID, и на порядки медленнее если задать условие только по одному ID.
20 июн 13, 05:48    [14457253]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3348
Изерлонер,

Ответ, как обычно, в планах выполнения.
20 июн 13, 06:15    [14457256]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
а не подскажете как посмотреть план выполнения? Я пока во всем этом чайник
20 июн 13, 06:48    [14457272]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
Планы нашел, но ни чего в них не могу понять.
20 июн 13, 06:59    [14457283]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
План по 2 ИД:

К сообщению приложен файл (по 2 ИД.sqlplan - 109Kb) cкачать
20 июн 13, 07:06    [14457290]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
План по 1 ИД (предполагаемый, по результату выполнения слишком долго ждать, более часа):

К сообщению приложен файл (по 1 ИД.sqlplan - 90Kb) cкачать
20 июн 13, 07:08    [14457293]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Ennor Tiegael
Member

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

Рекомендую также подумать, какие индексы / ключи можно создать на BASE.dbo.tblSumZehWithNorm - она у вас без кластерного индекса даже.
20 июн 13, 07:40    [14457321]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
да, действительно ключа нет. Это временная таблица (промежуточная), может потому и ключ не поставил.
20 июн 13, 08:04    [14457347]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
Статистику обновил, отсутствующие ключи добавил. Быстрее не стало
20 июн 13, 08:31    [14457406]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
kalimba
Member

Откуда:
Сообщений: 297
Изерлонер
Статистику обновил, отсутствующие ключи добавил. Быстрее не стало
Попробуйте
CREATE NONCLUSTERED INDEX [IX_test] ON dbo.qryCostFullWithNorm (IDZAK ASC) INCLUDE (iInvoice) 
, убедитесь что IDZAK именно строкового типа (nvrchar/varchar), если нет то убирайте кавычки (просто IN(1, 2, 3)) и перезапустите запрос с OPTION(RECOMPILE).
20 июн 13, 08:53    [14457454]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
aleks2
Guest
Изерлонер
Статистику обновил, отсутствующие ключи добавил. Быстрее не стало

Страдалец.

declare @I table(iInvoice (n)varchar(?) primary key clustered);

insert @I
Select DISTINCT iInvoice From BASE.dbo.qryCostFullWithNorm Where IDZAK in ('выборка ID через контролы формы");

Select a.iNumsk, a.iNum, strNumstr, dtDateb, c.strName, bitScan, strNote 
FROM Base..tblInvoices_Buh a Inner Join Base..tblContractor_Buh c 
ON a.iKF = c.iKF Left Join Income..tblIncomeOrder b 
ON a.iNumsk = b.iNumsk AND a.iNum =b.iNum 
inner join @I I on I.iInvoice = ?.iInvoice
WHERE c.iKF <> 468


ЗЫ. Это можно написать прям в запрос Access или процедурой оформить.
20 июн 13, 09:06    [14457520]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
kalimba
убедитесь что IDZAK именно строкового типа (nvrchar/varchar), если нет то убирайте кавычки (просто IN(1, 2, 3)) и перезапустите запрос с OPTION(RECOMPILE).

IDZAK однозначно не строкового типа integer. Я в примере выше 'строку' написал и пояснил что это ID выбираемые соответствующим контролом, реально там стоит:
Where IDZAK in (1413, 1516) -- или любые другие ID количество не ограничено (сколько в соответствующей таблице есть)
20 июн 13, 09:11    [14457540]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
Да ладно, черт с ним. Меня просто удивляет такая разница во времени запросов по одному ID или по нескольким. Ну понятно было бы если бы запрос с Where IDZAK in ([int], [int],...) дольше бы немного работал. А так запрос по одному ИД: Where IDZAK = 1513 работает дольше, и ладно бы дольше, но не до такой же степени. Реально до полутора часов ( При том что первый через одну-две секунды результат выдает.
20 июн 13, 09:19    [14457575]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
aleks2
Изерлонер
Статистику обновил, отсутствующие ключи добавил. Быстрее не стало

Страдалец.


ЗЫ. Это можно написать прям в запрос Access или процедурой оформить.


Крепко задумался. Я правильно понимаю что здесь создается некая промежуточная (временная) таблица куда сбрасываются все найденные iInvoice удовлетворяющие условию?

Это возможно решит проблему.. но я ее и так отчасти решил (добавил фиктивный ключ в запрос in ([..], [фикт. ключ]) - просто прописал в соответствующей функции VBA, теперь в запросе не может быть меньше двух значений, и все работает как и должно.

Я топик создал не с целью решения проблемы, а что бы понять чем вызвано подобное поведение запроса.
20 июн 13, 09:26    [14457602]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
aleks2
Guest
Изерлонер
Я топик создал не с целью решения проблемы, а что бы понять чем вызвано подобное поведение запроса.


Оптимизатор выбирает РАЗНЫЕ планы соединения таблиц и фильтрации. Да, это его ошибка.

Но, что ТЫ сделал, чтобы оптимизатор НЕ МОГ ошибиться?
Ничего.
20 июн 13, 09:32    [14457626]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3348
Изерлонер,

Это второе значение в списке помогает, можно сказать, случайно. Даже в плане "быстрого" запроса есть ошибки оценки кол-ва строк на 4 порядка. Там в том месте выбирается hash join, и при том количестве записей, которое реально приходит, он пока справляется. Но скоро данные прирастут, оптимальной станет стратегия merge, и ранее работавший запрос так же внезапно начнет тормозить. Тогда что будете делать?..
20 июн 13, 09:44    [14457680]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Изерлонер, проблему как раз-таки надо решить. Ибо с ростом объёмов информации запрос начнёт тупить и при 2-3-4-5 и т.д. IDшниках в списке IN.
Посмотрите на HASH MATCH, у вас их несколько. MERGE или LOOP оптимальнее будет. Периодически обновляйте статистику. Попробуйте дефрагментировать существующие индексы.
Просто так ничего не бывает.
20 июн 13, 09:57    [14457776]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
CREATE TABLE #IDZAK (IDAZAK INT) 
/*напихать в #IDZAK значений с контролов*/



AND iInvoice in (Select iInvoice From BASE.dbo.qryCostFullWithNorm Where IDZAK in ('выборка ID через контролы формы"))


==>

INNER JOIN  BASE.dbo.qryCostFullWithNorm  CFWN ON CFWN.iInvoice = a.iInvoice  
WHERE 
c.iKF <> 468 AND 
AND CFWN.IDZAK IN (SELECT  IDZAK FROM #IDZAK)  


и наверное повесить на BASE.dbo.qryCostFullWithNorm КЛАСТЕРНЫЙ индекс.
20 июн 13, 10:31    [14458044]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
Сергей Викт.
Посмотрите на HASH MATCH, у вас их несколько. MERGE или LOOP оптимальнее будет.


Я пока на это как баран на новые ворота смотрю :( В акцессе то еще толком не разобрался.
Хорошо, я так понимаю пример aleks2 решает конкретно эту проблему?

Подскажите пожалуйста что можно почитать по MS SQL чайнику вроде меня. По акцесс изучаю Геца.
И конкретно по планам ... как с ними работать и т.д. может ссылочка соответствующая есть?
20 июн 13, 10:38    [14458112]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
Cammomile

и наверное повесить на BASE.dbo.qryCostFullWithNorm КЛАСТЕРНЫЙ индекс.

Не знаю что такое кластерный индекс, но ключевое поле (автосчетчик) создал. Равно как и в нескольких других таблицах где оно отсутствовало.
20 июн 13, 10:41    [14458131]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Если совсем простенько, то по графическому плану смотрите глазами. Отлавливате САМЫЕ ЖИРНЫЕ СТРЕЛКИ, на концах самых жирных стрелок смотрите проценты выполнения операции.

Потом смотрите название операции, и по хелпам/гуглу/форумам изучаете как можно улучшить этот момент. Типа 31% кластерет индекс скан. Что такое кластеред индекс скан? Что лучше чем кластеред индекс скан? Как заменить кластеред индекс скан на то что лучше? 11% нестетд луп джойн. Что такое нестед луп джойн? Почему 11 % на него? Можно ли его поменять на что-то лучшее?

И тут надо четко понимать, что "большие проценты" это не значит что все плохо и неправильно. Это просто указатель на то, что данная операция занимает времени больше чем другие. А насколько это хорошо и правильно решаете вы
20 июн 13, 10:43    [14458161]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Изерлонер, автосчетчик это не кластерный индекс. Не надо было создавать автосчетчики там где это не нужно.

Прочитайте что такое кластерный/некластерный индекс на этом сайте.

И самое главное. За вас вашу беду тут никто не решит. Вам дали идеи и названия того, что надо изучать. Изучайте.
20 июн 13, 10:45    [14458171]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Изерлонер
Сергей Викт.
Посмотрите на HASH MATCH, у вас их несколько. MERGE или LOOP оптимальнее будет.


Я пока на это как баран на новые ворота смотрю :( В акцессе то еще толком не разобрался.
Хорошо, я так понимаю пример aleks2 решает конкретно эту проблему?

Подскажите пожалуйста что можно почитать по MS SQL чайнику вроде меня. По акцесс изучаю Геца.
И конкретно по планам ... как с ними работать и т.д. может ссылочка соответствующая есть?


Здесь достаточно много информации для полного понимания принципов анализа запросов

А так вообще:
технический справочник

Из книг можно посмотреть в сторону книг издательств Wrox или bhv (питерское переиздание)
Например: Леонард Лобел, Эндрю Дж. Браст, Стивен Форте: Разрабтка приложений на основе MS SQL Server 2008
20 июн 13, 10:48    [14458200]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение подчиненного запроса.  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Есть предположение, что если мужчина не знает за кластерные индексы начинать ему надо с азов, а "чем сик отличается от скана" и "что такое мердж дойн" отложить напотом.
20 июн 13, 10:52    [14458243]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить