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

Откуда:
Сообщений: 127
Добрый день.
Есть запрос, который генерит приложение и выполняется около минуты
SELECT TOP 50
        vwAE.*,
	common.dicAEDepartmentType.Description AS AE_DEPARTMENT_TYPE_DESC ,
        common.dicGPPractice.Description AS ORG_CODE_GP_DESC
FROM vwAE,
        tblDate ,
        common.dicAEDepartmentType ,
        common.dicGPPractice  ,     
        common.dicAttendanceAE
WHERE   ( vwAE.AE_ARRIVAL_DATEID = tblDate.DateYearMonthDay )
        AND ( vwAE.AE_DEPARTMENT_TYPE_ID = common.dicAEDepartmentType.ID )
        AND ( vwAE.ORG_CODE_GP_ID = common.dicGPPractice.ID )
	AND ( tblDate.FYDateYear = 2013 )
        AND ( vwAE.AE_ATTENDANCE_CATEGORY_ID = common.dicAttendanceAE.ID )

Первичный ключ на оснойной таблице (AE_ARRIVAL_DATEID, ID).
Если прописать четкий фильтр по полю AE_ARRIVAL_DATEID, то время 7 секнд:
SELECT TOP 50
        vwAE.*,
	common.dicAEDepartmentType.Description AS AE_DEPARTMENT_TYPE_DESC ,
        common.dicGPPractice.Description AS ORG_CODE_GP_DESC
FROM    vwAE,        
        common.dicAEDepartmentType ,
        common.dicGPPractice  ,     
        common.dicAttendanceAE
WHERE   ( vwAE.AE_DEPARTMENT_TYPE_ID = common.dicAEDepartmentType.ID )
        AND ( vwAE.ORG_CODE_GP_ID = common.dicGPPractice.ID )
        AND ( vwAE.AE_ATTENDANCE_CATEGORY_ID = common.dicAttendanceAE.ID )
        AND ( AE_ARRIVAL_DATEID BETWEEN 20130401 AND 20140331 )

Если добавить хинт WITH(FORCESEEK), то запрос отрабатывает за 0 секунд:
SELECT TOP 50
        vwAE.*,
	common.dicAEDepartmentType.Description AS AE_DEPARTMENT_TYPE_DESC ,
        common.dicGPPractice.Description AS ORG_CODE_GP_DESC
FROM    vwAE WITH(FORCESEEK),
        tblDate ,
        common.dicAEDepartmentType ,
        common.dicGPPractice  ,     
        common.dicAttendanceAE
WHERE   ( vwAE.AE_ARRIVAL_DATEID = tblDate.DateYearMonthDay )
        AND ( vwAE.AE_DEPARTMENT_TYPE_ID = common.dicAEDepartmentType.ID )
        AND ( vwAE.ORG_CODE_GP_ID = common.dicGPPractice.ID )
	AND ( tblDate.FYDateYear = 2013 )
        AND ( vwAE.AE_ATTENDANCE_CATEGORY_ID = common.dicAttendanceAE.ID )

Статистику обновил, ребилд всех индексов сделал.
Вопрос: как обойтись без хинта и получить такую же производительность.
Планы прилагаю.
27 ноя 13, 10:49    [15196769]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
pio777
Member

Откуда:
Сообщений: 127
План.

К сообщению приложен файл (1.7z - 7Kb) cкачать
27 ноя 13, 10:52    [15196792]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
aleks2
Guest
Я почти уверен, что это сервер саботирует - требует JOIN написать.
27 ноя 13, 10:53    [15196803]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
Glory
Member

Откуда:
Сообщений: 104760
pio777
Первичный ключ на оснойной таблице (AE_ARRIVAL_DATEID, ID).

А "основная" - это какая ?
Кроме этого ПК других индексов у всех объектов нет ?
А vwAE - это представление ?
А почему не использованы JOIN-s ?
А почему TOP без ORDER BY ?
27 ноя 13, 10:55    [15196825]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
хмхмхм
Guest
pio777,

обратите внимание на Estimated и Actual Number of Rows в вашем плане:

К сообщению приложен файл. Размер - 29Kb
27 ноя 13, 10:59    [15196849]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
хмхмхм
Guest
vwAE это и которой вы делаете select top 50 это вьюха?
27 ноя 13, 11:02    [15196869]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
pio777
Member

Откуда:
Сообщений: 127
Glory,

vwAE - представление остроится как селект с одной таблицы без дополнителных соединений + нескольно констант, описаный индекс на этой таблице. Других индексов на таблице нет.
На словарях ID первичный ключ.
На tblDate первичный ключ DateYearMonthDay.
С JOIN-ми сложилось исторически еще с 2000 сервера, эту часть приложения девелоперы не хотят переписывать.
Order не нужен, т.к. должен выдать 50 любых строк.
27 ноя 13, 11:06    [15196887]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
pio777
Member

Откуда:
Сообщений: 127
aleks2,

Ради интереса попробовал:
SELECT TOP 50
        vwAE.*,
	common.dicAEDepartmentType.Description AS AE_DEPARTMENT_TYPE_DESC ,
        common.dicGPPractice.Description AS ORG_CODE_GP_DESC
FROM    vwAE JOIN tblDate ON  ( vwAE.AE_ARRIVAL_DATEID = tblDate.DateYearMonthDay )
	JOIN common.dicAEDepartmentType ON ( vwAE.AE_DEPARTMENT_TYPE_ID = common.dicAEDepartmentType.ID )
	JOIN common.dicGPPractice ON ( vwAE.ORG_CODE_GP_ID = common.dicGPPractice.ID )
        JOIN common.dicAttendanceAE ON ( vwAE.AE_ATTENDANCE_CATEGORY_ID = common.dicAttendanceAE.ID )
WHERE  ( tblDate.FYDateYear = 2013 )


Ничего не поменялось, план и время остались прежними.
27 ноя 13, 11:09    [15196904]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
pio777
Member

Откуда:
Сообщений: 127
хмхмхм,

Это я и хочу убрать.
27 ноя 13, 11:11    [15196913]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
Glory
Member

Откуда:
Сообщений: 104760
pio777
Ничего не поменялось, план и время остались прежними.

Вы кэш очищали ?
И что со статистикой таблицы на скриншоте 15196849 ?
27 ноя 13, 11:18    [15196989]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
pio777
Member

Откуда:
Сообщений: 127
Glory,

Запускал dbcc freeproccache
На всех таблицах запускал update statistics with fullscan.
Показать статистику по tblAE?
27 ноя 13, 11:31    [15197074]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
Glory
Member

Откуда:
Сообщений: 104760
pio777
На всех таблицах запускал update statistics with fullscan.

А она есть ? Статистика по нужному полю то ?
27 ноя 13, 11:31    [15197079]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
pio777
Member

Откуда:
Сообщений: 127
Также таблица tblAE партиционированая по фискальным годам. И все в ней 36 милионов.
27 ноя 13, 11:33    [15197094]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
pio777
Member

Откуда:
Сообщений: 127
Glory,
dbcc show_statistics ( 'dbo.tblAE', PK__tblAE__0E07EDEF4627A077 ) with histogram;

+

RANGE_HI_KEYRANGE_ROWSEQ_ROWSDISTINCT_RANGE_ROWSAVG_RANGE_ROWS
2011040104813301
201104049520654298247603
2011040919194047273447985
201104114919853387149198
2011041617894645093444736.5
2011042014643746287348812.33
2011042624569152852549138.2
2011043013953952044346513
2011050415436347647351454.33
2011050918912254251447280.5
2011051314489946221348299.67
2011051819042746752447606.75
2011052318712353083446780.75
2011052818485644535446214
2011060114516045770348386.67
2011060513991347450346637.67
2011061123784544380547569
2011061514177048011347256.67
2011061913501345756345004.33
201106215266448005152664
2011062513731445036345771.33
201106274829855046148298
2011070114537847434348459.33
2011070515216549523350721.67
2011071018632947676446582.25
201107125486948658154869
201107159370646554246853
201107188896551094244482.5
2011072318248945145445622.25
201107254715852103147158
2011073018292545567445731.25
2011080314725246322349084
2011080713628845769345429.33
201108094956843252149568
2011081312745143926342483.67
201108154582949626145829
2011082017276043280443190
201108239634144273248170.5
2011082712712842206342376
2011083113847743537346159
2011090413447246703344824
2011090813385843822344619.33
2011091214041752979346805.67
2011091613955446897346518
2011092119245447227448113.5
2011092514046649507346822
201109275471948491154719
2011100114755849185349186
2011100516290249752354300.67
2011100913849148948346163.67
201110115467248768154672
2011101514344146133347813.67
201110174803253140148032
2011102217929544680444823.75
201110259792345023248961.5
2011102912923444020343078
201110314946850886149468
2011110518339043556445847.5
2011111018456044984446140
2011111413356950843344523
201111178986944802244934.5
2011112218564146157446410.25
201111244453845899144538
2011112813446951046344823
2012040213671855714345572.67
2012040718906051186447265
2012041115248249213350827.33
2012041724510348884549020.6
2012042218756448268446891
2012042719758847154449397
2012050219357049964448392.5
201205059721647678248608
2012050810260357348251301.5
2012051215088548944350295
201205145102957946151029
2012051815148249954350494
201205219869058706249345
2012052515794652609352648.67
2012052810841364537254206.5
201205305774256055157742
2012060315691050944352303.33
2012060611080755883255403.5
201206084927247729149272
2012061215400750576351335.67
201206145064250665150642
2012061814857359485349524.33
2012062215644249522352147.33
2012062510043760827250218.5
2012062916230652368354102
2012070210465659000252328
2012070615601450680352004.67
2012070910198059003250990
2012071315296650797350988.67
2012071715840751900352802.33
201207195134849890151348
2012072315249458247350831.33
2012072715660351647352201
2012073115930348814353101
2012080519239249215448098
2012080915030947683350103
2012081314979457088349931.33
201208155155249524151552
2012081914850951776349503
201208215705251044157052
2012082619499650024448749
2012083015373445767351244.67
2012090314310454959347701.33
2012090819418250569448545.5
2012091216202048980354006.67
2012091720218157542450545.25
2012092324969948744549939.8
201209255410149222154101
201209289947348968249736.5
2012100215716550522352388.33
2012100510048148961250240.5
2012100915638850401352129.33
201210129991848352249959
201210159882156193249410.5
2012101810031150564250155.5
201210219774850303248874
201210235668149789156681
201210269702244483248511
201210299327452238246637
201211019068346992245341.5
201211049286347939246431.5
2012110710151248922250756
201211109824048317249120
2012111625584848137551169.6
201211199837255999249186
201211229944149500249720.5
201211259505048789247525
2012112810380248214251901
201212019324246814246621
2012120724828847712549657.6
201212109727055000248635
201212139530046335247650
201212169934252085249671
2012121910564247176252821
201212229376551574246882.5
201212259846733912249233.5
201212289855648708249278
2012123110151645382250758
2013010310260049410251300
201301069704447390248522
201301099960946695249804.5
201301129092243864245461
201301159217745025246088.5
2013012016896243573442240.5
201301239525945755247629.5
201301269276945954246384.5
2013012910497451162252487
2013020214874748006349582.33
2013020510552048196252760
2013020914441247284348137.33
2013021523822848732547645.6
201302189931553158249657.5
201302219325644430246628
2013022724127849210548255.6
2013030314796752557349322.33
2013030611043652735255218
2013030910026149193250130.5
2013031210169549049250847.5
2013031614807148882349357
2013032226169045178552338
201303259017952563245089.5
2013032914164949915347216.33
201304019927251788249636
2013040410297347422251486.5
201304079796652265248983
2013041010481648777252408
2013041414421751489348072.33
2013041710988752000254943.5
2013042115114351968350381
2013042411047451893255237
2013042815019450600350064.67
2013050110991850660254959
2013050515293653167350978.67
2013050917018551400356728.33
2013051314793856717349312.67
2013051714742049206349140
201305209878658299249393
2013052519469448162448673.5
2013052915547845879351826
2013060319253256498448133
2013060714995949007349986.33
201306109962758040249813.5
2013061414993449235349978
201306179747258306248736
2013062115603051843352010
2013062515661151635352203.67
2013062915050149922350167
2013070316326451386354421.33
2013070715168452131350561.33
2013071117009652901356698.67
2013071515719461098352398
2013071915979251483353264
2013072316708352130355694.33
2013072820249451161450623.5
201307305570848820155708
2013073104714301
27 ноя 13, 11:44    [15197172]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
Glory
Member

Откуда:
Сообщений: 104760
pio777
Glory,
dbcc show_statistics ( 'dbo.tblAE', PK__tblAE__0E07EDEF4627A077 ) with histogram;



Замечательно. Ничего, что статистика нужна наверное по полю по которому происходит join ?
27 ноя 13, 11:46    [15197194]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
pio777
Member

Откуда:
Сообщений: 127
Glory,

Если вы хотите сказать что нужна статистика именно по полю AE_ARRIVAL_DATEID, то она не помогла.
27 ноя 13, 12:04    [15197346]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
SomewhereSomehow
Member

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

На первый взгляд, похоже, что вы столкнулись с эффектом целевого кол-ва строк на неравномерном распределении.
Попробуйте добавить в конец запроса option(fast 1000) (1000 - число из головы, можно попробовать 500, 2000, 5000, 10000 и т.д.) - посмотрите, что-то изменится? По-идее, оптимизатор должен посчитать сканирование слишком дорогим и выбрать поиск по индексу, план должен получиться примерно такой как и с подсказкой forceseek.
27 ноя 13, 12:29    [15197590]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31430
SomewhereSomehow
Попробуйте добавить в конец запроса option(fast 1000)
Странный этот оптимизатор, зачем ему option(fast 1000), если уже есть SELECT TOP 50?
27 ноя 13, 12:31    [15197605]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
SomewhereSomehow
Member

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

Это просто как проверка, к тому же, возможно это и не поможет.
В таком случае, можно попробовать такой прием declare @n int = 50; select top(@n) ... option(optimize for(@n = 1000)) - тоже как проверку.
Цель, как раз увеличить оценку сканирования, которая из-за top 50 представляется серверу очень низкой, хотя в реальности приводит почти к полному сканированию.
27 ноя 13, 12:44    [15197714]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
SomewhereSomehow,

Если статистика обновленная, а план без хинтов кривой, я бы попробовал построить фильтрованные статистики по секциям... Если дело именно только в этом запросе - то прибил бы sp_create_plang_uide
27 ноя 13, 13:47    [15198409]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
pio777
Member

Откуда:
Сообщений: 127
SomewhereSomehow,

Подсказки не помогли. А с переменной за 20 сек отработал.
Хотелось без подсказок все решить.
27 ноя 13, 16:41    [15200274]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
pio777
Member

Откуда:
Сообщений: 127
Алексей Куренков,

Фильтрованная статистика тоже ничего не поменяла.
27 ноя 13, 16:43    [15200295]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
SomewhereSomehow
Member

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

Понятно, похоже, это как раз такая ситуация в которой использовать forceseek рекомендуют сами товарищи из МС. Но если подсказки не подходят, то хотелось бы знать, какие есть возможности для изменения? Руководства планов? Переписать запрос? Попробуйте, например, в порядке эксперимента убрать из запроса таблицу common.dicAEDepartmentType, результат топ(50) материализовать во временной таблице, и с dicAEDepartmentType соединить уже эту временную таблицу.
27 ноя 13, 16:57    [15200414]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
pio777
Member

Откуда:
Сообщений: 127
SomewhereSomehow,

Запрос меняется в зависимости от того, какие фильтры выбирут в приложении.
Выполняются 2 запрос: 1 это наш топ, а второй это подсчет количества с теми же фильтрами.

Вариантов у меня мало:
1. C таблицами могу делать все что угодно.
2. В запросах могу сгенерить фильтр для AE_ARRIVAL_DATEID (чтоб исключить соединение с tblDate). Но к сожалению он не на столько быстр как хинт.
3. Хинт тоже не подходит, т.к. с ними не пройдут SELECT COUNT(*) FROM vwAE WITH(FORCESEEK).
27 ноя 13, 17:49    [15200738]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить запрос?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
pio777,
Хорошо что вы начали объяснять смысл, надо было с этого начинать. Ну а то соединение вы пробовали убрать? Просто ради эксперимента?
Может быть я ошибаюсь, но многое свидетельствует о такой проблеме:
Картинка с другого сайта.
У вас идет соединение с таблицей dicAEDepartmentType, и оптимизатор предполагает что ключи соединения будут располагаться равномерно, и значит, чтобы получить первые 50 записей - ему не нужно будет сканировать всю таблицу. Стоимость сканирования небольшого числа строк, поскольку это последовательный доступ - меньше стоимости поски того же числа строк (поскольку доступ случайный). Но оптимизатор не учитывает реальную кластеризацию, т.е. распределение строк. И получается то что получается. Попробуйте добавить в ваш запрос с топ, order by dicAEDepartmentType.ID desc или без desc в зависимости от того, какой там кластерный индекс.

А вообще, я так понял это - некий универсальный запрос?
Вы пытаетесь совместить две задачи - посчитать все и выбрать первые 50 записей - в один быстрый план? Я правильно понял?
Это оптимизатору трудно сделать, две принципиально разные стратегии...в одном случае, быстрее сканировать, в другом - быстрее найти найти по индексу.

Но если запрос формируется динамически - не вижу проблем оптимизировать один запрос, не затрагивая другой. Если в процедуре - нет проблем развести на разные ветки кода. Если запрос определяется параметрами - возможно поможет option(recompile) и если версия сервера позволяет - оптимизатор оставит только нужное.

Может быть я чего-то недопонял.
27 ноя 13, 19:16    [15201290]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить