Здравствуйте!

Нужно сделать некий отчет, который будет содержать информацию по активностям, в том числе вычисляемые поля по количеству активностей, их длительности и тп. На вход отчету нужно задавать как минимум диапазон дат. Стандартными итогами такой отчет получить не могу, т.к. конструктор не позволяет выполнять вычисления. Планирую сделать sql view, но в нее не знаю как передавать параметры, взятые из фильтра. Помогите, пожалуйста, с передачей параметров.

Нравится

6 комментариев

Отчет где будет строиться?

1) Был опыт вывода view в итоги

2) В excel отчеты

3) подключение вычисляемых полей из view к реестру раздела

Добрый день! Вы строите View c набором итоговых столбцов по которым уже и будите фильтровать из интерфейса.
Вот пример моего представления:

CREATE VIEW dbo.[VwDynamicOsago] ([Id],[CreatedAt],[WeekId],[MonthId],[Name], [InsuranceCount], 
[AvgPolicyCost], [PolicyIncome], [Cpa], [ProfitByOnePolicy], [Turnover], [Revenue], [AdsCost], [Profit], 
[MultiplexCoeff], [SingleTax], [BankExpenses], [MotivationCC],[BankRatio]) AS
SELECT 
	NEWID() [Id],	
	CONVERT(date, DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt])) [CreatedAt],
	[I].[WeekId] [WeekId],
	[I].[MonthId] [MonthId],
	'Динамика '+'('+CONVERT(varchar(10), DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt]), 120)+')' [Name],
	COUNT([I].[Id]) [InsuranceCount],
	AVG([I].BrokerDiscountedPayment) [AvgPolicyCost],
	AVG([I].Revenue) [PolicyIncome],
	dbo.fn_AbCalculateCpa([CpcCost].[AdsCost], COUNT([I].[Id])) [Cpa],
	dbo.fn_AbCalculateProfitByOnePolicy(SUM([I].Revenue), 
										dbo.fn_AbCalculateSingleTax(SUM([I].[Commission]), 5), 
										dbo.fn_AbCalculateBankExpenses(AVG([I].Revenue), dbo.fn_AbGetBankRatio(CONVERT(date, DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt])))), 
										dbo.fn_AbCalculateMotivationCC(SUM(CC_MOTIVATION.[Value]), COUNT([I].[Id])), 
										[CpcCost].[AdsCost], 
										COUNT([I].[Id])
										) [ProfitByOnePolicy],	
	SUM([I].BrokerDiscountedPayment) [Turnover],
	SUM([I].Revenue) [Revenue],
	[CpcCost].[AdsCost] [AdsCost],
	dbo.fn_AbCalculateProfit(SUM([I].Revenue), 
							 dbo.fn_AbCalculateSingleTax(SUM([I].[Commission]), 5), 
							 dbo.fn_AbCalculateBankExpenses(AVG([I].BrokerDiscountedPayment), dbo.fn_AbGetBankRatio(CONVERT(date, DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt])))), 
							 dbo.fn_AbCalculateMotivationCC(SUM(CC_MOTIVATION.[Value]),	COUNT([I].[Id])), 
							 [CpcCost].[AdsCost]
							 ) [Profit],
	IIF([CpcCost].[AdsCost] IS NOT NULL AND [CpcCost].[AdsCost] > 0, SUM([I].Revenue)/[CpcCost].[AdsCost],0) [MultiplexCoeff],
 
	dbo.fn_AbCalculateSingleTax(SUM([I].[Commission]), 5) [SingleTax],
	dbo.fn_AbCalculateBankExpenses(AVG([I].BrokerDiscountedPayment), dbo.fn_AbGetBankRatio(CONVERT(date, DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt])))) [BankExpenses],
	dbo.fn_AbCalculateMotivationCC(SUM(CC_MOTIVATION.[Value]), COUNT([I].[Id])) [MotivationCC],
	dbo.fn_AbGetBankRatio(CONVERT(date, DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt]))) [BankRatio]
 
FROM [AbInsurance] [I] WITH(NOLOCK)
LEFT OUTER JOIN [CpcCost] [CpcCost] WITH(NOLOCK) ON [CpcCost].[Date] = CONVERT(date, DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt]))
OUTER APPLY (SELECT SUM(IIF( DATEPART(HOUR, DATEADD(HOUR,dbo.fn_AbGetAddedHoursToSqlQuery(),CreatedAt)) > 18, 50, 40)) [Value]
			 FROM [AbInsurance] [I1] WITH(NOLOCK)
			 WHERE [I1].ProcessingReasonCCId IS NOT NULL AND [I1].Id = [I].Id
			 GROUP BY CONVERT(date,DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I1].[CreatedAt]))
			 ) AS CC_MOTIVATION
WHERE [I].StatusId = '195CD1AD-37B3-4F0A-B4FC-70E2BF0B3F06'
		AND [I].[CreatedAt] IS NOT NULL
GROUP BY CONVERT(date, DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt])), 'Динамика  '+'('+CONVERT(varchar(10), DATEADD(HOUR, dbo.fn_AbGetAddedHoursToSqlQuery(), [I].[CreatedAt]), 120)+')', [CpcCost].[AdsCost], [I].[MonthId], [I].WeekId

 

Может, лучше не создавать динамически view под каждый запуск отчёта, а при его запуске формировать программно в ESQ или Select выборку с нужными условиями и группировками и записывать её  результаты в некоторую (одну и ту же) служебную таблицу, добавив в отдельную колонку Id запуска. А при построении отчёта фильтровать её по этому Id. А когда после построения отчёта эти записи станут ненужными, чистить.

Александр, Ваша идея очень интересна. Спасибо! Объясните, пожалуйста, вкратце как можно на вкладке с итогами добавить контролы дат, для запроса диапазона дат у пользователя и кнопки Сформировать для инициирования формирования ESQ.

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

 

Вам необходимо реализовать страницу настроек отчёта (пример схемы в конфигурации — ContactAnniversariesReportFilter) и зарегистрировать её в БД, в таблице SysModuleAnalyticsReport, в колонке SysOptionsPageSchemaUId записи Вашего отчёта.

 

Для конкретной реализации с вычислениями, видимо, нужно будет сделать логику записи результатов в таблицу, а затем передавать в отчёт один параметр — Id для фильтрации.

Нигрескул Алексей пишет:

Добрый день! Вы строите View c набором итоговых столбцов по которым уже и будите фильтровать из интерфейса.

Алексей, ведь в стандартных итогах нет штатного фильтра. Можно будет только выгрузить всю таблицу в эксель и там уже средствами экселя фильтровать. А это очень неудобно. Или можно как-то добавить стандартный фильтр к произвольной вьюхе?

Показать все комментарии

Добрый день, коллеги.

У нас недавно была внедрена система terrasoft bpmonline которая использует MS SQL. Хотелось бы узнать, есть ли какие-то рекомендованные регламентные процедуры по поддержке этой СУБД. У меня есть относительно большой опыт в поддержке MS SQL для 1С и там к стандартным регламентным процедурам относят перестроение индексов, сбор статистики и очистка процедурного кеша. Эти операции реально помогают производительности 1С. Скажите, имеет ли смысл применять подобные регламентные операции для MS SQL в случае terrasoft bpmonline? Возможно, terrasoft имеет какие-то свои рекомендации (к сожалению, пока не сталкивался с ними) ? Заранее спасибо за ответ.

Нравится

2 комментария

Игорь, в общем случае всё будет очень похоже. Всё те же индексы и прочее.

Из специфического, в bpm'online есть таблицы с информацией, которая копится и не чистится, но особо не нужна по прошедствии времени.

Например, в SysUserSession при каждом входе пользователя или какой-то интеграции пишется новая запись. Если её никогда не чистить, она вырастает до больших размеров и работа с ней замедляется, встречаются deadlock-и. Аналогично, в IntegrationLog пишется информация при работе интеграций, если такие предусмотрены в Вашей системе. Другие подобные таблицы можно увидеть, построив в Management Studio отчёт «Disk Usage by Top Tables».

Плюс в оптимизации стоит учитывать, что в Terrasoft повсеместно используются в качестве первичных ключей GUID-ы.

Иногда объёмы данных столь велики, что для нормального выполнения запросов помогает только переход на SSD, поскольку HDD не справляется. И версию MS SQL лучше выбрать 2016 и новее.

Зверев Александр,

 Большое спасибо за Ваш ответ.

Показать все комментарии
Идея
Одобрена

 

Для поиска запросов, которые выполняются долго,  используем профайлер. В нём невозможно увидеть имя пользователя или машину инициатора запроса, т.к. они одинаковые для всех, и соответственно равны пользователю из строки подключения к БД и машине, на которой выполняется приложение.

Идея заключается в следующем: можно добавлять в запрос Id SysAdminUnit'а (и любую другую полезную информацию) как комментарий.Это упростит поиск некоторых проблем, а реализация этой функции не требует серьезных доработок и никак не повлияет на выполнение самих запросов.

3 комментария

Здравствуйте!

Передали данное пожелание команде разработки для анализа возможности внедрения данного функционала в будущих версиях системы.

Спасибо большое)

Добрый день!

Спасибо, идея принимается. Планируем сделать в ближайших релизах.

Показать все комментарии

Здравствуйте! Я в террасофте совсем новичок, только начинаю изучать. Версия ТС - 3.4.0.38. По форуму искала, вроде подходящего решения не нашла, либо не поняла что это "оно". Подскажите пожалуйста!
Была поставлена задача - по расписанию формировать файл с отчетом для Excel (есть аналог для работы пользователя в FR), выкладывать его в сеть и отправлять группе лиц.
Вроде как все решила через SQL, правда экселевский файл сформировала через spread xls на xml.
Несущественная проблема - при открытии на некоторых компьютерах ругается на несоответствие формата, с этим можно жить, но вот на телефонах этот файл не открывается в приложениях, но очень нужно.
В итоге встал вопрос о другом формате.. Возможно ли как то через sql или сам террасофт выполнить по расписанию формирование файла в формате xlsx или pdf (редактируемый)? Либо конвертацию сделать, может что-то вроде - открыть файл в sql через sp_OACreate 'Excel.Application', и сохранить его в xlsx..

Нравится

4 комментария

С помощью Terrasoft, также можно выполнить такую задачу, для этого необходимо на сервер создать задачу (Task Scheduler), которая будет запускаться по времени и вызывать Terrasoft командой TSClient.exe /wnd=wnd_ExportRep.

Где в окне wnd_ExportRep будет прописана логика экспорта.
Обратите внимание на сервисы scr_MSExcelLibrary, scr_MSExcelLibraryConsts, а также на сервис scr_UserReportCommon, в особенности на функцию GetExcelObject - которая возвращает Com объект на Excel.

Также можно изучить работу сервиса scr_ImportExcelWizardUtils, который автоматически создает отчеты в Excel, на основании его работы можете создать свой экспорт.

Хороший вариант! Спасибо! Правда мне очень много стоит изучить для реализации подобного..
А клиент терры будет открыватся на сервере или только процесс висеть? Если есть возможность подсказать подобные примеры решения буду очень признательна.

Пробовала запускать excel на террасофте через var ExcelApp = new ActiveXObject("Excel.Application");
но система выругалась на меня и клиент вылетел..

Можете взять мой пример запуска коннектора и создание Excel файла.
Алгоритм следующий:
1. Создайте файле с расширением js
2. Пропишите в нем следующий код:

function WSLog(LogText) {
	WScript.stdout.WriteLine(LogText);
}
 
function GetExcelObject() {
	var Excel = new ActiveXObject('Excel.Application');
	var Excel_wb = Excel.Workbooks.Add();
	Excel.Sheets(1).Name = "Данные";
	var SheetsCount = Excel.Sheets.Count;
	for (var i = 2; i <= SheetsCount; i++) {
		Excel.Sheets(2).Delete();
	}
	Excel.Visible = false;
	return Excel;
}
 
function Main() {
	var args = WScript.arguments;
	var ConfigurationName = "TS_3.4.1.113_XRM_SD_SoftKey_ENU_Tereshchuk";
	var AuthenticationMode = 1; //1 - DatabaseServer, 0 - Windows
	var UserName = "Supervisor";
	var UserPassword = "";
	var ExcelFileName = 'C:\\Temp\\TestExcel.xlsx';
	WSLog("Start!");
	var Connector =  new ActiveXObject('TSObjectLibrary.Connector');
	var Config;
	if (AuthenticationMode) {
		Config = Connector.OpenConfigurationByName(ConfigurationName, AuthenticationMode, UserName, UserPassword);
	} else {
		Config = Connector.OpenConfigurationByName(ConfigurationName, AuthenticationMode);
	}
	var Excel = GetExcelObject();
	Excel.Sheets(1).SaveAs(ExcelFileName);
	Excel.Visible = true;
	Connector.Logoff();
	WSLog("Finish!");
}
 
Main();

3. Создайте файл с расширением bat и пропишите в нем код:
%WinDir%/SysWOW64/cscript.exe /d D:\Projects\JS\RunTS.js

4. Запустите bat файл

Исходники: js.zip

Спасибо!
Так как задача была горящей, а 'Excel.Application' наотрез отказывается работать, временно решила через excelcnv.exe. Там правда очень много нюансов, за счет чего код получился совсем извращенным, но главное рабочим.. позже вернусь к приведению в нормальный вид ) попробую все-таки решить через террасофт по #1 предложенному ответу.

Показать все комментарии

Установка цвета для записи раздела происходит в функции grdDataOnGetRowDrawInfo сервиса scr_UsersGridArea.

Пользователь отображается серым цветом, только в том случае, если значение поле [IsInDatabase] источника данных ds_UserScript содержит enmNo.

Установка значения вычисляемого поля [IsInDatabase] происходит в событии OnDatasetCalcFields источника данных ds_UserScript:

     var SQLObjectName = GetDatasetFieldValue(Dataset, 'SQLObjectName');
     if (IsEmptyValue(SQLObjectName)) {
        return;
    }
    var IsInDatabase = Connector.DBEngine.GetIsUserExists(SQLObjectName);
    if (IsInDatabase) {
        Dataset.Values('IsInDatabase') = enmYes;
    } else {
        Dataset.Values('IsInDatabase') = enmNo;
    }

Функция ядра Connector.DBEngine.GetIsUserExists(SQLObjectName) возвращает true, только при условии, что sql запрос вернет значение равное 1:

SQL:

SELECT count(*) FROM [sysusers] WHERE [name] = 'LoginName' AND [issqlrole] = 0

Oracle:

SELECT COUNT(*) AS CNT FROM ALL_USERS  WHERE UPPER(USERNAME) = (SELECT UPPER("tbl_AdminUnit"."SQLObjectName")as NM FROM "tbl_AdminUnit" WHERE "tbl_AdminUnit"."SQLObjectName"  = 'LoginName');

где LoginName - логин пользователя на сервере

Примечание
Если sql запрос в orcale вернул значение больше 1, необходимо проверить параметры сессии (скорей всего не учитывается регистр в условии):
NLS_COMP
NLS_SORT

Регистр не учитывается:
NLS_COMP=ANSI;
NLS_SORT=BINARY_CI;

Регистр учитывается:
NLS_COMP=BINARY;
NLS_SORT=RUSSIAN;

Установить параметры сессии можно таким запросом:

ALTER SESSION SET NLS_COMP=BINARY;
ALTER SESSION SET NLS_SORT=RUSSIAN;

Нравится

Поделиться

0 комментариев
Показать все комментарии

в продолжение темы http://www.community.terrasoft.ru/blogs/10000
версия 2 :smile:

Чтобы в MSSQL из строки получить набор строк (например, для использования в качестве параметра для select ... where ... in (***)), предлагаю следующее:

declare @input_str nvarchar(4000) = '' -- входная строка
declare @IncludedTablesStringTable TABLE (Code uniqueidentifier) -- смени тип
declare @IncludedTablesStringCode nvarchar(4000)
declare @delimeter nvarchar(4000) = ', '
declare @pos int = 0
declare @len int = LEN(@input_str + cast(2 AS varchar)) - 1
declare @lend int = LEN(@delimeter + cast(2 AS varchar)) - 1
declare @oldpos int = 0
declare @substr nvarchar(4000)

while (@pos @len)
begin
        SET @substr = SUBSTRING(@input_str, @pos, @lend)
        IF (@substr = @delimeter OR @pos = @len)
        begin
                SET @substr = SUBSTRING(@input_str, @oldpos,
                        @pos - @oldpos + case when (@pos = @len) then 1 else 0 end
                )
                INSERT INTO @IncludedTablesStringTable (Code) VALUES (@substr)
                SET @pos = @pos + @lend
                SET @oldpos = @pos
                continue
        end
        SET @pos = @pos + 1
end

(SELECT Code FROM @IncludedTablesStringTable)

Комментарии:
- для использования вставляете (select Code from @IncludedTablesStringTable) туда, где нужен набор строк

- почему все строки nvarchar(4000)? Меня учили, что программа должна работать как можно в большей вариантивности входящих значений. А так как ничто не мешает использовать какую-то странную кодировку или какой-то гигантский разделитель, из ограничений на переменные поставил только юникод.

- что это?

declare @len int = LEN(@input_str + cast(2 AS varchar)) - 1
declare @lend int = LEN(@delimeter + cast(2 AS varchar)) - 1
дело в том, что нам нужен размер разделителя и строки, но LEN() обрезает пробелы в конце (из-за этого я потратил на часок больше времени :cry:). Поэтому идем на хитрость: добавляем знак в конце, считаем, отнимаем единицу. Ну а CAST() используется т.к. если вставлять код со строками в exec(), то начинаются сложности всякие, а так - мы обходим эту проблему. Конечно, можно использовать и другие решения для учета конечных пробелов, но все, что придумал, показались не надежными

- скрипт начинает путаться если разделители идут подряд (не разделены смыслом :lol:), т.е. при разделителе ',' строка '12345,6789,321,,,' распарсится в

12345
6789
321

,

- и последнее, в declare @IncludedTablesStringTable не забудьте указать тип данных тот, который вам нужен

Нравится

Поделиться

1 комментарий

вот тут разбираются нормальные способы :lol:
https://sqlperformance.com/2012/07/t-sql-queries/split-strings

Показать все комментарии
Публикация

Откройте MSSQL Management Studio и выполните следующий запрос:

RESTORE HEADERONLY

FROM DISK = N'c:\temp\dbMyDatabase.bak' --подставить путь к бэкапу

WITH NOUNLOAD;

Затем посмотрите содержимое колонки DatabaseVersion. Пример:

databaseversion

Затем сверьтесь с таблицей: http://weblogs.sqlteam.com/dang/archive/2011/07/17/internal-sql-server-d...

В данному случае у нас параметр равен 661, то есть, бэкап сделан на версии SQL Server 2008

Нравится

Поделиться

0 комментариев
Показать все комментарии

Ничто так не "убивает" базу, как "плохое" индексирование (С)

Создать правильные индексы - это только половина дела. Нужно еще и правильно ими управлять.

В процессе работы с базой, особенно, если данные в ней довольно часто модифицируются/добавляются/удаляются, со временем индексы приходят в некоторую "негодность". Увеличивается их "фрагментарность", ухудшается их влияние на скорость исполнения запросов к БД.
Оптимальным считается, когда уровень фрагментации индекса не превышает 10%, но для поддержания такого показателя необходимо проводить периодическую их дефрагментацию и реорганизацию.
(подробнее про реорганизацию и дефрагментацию)

Основным инструментом в процессе управления фрагментацией индексов выступает функция sys.dm_db_index_physical_stats (подробнее)

Для определения списка индексов с уровнем фрагментарности выше оптимальных 10% в своей работе я воспользовалась вот таким запросом:

DECLARE @db_name varchar(50) = N'db_name',
                @table_name varchar(250) = N'db_name.dbo.tbl_name'

SELECT  IndStat.database_id,
                IndStat.object_id,
                QUOTENAME(s.name) + '.' + QUOTENAME(o.name) AS [object_name],
                IndStat.index_id,
                QUOTENAME(i.name) AS index_name,
                IndStat.avg_fragmentation_in_percent,
                IndStat.partition_number,
                (SELECT count (*) FROM sys.partitions p
                        WHERE p.object_id = IndStat.object_id AND p.index_id = IndStat.index_id) AS partition_count
FROM sys.dm_db_index_physical_stats
    (DB_ID(@db_name), OBJECT_ID(@table_name), NULL, NULL , 'LIMITED') AS IndStat
        INNER JOIN sys.objects AS o ON (IndStat.object_id = o.object_id)
        INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
        INNER JOIN sys.indexes i ON (i.object_id = IndStat.object_id AND i.index_id = IndStat.index_id)
WHERE IndStat.avg_fragmentation_in_percent > 10 AND IndStat.index_id > 0

Если указать @table_name = NULL, тогда мы получим данные по всем таблицам указанной базы.
Если указать и @db_name = NULL - получим информацию по всем таблицам всех баз.

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

  • CONTROL на специфический объект БД.
  • VIEW DATABASE STATE для получения информации обо всех объектах определенной БД (@object_id = NULL).
  • VIEW SERVER STATE - для получения информации обо всех базах сервера (@database_id = NULL).

Так же перед использованием желательно обновить статистику БД.

Вышеприведенный запрос дает только справочную информацию. Но на основании функции sys.dm_db_index_physical_stats можно построить скрипт или хранимую процедуру, которая будет не только определять список индексов, нуждающихся в перестройке, но и будет сама проводить эту операцию.
Один из вариантов такого скрипта приведен ниже (проводит реорганизацию или перестройку(оффлайн) индексов таблиц текущей базы в зависимости от степени фрагментации):

USE [DATABASE];
GO

SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @dbid smallint;

-- Выбираем индексы с уровнем фрагментации выше 10%
-- Определяем текущую БД

SET @dbid = DB_ID();
SELECT
    [object_id] AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag, page_count
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0  
AND index_id > 0 -- игнорируем heap
AND page_count > 25; -- игнорируем маленькие таблицы

-- объявляем курсор для списка обрабатываемых partition
DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;

OPEN partitions;

-- цикл по partition
WHILE (1=1)
BEGIN
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

-- 30% считаем пределом для определения типа обновления индекса.
IF @frag 30.0
    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
    SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);
PRINT N'Выполнено: ' + @command;
END;

CLOSE partitions;
DEALLOCATE partitions;

-- удаляем временную таблицу
DROP TABLE #work_to_do;
GO

Еще посмотреть примеры скриптов/хранимок можно тут или вот тут.

Операцию по устранению дефрагментации индексов рекомендуется проводить регулярно (например, раз в неделю/месяц - в зависимости от величины операций модификации над хранимыми данными). Индексы, за состоянием которых не следят, могут очень существенно "просадить" производительность БД при исполнении запросов.

Нравится

Поделиться

0 комментариев
Показать все комментарии

Ничто не оптимизирует скорость работы базы так, как правильно подобранные индексы (С)

Многим, кто сталкивался с промышленной эксплуатацией баз данных определенных (скажем так, очень больших) размеров, приходилось проводить кропотливый анализ запросов на предмет возможной оптимизации скорости их выполнения.

Как оказалось, сама СУБД MSSQL версии 2005 и выше содержит механизм, грамотное использование которого может очень сильно облегчить работу по поиску некоторых узких мест.

Это sys.dm_db_missing_index_group_stats и связанные с ней функции (подробнее >>)
Данные для них формируются на основании статистики запросов к базе данных, и потому являются довольно хорошей информацией, от которой можно оттолкнуться при оптимизации.

Я не буду приводить тут подробный анализ ее использования, а только приведу запрос, который очень помог мне лично:

SELECT
        [Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),
        [TABLE] = [statement],
        [CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX ix_'
                        + sys.objects.name COLLATE DATABASE_DEFAULT
                        + '_'
                        + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,'')+ISNULL(mid.inequality_columns,''), '[', ''), ']',''), ', ','_')
                        + ' ON '
                        + [statement]
                        + ' ( ' + IsNull(mid.equality_columns, '')
                        + CASE
                                WHEN mid.inequality_columns IS NULL THEN ''
                                ELSE
                                        (CASE
                                                WHEN mid.equality_columns IS NULL THEN ''
                                                ELSE ','
                                         END)
                                        + mid.inequality_columns
                                END
                        + ' ) '
                        + CASE
                                WHEN mid.included_columns IS NULL THEN ''
                                        ELSE 'INCLUDE (' + mid.included_columns + ')'
                                END
                        + ';',
        mid.equality_columns,
        mid.inequality_columns,
        mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
        INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
        INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE   (migs.group_handle IN
                (SELECT TOP (500) group_handle
                FROM sys.dm_db_missing_index_group_stats WITH (nolock)
                ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
                AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1
ORDER BY [Impact] DESC , [CreateIndexStatement] DESC

Естественно, для выполнения этого запроса нужно обладать правом VIEW SERVER STATE или любым другим правом, включающим в себя VIEW SERVER STATE.
Так же перед использованием желательно обновить статистику БД.

P.S. Еще можно почитать (и даже посмотреть видео) вот тут.

P.S.2 Определяем, как часто "пользуются" индексами:

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
 I.[NAME] AS [INDEX NAME],
 USER_SEEKS,
 USER_SCANS,
 USER_LOOKUPS,
 USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
 INNER JOIN SYS.INDEXES AS I
 ON I.[OBJECT_ID] = S.[OBJECT_ID]
 AND I.INDEX_ID = S.INDEX_ID

Нравится

Поделиться

0 комментариев
Показать все комментарии

Часто случается, что нам в хранимках БД необходимо найти упоминания о каких-либо константах, которые, например, собираетесь удалить. И будет хорошо, если вы помните, где именно их использовали, а что если нет?

Так вот, далее я приведу пример нескольких запросов поиска по телу хранимок, триггеров и т.д.
Первый запрос осуществляет поиск по телу функций или процедур:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Your text for search%'
--AND ROUTINE_TYPE='PROCEDURE'
--OR ROUTINE_TYPE='FUNCTION'

Следующий запрос делает выборку из всех объектов, существующих в БД:

SELECT [type], [name], OBJECT_DEFINITION([object_id])
FROM sys.objects
WHERE [type] IN ('P','TR','FN') AND OBJECT_DEFINITION([object_id]) LIKE N'%Your text for search%';

Но sys.objects не показывает триггеры DDL, так как они не принадлежат области схемы. Все триггеры (DML и DDL) найдены в таблице sys.triggers, которая поддерживает смешанные правила имен для различных видов триггеров. (Материал с MSDN)

Какие существуют типы объектов:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

И последний способ - это поиск по таблице syscomments, которая содержит записи для всех представлений, правил, значений по умолчанию, триггеров, ограничений CHECK и DEFAULT, а также для всех хранимых процедур в базе данных.

SELECT DISTINCT so.name, sc.TEXT
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%Your text for search%'

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------

По просьбе Артёма написана процедура, возвращающая список объектов (название и тип), содержащих указанный текст.

CREATE PROCEDURE [dbo].[tsp_GetObjectsContainingText]
(
        @Text VARCHAR(Max),
        @Types VARCHAR(250) = 'P,TR,FN,TF'
)
AS
BEGIN
        SET NOCOUNT ON;
                       
        SELECT [name], [type_desc]
        FROM sys.objects
        WHERE EXISTS (
                SELECT d.Part FROM dbo.fn_ParseString(@Types, ',') d
                WHERE d.Part COLLATE  Cyrillic_General_CI_AI = [type] COLLATE  Cyrillic_General_CI_AI
        ) AND OBJECT_DEFINITION([object_id]) LIKE '%' + @Text + '%';
               
END

2-й входящий параметр Types по умолчанию = 'P,TR,FN,TF'.

Также для работы процедуры необходима функция fn_ParseString. Ниже её реализация:

CREATE FUNCTION [dbo].[fn_ParseString] (
        @ParseString nvarchar(4000), @Delimiter nvarchar(100))
returns @Result TABLE (
        [Line] int NOT NULL identity(1, 1),
        [Part] nvarchar(4000)
)
AS
begin
        declare @Part nvarchar(4000)
        declare @INDEX int
        SET @Delimiter = '%' + @Delimiter + '%'
        while (@ParseString > '')
        begin
                SET @INDEX = patindex(@Delimiter, @ParseString)
          IF (@INDEX > 0)
          begin
                SET @Part = substring(@ParseString, 1, @INDEX -1)
                        INSERT INTO @Result SELECT rtrim(ltrim(@Part))
                SET @ParseString = substring(@ParseString, @INDEX +1, len(@ParseString))
          end else
          begin
            SET @Part = substring(@ParseString, 1, len(@ParseString))
                INSERT INTO @Result SELECT rtrim(ltrim(@Part))
            break
          end
        end
  RETURN
end

Нравится

Поделиться

4 комментария

Максим, спасибо за ценную информацию!
Я бы добавил в список поиска также и

"Караша Максим Юрьевич" написал:
TF = SQL table-valued-function

т.к. они нередко применяются при разработке. И оформить это в виде готового решения - хранимой процедуры, которая на вход принимает текст для поиска, а на выходе получаем список объектов.

Да, присоединяюсь к пожеланиям Артёма.
Нужно реализовать это всё в базовом функционале TSAdmin - в окне Greap Search!

Да, чтоб разработчики этим пользовались, лучше разместить в уже известном месте, в GREP-search

Для Оракла не нашёл корректного поиска.
Вот как вариант:

  select DISTINCT user_objects.object_name, user_objects.object_type from user_objects 
  JOIN sys.user_source ON sys.user_source.name = user_objects.object_name
  WHERE user_objects.object_type in ('PROCEDURE','FUNCTION','TRIGGER')
  AND sys.user_source.Text like '%text%'

Но недостатки запроса в том, что в таблице sys.user_source тело хранимок разбито на много записей, содержащихся в нескольких рядках.
Например небольшой цикл может занимать несколько записей:
1. BEGIN
2. LOOP
3. -- do something with the data
4. END LOOP;

И уже поиск по условию

sys.user_source.Text like '%BEGIN%LOOP%'

ничего не вернёт.

Может кто знает более универсальный вариант?

Показать все комментарии