Новый скрипт по реиндексации

Обновленный скрипт по реиндекации

use GlobalBase;
DECLARE @command nvarchar(4000); 

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
IF OBJECT_ID('#work_to_do') IS NOT NULL
    DROP TABLE #work_to_do;
 
 SELECT      'BEGIN TRY ALTER INDEX ALL ON [' + OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE = ON) END TRY BEGIN CATCH ALTER INDEX ALL ON [' + OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE=OFF) END CATCH;' AS [Инструкция T-SQL]
INTO #work_to_do
FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS afp
WHERE afp.database_id = DB_ID()
AND   afp.index_type_desc IN ('CLUSTERED INDEX')
AND   (afp.avg_fragmentation_in_percent >= 15 OR afp.avg_page_space_used_in_percent <= 60)
AND   afp.page_count > 12
UNION ALL
SELECT      [Инструкция T-SQL] =
      CASE
            WHEN afp.avg_fragmentation_in_percent >= 15
            OR afp.avg_page_space_used_in_percent <= 60
            THEN 'BEGIN TRY ALTER INDEX [' + i.name + '] ON [' +OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+ OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE = ON) END TRY BEGIN CATCH ALTER INDEX ['+ i.name + '] ON [' +OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+ OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE=OFF) END CATCH;'
            WHEN (afp.avg_fragmentation_in_percent < 15 AND afp.avg_fragmentation_in_percent >= 10)
            OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
            THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+OBJECT_NAME(afp.OBJECT_ID) + '] REORGANIZE;'
      END
FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS afp
JOIN  sys.indexes AS i
ON    (afp.OBJECT_ID = i.OBJECT_ID AND afp.index_id = i.index_id)
AND   afp.database_id = DB_ID()
AND   afp.index_type_desc IN ('NONCLUSTERED INDEX')
AND   (
            (afp.avg_fragmentation_in_percent >= 10 AND    afp.avg_fragmentation_in_percent < 15)
      OR    (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
      )
AND   afp.page_count > 12
AND   afp.OBJECT_ID NOT IN    (    
                             SELECT      OBJECT_ID
                             FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED')
                             WHERE database_id = DB_ID()
                             AND   index_type_desc IN ('CLUSTERED INDEX')
                             AND   (avg_fragmentation_in_percent >= 15 OR avg_page_space_used_in_percent < 60)
                             AND   page_count > 1
                        )
--ORDER BY [Инструкция T-SQL]

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;
FETCH NEXT
           FROM partitions
           INTO @command;
-- Loop through the partitions.
WHILE (@@FETCH_STATUS = 0)
    BEGIN;
        print ''''+(@command)+'''';
        exec(@command)
        

        FETCH NEXT
           FROM partitions
           INTO @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;

Удаление устаревших данных с 1С за определенный период

Иногда просят почистить базу 1С за определенный период, стандартными методами это долгий и хлопотный процесс, используя же mssql получаем быстро и комфортно.

use work_TC_Digma;
Declare @Instruction as varchar(MAX) 
declare @DateB as varchar(MAX);
set @DateB='4012-01-01 00:00:00.000'
Declare TablesAndFields cursor for 
SELECT objects.name as Tablename, isnull(objects1.name,'') as Tablename1, columns.name as columnname 
FROM 
   dbo.sysobjects as objects 
left join dbo.syscolumns as columns 
    on objects.id = columns.id
Left join dbo.sysobjects as objects1
    on objects1.name like objects.name+'%' 
where objects.xtype = 'U'
and columns.xtype = 61 and (columns.name='_Period' or columns.name='_Date_Time')
order by objects.name, objects1.name desc
 
open TablesAndFields
 
Declare @TableName as varchar(100) 
Declare @TableName1 as varchar(100) 
Declare @ColumnName as varchar(100) 
 
FETCH NEXT FROM TablesAndFields 
    into @TableName, @TableName1, @ColumnName
 
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @TableName1 =@TableName
        set @Instruction = 'delete '+@TableName+' where '+@ColumnName+'<'''+@DateB+''''
    ELSE
        set @Instruction = 'delete '+@TableName1 +' where ' + @TableName+'_IDRRef in (select _IDRRef from '+@TableName+' where '+@ColumnName+'<'''+@DateB+''')'
    print @Instruction
    EXEC(@Instruction)
    

   FETCH NEXT FROM TablesAndFields
    into @TableName,@TableName1, @ColumnName
END
 
close TablesAndFields
deallocate TablesAndFields

Статья по анализу работы MSSQL

Очень правильная статья по анализу работы скула http://msdn.microsoft.com/ru-ru/magazine/cc135978.aspx

Mssql Regex

столкнулся с проблемой когда в скуле необходимо воспользоваться регулярными выражениями, после не долгого шуршания по гуглу нашел ресурс 1. разрешаем скулу пользоваться Ole Automation Procedures 2. Создаем в скуле необходимые процедуры

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
/*
The OLE Regex Match function
-----------------------------

Let's start off with something simple, a function for testing a string against a regular expression */

IF OBJECT_ID (N'dbo.RegexMatch') IS NOT NULL
   DROP FUNCTION dbo.RegexMatch
GO

CREATE FUNCTION dbo.RegexMatch
    (
      @pattern VARCHAR(2000),
      @matchstring VARCHAR(MAX)--Varchar(8000) got SQL Server 2000
    )
RETURNS INT
/* The RegexMatch returns True or False, indicating if the regular expression matches (part of) the string. (It returns null if there is an error).
When using this for validating user input, you'll normally want to check if the entire string matches the regular expression. To do so, put a caret at the start of the regex, and a dollar at the end, to anchor the regex at the start and end of the subject string.
*/ 
AS BEGIN
    DECLARE @objRegexExp INT,
        @objErrorObject INT,
        @strErrorMessage VARCHAR(255),
        @hr INT,
        @match BIT

    SELECT  @strErrorMessage = 'creating a regex object'
    EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
        --Specifying a case-insensitive match 
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
        --Doing a Test' 
    IF @hr = 0 
        EXEC @hr= sp_OAMethod @objRegexExp, 'Test', @match OUT, @matchstring
    IF @hr <> 0 
        BEGIN
            RETURN NULL
        END
    EXEC sp_OADestroy @objRegexExp
    RETURN @match
   END
GO

/*

With this function, the passing back of errors is rudimentary. If an OLE error occurs, then a null is passed back.

There are two other basic Regex functions available. With them, you can use regular expressions in all sorts of places in TSQL without having to get to direct grips with the rather awkward OLE interface.

The OLE Regex Replace function
-----------------------------

*/


IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL 
    DROP FUNCTION dbo.RegexReplace
GO
CREATE FUNCTION dbo.RegexReplace
    (
      @pattern VARCHAR(255),
      @replacement VARCHAR(255),
      @Subject VARCHAR(MAX),
      @global BIT = 1,
     @Multiline bit =1
    )
RETURNS VARCHAR(MAX)

/*The RegexReplace function takes three string parameters. The pattern (the regular expression) the replacement expression, and the subject string to do the manipulation to.

The replacement expression is one that can cause difficulties. You can specify an empty string '' as the @replacement text. This will cause the Replace method to return the subject string with all regex matches deleted from it (see "strip all HTML elements out of a string" below). 
To re-insert the regex match as part of the replacement, include $& in the replacement text. (see "find a #comment and add a TSQL --" below)
If the regexp contains capturing parentheses, you can use backreferences in the replacement text. $1 in the replacement text inserts the text matched by the first capturing group, $2 the second, etc. up to $9. (e.g. see import delimited text into a database below) To include a literal dollar sign in the replacements, put two consecutive dollar signs in the string you pass to the Replace method.*/
AS BEGIN
    DECLARE @objRegexExp INT,
        @objErrorObject INT,
        @strErrorMessage VARCHAR(255),
        @Substituted VARCHAR(8000),
        @hr INT,
        @Replace BIT

    SELECT  @strErrorMessage = 'creating a regex object'
    EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
    IF @hr = 0 
        SELECT  @strErrorMessage = 'Setting the Regex pattern',
                @objErrorObject = @objRegexExp
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
    IF @hr = 0 /*By default, the regular expression is case sensitive. Set the IgnoreCase property to True to make it case insensitive.*/
        SELECT  @strErrorMessage = 'Specifying the type of match' 
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global
    IF @hr = 0 
        SELECT  @strErrorMessage = 'Doing a Replacement' 
    IF @hr = 0 
        EXEC @hr= sp_OAMethod @objRegexExp, 'Replace', @Substituted OUT,
            @subject, @Replacement
     /*If the RegExp.Global property is False (the default), Replace will return the @subject string with the first regex match (if any) substituted with the replacement text. If RegExp.Global is true, the @Subject string will be returned with all matches replaced.*/   
    IF @hr <> 0 
        BEGIN
            DECLARE @Source VARCHAR(255),
                @Description VARCHAR(255),
                @Helpfile VARCHAR(255),
                @HelpID INT
   
            EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
                @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
            SELECT  @strErrorMessage = 'Error whilst '
                    + COALESCE(@strErrorMessage, 'doing something') + ', '
                    + COALESCE(@Description, '')
            RETURN @strErrorMessage
        END
    EXEC sp_OADestroy @objRegexExp
    RETURN @Substituted
   END
GO

/*
The OLE Regex Find (Execute) function
-----------------------------

This is the most powerful function for doing complex finding and replacing of text. As it passes back detailed records of the hits, including the location and the backreferences, it allows for complex manipulations.

This is written as a table function. The Regex Routine actually passes back a collection for each 'hit'. In the relational world, you'd normally represent this in two tables, so we've returned a left outer join of the two logical tables so as to pass back all the information. This seems to cater for all the uses we can think of. We also append an error column, which should be blank!

*/

IF OBJECT_ID(N'dbo.RegexFind') IS NOT NULL 
    DROP FUNCTION dbo.RegexFind
GO
CREATE FUNCTION RegexFind(
    @pattern VARCHAR(255),
    @matchstring VARCHAR(MAX),
    @global BIT = 1,
   @Multiline bit =1)
RETURNS
    @result TABLE
        (
        Match_ID INT,
          FirstIndex INT ,
          length INT ,
          Value VARCHAR(2000),
          Submatch_ID INT,
          SubmatchValue VARCHAR(2000),
         Error VARCHAR(255)
        )


AS -- columns returned by the function
   BEGIN
    DECLARE @objRegexExp INT,
        @objErrorObject INT,
        @objMatch INT,
        @objSubMatches INT,
        @strErrorMessage VARCHAR(255),
       @error VARCHAR(255),
        @Substituted VARCHAR(8000),
        @hr INT,
        @matchcount INT,
        @SubmatchCount INT,
        @ii INT,
        @jj INT,
        @FirstIndex INT,
        @length INT,
        @Value VARCHAR(2000),
        @SubmatchValue VARCHAR(2000),
        @objSubmatchValue INT,
        @command VARCHAR(8000),
        @Match_ID INT
        
    DECLARE @match TABLE
        (
          Match_ID INT IDENTITY(1, 1)
                       NOT NULL,
          FirstIndex INT NOT NULL,
          length INT NOT NULL,
          Value VARCHAR(2000)
        )    
    DECLARE @Submatch TABLE
        (
          Submatch_ID INT IDENTITY(1, 1),
          match_ID INT NOT NULL,
          SubmatchNo INT NOT NULL,
          SubmatchValue VARCHAR(2000)
        )
       


    SELECT  @strErrorMessage = 'creating a regex object',@error=''
    EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
    IF @hr = 0 
        SELECT  @strErrorMessage = 'Setting the Regex pattern',
                @objErrorObject = @objRegexExp
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
    IF @hr = 0 
        SELECT  @strErrorMessage = 'Specifying a case-insensitive match' 
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline
    IF @hr = 0 
        EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global
    IF @hr = 0 
        SELECT  @strErrorMessage = 'Doing a match' 
    IF @hr = 0 
        EXEC @hr= sp_OAMethod @objRegexExp, 'execute', @objMatch OUT,
            @matchstring
    IF @hr = 0 
        SELECT  @strErrorMessage = 'Getting the number of matches'     
    IF @hr = 0 
        EXEC @hr= sp_OAGetProperty @objmatch, 'count', @matchcount OUT
    SELECT  @ii = 0 
    WHILE @hr = 0
        AND @ii < @Matchcount
        BEGIN
/*The Match object has four read-only properties. 
The FirstIndex property indicates the number of characters in the string to the left of the match. 
The Length property of the Match object indicates the number of characters in the match. 
The Value property returns the text that was matched.*/
            SELECT  @strErrorMessage = 'Getting the FirstIndex property',
                    @command = 'item(' + CAST(@ii AS VARCHAR) + ').FirstIndex'    
            IF @hr = 0 
                EXEC @hr= sp_OAGetProperty @objmatch, @command,
                    @Firstindex OUT
            IF @hr = 0 
                SELECT  @strErrorMessage = 'Getting the length property',
                        @command = 'item(' + CAST(@ii AS VARCHAR) + ').Length'    
            IF @hr = 0 
                EXEC @hr= sp_OAGetProperty @objmatch, @command, @Length OUT
            IF @hr = 0 
                SELECT  @strErrorMessage = 'Getting the value property',
                        @command = 'item(' + CAST(@ii AS VARCHAR) + ').Value'    
            IF @hr = 0 
                EXEC @hr= sp_OAGetProperty @objmatch, @command, @Value OUT
            INSERT  INTO @match
                    (
                      Firstindex,
                      [Length],
                      [Value]
                    )
                    SELECT  @firstindex + 1,
                            @Length,
                            @Value
            SELECT  @Match_ID = @@Identity         
/*The SubMatches property of the Match object is a collection of strings. It will only hold values if your regular expression has capturing groups. The collection will hold one string for each capturing group. The Count property (returned as SubmatchCount) indicates the number of string in the collection. The Item property takes an index parameter, and returns the text matched by the capturing group. 
*/
            IF @hr = 0 
                SELECT  @strErrorMessage = 'Getting the SubMatches collection',
                        @command = 'item(' + CAST(@ii AS VARCHAR)
                        + ').SubMatches'    
            IF @hr = 0 
                SELECT  @strErrorMessage = 'Getting the number of submatches'     
            IF @hr = 0 
                EXEC @hr= sp_OAGetProperty @objSubmatches, 'count',
                    @submatchCount OUT
            SELECT  @jj = 0 
            WHILE @hr = 0
                AND @jj < @submatchCount
                BEGIN
                    IF @hr = 0 
                        SELECT  @strErrorMessage = 'Getting the submatch value property',
                                @command = 'item(' + CAST(@jj AS VARCHAR)
                                + ')' ,@submatchValue=NULL   
                    IF @hr = 0 
                        EXEC @hr= sp_OAGetProperty @objSubmatches, @command,
                            @SubmatchValue OUT
                    INSERT  INTO @Submatch
                            (
                              Match_ID,
                              SubmatchNo,
                              SubmatchValue
                            )
                            SELECT  @Match_ID,
                                    @jj+1,
                                    @SubmatchValue
                    SELECT  @jj = @jj + 1
                END   
                EXEC @hr= sp_OAGetProperty @objmatch, @command,
                    @objSubmatches OUT     
            SELECT  @ii = @ii + 1
        END
    IF @hr <> 0 
        BEGIN
            DECLARE @Source VARCHAR(255),
                @Description VARCHAR(255),
                @Helpfile VARCHAR(255),
                @HelpID INT
   
            EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
                @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
            SELECT  @Error = 'Error whilst '
                    + COALESCE(@strErrorMessage, 'doing something') + ', '
                    + COALESCE(@Description, '')
        END
    EXEC sp_OADestroy @objRegexExp
     EXEC sp_OADestroy        @objMatch
     EXEC sp_OADestroy        @objSubMatches

INSERT INTO @result
          (Match_ID,
          FirstIndex,
          [length],
          [Value],
          Submatch_ID,
          SubmatchValue,
         error)


    SELECT  m.[Match_ID],
           [FirstIndex],
           [length],
           [Value],[SubmatchNo],
           [SubmatchValue],@error
  FROM    @match m
    LEFT OUTER JOIN   @submatch s
    ON m.match_ID=s.match_ID    
IF @@rowcount=0 AND LEN(@error)>0
INSERT INTO @result(error) SELECT @error
RETURN 
END
GO

Очень часто возникает ситуации, когда в 1с нужно исправить один уникальный идентификато на другой, особенно когда у вас есть 2 базы с одинаковыми наименование или кодами, этот код поможет быстро и правильно исправить и установить уникальный идентификатор.

use GlobalBase;


--временные таблицы
if OBJECT_id('dbo.#TTable') is not null
    drop table dbo.#TTable;
go


use GlobalBase;
SET NOCOUNT ON
DECLARE @tablename sysname;
DECLARE @columnname sysname;
declare @SRef binary(16);
declare @TRef binary(16);
declare @inSRef binary(16);
declare @inTRef binary(16);
declare @sql nvarchar(max);
declare @sSRef nvarchar(34);
declare @sTRef nvarchar(34);


select TSource._IDRRef as [NewRef], TTarget._IDRRef as [OldRef]
        ,TSource._Description as NEW_Description, TTarget._Description as OLD_Description        
into dbo.#TTable
    from rl_distrib.dbo._Reference82 as TSource
left join GlobalBase.dbo._Reference141 as TTarget on TSource._Description=TTarget._Description collate DATABASE_DEFAULT 
where TSource._IDRRef not in (select _IDRRef from GlobalBase.dbo._Reference141)  and (not TTarget._IDRRef is null)
and TSource._Description not in 
(select _Description from rl_distrib.dbo._Reference82 -- проверка на задвоенные значения
group by _Description
having count(_Description)>1)


    DECLARE cur_name CURSOR SCROLL FOR
        SELECT table_name=sysobjects.name,
            column_name=syscolumns.name
            --datatype=systypes.name,
            --length=syscolumns.length
        FROM sysobjects
        JOIN syscolumns ON sysobjects.id = syscolumns.id
        JOIN systypes ON syscolumns.xtype=systypes.xtype
        WHERE sysobjects.xtype='U' and systypes.name='binary' and syscolumns.length=16 and Right(syscolumns.name,3)='Ref'
        ORDER BY sysobjects.name,syscolumns.colid
    OPEN cur_name
    FETCH NEXT FROM cur_name INTO @tablename, @columnname
    while @@FETCH_STATUS = 0
    BEGIN
        set @sSRef = Convert(nvarchar(34),CONVERT(char(34),@SRef,1));
        set @sTRef = Convert(nvarchar(34),CONVERT(char(34),@TRef,1));
        set @sql = N'update updateTarget set ' + @columnname + N'= tablesource.[NewRef] 
        From 
            GlobalBase.dbo.'+@tablename+N' AS updateTarget
            Join 
            dbo.#TTable as tablesource
                on updateTarget.' + @columnname + N' = tablesource.[OldRef]';
        
        
        
        
        --set @sql = 'select @TRef'
        
        -- EXEC (@sql )
        EXEC sp_executesql @stmt = @sql;

        FETCH NEXT FROM cur_name INTO @tablename, @columnname
        
    END
    CLOSE cur_name
    DEALLOCATE cur_name
if OBJECT_id('dbo.#TTable') is not null
    drop table dbo.#TTable;
go

Попался на одну веселую проблему которую решал, довольно много времени. Вот есть 2 запроса:

SELECT *
            
    
FROM work_Global_Center.dbo._AccumRg5908 as TableTarget --WITH(TABLOCK)
    LEFT JOIN work_Global_Digma.dbo._AccumRg6186 as TableSource
        ON TableTarget._Period=TableSource._Period 
            AND TableTarget._RecorderTRef=(CASE TableSource._RecorderTRef  
                                                        WHEN CONVERT(binary(4),112) then CONVERT(binary(4),107)  
                                                        WHEN CONVERT(binary(4),102) then CONVERT(binary(4),97)  
                                                        WHEN CONVERT(binary(4),181) then CONVERT(binary(4),176)  
                                                        WHEN CONVERT(binary(4),119) then CONVERT(binary(4),114)  
                                                        WHEN CONVERT(binary(4),114) then CONVERT(binary(4),109)  
                                                        WHEN CONVERT(binary(4),142) then CONVERT(binary(4),137)  
                                                        WHEN CONVERT(binary(4),170) then CONVERT(binary(4),165)  
                                                        WHEN CONVERT(binary(4),123) then CONVERT(binary(4),118)  
                                                        WHEN CONVERT(binary(4),153) then CONVERT(binary(4),148)  
                                                        WHEN CONVERT(binary(4),109) then CONVERT(binary(4),104)  
                                                        WHEN CONVERT(binary(4),127) then CONVERT(binary(4),122)  
                                                        WHEN CONVERT(binary(4),182) then CONVERT(binary(4),177)  
                                                        WHEN CONVERT(binary(4),135) then CONVERT(binary(4),130)  
                                                        WHEN CONVERT(binary(4),165) then CONVERT(binary(4),160)  
                                                        WHEN CONVERT(binary(4),111) then CONVERT(binary(4),106)  
                                                        WHEN CONVERT(binary(4),143) then CONVERT(binary(4),138)  
                                                        WHEN CONVERT(binary(4),146) then CONVERT(binary(4),141)  
                                                        WHEN CONVERT(binary(4),154) then CONVERT(binary(4),149)  
                                                        WHEN CONVERT(binary(4),160) then CONVERT(binary(4),155)  
                                                        WHEN CONVERT(binary(4),161) then CONVERT(binary(4),156)  
                                                        ELSE TableSource._RecorderTRef END)
            AND TableTarget._RecorderRRef=TableSource._RecorderRRef
            AND TableTarget._LineNo =TableSource._LineNo
WHERE TableTarget._Period> =DATEADD(DAY,-2,getdate())
        and TableSource._LineNo is null

Этот запрос выполнялся около 2 минут!!!!!!!!!! А этот:

Declare @SelectPeriod datetime;
set @SelectPeriod = DATEADD(DAY,-2,getdate())
SELECT *
            
    
FROM work_Global_Center.dbo._AccumRg5908 as TableTarget --WITH(TABLOCK)
    LEFT JOIN work_Global_Digma.dbo._AccumRg6186 as TableSource
        ON TableTarget._Period=TableSource._Period 
            AND TableTarget._RecorderTRef=(CASE TableSource._RecorderTRef  
                                                        WHEN CONVERT(binary(4),112) then CONVERT(binary(4),107)  
                                                        WHEN CONVERT(binary(4),102) then CONVERT(binary(4),97)  
                                                        WHEN CONVERT(binary(4),181) then CONVERT(binary(4),176)  
                                                        WHEN CONVERT(binary(4),119) then CONVERT(binary(4),114)  
                                                        WHEN CONVERT(binary(4),114) then CONVERT(binary(4),109)  
                                                        WHEN CONVERT(binary(4),142) then CONVERT(binary(4),137) 
                                                        WHEN CONVERT(binary(4),170) then CONVERT(binary(4),165)  
                                                        WHEN CONVERT(binary(4),123) then CONVERT(binary(4),118)  
                                                        WHEN CONVERT(binary(4),153) then CONVERT(binary(4),148)  
                                                        WHEN CONVERT(binary(4),109) then CONVERT(binary(4),104)  
                                                        WHEN CONVERT(binary(4),127) then CONVERT(binary(4),122)  
                                                        WHEN CONVERT(binary(4),182) then CONVERT(binary(4),177)  
                                                        WHEN CONVERT(binary(4),135) then CONVERT(binary(4),130)  
                                                        WHEN CONVERT(binary(4),165) then CONVERT(binary(4),160)  
                                                        WHEN CONVERT(binary(4),111) then CONVERT(binary(4),106)  
                                                        WHEN CONVERT(binary(4),143) then CONVERT(binary(4),138)  
                                                        WHEN CONVERT(binary(4),146) then CONVERT(binary(4),141)  
                                                        WHEN CONVERT(binary(4),154) then CONVERT(binary(4),149) 
                                                        WHEN CONVERT(binary(4),160) then CONVERT(binary(4),155)  
                                                        WHEN CONVERT(binary(4),161) then CONVERT(binary(4),156)  
                                                        ELSE TableSource._RecorderTRef END)
            AND TableTarget._RecorderRRef=TableSource._RecorderRRef
            AND TableTarget._LineNo =TableSource._LineNo
WHERE TableTarget._Period> =@SelectPeriod
        and TableSource._LineNo is null

Меньше секунды........ но что очень весело, при изменении DATEADD(DAY,-2,getdate()) на DATEADD(ww,-2,getdate()), или же месяц давало повышение существенно быстродействие, фактически разница составляла больше 1,5 минуты.

Нашел очень интересные рекомендации по работе со скулом.

Рекомендации для разработчиков в среде SQL server .

 

Почему важно соблюдать правила кодирования

(цитата архитекторов SQL Server):

 

 “you should use this server as we build it for”.

« Вы должны использовать этот сервер  так , как мы его разработали»

 

 

В данном документе рекомендации даны в сжатой форме . В более развернутой форме  рекомендации можно найти в документах Microsoft для ЦИТТРАНС .

 

1. Именуйте объекты полностью (таблицы и процедуры).

Вместо SELECT <поле> FROM VagonArxOper

всегда используйте SELECT <поле> FROM dbo.VagonArxOper .

При вызове процедур так же в обязательном порядке необходимо указывать имя владельца :

            EXEC dbo.pSksMiInsertVagon  , вместо EXEC pSksMiInsertVagon 

Суть данной рекомендации заключается в том, что оптимизатор запросов SQL Server работает только с полностью именованными объектами, то есть с таблицами, которые объявлены как dbo.VagonArxOper (например). Если вы избегаете указывать схему, которой принадлежит таблица, то построитель плана выполнения запроса вынужден терять время на поиск полного имени, для чего вынужден просматривать системные таблицы на предмет поиска этого полного имени. Используя рекомендуемую практику, вы тем самым окажете помощь оптимизатору запросов в процессе компиляции плана построения запроса, сократив время его “ненужной” работы и избежав при этом ненужных перекомпиляций планов выполнения запросов.

 

Хорошей и более эффективной практикой является полное название полей таблицы    :

Select TOP 1 v.Nov_Vag From dbo.Vagon v  where v.Vagid = @Vagid

 

 

2. Использование WITH NOLOCK или WITH READUNCOMMITTED

Использование хинтов WITH (NOLOCK) в выражениях SELECT statements позволяет избежать генерации нежелательных блокировок. Такой подход обеспечивает значительное увеличение производительности  решения, так как и сам SELECT работает быстрее, а самое главное -  не блокируются параллельные задачи .

Рекомендуется вместо использования хинта NOLOCK просто понижать уровень изоляции транзакции с помощью команды SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED. Преимущество данного подхода заключается в том, что данный уровень изоляции будет распространяться на все объекты, используемые в запросе, тогда как указание хинта NOLOCK на одной таблице и его отсутствие на других таблицах, задействованных в запросе, ничего не даст.

TABLOCK

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

3.Возвращайте из запроса только те поля, которые необходимы.

Одной из наиболее часто встречающихся проблем производительности является написание запросов в стиле SELECT *.  Использование конструкции SELECT * не только возвратит вам ненужные данные, но также может вызвать операцию ПОЛНОГО ПРОСМОТРА вместо ПОИСКА ПО ИНДЕКСУ вне зависимости от наличия условия WHERE.

4. Используйте команду EXISTS .

            Используйте команду COUNT(*) тогда и только тогда , когда вам надо знать количество записей . COUNT на время исполнения блокирует для  просмотра всю таблицу.

Для проверки факта наличия записи по какому-то условию В ОБЯЗАТЕЛЬНОМ ПОРЯДКЕ следует использовать команду IF EXISTS.     Например :

IF  exists  (SELECT *  FROM  dbo.nvServerNastr  WITH  (NOLOCK )WHERE NastrID = 5 AND Data = 1)

5. Необходимо писать корректные запросы.

-       Никогда автоматически не добавляйте ключевое слово DISTINCT в выражение SELECT.

Полная выборка отработает быстрее , чем выборка с ключевым словом DISTINCT

-       Если вы выбираете одну запись , то обязательно используйте опцию TOP 1 , это сокращает               время выборки , т.к. поиск идет до первой найденной записи , иначе идет просмотр всей таблицы .Например :

Select TOP 1 v.Nov_Vag From dbo.Vagon v  where v.Vagid = @Vagid

-       все соединения между таблицами (JOIN) должны быть верные, а все ключи, используемые для соединения таблиц, указаны в условиях ON.

6.Избегайте использования таких ресурсоемких операторов как NOT LIKE.

Некоторые операторы SQL в результате выполнения считаются очень ресурсоемкими. Оператор LIKE в формате “%значение%” почти всегда вызывает сканирование таблицы. Причиной сканирования является наличие % в начале значения для поиска. Оператор LIKE в формате “значение%” всегда будет использовать поиск по индексу, что значительно более эффективно.

Операторы отрицания, такие как NOT LIKE, почти всегда очень неэффективны, и всегда, при возможности ,следует избегать их использования. Если вам необходимо проверить наличие строк / условий, необходимо использовать конструкции IF EXISTS или IF NOT EXISTS.

Если все же вам необходимо выполнить операцию NOT LIKE, рекомендуется использовать новые команды из синтаксиса SQL Server 2005, такие как EXCEPT и INTERSECT.

7.Получение пересекающихся множеств методом NOT IN ()

Например

SELECT  t1.* FROM dbo.trainarxoper  t1  WHERE trnid NOT IN

            (SELECT trnid FROM train)

Намного эффективнее будет следующая конструкция

SELECT t1.* FROM dbo.trainarxoper t1

            LEFT JOIN dbo.train t2 on t2.OperID = t1.OperID

WHERE t2.OperID is null

 

8.Избегайте явного или неявного использования функций в условии поиска WHERE.

Оптимизатор не всегда может выбрать индекс при использовании функции в условии поиска where по той причине, что условие where будет рассматриваться как выражение, а не как индексированное поле. Общей проблемой является работа с полями типа datetime.

Следующий запрос, который использует функцию над полем datetime, вызовет полное сканирование таблицы, не смотря на то, что существует соответствующий индекс:

SELECT OperID 

FROM dbo.VagonArxOper 

WHERE DATEADD(day, 15, Date_Pop) = @Date

Однако, если мы изменим запрос следующим образом, то будет использоваться индекс:

SELECT vo.OperID 

FROM dbo.VagonArxOper vo

WHERE vo.Date_Pop = DATEADD(day, -15, @Date)

9.Использование функций обработки даты в условиях поиска

например

select count (*) from  trainarxoper where datediff(hour,date_sys,getdate()) < 12 

При выполнении данного оператора при поиске по индексному полю date_sys будет осуществлена операция INDEX_SCAN. Если переписать данный оператор следующим образом

Declare @H datetime

SET @H = dateadd(hour, -12, getdate())

select count (*) from  trainarxoper where date_sys > @H

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

 

10.Не выполняйте динамический код  с использованием команды EXEC() – это одна из самых ресурсоемких команд .

11.Используйте хранимые процедуры и параметризованные запросы.

Весь выполняемый код следует создавать в виде выполняемых пакетов команд – хранимых процедур или функций. Если, в крайнем случае, появляется необходимость выполнить динамический код, то стоит отдать предпочтение использованию sp_executesql с параметрами вместо EXEC() без параметров.

 

12. Правильно оформляйте тексты .

В текстах процедур , триггеров , View в обязательном порядке должны присутствовать следующие атрибуты :

-       автор

-       назначение и описание входных / выходных параметров

-       дата создания

-       дата последней корректировки

-       комментарии к основным функциональным блокам

Следует помнить , что жизнь программы состоит на 80 % из сопровождения 

И самый дорогой вид экономии , это экономия на комментариях .

13.Минимизируйте использование курсоров.

Курсоры принуждают ядро базы данных последовательно выбирать строки из таблиц, при этом управляя блокировками, для получения некоего набора данных. По возможности необходимо использовать forward_only и read_only курсоры (курсоры, открытые только для чтения и только для просмотра по ходу движения курсора), так как они в наименьшей степени поглощают ресурсы сервера. Все другие типы курсоров используют временную базу данных tempdb очень интенсивно, что приводит к перерасходу ресурсов.

В любом случае код, написанный с использованием самого эффективного курсора, выполняется на 25% медленнее, чем тот же код, написанный с помощью обычной комбинации SELECT … с последующей обработкой.

Создание курсора на базе временной таблицы вдвойне неэффективно.

14.Чрезмерно частое создание временных табличных вместо создания табличных переменных.

Повсеместно используемой практикой в процессе написания хранимых процедур является использование локальных временных таблиц (например, #temp). При этом данные таблицы содержат маленькие наборы данных (не более 100 строк), не индексируются, и удаляются перед окончанием процедуры.

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

-          план исполнения процедуры не будет часто перекомпилироваться в зависимости от состояния базы tempdb,

-          временные данных буду храниться в оперативной памяти (стеке выполнения) процедуры

o    на них не будут влиять блокировки, возникающие в это время в tempdb

o    доступ к таким таблицам в памяти будет осуществляться быстрее

 

Лишь в тех случаях, когда

-          вы можете предвидеть, что во временную таблицу будет помещен большой набор данных (десятки тысяч строк и более),

-          вы планируете индексировать временную таблицу для ускорения процесса обработки данных,

оправданно использование временных таблиц, созданных оператором CREATE TABLE #table.

15.Минимизируйте использование триггеров.

Следует максимально сократить использование триггеров . а на таблицах общего пользования вообще отказаться от их использования .

 Триггер позволяет с минимальными усилиями разработчика получить локальный результат .

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

Заменой триггеров должны стать прикладные решения

16.Минимизируйте использование объектов SQL для контроля целостности  .

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

 

17.Избегайте написания универсальных процедур.

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

В качестве альтернативы следует написать нескольких специализированных процедур . Этот  вариант свободен от указанных недостатков .

18.Соблюдайте простые правила.

-       не используйте лишних операций CONVERT  и ISNULL

-       используйте  опции SET NOCOUNT ON в заголовке процедуры.

- В конкурентной предпочтительнее использование SCOPE_IDENTITY вместо @@IDENTITY по той причине, что SCOPE_IDENTITY гарантирует получение значение  IDENTITY из той таблицы, в которую вставлялись данные. В случае наличия (и срабатывания) триггера на таблице значение IDENTITY может быть неверным (принадлежащим другой таблице).

 

 

19 . Не менять в процедурах настройки базы

Согласно рекомендаций Microsoft необходимо использовать если не стандартные, то однообразные настройки с базе данных. Рекомендованные настройки базы данных выглядят следующим образом:

 

ANSI_NULLS                                       ON

ANSI_PADDING                                              ON

ANSI_WARNINGS                                            ON

ARITHABORT                                       ON

CONCAT_NULL_YIELDS_NULL                        ON

QUOTED_IDENTIFIER                          ON

 

 

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

 

20. Персональное объявление переменных вместо групповой операции объявления.

 

Например, вместо

declare @PPOSFOR_DAT datetime

declare @PPOSFOR_IPR int

declare @PPOSOSV_DAT datetime

declare @PPOSOSV_IPR int

declare @PDL numeric(5,1)

declare @PDR numeric(5,1)

declare @PGRUZ int

 

рекомендуется использовать

 

declare @PPOSFOR_DAT datetime, @PPOSFOR_IPR int, @PPOSOSV_DAT datetime,

@PPOSOSV_IPR int, @PDL numeric(5,1), @PDR numeric(5,1), @PGRUZ int

 

21.  Построчное присвоения значений переменным

 

Например, вместо

set @opToArenda = 7 -- код операции сдачи в аренду

set @opFrArenda = 8 -- код операции приема из аренды

set @opDelWag   = 10 -- код операции исключения из инвентаря

set @opNewWag   = 11 -- код операции исключения из инвентаря

set @opOpKsarm = 53 -- код общей операции отчетности в базе КСАРМ

необходимо использовать

select @opToArenda = 7, -- код операции сдачи в аренду

@opFrArenda = 8, -- код операции приема из аренды

@opDelWag  = 10, -- код операции исключения из инвентаря

@opNewWag  = 11, -- код операции исключения из инвентаря

@opOpKsarm = 53 -- код общей операции отчетности в базе КСАРМ

 

Выполнение пакета присвоений одним оператором будет эффективнее.

Но вместо select @uslovie=NULL рекоммендуется использовать set @uslovie=NULL.

 

22. Создание таблиц методом SELECT INTO.

 

Например, вместо

 

select convert(int,0) as idW,B.wagId ,B.wagn ,B.catg ,B.kind ,B.own ,B.state

       ,B.syst ,B.flLoad ,B.opId ,B.owner

into #acWkPere1

from #acWkPere A LEFT JOIN acDu8St B  on( A.idW = B.wagId )where 1=0

 

рекомендуется использовать явное объявление таблиц с помощью оператора CREATE TABLE #temp или использовать табличную переменную (что более эффективно), нежели использовать оператор SELECT, если заранее известно, что этот оператор не вернет данных, как в приведенном примере.

 

23.Выполнение оператора INSERT INTO без указания списка полей.

Например

insert into #SX select A.stationEsr,P.value

  from #S A left join

   ( select S.stationEsr,ISNULL( (select P.value from nvacPaspN P where [id]=23 and P.stationEsr=S.stationEsr)

                                ,(select P.value from nvacPaspN P where [id]=23 and P.stationEsr=0) ) as sx

     from #S S   ) as B

  on A.stationEsr=B.stationEsr

 

Необходимо

insert into #SX (stationEsr, sx) select A.stationEsr,P.value

  from #S A left join …………..

 

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