|
Помогите оптимизировать 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_Строкой - в запрос |
Форум | Правила | Описание | Объявления | Секции | Поиск | Книга знаний | Вики-миста |