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

Откуда:
Сообщений: 4
развернули 1С 7.7 под ms sql 2008. запрос теперь нужно как-то оптимизировать, а то скорость формирования отчета - угрожающе низкая
SELECT ACCID,CURRID,FLAGS,SC1=CASE WHEN VSC0=2509 THEN SC0 WHEN VSC1=2509 THEN SC1 WHEN VSC2=2509 THEN SC2 ELSE ' ' END
,SUM_=CASE KIND WHEN '1' THEN SD ELSE 0 END,AMOUNT=CASE KIND WHEN '3' THEN SD ELSE 0 END
FROM #SCID DTSC0,dbo._1SBKTTL BKTTL(NOLOCK)
WHERE DATE='20111001' AND ( DTSC0.CORR=0 AND DTSC0.VSC=2508 AND ( BKTTL.VSC0=2508 AND BKTTL.SC0=DTSC0.SC
OR BKTTL.VSC1=2508 AND BKTTL.SC1=DTSC0.SC
OR BKTTL.VSC2=2508 AND BKTTL.SC2=DTSC0.SC
) )
AND ( BKTTL.VSC0=2509 OR BKTTL.VSC1=2509 OR BKTTL.VSC2=2509 )
AND (KIND = '1' OR KIND = '3')
) AS SD

К сообщению приложен файл (24022012-1.7z - 9Kb) cкачать
24 фев 12, 10:50    [12145426]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2793
Попробуйту избавиться от OR разбив подзапрос на несколько отдельных запросов без OR объединив их через union. Ну и , соответсвующие индексы.
24 фев 12, 10:59    [12145470]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
Prolog
Попробуйту избавиться от OR разбив подзапрос на несколько отдельных запросов без OR объединив их через union. Ну и , соответсвующие индексы.


как я понимаю, в 1С нельзя ручками запросы писать и править...

ps но могу ошибаться...
24 фев 12, 11:15    [12145550]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Prolog
Попробуйту избавиться от OR разбив подзапрос на несколько отдельных запросов без OR объединив их через union. Ну и , соответсвующие индексы.

Явно мимо, на планы то посмотрите.
24 фев 12, 11:20    [12145593]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SubLunaAmo
развернули 1С 7.7 под ms sql 2008. запрос теперь нужно как-то оптимизировать, а то скорость формирования отчета - угрожающе низкая
SELECT ACCID,CURRID,FLAGS,SC1=CASE WHEN VSC0=2509 THEN SC0 WHEN VSC1=2509 THEN SC1 WHEN VSC2=2509 THEN SC2 ELSE ' ' END
,SUM_=CASE KIND WHEN '1' THEN SD ELSE 0 END,AMOUNT=CASE KIND WHEN '3' THEN SD ELSE 0 END
FROM #SCID DTSC0,dbo._1SBKTTL BKTTL(NOLOCK)
WHERE DATE='20111001' AND ( DTSC0.CORR=0 AND DTSC0.VSC=2508 AND ( BKTTL.VSC0=2508 AND BKTTL.SC0=DTSC0.SC
OR BKTTL.VSC1=2508 AND BKTTL.SC1=DTSC0.SC
OR BKTTL.VSC2=2508 AND BKTTL.SC2=DTSC0.SC
) )
AND ( BKTTL.VSC0=2509 OR BKTTL.VSC1=2509 OR BKTTL.VSC2=2509 )
AND (KIND = '1' OR KIND = '3')
) AS SD

Почему вы выложили только один запрос (и тот обрезанный) когда у вас их там штуки 4?
Почему вы решили что именно этот запрос тормозит?
Это предварительный план из которого мало что можно сказать это раз. Во-вторых план может быть совсем другой в зависимости от того сколько строк будет в #SCID, о чем тоже можно только догадываться. Нужен реальный план.
А еще у вас там цикл который не понятно сколько раз выполнится:

    WHILE 1=1 BEGIN
        SELECT @SCID=MAX(SC) FROM #SCID WHERE CORR=0 AND VSC=-2508
        IF @SCID IS NULL BREAK
        INSERT INTO #SCID SELECT 0, CASE WHEN ISFOLDER=2 THEN 2508 ELSE -2508 END, ID FROM dbo.SC33 WHERE PARENTID=SUBSTRING(@SCID, 1, 9)
        DELETE FROM #SCID WHERE CORR=0 AND VSC=-2508 AND SC=@SCID
    END

Ну и напоследок, обновлять статистику пробовали?
24 фев 12, 11:26    [12145626]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
SanyL
Member

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

да походу там не в статистике проблема... конечно если судить по вылоденному плану...

А выложил он его походу потому что из-за него идет 100% нагрузки, согласно данному плану. Реальный план действительно покажет как на самом деле идет распределение нагрузки - ток реальный не отличается больше ни чем от предварительного...
24 фев 12, 11:32    [12145669]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SanyL
Mind,
да походу там не в статистике проблема... конечно если судить по вылоденному плану...
А в чем там проблема?
SanyL
А выложил он его походу потому что из-за него идет 100% нагрузки, согласно данному плану.
Эти 100% предполагаемой нагрузки могут быть абсолютно ни о чем.
SanyL
Реальный план действительно покажет как на самом деле идет распределение нагрузки - ток реальный не отличается больше ни чем от предварительного...
В данном случае реальный план может отличаться от предварительного. Как насчет перекомпиляции из-за временной таблицы?
24 фев 12, 20:52    [12150170]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
А у меня вообще какой-то покоцаный план скачался из архива...
Все if обрываются на условии, никаких циклов я там не увидел, в запросе который более-менее целиком, только какой-то кросс-джоин маячил, т.к. в услвии джойны типа такого (t1.a = t2.a or t1.b = m)...
Ничего не понял.
Ну и да, полный, действительный план будет более полезен...
24 фев 12, 22:22    [12150533]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
SubLunaAmo
Member

Откуда:
Сообщений: 4
SanyL, да, под 1С sql-запрос не знаю как переписать... по моему не получится(
27 фев 12, 02:15    [12154539]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
Mind
SanyL
Mind,
да походу там не в статистике проблема... конечно если судить по вылоденному плану...
А в чем там проблема?
SanyL
А выложил он его походу потому что из-за него идет 100% нагрузки, согласно данному плану.
Эти 100% предполагаемой нагрузки могут быть абсолютно ни о чем.
SanyL
Реальный план действительно покажет как на самом деле идет распределение нагрузки - ток реальный не отличается больше ни чем от предварительного...
В данном случае реальный план может отличаться от предварительного. Как насчет перекомпиляции из-за временной таблицы?



Отличие реального плана от временного состоит только в результирующей расстановке нагрузок... т.е. MSSQL не строит план по нескольку раз - он строит план выполенния и оценивает нагрузки (это мы называем предварительным планом) дальше он в соответствии с ним выполняет запрос и по факту выдает как на самом деле разлеглись нагрузки (а тут мы смотрим реальный)...
27 фев 12, 11:20    [12155469]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
SubLunaAmo
Member

Откуда:
Сообщений: 4
я весь запрос выложила, не ужели только прямой запрос вместо запроса к БИ спасти может?? Это же прямо дикую кучу отчетов переписывать, УЖС!!!

К сообщению приложен файл (28022012-диффЗапрос.7z - 9Kb) cкачать
28 фев 12, 11:00    [12162076]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
ушедший в даль
Guest
1С ка она такая хитрая....
28 фев 12, 11:09    [12162138]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SanyL
Mind
пропущено...
А в чем там проблема?
пропущено...
Эти 100% предполагаемой нагрузки могут быть абсолютно ни о чем.
пропущено...
В данном случае реальный план может отличаться от предварительного. Как насчет перекомпиляции из-за временной таблицы?



Отличие реального плана от временного состоит только в результирующей расстановке нагрузок... т.е. MSSQL не строит план по нескольку раз - он строит план выполенния и оценивает нагрузки (это мы называем предварительным планом) дальше он в соответствии с ним выполняет запрос и по факту выдает как на самом деле разлеглись нагрузки (а тут мы смотрим реальный)...

SanyL
Mind
пропущено...
А в чем там проблема?
пропущено...
Эти 100% предполагаемой нагрузки могут быть абсолютно ни о чем.
пропущено...
В данном случае реальный план может отличаться от предварительного. Как насчет перекомпиляции из-за временной таблицы?



Отличие реального плана от временного состоит только в результирующей расстановке нагрузок... т.е. MSSQL не строит план по нескольку раз - он строит план выполенния и оценивает нагрузки (это мы называем предварительным планом) дальше он в соответствии с ним выполняет запрос и по факту выдает как на самом деле разлеглись нагрузки (а тут мы смотрим реальный)...

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

USE AdventureWorks
CREATE TABLE #TEMP(DepartmentID int )

INSERT INTO #TEMP
SELECT DepartmentID
FROM HumanResources.Department

SELECT * 
FROM HumanResources.EmployeeDepartmentHistory EDH
INNER JOIN #TEMP T ON T.DepartmentID = EDH.DepartmentID

DROP TABLE #TEMP
И планы для SELECTа,

Estimated:
  |--Nested Loops(Inner Join, OUTER REFERENCES:([EDH].[EmployeeID], [EDH].[DepartmentID], [EDH].[ShiftID], [EDH].[StartDate]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([T].[DepartmentID]))
| |--Table Scan(OBJECT:([tempdb].[dbo].[#TEMP] AS [T]))
| |--Index Seek(OBJECT:([AdventureWorks].[HumanResources].[EmployeeDepartmentHistory].[IX_EmployeeDepartmentHistory_DepartmentID] AS [EDH]), SEEK:([EDH].[DepartmentID]=[tempdb].[dbo].[#TEMP].[DepartmentID] as [T].[DepartmentID]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([AdventureWorks].[HumanResources].[EmployeeDepartmentHistory].[PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID] AS [EDH]), SEEK:([EDH].[EmployeeID]=[AdventureWorks....) LOOKUP ORDERED FORWARD)

Actual:
  |--Hash Match(Inner Join, HASH:([T].[DepartmentID])=([EDH].[DepartmentID]), RESIDUAL:([AdventureWorks].[HumanResources].[EmployeeDepartmentHistory].[DepartmentID] as [EDH].[DepartmentID]=[tempdb].[dbo].[#TEMP].[DepartmentID] as [T].[DepartmentID]))
|--Table Scan(OBJECT:([tempdb].[dbo].[#TEMP] AS [T]))
|--Clustered Index Scan(OBJECT:([AdventureWorks].[HumanResources].[EmployeeDepartmentHistory].[PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID] AS [EDH]))

И еще, реальный план не показывает никаких реальных нагрузок. Все проценты и стоимости базируются исключительно на предварительных оценках. Все что добавляется в реальном плане так это Actual Number of Rows.
28 фев 12, 21:56    [12166784]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SubLunaAmo
я весь запрос выложила, не ужели только прямой запрос вместо запроса к БИ спасти может?? Это же прямо дикую кучу отчетов переписывать, УЖС!!!

Реальный план нужен, а не предварительный.

Если кто захочет поразбираться, то вот весь запрос, а не тот обрубок что был выложен в начале топика:
SET NOCOUNT ON;
CREATE TABLE #SCID( CORR TINYINT,VSC INTEGER,SC CHAR(13), PRIMARY KEY( CORR, VSC, SC ) )
DECLARE @SCID CHAR(13)
IF EXISTS ( SELECT * FROM SC33 WHERE ID='   BP5СКЛ' AND ISFOLDER=1 ) BEGIN
    INSERT INTO #SCID SELECT 0, CASE WHEN ISFOLDER=2 THEN 2508 ELSE -2508 END, ID FROM SC33 WHERE PARENTID='   BP5СКЛ'
    WHILE 1=1 BEGIN
        SELECT @SCID=MAX(SC) FROM #SCID WHERE CORR=0 AND VSC=-2508
        IF @SCID IS NULL BREAK
        INSERT INTO #SCID SELECT 0, CASE WHEN ISFOLDER=2 THEN 2508 ELSE -2508 END, ID FROM SC33 WHERE PARENTID=SUBSTRING(@SCID, 1, 9)
        DELETE FROM #SCID WHERE CORR=0 AND VSC=-2508 AND SC=@SCID
    END
END
ELSE
    INSERT #SCID VALUES( 0, 2508, '   BP5СКЛ' )

IF EXISTS(SELECT * FROM _1SACCS WITH (NOLOCK) WHERE (SC0 = 2508 OR SC1 = 2508 OR SC2 = 2508) AND (SC0 = 2509 OR SC1 = 2509 OR SC2 = 2509) AND (SC0 = 2508 AND OSC0 = 1 OR SC1 = 2508 AND OSC1 = 1 OR SC2 = 2508 AND OSC2 = 1 OR SC0 = 2509 AND OSC0 = 1 OR SC1 = 2509 AND OSC1 = 1 OR SC2 = 2509 AND OSC2 = 1))
 SELECT 1 ELSE SELECT 0

SELECT ACCID,CURRID,FLAGS=MAX(FLAGS),SC1,SUM_=SUM(SUM_),AMOUNT=SUM(AMOUNT)
FROM
(
SELECT ACCID,CURRID,FLAGS,SC1=CASE  WHEN VSC0=2509 THEN SC0 WHEN VSC1=2509 THEN SC1 WHEN VSC2=2509 THEN SC2 ELSE '             ' END
,SUM_=CASE KIND WHEN '1' THEN SD ELSE 0 END,AMOUNT=CASE KIND WHEN '3' THEN SD ELSE 0 END
FROM  #SCID DTSC0,_1SBKTTL BKTTL WITH (NOLOCK)
WHERE DATE='20111001' AND ( DTSC0.CORR=0 AND DTSC0.VSC=2508 AND ( BKTTL.VSC0=2508 AND BKTTL.SC0=DTSC0.SC
 OR BKTTL.VSC1=2508 AND BKTTL.SC1=DTSC0.SC
 OR BKTTL.VSC2=2508 AND BKTTL.SC2=DTSC0.SC
 ) )
 AND ( BKTTL.VSC0=2509 OR BKTTL.VSC1=2509 OR BKTTL.VSC2=2509 )
 AND (KIND = '1' OR KIND = '3')
) AS SD
GROUP BY ACCID,CURRID,SC1
HAVING SUM(SUM_)<>0 OR SUM(AMOUNT)<>0

SELECT ACCID,CURRID,FLAGS=MAX(FLAGS),SC1,SUM_=SUM(SUM_),AMOUNT=SUM(AMOUNT)
FROM
(
SELECT ACCID,CURRID,FLAGS,SC1=CASE  WHEN VSC0=2509 THEN SC0 WHEN VSC1=2509 THEN SC1 WHEN VSC2=2509 THEN SC2 ELSE '             ' END
,SUM_=CASE KIND WHEN '1' THEN SD ELSE 0 END,AMOUNT=CASE KIND WHEN '3' THEN SD ELSE 0 END
FROM  #SCID DTSC0,_1SBKTTL BKTTL WITH (NOLOCK)
WHERE DATE='20111001' AND ( DTSC0.CORR=0 AND DTSC0.VSC=2508 AND ( BKTTL.VSC0=2508 AND BKTTL.SC0=DTSC0.SC
 OR BKTTL.VSC1=2508 AND BKTTL.SC1=DTSC0.SC
 OR BKTTL.VSC2=2508 AND BKTTL.SC2=DTSC0.SC
 ) )
 AND ( BKTTL.VSC0=2509 OR BKTTL.VSC1=2509 OR BKTTL.VSC2=2509 )
 AND (KIND = '1' OR KIND = '3')
) AS SD
GROUP BY ACCID,CURRID,SC1
HAVING SUM(SUM_)<>0 OR SUM(AMOUNT)<>0

SELECT ACCID,CURRID,FLAGS=MAX(FLAGS),DATE,SC1,SUMDT=SUM(SUMDT),SUMKT=SUM(SUMKT),AMOUNTDT=SUM(AMOUNTDT),AMOUNTKT=SUM(AMOUNTKT)
 FROM
 (
SELECT ACCID,CURRID,FLAGS,DATE=CONVERT( DATETIME, '20111001' ),SC1=CASE  WHEN VSC0=2509 THEN SC0 WHEN VSC1=2509 THEN SC1 WHEN VSC2=2509 THEN SC2 ELSE '             ' END
,SUMDT=CASE KIND WHEN '1' THEN OBDT1+OBDT2+OBDT3 ELSE 0 END,SUMKT=CASE KIND WHEN '1' THEN OBKT1+OBKT2+OBKT3 ELSE 0 END,AMOUNTDT=CASE KIND WHEN '3' THEN OBDT1+OBDT2+OBDT3 ELSE 0 END,AMOUNTKT=CASE KIND WHEN '3' THEN OBKT1+OBKT2+OBKT3 ELSE 0 END
FROM  #SCID DTSC0,_1SBKTTL BKTTL WITH (NOLOCK)
WHERE DATE>='20111001' AND DATE<='20111001' AND KIND>='1' AND KIND<='3' AND ( DTSC0.CORR=0 AND DTSC0.VSC=2508 AND ( BKTTL.VSC0=2508 AND BKTTL.SC0=DTSC0.SC
 OR BKTTL.VSC1=2508 AND BKTTL.SC1=DTSC0.SC
 OR BKTTL.VSC2=2508 AND BKTTL.SC2=DTSC0.SC
 ) )
 AND ( BKTTL.VSC0=2509 OR BKTTL.VSC1=2509 OR BKTTL.VSC2=2509 )
) AS OB
 GROUP BY ACCID,CURRID,DATE,SC1
HAVING SUM(SUMDT)<>0 OR SUM(SUMKT)<>0 OR SUM(AMOUNTDT)<>0 OR SUM(AMOUNTKT)<>0 ORDER BY DATE
28 фев 12, 22:03    [12166807]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
SubLunaAmo
Member

Откуда:
Сообщений: 4
Mind, я же вчера весь запрос выложила)
Только не пойму, может не на той ветке вопрошаю?? Кто столкнулся с проблемой "затупа" стандартных запросов (например к БИ) при переводе 1С семерки на sql? Сам стандартный запрос С-ки к БИ "ВыполнитьЗапрос(<?>,,,,,,,)" как оптимизировать? он то на самом деле и отжирает львиную долю времени формирования отчета...
а в переводимой под SQL БД таких запросов куча! Их что все перерисовывать? Ну должно же быть какое-то лекарство!!
29 фев 12, 02:16    [12167294]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SubLunaAmo
Mind, я же вчера весь запрос выложила)

Тут с форума ничего не удаляется, так что где выложили, там и спрашивайте :)

SubLunaAmo
Только не пойму, может не на той ветке вопрошаю?? Кто столкнулся с проблемой "затупа" стандартных запросов (например к БИ) при переводе 1С семерки на sql? Сам стандартный запрос С-ки к БИ "ВыполнитьЗапрос(<?>,,,,,,,)" как оптимизировать? он то на самом деле и отжирает львиную долю времени формирования отчета...
а в переводимой под SQL БД таких запросов куча! Их что все перерисовывать? Ну должно же быть какое-то лекарство!!

Что такое 1С семерка, а тем более БИ я понятия не имею. А лекарство может быть только после диагностики, а пациент всё время сбегает и реальные планы не показывает.
29 фев 12, 09:47    [12167757]     Ответить | Цитировать Сообщить модератору
 Re: как оптимизировать план запроса в 1С под SQL2008  [new]
SergePnb
Member

Откуда: Киев
Сообщений: 456
Mind
Что такое 1С семерка, а тем более БИ я понятия не имею

1С семерка - 1Сv7.x, БИ - объект бухгалтерские итоги внутри оной, что означает ТС пишет запросы на "языке" 1С, а та, в свою очередь, транслирует их в tSQL. Влиять на генерацию tSQL кода в этой схеме ТС не в состоянии (хотя вроде как есть там какие-то сторонние приблуды, но это не БИ точно), так что идеи хинтования, изменения тела запроса и пр предлагать не имеет смысла. Я думаю что для начала ТС надо проверить как сервер себя "чувствует" - достаточно ли памяти, нет ли узкого места по ЦП, актуальны ли статистики и пр. Если все Ок, то с тем ужасом, что генерирует 1Сv7 придется перейти к "прямым" запросам
29 фев 12, 13:09    [12169431]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить