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

Откуда:
Сообщений: 1058
Добрый день!

Возникла такая проблема

Есть огромный запрос с параметрами.
я читал. чтобы оптимизировать запрос надо использовать динамический sql
К сожалению запрос не помещается в nvarchar(8000)

Что делать, чтобы обойти проблему?

Спасибо
15 фев 15, 13:45    [17267113]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
step_ks
Member

Откуда:
Сообщений: 936
nvarchar(8000) не бывает.
Какая версия сервера? Если выше 2000, то nvarchar(max).
15 фев 15, 13:59    [17267155]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
aleks2
Guest
Давным-давно... можно было обходить так:
declare  @nvarchar1 nvarchar(4000), @nvarchar2 nvarchar(4000), @nvarchar3 nvfrchar(4000)...

exec (@nvarchar1 + @nvarchar2 + @nvarchar3...)
15 фев 15, 14:49    [17267309]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
кириллk
Member

Откуда:
Сообщений: 1058
Спасибо!
16 фев 15, 10:16    [17269622]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
Glory
Member

Откуда:
Сообщений: 104751
кириллk
я читал. чтобы оптимизировать запрос надо использовать динамический sql

Для того, чтобы оптимизировать, нужно знать конкретную проблему конкретного запроса.
16 фев 15, 10:18    [17269630]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
_nautilus_
Member

Откуда: Отсюда
Сообщений: 1216
Привет.

Подниму тему, т.к. та же самая проблема нарисовалась. В СП формируется динамический запрос, который в зависимости от величины заданного интервала может быть довольно большим. Например, если интервал 30 дней, то в запросе 90 подзапросов, поскольку для каждого отдельного дня нужно 3 подзапроса. Длина текста из 3 подзапросов = 2336 символов.

Такое предложение:

aleks2
Давным-давно... можно было обходить так:
declare  @nvarchar1 nvarchar(4000), @nvarchar2 nvarchar(4000), @nvarchar3 nvfrchar(4000)...

exec (@nvarchar1 + @nvarchar2 + @nvarchar3...)


не помогает, поскольку заранее неизвестно, какой будет интервал и неизвестно, сколько переменных nvarchar(4000) в итоге придется прописать. Нет, можно, конечно, заранее прописать штук 200 переменных и потом развести кучу IF/ELSE, но эта затея как-то пока не трогает.

Пока я не придумал ничего лучше, кроме как текст 3 подзапросов за каждый отдельный день загонять во временную таблицу, а после того, как она будет заполнена собирать итоговый запрос:

exec (@Fields + 'SELECT field FROM #df for xml path(' + '''' + '''' + ')' + @From + @Where + @GroupBy + @Having)

Но что-то оно не очень работает. Я посмотрел, если выполнить просто exec ('SELECT field FROM #df for xml path(' + '''' + '''' + ')'), то в итоге строка, собранная из подзапросов, возвращается в виде XML, результат которого как-то не очень получается соединить с остальными частями запроса.

Собственно вопрос - может я как-то не так использую for xml path('')? Ну или в принципе надо это все как-то иначе делать. Спасибо.
16 июл 15, 00:01    [17897577]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31783
_nautilus_
Нет, можно, конечно, заранее прописать штук 200 переменных и потом развести кучу IF/ELSE
Зачем кучу IF/ELSE? Делаете в переменных пустые строки, и выполняете exec (@nvarchar1 + @nvarchar2 + @nvarchar3...) для всех переменных.

Но вообще задача адская, думаю, как это "для каждого отдельного дня нужно 3 подзапроса"? Неужели нельзя сделать одним запросом???
16 июл 15, 00:27    [17897606]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31783
_nautilus_,

И поячему вообще нужно много переменных? Хоть там 100500 запросов, почему их нельзя положить в одну переменную???
16 июл 15, 00:29    [17897608]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
_nautilus_
Member

Откуда: Отсюда
Сообщений: 1216
alexeyvg
_nautilus_
Нет, можно, конечно, заранее прописать штук 200 переменных и потом развести кучу IF/ELSE
Зачем кучу IF/ELSE? Делаете в переменных пустые строки, и выполняете exec (@nvarchar1 + @nvarchar2 + @nvarchar3...) для всех переменных.


Понимаете, изначально я как-то не рассчитал, что длина текста может быть большой. У меня все подзапросы планировалось генерировать в цикле WHILE и записываться в переменную @ListFields, после чего:

exec (@Fields + @ListFields + @From + @Where + @GroupBy + @Having)

Оно все и генерируется и сама процедура компактная, просто в 8000 откровенно не влазят подзапросы все эти. А иначе без кучи IF/ELSE я не знаю как делать. Вдруг они там вообще захотят за квартал взять данные. Это получается 270 подзапросов. У меня размер этой процедуры будет такой, что весь смысл пропадает.

alexeyvg
Но вообще задача адская, думаю, как это "для каждого отдельного дня нужно 3 подзапроса"? Неужели нельзя сделать одним запросом???


Есть список скажем так сотрудников, которым звонят. Фиксируется ид сотрудника, дата/время начала разговора, и дата/время окончания разговора. Ну и ид клиента. Нужно узнать, какое количество клиентов общались с тем или иным сотрудником, причем разбивка идет в зависимости от длительности разговора. Меньше 10 минут. Больше 10, но меньше 30 минут. Больше 30 минут, но меньше часа. Есть еще больше часа, но там минимальное количество, может и не надо. В итоге идет общая выборка, плюс 1 подзапрос - это одна колонка в которой подсчитывается количество. 1 день + 3 временных интервала = 3 подзапроса. 1 месяц + 3 временных интервала = 90 подзапросов.
16 июл 15, 00:43    [17897634]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
Oleksii Kovalov
Member

Откуда:
Сообщений: 100
_nautilus_
1 день + 3 временных интервала = 3 подзапроса. 1 месяц + 3 временных интервала = 90 подзапросов.

Зачем так? Вы не знаете как объединять таблицы?
16 июл 15, 01:13    [17897653]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
Oleksii Kovalov
Member

Откуда:
Сообщений: 100
что нибудь типа такого - подсчет числа объектов в базе по диапазонам количества колонок

declare @interval table(f int, t int)
insert into @interval values(1,3)
insert into @interval values(4,8)
insert into @interval values(9,9999)

;with CTE
as
(
select object_id,count(*) as cnt
from	sys.columns
group by object_id
)
select i.f,i.t,count(*)
from	cte c inner join @interval i on c.cnt between i.f and i.t
group by i.f,i.t
order by 1



f           t           
----------- ----------- -----------
1 3 66
4 8 518
9 9999 1305
16 июл 15, 01:18    [17897661]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
_nautilus_
Member

Откуда: Отсюда
Сообщений: 1216
Oleksii Kovalov
_nautilus_
1 день + 3 временных интервала = 3 подзапроса. 1 месяц + 3 временных интервала = 90 подзапросов.

Зачем так? Вы не знаете как объединять таблицы?


Может, конечно, что-то и упустил. А как бы Вы решили эту задачу?
16 июл 15, 01:24    [17897668]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
_nautilus_
Member

Откуда: Отсюда
Сообщений: 1216
Oleksii Kovalov
что нибудь типа такого - подсчет числа объектов в базе по диапазонам количества колонок


Я так понимаю в третьей колонке подсчитываются итоговые данные?
16 июл 15, 01:32    [17897681]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
_nautilus_
Member

Откуда: Отсюда
Сообщений: 1216
Oleksii Kovalov
select i.f,i.t,count(*)
from	cte c inner join @interval i on c.cnt between i.f and i.t
group by i.f,i.t
order by 1



Ну, допустим, count(*) подсчитывает. Но это получается 1 колонка для 1 between i.f and i.t. А таких битвинов за сутки нужно 3 посчитать. Плюс каждые сутки это по идее отдельный where с between по 1 дате, а дат может быть > 1.
16 июл 15, 01:42    [17897692]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
Oleksii Kovalov
Member

Откуда:
Сообщений: 100
_nautilus_
Oleksii Kovalov
select i.f,i.t,count(*)
from	cte c inner join @interval i on c.cnt between i.f and i.t
group by i.f,i.t
order by 1



Ну, допустим, count(*) подсчитывает. Но это получается 1 колонка для 1 between i.f and i.t. А таких битвинов за сутки нужно 3 посчитать. Плюс каждые сутки это по идее отдельный where с between по 1 дате, а дат может быть > 1.


тут возможны 2 подхода
первый подход гласит что "транспонирование отчета должен заниматься клиент", т.е. разворачивать такую информацию по горизонтали должны какой нить pivot table в экселе
второй подход гласит что "ну и фиг с ним, для фиксированного к-ва столбцов мы можем сделать pivot и на сиквеле"

Выбирайте тот подход, который вам ближе.

зы добавлять дополнительные (суточные) итоги - учитесь сами, там ничего хитрого нет
16 июл 15, 01:52    [17897701]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31783
_nautilus_
Оно все и генерируется и сама процедура компактная, просто в 8000 откровенно не влазят подзапросы все эти
Так используйте переменную nvarchar(max) - туда влезает 2 млрд. символов.
_nautilus_
1 день + 3 временных интервала = 3 подзапроса. 1 месяц + 3 временных интервала = 90 подзапросов.
Всё равно непонятно, что мешает это сделать одним запросам.
Для разбиения результатов одного запроса по горизонтали, по столбцам, в MSSQL есть конструкция PIVOT.

Или на клиенте - что я предпочитаю, ведь так и отчёт делать проще, не нужно стотыщ колонок в нём рисовать.
Например, в SSRS для этого есть компонент Matrix, в нём можно указать разворачивание по ширине, строки в столбцы.

Ой, вот, собственно, прочитал, что Oleksii Kovalov про такие варианты уже рассказал :-)
16 июл 15, 12:02    [17898799]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
_nautilus_
Member

Откуда: Отсюда
Сообщений: 1216
alexeyvg
Так используйте переменную nvarchar(max) - туда влезает 2 млрд. символов.


Вот, отлично, спасибо, теперь наконец отрабатывает так, как и планировалось

_nautilus_
Всё равно непонятно, что мешает это сделать одним запросам.
Для разбиения результатов одного запроса по горизонтали, по столбцам, в MSSQL есть конструкция PIVOT.


Я этим PIVOT раньше никогда не пользовался. Я так примерно понял, что сначала нужно вроде заполнить таблицу отдельными итогами по каждому, например:

Коля 01.07.2015 5
Коля 02.07.2015 9
Коля 03.07.2015 4
Вася 01.07.2015 8
Вася 02.07.2015 7
Вася 03.07.2015 9
Рома 01.07.2015 9
Рома 02.07.2015 5
Рома 03.07.2015 7

после чего эти данные уже на втором этапе разворачиваются в сгруппированную сводную таблицу типа:


Сотрудник 01.07 02.07 03.07
Коля 5 9 4
Вася 8 7 9
Рома 9 5 7

и т.д. в таком дахе. Надо будет попробовать.

alexeyvg
Например, в SSRS для этого есть компонент Matrix, в нём можно указать разворачивание по ширине, строки в столбцы.


Я пока даже не знаю на чем делать. На прошлых проектах в основном был FastReport лицензионный и на нем была основная обработка. Думал тут попробовать Crystal, он вроде его напоминает, так у него нету Express версии в отличие от среды и СУБД. Соответственно пока все данные готовятся на сервере, а на клиенте просто отображаются с минимальными изменениями. Вот про SSRS первый раз слышу, надо будет почитать, спасибо.
16 июл 15, 15:50    [17900471]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
_nautilus_
Member

Откуда: Отсюда
Сообщений: 1216
Oleksii Kovalov
второй подход гласит что "ну и фиг с ним, для фиксированного к-ва столбцов мы можем сделать pivot и на сиквеле"

Выбирайте тот подход, который вам ближе.

зы добавлять дополнительные (суточные) итоги - учитесь сами, там ничего хитрого нет


Ок, спасибо за совет, буду разбираться.
16 июл 15, 15:51    [17900478]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31783
_nautilus_
Я этим PIVOT раньше никогда не пользовался. Я так примерно понял, что сначала нужно вроде заполнить таблицу отдельными итогами по каждому, например:

Коля 01.07.2015 5
Коля 02.07.2015 9
Коля 03.07.2015 4
Вася 01.07.2015 8
Вася 02.07.2015 7
Вася 03.07.2015 9
Рома 01.07.2015 9
Рома 02.07.2015 5
Рома 03.07.2015 7

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

Нужно сначала написать запрос, который возвращает нужные данные, а потом добавить к нему конструкцию PIVOT, и результаты развернуться в ширину.
_nautilus_
alexeyvg
Например, в SSRS для этого есть компонент Matrix, в нём можно указать разворачивание по ширине, строки в столбцы.

Я пока даже не знаю на чем делать. На прошлых проектах в основном был FastReport лицензионный и на нем была основная обработка. Думал тут попробовать Crystal, он вроде его напоминает, так у него нету Express версии в отличие от среды и СУБД. Соответственно пока все данные готовятся на сервере, а на клиенте просто отображаются с минимальными изменениями. Вот про SSRS первый раз слышу, надо будет почитать, спасибо.
Мне даже как то не верится, что есть такие построители отчётов, которые не умеют разворачивать "строки в столбцы". Это по моему какая то фантастика, не будет востребована такая отчётная система...

Просто почитайте документацию по выбранной системе, поищите в инете по соотв. ключевым словам - наверняка там такое есть. И жизнь сразу упростится.

Да, и про SSRS почитайте, для работы с MSSQL неплохая система.
16 июл 15, 17:30    [17901096]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
bananazury
Member

Откуда:
Сообщений: 76
а подскажите, с какой версии MS SQL динамический скл поддерживает nvarchar(max) ? У меня только 8000 поддерживает.
20 июл 15, 21:24    [17914796]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
o-o
Guest
bananazury
а подскажите, с какой версии MS SQL динамический скл поддерживает nvarchar(max) ? У меня только 8000 поддерживает.

nvarchar(max) >= 2005
+
If you are on SQL 2000 or SQL 7, there is a limitation with sp_executesql when it comes to the length of the SQL string. While the parameter is ntext, you cannot use this data type for local variables. Thus, you will have to stick to nvarchar(4000). In many cases this will do fine, but it is not uncommon to exceed that limit. In this case, you will need to use EXEC()
you can say:
EXEC(@sql1 + @sql2 + @sql3)
here all of @sql1, @sql2 and @sql3 can be 4000 characters long – or even 8000 characters as EXEC() permits you to use varchar.
20 июл 15, 23:35    [17915036]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с динамическим sql  [new]
Oleksii Kovalov
Member

Откуда:
Сообщений: 100
sp_execresultset
21 июл 15, 01:19    [17915156]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить