Формирование отчёта на основе таблицы с БОЛЬШИМ объёмом данных

[Marisha]

Добрый день.

появилась следующая проблема:

есть отчёт, формирование его достаточно простое, но формируется на основе данных из Value Entry, а данных там на данный момент достаточно много (около 17 Гигов) и соответственно сбор данных происходит ОЧЕНЬ долго.
Проблема в том, что не могу использовать FlowFields, вынуждена передвигаться по записям Value Entry.
Аналогичный отчёт с использованием FlowFields работает достаточно быстро. Запрос написанный непосредственно на SQL (с group by) выдаёт данные уже через 10 мин.

Что можно предпринять в данном случае? Может использовать процедуру SQL? но как передать туда параметры (фильтр) из Навижн?
Может на SQL сервере что-либо нужно выполнить? (такое ощущение, что второй раз запуская тотже рапорт, результат выдаётся горазо быстрее).

Навижн 3.70. База на SQL больше 100 Гигов.

Спасибо за помощь.

[anatol33]

Хождение по записям в navision это процесс длительный и не блпгодарный, при большом наборе данных. Средствами C/AL скорости не добьетесь.

Постарайтесь переделать логику отчета под FlowFields. Это можно сделать с помощью дополнительной таблицы специально для одного отчета.

Еще как вариант использовать T-SQL. Параметр зачем передавать? Сделать свою форму запроса параметров отчета.

P.S.
На мой взгляд оптимизация таких отчетов нужна только в случае использования отчета хотябы раз в неделю.
У меня бывали случаи, что просили оптимизировать отчет, который запускался раз в месяц и работал сутки.
На такие пожелания надо вежливо разьяснять, что один день не так уж и много

[.Quattro.]

Можно сделать View на SQL-сервере, которая будет возвращать сгруппированные записи.
Подцепить ее в Navision (Через Linked Object).
Тогда фильтры можно использовать.

[rmv]

Если позволяет ключ можете использовать конструкцию вида
ValueEntry.find('-');
with ValueEntry do repeat
setrange("Field 1", Field 1");
setrange("Field 2", Field 2");
calcums(SomeField);
.......
setrange("Field 1");
setrange("Field 2");
find('+');
until ValueEntry next=0;
Если результирующий набор содержит немного записей, не исключаю что отчет будет работать быстрее чем с group by.

[Fordewind]

100 ГИГОВ? это сколько в годах? Компрессию не пробовали делать?

[Marisha]

.Quattro.: попробую сделать view. А через Linked Object не будет медленно.

rmv: частично я так и делаю. Медленно. С FlowField гораздо быстрее, но не все подходящие FlowField есть, а создавать дополнительные при данном объёме базы как-то не хочется. Почему частично. Потому, что есть данные, сбор, которых зависит от информации, которая находится в другой таблице. Т. е. фольтровать не могу.

Fordewind: проект получили в наследство с "кучей" дороботок, так что не всё пока о нём знаю. Судя по данным - это около 8 лет. Концелярские товары, так что думаю ничего удивительного. Компрессию похоже делали. Ещё раз повторить этот процесс пока опасаюсь из-за дороботок.

[rmv]

Цитата:

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

rmv: частично я так и делаю. Медленно. С FlowField гораздо быстрее, но не все подходящие FlowField есть, а создавать дополнительные при данном объёме базы как-то не хочется. Почему частично. Потому, что есть данные, сбор, которых зависит от информации, которая находится в другой таблице. Т. е. фольтровать не могу.

С таким объемом неудивительно. Если расчет по опорной таблице с calcfields происходит гораздо быстрее нежели find('+') и calcums, возможно проблема в низкой селективности используемого ключа.
Иначе говоря основной время ухолит на find('+').
С linked object медленно не будет, но простая группировка думаю Вас не спасет. Как вариант предлагаю построить расчетные поля в linked object на тех же flow фильтрах.
К примеру - требуется получить себестоимость в разрезе товар, склад, первое измерение:
1. Строите запрос с group by по требуемым полям и цепляете к Наву.
2. Дополнительно определяете flow фильтры и flow поля на книгу операций.
Результат - имеем новую опорную таблицу с необходимым срезом данных, в которой будем считать "быстрыми" calcfields.
Третий вариант (пожалуй самый быстрый) - динамически формировать sql запрос и выводит данные из него

[artkashin]

На таких объемах данных просто вам пора отказываться от формирования отчетов из Nav. Пора переходить к OLAP-системам, используйте Analysis Services, экспорт данных в Excel. И отказывайтесь от технологии SIFT вообще, где это возможно.

[Marisha]

OLAP - это хорошо, но клиент пока сопротивляется.

На данный момент получилось создать процедуру на SQL, которая собирает данные за определёммый период времени уже в необходимом мне виде. В Навижн беру эти данные и просто отображаю.
Процесс занял 10 мин. У клиента подобная информация собирается в течение ночи (с использованием станданых средств Навижн, SIFT).
Так что, сижу и радуюсь :-)

Остался открытым вопрос передачи в процедуру определённого фильтра. Напр-р клиент желает фильтровать Value Entry по Location Code, t.e. Loc1|Loc2..Loc10. Как это реализовать в SQL?

Как понимаю, у меня просто пока не достаточные знания SQL для этого.

[RedFox]

Цитата:

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

Остался открытым вопрос передачи в процедуру определённого фильтра. Напр-р клиент желает фильтровать Value Entry по Location Code, t.e. Loc1|Loc2..Loc10. Как это реализовать в SQL?

Попробуйте передать строкой большой длины (Text 256)

[.Quattro.]

http://naviart.ru/nav-sql-filters
Статейка Вам поможет

[Marisha]

Функция в статье неплохая. Довольно корректно конвертирует фильтр, заданный в Навижн на приемлемый в SQL.
Только мне пока не удалось использовать данную конвертированную строку в проседуре SQL в условии WHERE.
Т. е. напр-р:

SELECT dbo.Fn_Filter_Convert('No_','*4*|000006..0101-004')
возвращает:
[No_] LIKE '%4%' OR [No_] BETWEEN '000006' AND '0101-004'

Казалось бы всё хорошо, но когда подставляю данную функцию уже в select

select *
from [CompanyName$Item]
where dbo.Fn_Filter_Convert('No_','*4*|000006..0101-004')

получаю ошибку

Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

Может кто использует данную функцию. Поделитесь опытом.

Спасибо за помощь.

[artkashin]

Цитата:

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

Казалось бы всё хорошо, но когда подставляю данную функцию уже в select

select *
from [CompanyName$Item]
where dbo.Fn_Filter_Convert('No_','*4*|000006..0101-004')


получаю ошибку

Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

Так нельзя. Вы пытаетесь в статическом SQL запросе выполнить динамический.
Тут возможны два варианта.
Первый - вы возвращаете в Navision результат вопроса
SELECT dbo.Fn_Filter_Convert('No_','*4*|000006..0101-004'), скажем в переменную x
а потом, формируя уже запрос к серверу из C/AL
SQLStr := "select * from [CompanyName$Item] WHERE "+ x

[Marisha]

К сожалению этот вариант не подходит. Из Навижн я запускаю SQL процедуру, довольно громоздкую (из-за чего собственно весь "сыр-бор"), но работающую довольно быстро, в которой и есть SELECT, использующий данный фильтр из Навижн.

А какой 2-ой вариант?

[.Quattro.]

Выполните запрос Exec'ом:
Exec('select * from [CompanyName$Item]where ' + dbo.Fn_Filter_Convert('No_','*4*|000006..0101-004'))

[andtorino]

Если еще актуально, то у меня есть ряд громоздких выборок, формирующихся на SQL.
Я делаю так, на SQL-сервер положил хранимую процедуру, которая объявлена как:

--Start
CREATE procedure dbo.[report_location] (@filterLocation NVARCHAR(30)) AS
DECLARE @Str1 NVARCHAR(4000)
DECLARE @Str2 NVARCHAR(4000)
DECLARE @strfilterLocation NVARCHAR(250)
SELECT @strfilterLocation = dbo.Fn_Filter_Convert('[Location Code]',@filterLocation)
--далее идет сама выборка
SET @Str1 =
N' Select ....много кода....'
SET @Str2 =
N' left join ....много кода....WHERE '+@strfilterLocation

exec (@Str1+@Str2)
--End

Пример вызова процедуры: exec dbo.[report_location] 'AAA|BBB'

[Marisha]

Огромное спасибо andtorino!
Вы мне на самом деле очень помогли.
Как раз этого мне в данном случае и не хватало.

Ещё раз Всем спасибо! Средствами SQL и при помощи выложенной функции удалось решить проблему.