bromi
22.02.2017, 17:19
Добрый день.
Преамбула:
1) имели версию 7.5.2 самую последнюю очень долго, дилер провёл обновление на 7.5.7.55 через промежуточное 7.5.4.239, без инсталлятора, копирование поверх и запуском рефсервера.
2) Попутно произвели переезд с 2008R2SP3 на 2016SP1 SQL методом восстановления базы из бэкапа и замены коннект строки в менеджере при usesql=0.
Обнаружили, что кубы по расходу блюд считаются безумно долго и вылетают за все разумные пределы. Более плотное расследование показало, что куб и до апгрейда ходил по грани, высчитываясь 3 часа ночами, после апгрейда стал 4 часа. База немаленькая, около 6гб, за 2 года, но это не повод, подумали мы, размер итоговой плоской таблицы из которой собирается Куб, всего 2 мильона строк. Не на 4 часа проблема. На практике время занимает именно выгрузка данных через вьюшку из SQL: (сейчас стоит куб лимитированный по времени, поэтому занимает "всего" 2 часа.
22.02 01:00:02 Cube "Куб для отчетов по расходу блюд" timeout set
22.02 01:00:02 Cube "Куб для отчетов по расходу блюд" struct loaded
22.02 01:00:02 Cube "Куб для отчетов по расходу блюд" -> Load from ADO
22.02 03:04:30 Cube "Куб для отчетов по расходу блюд" <- Load from ADO
22.02 03:04:30 Cube "Куб для отчетов по расходу блюд" cube loaded
22.02 03:04:30 Cube "Куб для отчетов по расходу блюд" building started
22.02 03:05:43 Cube "Куб для отчетов по расходу блюд" cube saved
22.02 03:05:43 Cube "Куб для отчетов по расходу блюд" processed.Ok
22.02 03:05:43 Cube processor stopped, TotalSize=13769368
Я не эксперт по оптимизации SQL, но с профильными коллегами начал копать, обнаружил, что запрос выполняется на сервере весьма неэффективно, джойны происходят странно и вообще. Более простую версию запроса (не по всем таблицам) хинтами по джойнам удалось оптимизировать на пару порядков, то есть реально сократить с 20 минут до 10 секунд (sic!).
Теперь амбула: прежде чем браться за переписывание полного огромного запроса этого куба (кстати, там внутри есть джойн по вьюшке, дублирующей половину используемых столбцов уже внутри, и естественно не имеющей индекса -- LEFT JOIN SaleObjects SaleObjects00) -- коллеги, подскажите, как выглядит у вас запрос этого куба? Может быть, в свежих версиях запрос давно оптимизирован, но очевидно что при таком апгрейде он не изменится?
(По совету дилера я пробовал запустить Refsrv с Upgradeanytime=1, в надежде на перестройку вьюшки, полный лог попыток можно увидеть на http://pastebin.com/YSwsg0Fp, кроме дампов стэка необычного я там нашёл только
>> 158:Too big time from switch 29000. task dump printed (такие стали появляться в логе после апгрейда на 7.5.5 в целом)
>> 381:Item with id 201 already exists: ignored in TFieldedSortCollection for TRK7ColorSchemeDetail
>> 5004:Exception "Exception Exception with message "Недопустимое имя объекта "vrk7CubeView1002""" (code: -1) during cube loading
1002 -- это как раз куб по расходу блюд, не знаю, пытался ли он его пересоздать но зафейлил, или в чём история.
Так или иначе, мой текущий SQL-запрос этого куба выглядит так (в менеджерской части, на практике он дёргает вьюшку в скле):
SELECT
EMPLOYEES00."NAME" AS "WAITER",
SaleObjects00."CODE" AS "CODE",
SaleObjects00."NAME" AS "DISH",
PayBindings."QUANTITY" AS "QUANTITY",
PayBindings."PAYSUM" AS "PAYSUM",
PayBindings."PRICESUM" AS "PRLISTSUM",
CLASSIFICATORGROUPS0000.NAME AS "CATEGORY",
DishDiscounts00."EXCLUDEFROMEARNINGS" AS "EXCLUDEFROMEARNINGS",
UNCHANGEABLEORDERTYPES00."NAME" AS "ORDERCATEGORY",
PrintChecks00."CHECKNUM" AS "CHECKNUM",
GLOBALSHIFTS00."SHIFTNUM" AS "SHIFTNUM",
OrderSessions00."PRINTAT" AS "PRINTAT___13",
GLOBALSHIFTS00."SHIFTDATE" AS "SHIFTDATE",
dbo.propCategPath(MENUITEMS00.SIFR) AS "CATEGPATH",
CASHES00."NAME" AS "CLOSESTATION",
CASHGROUPS00."NETNAME" AS "NETNAME",
CURRENCYTYPES00."NAME" AS "CURRENCYTYPE",
CURRENCIES00."NAME" AS "CURRENCY",
CURRENCIES00."CODE" AS "CURRENCYCODE",
CLASSIFICATORGROUPS0000.SORTORDER AS "SORTORDER",
Shifts00."PRINTSHIFTNUM" AS "CASHSHIFTNUM",
TABLES00."NAME" AS "TABLE",
Orders00."ORDERNAME" AS "ORDERNAME",
PaymentsExtra00."CARDNUM" AS "CARDNUM",
trk7EnumsValues1E00.UserMName AS "OBJKIND",
PayBindings."TAXESADDED" AS "TAXESADDED",
RESTAURANTS00."NAME" AS "RESTAURANTNAME",
EMPLOYEES01."NAME" AS "DISHCREATOR",
CurrLines00."DBKURS" AS "DBKURS",
MENUITEMS01."NAME" AS "COMBODISH",
PrintChecks00."CLOSEDATETIME" AS "CLOSEDATETIME___37",
trk7EnumsValues3600.UserMName AS "STATUS",
CATEGLIST00."NAME" AS "NAME1",
CATEGLIST00."CODE" AS "CODE1",
CLASSIFICATORGROUPS0000.NAME AS "F00000040"
FROM PAYBINDINGS
JOIN CurrLines CurrLines00
ON (CurrLines00.Visit = PayBindings.Visit) AND (CurrLines00.MidServer = PayBindings.MidServer) AND (CurrLines00.UNI = PayBindings.CurrUNI)
JOIN PrintChecks PrintChecks00
ON (PrintChecks00.Visit = CurrLines00.Visit) AND (PrintChecks00.MidServer = CurrLines00.MidServer) AND (PrintChecks00.UNI = CurrLines00.CheckUNI)
JOIN Orders Orders00
ON (Orders00.Visit = PayBindings.Visit) AND (Orders00.MidServer = PayBindings.MidServer) AND (Orders00.IdentInVisit = PayBindings.OrderIdent)
LEFT JOIN EMPLOYEES EMPLOYEES00
ON (EMPLOYEES00.SIFR = Orders00.MainWaiter)
LEFT JOIN SaleObjects SaleObjects00
ON (SaleObjects00.Visit = PayBindings.Visit) AND (SaleObjects00.MidServer = PayBindings.MidServer) AND (SaleObjects00.DishUNI = PayBindings.DishUNI) AND (SaleObjects00.ChargeUNI = PayBindings.ChargeUNI)
LEFT JOIN SessionDishes SessionDishes00
ON (SessionDishes00.Visit = SaleObjects00.Visit) AND (SessionDishes00.MidServer = SaleObjects00.MidServer) AND (SessionDishes00.UNI = SaleObjects00.DishUNI)
LEFT JOIN MENUITEMS MENUITEMS00
ON (MENUITEMS00.SIFR = SessionDishes00.Sifr)
LEFT JOIN DISHGROUPS DISHGROUPS0000
ON (DISHGROUPS0000.CHILD = MENUITEMS00.SIFR) AND (DISHGROUPS0000.CLASSIFICATION = 512)
LEFT JOIN CLASSIFICATORGROUPS CLASSIFICATORGROUPS0000
ON CLASSIFICATORGROUPS0000.SIFR * 256 + CLASSIFICATORGROUPS0000.NumInGroup = DISHGROUPS0000.PARENT
LEFT JOIN DishDiscounts DishDiscounts00
ON (DishDiscounts00.Visit = SaleObjects00.Visit) AND (DishDiscounts00.MidServer = SaleObjects00.MidServer) AND (DishDiscounts00.UNI = SaleObjects00.ChargeUNI)
LEFT JOIN UNCHANGEABLEORDERTYPES UNCHANGEABLEORDERTYPES00
ON (UNCHANGEABLEORDERTYPES00.SIFR = Orders00.UOT)
JOIN GLOBALSHIFTS GLOBALSHIFTS00
ON (GLOBALSHIFTS00.MidServer = Orders00.MidServer) AND (GLOBALSHIFTS00.ShiftNum = Orders00.iCommonShift)
LEFT JOIN OrderSessions OrderSessions00
ON (OrderSessions00.Visit = SaleObjects00.Visit) AND (OrderSessions00.MidServer = SaleObjects00.MidServer) AND (OrderSessions00.UNI = SaleObjects00.SessionUNI)
LEFT JOIN CASHES CASHES00
ON (CASHES00.SIFR = PrintChecks00.iCloseStation)
LEFT JOIN CASHGROUPS CASHGROUPS00
ON (CASHGROUPS00.SIFR = PayBindings.Midserver)
LEFT JOIN CURRENCYTYPES CURRENCYTYPES00
ON (CURRENCYTYPES00.SIFR = CurrLines00.iHighLevelType)
LEFT JOIN CURRENCIES CURRENCIES00
ON (CURRENCIES00.SIFR = CurrLines00.Sifr)
LEFT JOIN Shifts Shifts00
ON (Shifts00.MidServer = PrintChecks00.MidServer) AND (Shifts00.iStation = PrintChecks00.iCloseStation) AND (Shifts00.ShiftNum = PrintChecks00.iShift)
LEFT JOIN TABLES TABLES00
ON (TABLES00.SIFR = Orders00.TableID)
LEFT JOIN PaymentsExtra PaymentsExtra00
ON (PaymentsExtra00.Visit = CurrLines00.Visit) AND (PaymentsExtra00.MidServer = CurrLines00.MidServer) AND (PaymentsExtra00.PayUNI = CurrLines00.PayUNIForOwnerInfo)
LEFT JOIN trk7EnumsValues trk7EnumsValues1E00
ON (trk7EnumsValues1E00.EnumData = SaleObjects00.OBJKIND) AND (trk7EnumsValues1E00.EnumName = 'tSaleObjectKind')
LEFT JOIN RESTAURANTS RESTAURANTS00
ON (RESTAURANTS00.SIFR = CASHGROUPS00.Restaurant)
LEFT JOIN EMPLOYEES EMPLOYEES01
ON (EMPLOYEES01.SIFR = SaleObjects00.iAuthor)
LEFT JOIN SessionDishes SessionDishes01
ON (SessionDishes01.Visit = SessionDishes00.Visit) AND (SessionDishes01.Midserver = SessionDishes00.Midserver) AND (SessionDishes01.UNI = SessionDishes00.ComboDishUNI)
LEFT JOIN MENUITEMS MENUITEMS01
ON (MENUITEMS01.SIFR = SessionDishes01.Sifr)
LEFT JOIN trk7EnumsValues trk7EnumsValues3600
ON (trk7EnumsValues3600.EnumData = GLOBALSHIFTS00.STATUS) AND (trk7EnumsValues3600.EnumName = 'TRecordStatus')
LEFT JOIN CATEGLIST CATEGLIST00
ON (CATEGLIST00.SIFR = MENUITEMS00.PARENT)
WHERE
((PrintChecks00."STATE" = 6))
AND (GLOBALSHIFTS00."SHIFTDATE" BETWEEN CONVERT(DATETIME, '2016.01.01', 102) AND CONVERT(DATETIME, '2100.01.01', 102))
AND ((GLOBALSHIFTS00.STATUS = 3))
Поможете?
Преамбула:
1) имели версию 7.5.2 самую последнюю очень долго, дилер провёл обновление на 7.5.7.55 через промежуточное 7.5.4.239, без инсталлятора, копирование поверх и запуском рефсервера.
2) Попутно произвели переезд с 2008R2SP3 на 2016SP1 SQL методом восстановления базы из бэкапа и замены коннект строки в менеджере при usesql=0.
Обнаружили, что кубы по расходу блюд считаются безумно долго и вылетают за все разумные пределы. Более плотное расследование показало, что куб и до апгрейда ходил по грани, высчитываясь 3 часа ночами, после апгрейда стал 4 часа. База немаленькая, около 6гб, за 2 года, но это не повод, подумали мы, размер итоговой плоской таблицы из которой собирается Куб, всего 2 мильона строк. Не на 4 часа проблема. На практике время занимает именно выгрузка данных через вьюшку из SQL: (сейчас стоит куб лимитированный по времени, поэтому занимает "всего" 2 часа.
22.02 01:00:02 Cube "Куб для отчетов по расходу блюд" timeout set
22.02 01:00:02 Cube "Куб для отчетов по расходу блюд" struct loaded
22.02 01:00:02 Cube "Куб для отчетов по расходу блюд" -> Load from ADO
22.02 03:04:30 Cube "Куб для отчетов по расходу блюд" <- Load from ADO
22.02 03:04:30 Cube "Куб для отчетов по расходу блюд" cube loaded
22.02 03:04:30 Cube "Куб для отчетов по расходу блюд" building started
22.02 03:05:43 Cube "Куб для отчетов по расходу блюд" cube saved
22.02 03:05:43 Cube "Куб для отчетов по расходу блюд" processed.Ok
22.02 03:05:43 Cube processor stopped, TotalSize=13769368
Я не эксперт по оптимизации SQL, но с профильными коллегами начал копать, обнаружил, что запрос выполняется на сервере весьма неэффективно, джойны происходят странно и вообще. Более простую версию запроса (не по всем таблицам) хинтами по джойнам удалось оптимизировать на пару порядков, то есть реально сократить с 20 минут до 10 секунд (sic!).
Теперь амбула: прежде чем браться за переписывание полного огромного запроса этого куба (кстати, там внутри есть джойн по вьюшке, дублирующей половину используемых столбцов уже внутри, и естественно не имеющей индекса -- LEFT JOIN SaleObjects SaleObjects00) -- коллеги, подскажите, как выглядит у вас запрос этого куба? Может быть, в свежих версиях запрос давно оптимизирован, но очевидно что при таком апгрейде он не изменится?
(По совету дилера я пробовал запустить Refsrv с Upgradeanytime=1, в надежде на перестройку вьюшки, полный лог попыток можно увидеть на http://pastebin.com/YSwsg0Fp, кроме дампов стэка необычного я там нашёл только
>> 158:Too big time from switch 29000. task dump printed (такие стали появляться в логе после апгрейда на 7.5.5 в целом)
>> 381:Item with id 201 already exists: ignored in TFieldedSortCollection for TRK7ColorSchemeDetail
>> 5004:Exception "Exception Exception with message "Недопустимое имя объекта "vrk7CubeView1002""" (code: -1) during cube loading
1002 -- это как раз куб по расходу блюд, не знаю, пытался ли он его пересоздать но зафейлил, или в чём история.
Так или иначе, мой текущий SQL-запрос этого куба выглядит так (в менеджерской части, на практике он дёргает вьюшку в скле):
SELECT
EMPLOYEES00."NAME" AS "WAITER",
SaleObjects00."CODE" AS "CODE",
SaleObjects00."NAME" AS "DISH",
PayBindings."QUANTITY" AS "QUANTITY",
PayBindings."PAYSUM" AS "PAYSUM",
PayBindings."PRICESUM" AS "PRLISTSUM",
CLASSIFICATORGROUPS0000.NAME AS "CATEGORY",
DishDiscounts00."EXCLUDEFROMEARNINGS" AS "EXCLUDEFROMEARNINGS",
UNCHANGEABLEORDERTYPES00."NAME" AS "ORDERCATEGORY",
PrintChecks00."CHECKNUM" AS "CHECKNUM",
GLOBALSHIFTS00."SHIFTNUM" AS "SHIFTNUM",
OrderSessions00."PRINTAT" AS "PRINTAT___13",
GLOBALSHIFTS00."SHIFTDATE" AS "SHIFTDATE",
dbo.propCategPath(MENUITEMS00.SIFR) AS "CATEGPATH",
CASHES00."NAME" AS "CLOSESTATION",
CASHGROUPS00."NETNAME" AS "NETNAME",
CURRENCYTYPES00."NAME" AS "CURRENCYTYPE",
CURRENCIES00."NAME" AS "CURRENCY",
CURRENCIES00."CODE" AS "CURRENCYCODE",
CLASSIFICATORGROUPS0000.SORTORDER AS "SORTORDER",
Shifts00."PRINTSHIFTNUM" AS "CASHSHIFTNUM",
TABLES00."NAME" AS "TABLE",
Orders00."ORDERNAME" AS "ORDERNAME",
PaymentsExtra00."CARDNUM" AS "CARDNUM",
trk7EnumsValues1E00.UserMName AS "OBJKIND",
PayBindings."TAXESADDED" AS "TAXESADDED",
RESTAURANTS00."NAME" AS "RESTAURANTNAME",
EMPLOYEES01."NAME" AS "DISHCREATOR",
CurrLines00."DBKURS" AS "DBKURS",
MENUITEMS01."NAME" AS "COMBODISH",
PrintChecks00."CLOSEDATETIME" AS "CLOSEDATETIME___37",
trk7EnumsValues3600.UserMName AS "STATUS",
CATEGLIST00."NAME" AS "NAME1",
CATEGLIST00."CODE" AS "CODE1",
CLASSIFICATORGROUPS0000.NAME AS "F00000040"
FROM PAYBINDINGS
JOIN CurrLines CurrLines00
ON (CurrLines00.Visit = PayBindings.Visit) AND (CurrLines00.MidServer = PayBindings.MidServer) AND (CurrLines00.UNI = PayBindings.CurrUNI)
JOIN PrintChecks PrintChecks00
ON (PrintChecks00.Visit = CurrLines00.Visit) AND (PrintChecks00.MidServer = CurrLines00.MidServer) AND (PrintChecks00.UNI = CurrLines00.CheckUNI)
JOIN Orders Orders00
ON (Orders00.Visit = PayBindings.Visit) AND (Orders00.MidServer = PayBindings.MidServer) AND (Orders00.IdentInVisit = PayBindings.OrderIdent)
LEFT JOIN EMPLOYEES EMPLOYEES00
ON (EMPLOYEES00.SIFR = Orders00.MainWaiter)
LEFT JOIN SaleObjects SaleObjects00
ON (SaleObjects00.Visit = PayBindings.Visit) AND (SaleObjects00.MidServer = PayBindings.MidServer) AND (SaleObjects00.DishUNI = PayBindings.DishUNI) AND (SaleObjects00.ChargeUNI = PayBindings.ChargeUNI)
LEFT JOIN SessionDishes SessionDishes00
ON (SessionDishes00.Visit = SaleObjects00.Visit) AND (SessionDishes00.MidServer = SaleObjects00.MidServer) AND (SessionDishes00.UNI = SaleObjects00.DishUNI)
LEFT JOIN MENUITEMS MENUITEMS00
ON (MENUITEMS00.SIFR = SessionDishes00.Sifr)
LEFT JOIN DISHGROUPS DISHGROUPS0000
ON (DISHGROUPS0000.CHILD = MENUITEMS00.SIFR) AND (DISHGROUPS0000.CLASSIFICATION = 512)
LEFT JOIN CLASSIFICATORGROUPS CLASSIFICATORGROUPS0000
ON CLASSIFICATORGROUPS0000.SIFR * 256 + CLASSIFICATORGROUPS0000.NumInGroup = DISHGROUPS0000.PARENT
LEFT JOIN DishDiscounts DishDiscounts00
ON (DishDiscounts00.Visit = SaleObjects00.Visit) AND (DishDiscounts00.MidServer = SaleObjects00.MidServer) AND (DishDiscounts00.UNI = SaleObjects00.ChargeUNI)
LEFT JOIN UNCHANGEABLEORDERTYPES UNCHANGEABLEORDERTYPES00
ON (UNCHANGEABLEORDERTYPES00.SIFR = Orders00.UOT)
JOIN GLOBALSHIFTS GLOBALSHIFTS00
ON (GLOBALSHIFTS00.MidServer = Orders00.MidServer) AND (GLOBALSHIFTS00.ShiftNum = Orders00.iCommonShift)
LEFT JOIN OrderSessions OrderSessions00
ON (OrderSessions00.Visit = SaleObjects00.Visit) AND (OrderSessions00.MidServer = SaleObjects00.MidServer) AND (OrderSessions00.UNI = SaleObjects00.SessionUNI)
LEFT JOIN CASHES CASHES00
ON (CASHES00.SIFR = PrintChecks00.iCloseStation)
LEFT JOIN CASHGROUPS CASHGROUPS00
ON (CASHGROUPS00.SIFR = PayBindings.Midserver)
LEFT JOIN CURRENCYTYPES CURRENCYTYPES00
ON (CURRENCYTYPES00.SIFR = CurrLines00.iHighLevelType)
LEFT JOIN CURRENCIES CURRENCIES00
ON (CURRENCIES00.SIFR = CurrLines00.Sifr)
LEFT JOIN Shifts Shifts00
ON (Shifts00.MidServer = PrintChecks00.MidServer) AND (Shifts00.iStation = PrintChecks00.iCloseStation) AND (Shifts00.ShiftNum = PrintChecks00.iShift)
LEFT JOIN TABLES TABLES00
ON (TABLES00.SIFR = Orders00.TableID)
LEFT JOIN PaymentsExtra PaymentsExtra00
ON (PaymentsExtra00.Visit = CurrLines00.Visit) AND (PaymentsExtra00.MidServer = CurrLines00.MidServer) AND (PaymentsExtra00.PayUNI = CurrLines00.PayUNIForOwnerInfo)
LEFT JOIN trk7EnumsValues trk7EnumsValues1E00
ON (trk7EnumsValues1E00.EnumData = SaleObjects00.OBJKIND) AND (trk7EnumsValues1E00.EnumName = 'tSaleObjectKind')
LEFT JOIN RESTAURANTS RESTAURANTS00
ON (RESTAURANTS00.SIFR = CASHGROUPS00.Restaurant)
LEFT JOIN EMPLOYEES EMPLOYEES01
ON (EMPLOYEES01.SIFR = SaleObjects00.iAuthor)
LEFT JOIN SessionDishes SessionDishes01
ON (SessionDishes01.Visit = SessionDishes00.Visit) AND (SessionDishes01.Midserver = SessionDishes00.Midserver) AND (SessionDishes01.UNI = SessionDishes00.ComboDishUNI)
LEFT JOIN MENUITEMS MENUITEMS01
ON (MENUITEMS01.SIFR = SessionDishes01.Sifr)
LEFT JOIN trk7EnumsValues trk7EnumsValues3600
ON (trk7EnumsValues3600.EnumData = GLOBALSHIFTS00.STATUS) AND (trk7EnumsValues3600.EnumName = 'TRecordStatus')
LEFT JOIN CATEGLIST CATEGLIST00
ON (CATEGLIST00.SIFR = MENUITEMS00.PARENT)
WHERE
((PrintChecks00."STATE" = 6))
AND (GLOBALSHIFTS00."SHIFTDATE" BETWEEN CONVERT(DATETIME, '2016.01.01', 102) AND CONVERT(DATETIME, '2100.01.01', 102))
AND ((GLOBALSHIFTS00.STATUS = 3))
Поможете?