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

Откуда: Левый берег
Сообщений: 415
Здравствуйте !
ms sql server 2008

Есть процедура долго выполняемая под пользователем роли паблик (4 сек), под админом практически мгновенно. Задался вопросом почему .... Эта процедура (№1) содержит в себе выполнение еще процедуры (№2), которая содержит динамический запрос, при пошаговом выполнении тормоз в несколько секунд происходит при выполнение №2 именно в месте выполнения динам. запроса и почему то только под ограниченным пользователем. Отмечу что по тексту процедур алгоритм выполнения для пользов. админ и паблик не отличается. Хотел выложить листинг динам. запроса ... Но выявил еще один факт процедура №2 если ее выполнить самостоятельно под пользов. паблик вообще не тормозит => дело не в кодинге этого динам. запроса.

Вопрос
1) почему разница по времени в выполнении процедуры №1 для пользов. с разными правами ?
2) почему для огранич. пользователя выполнение проц. №2 в обход №1 происходит мгновенно, а через №1 4 сек. ?

Спасибо большое ...
7 июн 16, 15:28    [19267567]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение динамического запроса  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
План выполнения бы показали... под обоими пользователями
7 июн 16, 15:31    [19267587]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение динамического запроса  [new]
Алексаша
Member

Откуда: Левый берег
Сообщений: 415
Планы читать не умею. На скринах они идентичны. Разница только во времени выполнения

К сообщению приложен файл. Размер - 118Kb
7 июн 16, 15:44    [19267659]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение динамического запроса  [new]
Алексаша
Member

Откуда: Левый берег
Сообщений: 415
И вот медленный план

К сообщению приложен файл. Размер - 146Kb
7 июн 16, 15:45    [19267666]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение динамического запроса  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Вернуть identity колонку так быстрее будет:

SELECT @AutoIDField = c.name
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.tbl', 'U')
    AND c.is_identity = 1

Ок... Можно полный запрос показать? Метаданные я люблю :)
7 июн 16, 15:50    [19267705]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение динамического запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9162
Вы дали паблику CONTROL, ALTER или VIEW DEFINITION ? Wow!
7 июн 16, 16:22    [19267858]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение динамического запроса  [new]
Алексаша
Member

Откуда: Левый берег
Сообщений: 415
Владислав Колосов,

VIEW DEFINITION у паблика такого разрешения нет. Есть разрешение на выполнение процедуры №1. Последняя в свою очередь вызывает процедуру №2, для выполнения кот. необх. VIEW DEFINITION поэтому процедура № 2 вызывается в другом контексте (если я правильно использую терминологию).
EXECUTE AS SELF
7 июн 16, 18:09    [19268316]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение динамического запроса  [new]
Алексаша
Member

Откуда: Левый берег
Сообщений: 415
AlanDenton,

Завтра полный запрос. Надеюсь поможете.
7 июн 16, 18:15    [19268338]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение динамического запроса  [new]
Алексаша
Member

Откуда: Левый берег
Сообщений: 415
Продолжаем разговор ...
Процедура №1 проверяет условие возможности редактирования записи, если не прошло 24 часов с момента вставки записи. Для этого процедура №1 обращается в своем теле к процедуре №2 кот. умеет достать лог изменений указанной таблицы. данные об изменении записей таблице хранятся в таблице с полем типа XML

Процедура №1
+
ALTER PROCEDURE [справочник_обновление].[хп_ПроверкаУсловий]
	@TableName				VARCHAR(100)
	,@AutoIDFieldValue		VARCHAR(100)
	,@ОбратнаяСвязь			VARCHAR(1000) OUT
	,@CanEdit				BIT = 1 OUT -- по умаолчанию можно редактировать
	,@EditOfHours			SMALLINT = 24 -- кол. часов на редактирование 	
AS
BEGIN
	SET NOCOUNT ON;

	SET @ОбратнаяСвязь = ''
	
	DECLARE
		@SCHEMA					VARCHAR(100) 
		,@TableNameOnly			VARCHAR(100) 
		,@AutoIDField			VARCHAR(100) 

	-- надо найти автоинкрементное поле
	SET @CanEdit = 1
	
	SELECT @AutoIDField = c.name
	FROM sys.columns c
	WHERE c.object_id = OBJECT_ID (@TableName, 'U')
		AND c.is_identity = 1
	
	-- нахожу дату последнего редактирования записи
	DECLARE
		@TempTable TABLE (ид int, DateTimeStamp DATETIME, HostName VARCHAR(100), UserLogin VARCHAR(100), Op INTEGER)
	DECLARE
		@ДатаТекущая DATETIME = GETDATE()
		,@ДатаСоздания DATETIME = NULL
		,@ПрошлоВремениСмоментаРедактирования INT
	
	INSERT @TempTable
		EXECUTE лог.хп_ЛогИзмененийТаблицы @TableName, '1', @AutoIDField, @AutoIDFieldValue
		
	SELECT TOP 1
		@ДатаСоздания = DateTimeStamp
		FROM @TempTable
		WHERE 
			ид = @AutoIDFieldValue -- навсякий случай поидее из процедуры только с этитим ид
			AND Op = 2 -- вставка
		ORDER BY DateTimeStamp DESC
	
	SET @ПрошлоВремениСмоментаРедактирования  = DATEDIFF(hour, @ДатаСоздания, @ДатаТекущая)
	
	IF (@ПрошлоВремениСмоментаРедактирования > @EditOfHours) OR (@ДатаСоздания IS NULL)
		BEGIN
		--SET @ВсеИД = 0
		SET @ОбратнаяСвязь = 'С момента создания данных прошло более ' + CAST(@EditOfHours AS VARCHAR(3)) + ' часов.'
			+ CHAR(13) + 'Сохранятся только те данные, которые ранее по тем или иным причинам не были введены !!!'
			+ CHAR(13) + 'Удаление ЗАПРЕЩЕНО !!!'
			
		SET @CanEdit = 0
		END
			
END


Процедура №2
+
ALTER PROCEDURE [лог].[хп_ЛогИзмененийТаблицы]
  @ТаблИмя        VARCHAR(100)
  ,@ВозвратитьПоля VARCHAR(250) = NULL --через пробел указать порядковый номер полей кот. необходимо возвратить в логируемой таблице
  ,@ПолеИмя        VARCHAR(100) = NULL -- по которому идет поиск
  ,@ПолеЗначение   VARCHAR(500) = NULL -- искомое значение поля
WITH EXECUTE AS SELF -- здесь используется динамический запрос
-- и чтоб он работал под пользователем делаю выполнение в другом контексте
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @СообщениеОбОшибке VARCHAR(MAX),
          @СтепеньОшибки     TINYINT     = 16,
          @SQL               NVARCHAR(MAX),
          @Схема             VARCHAR(1000),
          @СхемаТабл         VARCHAR(25) = substring(@ТаблИмя, 1, charindex('.', @ТаблИмя) - 1),
          @ТаблИмяБС         VARCHAR(100),
          @ПолеТип           VARCHAR(25)

  SET @ТаблИмяБС = right(@ТаблИмя, len(@ТаблИмя) - len(@СхемаТабл) - 1)

  -- проверит существование таблицы
  IF OBJECT_ID (@ТаблИмя, N'U') IS NULL
  BEGIN
    SET @СообщениеОбОшибке = 'Для выборки лога таблицы указана не существующая таблица = "' + @ТаблИмяБС/*@ТаблИмя*/ + '" !!!'
    RAISERROR (@СообщениеОбОшибке, @СтепеньОшибки, 1)
    RETURN
  END

  -- для распарсинга xml надо знать тип полей и плюс добавляю служебные кто и когда делал изменение	
  SET @Схема = '(' +
  -- усли @ВозвратитьПоля NULL то надо возвратить все поля поэтому и 2 запроса с WHERE IN и без него
  CASE
    WHEN @ВозвратитьПоля IS NULL THEN
      (SELECT c.COLUMN_NAME + ' ' + c.DATA_TYPE + isnull('(' + cast(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')', '') + ', '
       FROM
         INFORMATION_SCHEMA.COLUMNS c
       WHERE
         TABLE_NAME = @ТаблИмяБС
         AND TABLE_SCHEMA = @СхемаТабл
       -- ======================================================================================
       --AND c.ORDINAL_POSITION IN (SELECT NUMBER FROM dbo.iter_intlist_to_table(@ВозвратитьПоля))
       FOR XML
         PATH (''))
    ELSE
      (SELECT c.COLUMN_NAME + ' ' + c.DATA_TYPE + isnull('(' + cast(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')', '') + ', '
       FROM
         INFORMATION_SCHEMA.COLUMNS c
       WHERE
         TABLE_NAME = @ТаблИмяБС
         AND TABLE_SCHEMA = @СхемаТабл
         AND c.ORDINAL_POSITION IN (SELECT NUMBER
                                    FROM
                                      dbo.iter_intlist_to_table(@ВозвратитьПоля))
       FOR XML
         PATH (''))
  END + ' DateTimeStamp DateTime, HostName VARCHAR(100), UserLogin VARCHAR(100), Op TINYINT)'

  -- надо узнать тип поля по которому ведется пойск
  SELECT @ПолеТип = c.DATA_TYPE + isnull('(' + cast(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')', '')
  FROM
    INFORMATION_SCHEMA.COLUMNS c
  WHERE
    TABLE_NAME = @ТаблИмяБС
    AND TABLE_SCHEMA = @СхемаТабл
    AND COLUMN_NAME = @ПолеИмя

  -- проверка правильно ли указано поле если указано
  IF (@ПолеИмя IS NOT NULL) AND (@ПолеТип IS NULL)
  BEGIN
    SET @СообщениеОбОшибке = 'В параметре процедуры указано несуществующее поле - "' + @ПолеИмя + '" таблицы - "' + @ТаблИмя + '"!!!'
    RAISERROR (@СообщениеОбОшибке, @СтепеньОшибки, 1)
    RETURN
  END

  SET @SQL = '
		DECLARE 
			@docHandle int, @xmlDocument NVARCHAR(MAX)
		SET @xmlDocument =
		(SELECT (dt.FieldValues).query(N''/Rec'')
		  FROM [лог].[tb_secDataTrail] dt
		  WHERE 
			dt.TableName = ''' + @ТаблИмя + '''' + isnull('AND dt.FieldValues.value(''(/Rec/@' + @ПолеИмя + ')[1]'',''' + @ПолеТип + ''') = CAST (''' + @ПолеЗначение + ''' AS ' + @ПолеТип + ')', '') + ' for xml path(''''))
	
	SET @xmlDocument = ''<FieldValues>'' + @xmlDocument + ''</FieldValues>''
	
	EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
	
	SELECT * FROM OPENXML(@docHandle, N''/FieldValues/Rec'') WITH ' + @Схема /*@ТаблИмя*/ + '
	
	EXEC sp_xml_removedocument @docHandle'
	
	-- заметил что тип varchar(max) определяется как varchar(-1) перед исполнением этого запроса надо это исправить
	SET @SQL = REPLACE(@SQL, 'varchar(-1)', 'varchar(MAX)')
	
  EXEC (@SQL)
END


Повторюсь ... под админ выполняется мгновенно. Под пабликом процедура №1 3-4 секунды. При пошаговом выполнении некий тормоз приходится в месте EXEC (@SQL) - процедура №2. И в тоже время если выполнить напрямую №2 под пабликом то мгновенно.

Сообщение было отредактировано: 8 июн 16, 10:29
8 июн 16, 09:14    [19269529]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение динамического запроса  [new]
Алексаша
Member

Откуда: Левый берег
Сообщений: 415
Владислав Колосов,

Ваш вопрос должен был меня сразу направить в решение ... но. Проблему решил. Она была логическая чтоли ...
В процедуре №1 есть запрос к метаданным кот. паблику не доступны => процедура №2 вызывалась с аргументом NULL что и приводило к большим затратам ...
8 июн 16, 11:32    [19270305]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение динамического запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9162
Алексаша,

SELF давать рискованно, т.к. принципал-создатель может быть удален, отключен и прочее вместе с именем входа. Лучше создайте пользователя без логина (или с привязкой к сертификату) и ему выдайте требуемые права вместе с явным EXECUTE AS.
8 июн 16, 12:17    [19270617]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение динамического запроса  [new]
Алексаша
Member

Откуда: Левый берег
Сообщений: 415
Владислав Колосов,

спасибо, учту.
8 июн 16, 13:44    [19271185]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение динамического запроса  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Примерно как-то так... Хотя если честно, это упростить бы не мешало.

+
ALTER PROCEDURE [справочник_обновление].[хп_ПроверкаУсловий]
(
      @TableName SYSNAME
    , @AutoIDFieldValue VARCHAR(100)
    , @ОбратнаяСвязь VARCHAR(1000) OUT
    , @CanEdit BIT = 1 OUT
    , @EditOfHours SMALLINT = 24
)
AS BEGIN

    SET NOCOUNT ON;

    SELECT @ОбратнаяСвязь = ''
         , @CanEdit = 1

    DECLARE @AutoIDField SYSNAME = (
            SELECT c.name
            FROM sys.columns c
            WHERE c.[object_id] = OBJECT_ID(@TableName, 'U')
                AND c.is_identity = 1
        )

    DECLARE @TempTable TABLE (
        ид INT,
        DateTimeStamp DATETIME,
        HostName VARCHAR(100),
        UserLogin VARCHAR(100),
        Op INTEGER
    )

    DECLARE @ДатаСоздания DATETIME = NULL

    INSERT @TempTable
    EXEC лог.хп_ЛогИзмененийТаблицы @TableName, '1', @AutoIDField, @AutoIDFieldValue

    SELECT TOP (1) @ДатаСоздания = DateTimeStamp
    FROM @TempTable
    WHERE ид = @AutoIDFieldValue
        AND Op = 2
    ORDER BY DateTimeStamp DESC

    IF DATEDIFF(HOUR, @ДатаСоздания, GETDATE()) > @EditOfHours OR @ДатаСоздания IS NULL
    BEGIN

        SELECT @CanEdit = 0
            ,  @ОбратнаяСвязь = N'С момента создания данных прошло более ' + CAST(@EditOfHours AS VARCHAR(3)) + N' часов.'
                + CHAR(13) + N'Сохранятся только те данные, которые ранее по тем или иным причинам не были введены !!!'
                + CHAR(13) + N'Удаление ЗАПРЕЩЕНО !!!'

    END

END

ALTER PROCEDURE [лог].[хп_ЛогИзмененийТаблицы]
(
      @ТаблИмя SYSNAME
    , @ВозвратитьПоля VARCHAR(250) = NULL
    , @ПолеИмя SYSNAME = NULL
    , @ПолеЗначение VARCHAR(500) = NULL
)
WITH RECOMPILE
AS BEGIN

    SET NOCOUNT ON;

    DECLARE @СообщениеОбОшибке VARCHAR(MAX),
            @SQL NVARCHAR(MAX),
            @Схема VARCHAR(1000),
            @ПолеТип VARCHAR(25),
            @object_id INT = OBJECT_ID(@ТаблИмя, 'U')

    IF @object_id IS NULL
    BEGIN
        SET @СообщениеОбОшибке = 'Для выборки лога таблицы указана не существующая таблица = "' + PARSENAME(@ТаблИмя, 1) + '" !!!'
        RAISERROR (@СообщениеОбОшибке, 16, 1)
        RETURN
    END

    SELECT @ПолеТип = TYPE_NAME(c.system_type_id) + ISNULL('(' + CAST(c.max_length AS VARCHAR(10)) + ')', '')
    FROM sys.columns c
    WHERE c.[object_id] = @object_id
        AND c.name = @ПолеИмя

    IF @ПолеИмя IS NOT NULL AND @ПолеТип IS NULL
    BEGIN
        SET @СообщениеОбОшибке = 'В параметре процедуры указано несуществующее поле - "' + @ПолеИмя + '" таблицы - "' + @ТаблИмя + '"!!!'
        RAISERROR (@СообщениеОбОшибке, 16, 1)
        RETURN
    END

    DECLARE @ids TABLE (id INT PRIMARY KEY)
    IF @ВозвратитьПоля IS NOT NULL
        INSERT INTO @ids
        SELECT number
        FROM dbo.iter_intlist_to_table(@ВозвратитьПоля)

    SELECT @Схема = '(' +
        (SELECT c.name + ' ' + TYPE_NAME(c.system_type_id) + ISNULL('(' + CAST(c.max_length AS VARCHAR(10)) + ')', '') + ', '
        FROM sys.columns c
        WHERE c.[object_id] = @object_id
            AND (
                    @ВозвратитьПоля IS NULL
                OR
                    c.column_id IN (SELECT * FROM @ids)
            )
        FOR XML PATH ('')) + ' DateTimeStamp DateTime, HostName VARCHAR(100), UserLogin VARCHAR(100), Op TINYINT)'

    SET @SQL = '
        DECLARE @docHandle int, @xmlDocument NVARCHAR(MAX)
        SET @xmlDocument =
        (
            SELECT (dt.FieldValues).query(N''/Rec'')
            FROM [лог].[tb_secDataTrail] dt
            WHERE dt.TableName = ''' + @ТаблИмя + '''' + ISNULL('AND dt.FieldValues.value(''(/Rec/@' + @ПолеИмя + ')[1]'',''' + @ПолеТип + ''') = CAST (''' + @ПолеЗначение + ''' AS ' + @ПолеТип + ')', '') + '
            for xml path('''')
        )

    SET @xmlDocument = ''<FieldValues>'' + @xmlDocument + ''</FieldValues>''

    EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
    SELECT *
    FROM OPENXML(@docHandle, N''/FieldValues/Rec'') WITH ' + @Схема /*@ТаблИмя*/ + '
    EXEC sp_xml_removedocument @docHandle'

    SET @SQL = REPLACE(@SQL, 'varchar(-1)', 'varchar(MAX)')
    EXEC (@SQL)

END
8 июн 16, 14:01    [19271291]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение динамического запроса  [new]
Алексаша
Member

Откуда: Левый берег
Сообщений: 415
Владислав Колосов,

Спасибо за советы Ваш вариант лаконичнее чтоли, более удобочитаем. Но не получается рабочим в этом месте.
Ниже приведеный часть кода дает результат длины и для целочисленных типов.
     SELECT @Схема = '(' +
        (SELECT c.name + ' ' + TYPE_NAME(c.system_type_id) + ISNULL('(' + CAST(c.max_length AS VARCHAR(10)) + ')', '') + ', '
        FROM sys.columns c
        WHERE c.[object_id] = @object_id
            AND (
                    @ВозвратитьПоля IS NULL
                OR
                    c.column_id IN (SELECT * FROM @ids)
            )
        FOR XML PATH ('')) + ' DateTimeStamp DateTime, HostName VARCHAR(100), UserLogin VARCHAR(100), Op TINYINT)'

Результат (id int(4), name varchar(300) .....)
Нужно (id int, name varchar(300) .....)
Т.е. длину указывать только для символьных типов
Подскажите. Спасибо.
9 июн 16, 09:18    [19274358]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить