Запррсы с PIVOT

@golandy · Регистрация: 11.01.2014

Author24 — интернет-сервис помощи студентам

Привет всем. Может кто нибудь знает есть ли у PIVOT оптимизация с точки зрения скорости?

например запрос тормозит сильно:

SQL

SET @execText = N'SET @xmlOut =
        (SELECT dbo.fGetDIDName(mpc.DID)as  ''@DIDName'',
            mpc.DID                     as  ''@DID'',
            mpc.StateNewDrugs           as  ''@stateNewDrugs'', 
            mpc.StateDepartament        as  ''@stateDepartament'',
            mpc.StateHeaderOfPlan       as  ''@stateHeaderOfPlan'',
            (SELECT * 
             FROM 
                (SELECT pr1.productID   prodID, 
                    pr1.grCode          grC,
                    pr1.grCode1         grC1,
                    pr1.grName          grN,
                    pr1.grName1         grN1,
                    pr1.isNewInnov      isNewInnov,
                    pr1.regCountres     regC,
                    pr1.productCode     prodC,
                    pr1.commercialName  commN,
                    pr1.measure         meas,
                    ' + @countryNames + '
                 FROM #prices pr1 LEFT JOIN
                    (SELECT pn.countryN, pn.ProductID, pn.VolumeMonth
                     FROM #tmp as pn
                    ) as p PIVOT (SUM(VolumeMonth) FOR countryN IN(' + @countryNames + ')) as res
                    ON res.ProductID = pr1.ProductID
                 WHERE  pr1.DID = p1.DID
                ) as [plan]
             FOR XML AUTO, TYPE
            )
        FROM tbPEMonthPlanChecks mpc LEFT JOIN #prices p1 
            ON p1.DID = mpc.DID
        WHERE mpc.PlanMonthID = @planMonthID AND mpc.PlanMonthVersion = @planMonthVersion
            AND (@roleSONP = 1 OR p1.DID IS NOT NULL)
        GROUP BY mpc.DID, p1.DID, mpc.StateNewDrugs, mpc.StateDepartament,mpc.StateHeaderOfPlan
        ORDER BY mpc.DID
        FOR XML PATH(''DIDsPlan''))
        '

@nixon93nixon · 27.03.2014, 15:30

всегда сторонюсь пивота, но когда без него не обойтись, то мастерю без оптимизации, сложен он для моего понимая
разве что с непосредственно с запросами можно поиграться, посмотри план выполнения, может он не по индексированным полям идет и из-за этого долго

@invm · 27.03.2014, 17:01

У вас запрос с pivot - инвариант. Можно выполнить один раз и сохранить результат во временной таблице с PK. Заодно от DSQL в основном запросе избавитесь.

Добавлено через 6 минут
Насчет отказа от DSQL в основном запросе я погорячился.

@golandy · 27.03.2014, 17:35 **[ТС]**

Сообщение от invm

У вас запрос с pivot - инвариант. Можно выполнить один раз и сохранить результат во временной таблице с PK.

А что это значит? У меня в таблицу не получится т.к. countryNames у меня переменная типа string.

@invm · 27.03.2014, 17:48

Сообщение от golandy

У меня в таблицу не получится т.к. countryNames у меня переменная типа string.

Это не есть препятствие:

T-SQL

declare @s varchar(max), @CountryNames varchar(max) = 'Russian Federation, Poland, United Kingdom, Spain';
 
begin tran;
 
create table #t (id int primary key);
 
select
 @s = 'alter table #t add [' + replace(@CountryNames, ', ', '] int null; alter table #t add [') + '] int null;'
 
exec(@s);
 
select * from #t;
 
rollback;

@golandy · 27.03.2014, 17:58 **[ТС]**

А если пишет

The identifier that starts with '[c_1] varchar(20) null; alter table #t add [[c_1001] varchar(20) null; alter table #t add [[c_1004] varchar(20) null; alter tabl' is too long. Maximum length is 128.

просто у меня @CountryNames имеет вид [c_4801], [c_4701] и т.д....

@invm · 27.03.2014, 18:08

Сообщение от golandy

просто у меня @CountryNames имеет вид [c_4801], [c_4701] и т.д....

Ну тогда не надо квотить наименования столбцов при формировании строки с alter'ами.

@golandy · 27.03.2014, 18:18 **[ТС]**

имеете виду так?

SQL

1	SELECT @s = 'alter table #t add ' + REPLACE(@CountryNames, ', ', ' int null; alter table #t add [') + '] int null;'

Я единственное что не могу доехать как мне потом вынести часть запроса в отдельную таблицу

Можно выполнить один раз и сохранить результат во временной таблице с PK

?

Добавлено через 3 минуты
если я правильно понял то мне можно вынести

SQL

(SELECT pr1.productID   prodID, 
                    pr1.grCode          grC,
                    pr1.grCode1         grC1,
                    pr1.grName          grN,
                    pr1.grName1         grN1,
                    pr1.isNewInnov      isNewInnov,
                    pr1.regCountres     regC,
                    pr1.productCode     prodC,
                    pr1.commercialName  commN,
                    pr1.measure         meas,
                    ' + @countryNames + '
                 FROM #prices pr1 LEFT JOIN
                    (SELECT pn.countryN, pn.ProductID, pn.VolumeMonth
                     FROM #tmp AS pn
                    ) AS p PIVOT (SUM(VolumeMonth) FOR countryN IN(' + @countryNames + ')) AS res
                    ON res.ProductID = pr1.ProductID
                 WHERE  pr1.DID = p1.DID
                ) AS [plan]

в отдельную таблицу запросом?

@pincet · 27.03.2014, 18:30

INTO спасет отца русской демократии

@golandy · 27.03.2014, 18:31 **[ТС]**

Сообщение от pincet

INTO спасет отца русской демократии

если спасет есть пример?
как вставить во временную таблицу я вкурсе. Вопрос как вставить тот кусок кода в запросе есть перемнные величины

@invm · 27.03.2014, 18:47

Строим временную таблицу, показанным выше способом. Затем

T-SQL

insert into <Временная таблица>
select
 res.*
from
(SELECT pn.countryN, pn.ProductID, pn.VolumeMonth
                     FROM #tmp AS pn
                    ) AS p PIVOT (SUM(VolumeMonth) FOR countryN IN(' + @countryNames + ')) AS res

Далее, думаю, понятно что делать.

@golandy · 27.03.2014, 19:19 **[ТС]**

а скажите это же в sp_executesql надо выполнять?

@invm · 27.03.2014, 19:33

Сообщение от golandy

а скажите это же в sp_executesql надо выполнять?

Нет. Я просто поторопился, надо так:

T-SQL

insert into <Временная таблица>
exec('
select
 res.*
from
(SELECT pn.countryN, pn.ProductID, pn.VolumeMonth
                     FROM #tmp AS pn
                    ) AS p PIVOT (SUM(VolumeMonth) FOR countryN IN(' + @countryNames + ')) AS res');

@golandy · 28.03.2014, 23:43 **[ТС]**

план запроса вот

@invm · 28.03.2014, 23:53

Пока не увидел ничего криминального. Теперь тоже самое вместе с insert во времянку, плюс добавить статистику по time и io.

@golandy · 31.03.2014, 15:44 **[ТС]**

планы запроса и ddl таблиц

@nixon93nixon 63 / 63 / 21 Регистрация: 08.02.2013 Сообщений: 262
	27.03.2014, 15:30	2
	всегда сторонюсь пивота, но когда без него не обойтись, то мастерю без оптимизации, сложен он для моего понимая разве что с непосредственно с запросами можно поиграться, посмотри план выполнения, может он не по индексированным полям идет и из-за этого долго 0

@invm 3499 / 2083 / 742 Регистрация: 02.06.2013 Сообщений: 5,078
	27.03.2014, 17:01	3
	У вас запрос с pivot - инвариант. Можно выполнить один раз и сохранить результат во временной таблице с PK. Заодно от DSQL в основном запросе избавитесь. Добавлено через 6 минут Насчет отказа от DSQL в основном запросе я погорячился. 0

@golandy 73 / 73 / 20 Регистрация: 11.01.2014 Сообщений: 252 Записей в блоге: 2
	27.03.2014, 17:35 [ТС]	4
	Сообщение от invm У вас запрос с pivot - инвариант. Можно выполнить один раз и сохранить результат во временной таблице с PK. А что это значит? У меня в таблицу не получится т.к. countryNames у меня переменная типа string. 0

@golandy 73 / 73 / 20 Регистрация: 11.01.2014 Сообщений: 252 Записей в блоге: 2
	27.03.2014, 17:58 [ТС]	6
	А если пишет The identifier that starts with '[c_1] varchar(20) null; alter table #t add [[c_1001] varchar(20) null; alter table #t add [[c_1004] varchar(20) null; alter tabl' is too long. Maximum length is 128. просто у меня @CountryNames имеет вид [c_4801], [c_4701] и т.д.... 0

@invm 3499 / 2083 / 742 Регистрация: 02.06.2013 Сообщений: 5,078
	27.03.2014, 18:08	7
	Сообщение от golandy просто у меня @CountryNames имеет вид [c_4801], [c_4701] и т.д.... Ну тогда не надо квотить наименования столбцов при формировании строки с alter'ами. 0

@pincet 1643 / 1144 / 171 Регистрация: 23.07.2010 Сообщений: 6,781
	27.03.2014, 18:30	9
	INTO спасет отца русской демократии 0

@golandy 73 / 73 / 20 Регистрация: 11.01.2014 Сообщений: 252 Записей в блоге: 2
	27.03.2014, 18:31 [ТС]	10
	Сообщение от pincet INTO спасет отца русской демократии если спасет есть пример? как вставить во временную таблицу я вкурсе. Вопрос как вставить тот кусок кода в запросе есть перемнные величины 0

@golandy 73 / 73 / 20 Регистрация: 11.01.2014 Сообщений: 252 Записей в блоге: 2
	27.03.2014, 19:19 [ТС]	12
	а скажите это же в sp_executesql надо выполнять? 0

@invm 3499 / 2083 / 742 Регистрация: 02.06.2013 Сообщений: 5,078
	28.03.2014, 23:53	15
	Пока не увидел ничего криминального. Теперь тоже самое вместе с insert во времянку, плюс добавить статистику по time и io. 0

	31.03.2014, 15:44