Имя: Пароль:
IT
 
Помогите оптимизировать SQL запрос
0 1Сергей
 
29.03.13
07:53
Обращаюсь из другой программы к 1С-совской базе на SQL.
Здесь:
   _Document6813 AS M - Шапка нужного документа
   _Document6813_VT6850 AS D - Табличная часть документа
   _InfoRg4714 AS RegProperty - Регистр сведений ЗначенияСвойствОбъектов
   _Reference35 AS Property - Справочник ЗначенияСвойствОбъектов
   

SELECT
   Types.TypeCode AS TypeCode,
   Types.TypeName AS TypeName,
   Cities.CityCode AS CityCode,
   Cities.CityName AS CityName,
   SUM(1) AS Qty,
   SUM(D._Fld6859) AS Fare,
   SUM(D._Fld6860) AS Tax,
   SUM(D._Fld6861) AS SscCharge,
   SUM(D._Fld6864) AS Comm,
   SUM(D._Fld6867) AS Discount,
   SUM(D._Fld6870 + D._Fld6873) AS AgCharge,
   SUM(D._Fld6859 + D._Fld6860 - D._Fld6864) AS ToCompany,
   SUM(D._Fld6875) AS Polucheno
FROM
   _Document6813 AS M
   INNER JOIN
       _Document6813_VT6850 AS D
   ON
       M._IDRRef = D._Document6813_IDRRef
   LEFT OUTER JOIN (
       SELECT
           CAST(Property._IDRRef AS uniqueidentifier) AS TypeCode,
           Property._Description AS TypeName,
           RegProperty._Fld4715_RRRef
       FROM
           _InfoRg4714 AS RegProperty
           LEFT OUTER JOIN
               _Reference35 AS Property
           ON
               Property._IDRRef = RegProperty._Fld4717_RRRef
       WHERE
           CAST(RegProperty._Fld4716RRef AS UniqueIdentifier) = CAST('5b488bad-2939-fdff-11e2-77332c339912' AS UniqueIdentifier)
   ) AS Types
   ON
       M._Fld6833RRef = Types._Fld4715_RRRef
   LEFT OUTER JOIN (
       SELECT
           CAST(Property._IDRRef AS uniqueidentifier) AS CityCode,
           Property._Description AS CityName,
           RegProperty._Fld4715_RRRef
       FROM
           _InfoRg4714 AS RegProperty
           LEFT OUTER JOIN
               _Reference35 AS Property
           ON
               Property._IDRRef = RegProperty._Fld4717_RRRef
       WHERE
           CAST(RegProperty._Fld4716RRef AS UniqueIdentifier) = CAST('5b488bad-2939-fdff-11e2-7beb1c993d16' AS UniqueIdentifier)
   ) AS Cities
   ON
       M._Fld6849RRef = Cities._Fld4715_RRRef
WHERE
   (M._Marked = 0x00)
   AND (M._Posted = 0x01)
   AND (M._Date_Time >= DATEADD(YEAR, 2000, @DateStart))
   AND (M._Date_Time <= DATEADD(SECOND, 86399, DATEADD(YEAR, 2000, @DateEnd)))
GROUP BY
   Types.TypeCode, Types.TypeName, Cities.CityCode, Cities.CityName
ORDER BY
   TypeName, CityName

Без обращения к табличкам Регистра и Справочника запрос выполняется меньше секунды. Как только пытаюсь вытащить Types и Cities, то запрос выполняется очень долго и вылетает по таймауту.

PS Временные таблицы в данном месте программы использовать не могу.
1 Chai Nic
 
29.03.13
08:00
А что в плане запроса?
2 Bugmenot
 
29.03.13
08:01
Покажи план запроса
3 Bugmenot
 
29.03.13
08:14
Так, возможно, будет чуть меньший i/o и расход памяти на агрегацию:

with totals as
(select M._Fld6833RRef, M._Fld6849RRef
   SUM(1) AS Qty,
   SUM(D._Fld6859) AS Fare,
   SUM(D._Fld6860) AS Tax,
   SUM(D._Fld6861) AS SscCharge,
   SUM(D._Fld6864) AS Comm,
   SUM(D._Fld6867) AS Discount,
   SUM(D._Fld6870 + D._Fld6873) AS AgCharge,
   SUM(D._Fld6859 + D._Fld6860 - D._Fld6864) AS ToCompany,
   SUM(D._Fld6875) AS Polucheno
FROM
   _Document6813 AS M
   INNER JOIN
       _Document6813_VT6850 AS D
   ON
       M._IDRRef = D._Document6813_IDRRef
WHERE
   (M._Marked = 0x00)
   AND (M._Posted = 0x01)
   AND (M._Date_Time >= DATEADD(YEAR, 2000, @DateStart))
   AND (M._Date_Time <= DATEADD(SECOND, 86399, DATEADD(YEAR, 2000, @DateEnd)))
GROUP BY
   M._Fld6833RRef, M._Fld6849RRef),
refs as (SELECT
           CAST(RegProperty._Fld4716RRef AS UniqueIdentifier) as reftype
           CAST(Property._IDRRef AS uniqueidentifier) AS TypeCode,
           Property._Description AS TypeName,
           RegProperty._Fld4715_RRRef
       FROM
           _InfoRg4714 AS RegProperty
           LEFT OUTER JOIN
               _Reference35 AS Property
           ON
               Property._IDRRef = RegProperty._Fld4717_RRRef
       WHERE
           CAST(RegProperty._Fld4716RRef AS UniqueIdentifier) in (
                CAST('5b488bad-2939-fdff-11e2-77332c339912' AS UniqueIdentifier),
                CAST('5b488bad-2939-fdff-11e2-7beb1c993d16' AS UniqueIdentifier))
SELECT
   Types.TypeCode AS TypeCode,
   Types.TypeName AS TypeName,
   Cities.CityCode AS CityCode,
   Cities.CityName AS CityName,
   Qty, Fare, Tax, SscCharge, Comm, Discount, AgCharge, ToCompany, Polucheno
FROM
   Totals
   left outer join refs Types on Totals._Fld6833RRef = Types._Fld4715_RRRef and Types.reftype=CAST('5b488bad-2939-fdff-11e2-77332c339912' AS UniqueIdentifier)
   left outer join refs Cities on Totals._Fld6849RRef = Cities._Fld4715_RRRef and Cities.reftype=CAST('5b488bad-2939-fdff-11e2-7beb1c993d16' AS UniqueIdentifier)
ORDER BY
   TypeName, CityName
4 Bugmenot
 
29.03.13
08:15
нда... syntax error...
Добавить запятую в конце второй строки:
(select M._Fld6833RRef, M._Fld6849RRef,
5 Bugmenot
 
29.03.13
08:19
и закрывающая скобка в 39 строке:
CAST('5b488bad-2939-fdff-11e2-7beb1c993d16' AS UniqueIdentifier)))
6 1Сергей
 
29.03.13
08:25
(1), (2) Блин, не могу найти как его получить в Вижал Студио
7 s03
 
29.03.13
08:36
Возьми для начала отдельно запрос получающий Types и проверь насколько он быстро выполняется (сам по себе, отдельно от остального), затем то же самое с Cities
8 1Сергей
 
29.03.13
08:36
(3) что-то не вижу где города выбираются
9 1Сергей
 
29.03.13
08:37
(7) Вообще, странно как-то. выполнил запрос (0) в самом SQL - меньше секунды. А в VS тормоза...
10 МихаилМ
 
29.03.13
08:38
выкиньте все
CAST( AS UniqueIdentifier)
11 1Сергей
 
29.03.13
08:40
(10) в каком месте?
12 1Сергей
 
29.03.13
08:40
(10) как мне вот это тогда сделать?

CAST(RegProperty._Fld4716RRef AS UniqueIdentifier) = CAST('5b488bad-2939-fdff-11e2-7beb1c993d16' AS UniqueIdentifier)
13 1Сергей
 
29.03.13
08:47
в одинесных таблицах все ссылки хранятся в формате binary data
14 Bugmenot
 
29.03.13
08:47
(8) Выбираются вместе с Types, а при связывании с агрегированными данными отфильтровываются.
Иногда это маневр позволяет несколько снизить физические чтения, иногда - даже значительно.
(6) Возьми квери аналайзер или энтерпрайз манагер или что там у тебя. Только реальный план выполнения и статистика с трассировкой - критерии истины. Остальное - домыслы и догадки.
15 Fragster
 
гуру
29.03.13
08:48
вот это мешает:

CAST(RegProperty._Fld4716RRef AS UniqueIdentifier) = CAST('5b488bad-2939-fdff-11e2-7beb1c993d16' AS UniqueIdentifier)
16 Fragster
 
гуру
29.03.13
08:48
ну и это:
          CAST(RegProperty._Fld4716RRef AS UniqueIdentifier) in (
               CAST('5b488bad-2939-fdff-11e2-77332c339912' AS UniqueIdentifier),
               CAST('5b488bad-2939-fdff-11e2-7beb1c993d16' AS UniqueIdentifier))
17 Fragster
 
гуру
29.03.13
08:50
RegProperty._Fld4716RRef = CAST(CAST('5b488bad-2939-fdff-11e2-7beb1c993d16' AS UniqueIdentifier) as varbinary)
18 1Сергей
 
29.03.13
08:50
(17) теже яйца, только в профиль
19 Fragster
 
гуру
29.03.13
08:50
еще надо _Fld4716TRef ограничить, если есть
20 Fragster
 
гуру
29.03.13
08:51
(18) нифига
21 Fragster
 
гуру
29.03.13
08:51
(20)+ ты просто не дочитал до (19)
22 Bugmenot
 
29.03.13
08:51
(13) тогда, наоборот, сделай конвертацию в binary data, а не UniqueIdentifier. Иначе индексы становятся неприменимы.
23 МихаилМ
 
29.03.13
08:52
12

либо

RegProperty._Fld4716RRef  =

CAST(CAST('5b488bad-2939-fdff-11e2-7beb1c993d16' AS UniqueIdentifier) as binary(16))

либо

RegProperty._Fld4716RRef  = Ox0xAD8B485B3929FFFD11E27BEB1C993D16
24 Fragster
 
гуру
29.03.13
08:52
ну и да, Строка(Ссылка.УникальныйИдентификатор()) <> CAST(.rref as UniqueIdentifier)
25 МихаилМ
 
29.03.13
08:53
+(23)

Ox0xAD8B485B3929FFFD11E27BEB1C993D16

читать

0xAD8B485B3929FFFD11E27BEB1C993D16
26 1Сергей
 
29.03.13
08:53
(25) это ты как конвертнул?
27 Fragster
 
гуру
29.03.13
08:54
а что мешает наваять запрос в 1ске, и профайлером его псмотреть? там же реально есть tref ограничение для составного типа. и индекс там по нескольким колонкам для составных типов...
28 Chai Nic
 
29.03.13
08:54
(6) Да там вроде по русски всё написано в студии, меню Запрос. Можно получить оценочный план, можно включить показ действительного плана и выполнить запрос.
29 Fragster
 
гуру
29.03.13
08:55
(28) и так понятно, что индекс не используется
30 МихаилМ
 
29.03.13
08:55
(26)
да когда же мы с Вами на "ты" успели перейти ?
31 1Сергей
 
29.03.13
09:07
(23) CAST(CAST('5b488bad-2939-fdff-11e2-7beb1c993d16' AS UniqueIdentifier) as binary(16))
частично помогло. Теперь запрос за месяц выполняется. За три месяца всё-равно не успевает
32 GANR
 
29.03.13
09:08
План запроса в студию
33 Fragster
 
гуру
29.03.13
09:09
(31)->(19)
34 Fragster
 
гуру
29.03.13
09:09
(32) тэйбл скан на теэбл скане
35 1Сергей
 
29.03.13
09:26
вот так выглядит план запроса в SQL
http://savepic.org/3139741.png
36 Fragster
 
гуру
29.03.13
09:33
(35) к какому тексту запроса-то?
37 1Сергей
 
29.03.13
09:34
Выходит запрос нормальный всё-таки. Раз более 80% уходит на сам документ.

(36) к (0)
38 Fragster
 
гуру
29.03.13
09:34
WHERE
  (M._Marked = 0x00)
  AND (M._Posted = 0x01)
  AND (M._Date_Time >= DATEADD(YEAR, 2000, @DateStart))
  AND (M._Date_Time <= DATEADD(SECOND, 86399, DATEADD(YEAR, 2000, @DateEnd)))  в условие первого иннер джоина (хотя вряд-ли что-то изменить, но поэкспериментировать можно)
39 Fragster
 
гуру
29.03.13
09:34
(37) не на документ, а на таб часть!
40 1Сергей
 
29.03.13
09:39
(39)
на таб.часть - 56%
на шапку - 26 %
41 GANR
 
29.03.13
11:36
(35)(40) А нельзя ли эти scan http://ximage.ru/data/imgs/1364542463.jpg убрать, установив индекс по полям/условиям соединения?
42 GANR
 
29.03.13
11:45
+(41) Индекс по реквизиту табличной части создается при включении объекта конфигурации в критерий отбора через этот реквизит. http://www.1c.ru/news/info.jsp?id=15652, страница 678, том 2.
43 Chai Nic
 
29.03.13
11:50
Я фигею, автор до сих пор план не предоставил
44 1Сергей
 
29.03.13
11:52
(41), (42) там же соединениние по ссылке дока. И индекс есть. Хотя, я не знаю как в запросе явно указать индекс.
(43) см (35)
45 GANR
 
29.03.13
11:53
(43) предоставил (35)(40), хотя _IDRRef - это-же кластерный индекс, т.е. (42) не нужно. Так почему же там scan ?
46 Chai Nic
 
29.03.13
11:54
А это фактический или оценочный?
47 1Сергей
 
29.03.13
11:54
(45) что значит scan? что не используется индекс?
48 GANR
 
29.03.13
11:59
(47) seek - значит что-то ищется по индексу, а clustered index scan, как я понимаю, это просто тупо сканирование всей таблицы. Если это ещё и в цикле - вообще атас.
49 GANR
 
29.03.13
12:09
А dbcc dbreindex даст что-то, интересно?
50 GANR
 
29.03.13
12:10
51 1Сергей
 
29.03.13
12:17
(48), (49), (50) дело в том, что если вручную запустить запрос, то он выполняется меньше секунды, а когда я компилю прогу и из неё этот запрос жутко тупит. Подозрения, что VS как-то не так использует индексы или вообще не использует.
План запроса из (35) я сделал в самом SQL, т.к. в VS не нашел как его сделать
52 Bugmenot
 
29.03.13
13:25
(51) Отличия в параметризации запроса. "В самом SQL" наверняка использовались литералы вместо параметров, тогда как в VS (точнее в сторонней программе) используются биндинги.
53 Speshuric
 
29.03.13
13:29
(0)
1. Нужен запрос и план, причем план в xml. см. SET SHOWPLAN_XML.
2. Про CAST выше сказали
3. Судя по всему LEFT JOIN в этом запросе хорошо заменяется на INNER JOIN. Или там нужны итоги по NULL?
4. План запроса будет зависеть от параметров. Актуальный план легко выковыривается из профайлера.
54 1Сергей
 
29.03.13
14:20
55 1Сергей
 
29.03.13
15:35
Сильно переделал запрос, использую View

SELECT        Types.TypeCode, MAX(Types.TypeName) AS TypeName, Cities.CityCode, MAX(Cities.CityName) AS CityName, SUM([Document].Qty) AS Qty, SUM([Document].Fare)
                        AS Fare, SUM([Document].Tax) AS Tax, SUM([Document].SscCharge) AS SscCharge, SUM([Document].Comm) AS Comm, SUM([Document].Discount) AS Discount,
                        SUM([Document].AgCharge) AS AgCharge, SUM([Document].ToCompany) AS ToCompany, SUM([Document].Polucheno) AS Polucheno
FROM            (SELECT        M._IDRRef AS DocID, M._Fld6833RRef AS CustID, M._Fld6849RRef AS BranchID, SUM(1) AS Qty, SUM(D._Fld6859) AS Fare, SUM(D._Fld6860) AS Tax,
                                                   SUM(D._Fld6861) AS SscCharge, SUM(D._Fld6864) AS Comm, SUM(D._Fld6867) AS Discount, SUM(D._Fld6870 + D._Fld6873) AS AgCharge,
                                                   SUM(D._Fld6859 + D._Fld6860 - D._Fld6864) AS ToCompany, SUM(D._Fld6875) AS Polucheno
                         FROM            _Document6813 AS M WITH (INDEX (PK___Documen__AC8ED0C418F76D81)) INNER JOIN
                                                   _Document6813_VT6850 AS D ON M._IDRRef = D._Document6813_IDRRef
                         WHERE        (M._Marked = 0x00) AND (M._Posted = 0x01) AND (M._Date_Time >= DATEADD(YEAR, 2000, @DateStart)) AND (M._Date_Time <= DATEADD(SECOND,
                                                   86399, DATEADD(YEAR, 2000, @DateEnd)))
                         GROUP BY M._IDRRef, M._Fld6833RRef, M._Fld6849RRef) AS [Document] LEFT OUTER JOIN
                        Reporting.dbo.ViewCustType AS Types ON [Document].CustID = Types.CustID LEFT OUTER JOIN
                        Reporting.dbo.ViewBranchCity AS Cities ON [Document].BranchID = Cities.BranchID
GROUP BY Types.TypeCode, Cities.CityCode
ORDER BY TypeName, CityName


Вот как он выглядит в профайлере:
exec sp_executesql N'SELECT        Types.TypeCode, MAX(Types.TypeName) AS TypeName, Cities.CityCode, MAX(Cities.CityName) AS CityName, SUM([Document].Qty) AS Qty, SUM([Document].Fare)
                        AS Fare, SUM([Document].Tax) AS Tax, SUM([Document].SscCharge) AS SscCharge, SUM([Document].Comm) AS Comm, SUM([Document].Discount) AS Discount,
                        SUM([Document].AgCharge) AS AgCharge, SUM([Document].ToCompany) AS ToCompany, SUM([Document].Polucheno) AS Polucheno
FROM            (SELECT        M._IDRRef AS DocID, M._Fld6833RRef AS CustID, M._Fld6849RRef AS BranchID, SUM(1) AS Qty, SUM(D._Fld6859) AS Fare, SUM(D._Fld6860) AS Tax,
                                                   SUM(D._Fld6861) AS SscCharge, SUM(D._Fld6864) AS Comm, SUM(D._Fld6867) AS Discount, SUM(D._Fld6870 + D._Fld6873) AS AgCharge,
                                                   SUM(D._Fld6859 + D._Fld6860 - D._Fld6864) AS ToCompany, SUM(D._Fld6875) AS Polucheno
                         FROM            _Document6813 AS M WITH (INDEX (PK___Documen__AC8ED0C418F76D81)) INNER JOIN
                                                   _Document6813_VT6850 AS D ON M._IDRRef = D._Document6813_IDRRef
                         WHERE        (M._Marked = 0x00) AND (M._Posted = 0x01) AND (M._Date_Time >= DATEADD(YEAR, 2000, @DateStart)) AND (M._Date_Time <= DATEADD(SECOND,
                                                   86399, DATEADD(YEAR, 2000, @DateEnd)))
                         GROUP BY M._IDRRef, M._Fld6833RRef, M._Fld6849RRef) AS [Document] LEFT OUTER JOIN
                        Reporting.dbo.ViewCustType AS Types ON [Document].CustID = Types.CustID LEFT OUTER JOIN
                        Reporting.dbo.ViewBranchCity AS Cities ON [Document].BranchID = Cities.BranchID
GROUP BY Types.TypeCode, Cities.CityCode
ORDER BY TypeName, CityName',N'@DateStart datetime,@DateEnd datetime',@DateStart='2013-03-01 00:00:00',@DateEnd='2013-03-31 00:00:00'

Как план получить не пойму
56 Speshuric
 
29.03.13
16:11
(55) В профайлере событие "Showplan XML Statistics Profile" отслежиивать
57 Зойч
 
29.03.13
16:13
да сразу понятно, что ты со свойствами неверно работаешь.
Это классическая ошибка. ЕЕ допускают 95% прогов 1с
58 Serginio1
 
29.03.13
16:39
Кстати а откуда ты берешь '5b488bad-2939-fdff-11e2-7beb1c993d16' v8: Как перевести ГУИД в число и обратно?
59 1Сергей
 
01.04.13
06:55
(58) тупо посмотрел в таблице
60 1Сергей
 
01.04.13
11:27
В общем, пока решил проблему так:
Создал таблички CustType и BranchCity. Создал для них индексы. Буду обновлять эти таблички при начале работы системы. Теперь запросы выполняются довольно быстро
61 Зойч
 
01.04.13
11:28
(60) нужно джойн регистра по 2 полям делать,тогда все быстро будет
62 1Сергей
 
01.04.13
11:36
(61) так и делал же, тормоза :(
63 Serginio1
 
01.04.13
18:58
(59 ) Вообще то в табице они имеют
тип binary(16)
0xBB67BF402C745543ABB9DFF6A60BD701
А вот в 1С они как раз '5b488bad-2939-fdff-11e2-7beb1c993d16' при доступе через УникальныйИдентификатор()

Преобразование их через алгоритмы в 58
64 1Сергей
 
02.04.13
07:01
Скажите пожалуйста, при удалении таблицы в SQL с помощью DROP TABLE также удаляются и индексы?
65 1Сергей
 
02.04.13
07:08
(63) так получил: CAST(RegProperty._Fld4716RRef AS UniqueIdentifier)
66 МихаилМ
 
02.04.13
08:43
(64)
да. индексы удаляются вместе с таблицей
67 1Сергей
 
02.04.13
08:46
(66) спс
68 Serginio1
 
02.04.13
14:41
(64) А зачем лишние касты туда обратно?
69 1Сергей
 
03.04.13
10:26
(68) с одной стороны БИНАРИ с другой НВАРЧАР. Напрямую не получится, т.к. на самом деле это ГУИД
70 Serginio1
 
03.04.13
14:46
Да нет бинари(16) отображается как 0xBB67BF402C745543ABB9DFF6A60BD701
Я использую при прямых вызовах.
Например
|Declare @Прайс as binary(16) = "+ПолучитьGUIDПоУникальномуИдентификатору(ТипЦен.УникальныйИдентификатор())+"
|Declare @Импорт as Binary(1) ="+ ?(ТипЦен.Импорт,"0x01","0x00")+";
71 Serginio1
 
03.04.13
14:49
Кстати с Binary(1)  ты напрямую работаешь.
(M._Marked = 0x00)
72 karabas11
 
04.04.13
16:17
ЗначениеВСтрокуВнутр(Справочники.Организации.Пустаяссылка()) -->> {"#",093420e7-8670-489f9b24-7115a5a170de,30:00000000000000000000000000000000}

Где ГУИД = 093420e7-8670-489f9b24-7115a5a170de
ТипОбъекта  = 30
ИДОбъекта = 00000000000000000000000000000000

rref = "0x"+ИДОбъекта;

Select * from [ляля] where  fldRRef = rref
73 1Сергей
 
05.04.13
07:06
(72) Спасибо
74 GANR
 
05.04.13
10:43
75 GANR
 
05.04.13
11:15
Функция UUIDToGUID(UUID) Экспорт
   Возврат Прав(UUID, 8) + "-" + Сред(UUID, 21, 4) + "-" + Сред(UUID, 17, 4) + "-" + Лев(UUID, 4) + "-" + Сред(UUID, 5, 12);
КонецФункции


Функция GUIDToUUID(GUID) Экспорт
   Возврат Сред(GUID, 20, 4) + Прав(GUID, 12) + Сред(GUID, 15, 4) + Сред(GUID, 10, 4) + Лев(GUID, 8);
КонецФункции

GUID_Строкой = Строка(Ссылка.УникальныйИдентификатор());
UUID_Строкой = GUIDToUUID(GUID_Строкой);

// далее - UUID_Строкой - в запрос