How to reduce the running time of a query in SQL server?

Question

I have a query that when it runs it’s taking a little while to bring results. And I don’t know where to start to improve this query. If anyone can help me I’ll be grateful.

The query copies record that are in a reference table that has two columns (Name of all banks and all tables of my bank) and then joins the result of this table "REFERENCE" to power insert into another table "CONSULTATION" to list all the banks, tables, and other columns referring to those two columns which I mentioned before the table "REFERENCE". Here is the query:

 /*INDICANDO QUE QUERO USAR O BANCO DADOS(OBRIGATÓRIO) */
use Dados

 /*DECLARANDO VARIAVEIS PARA MINHA TABELA RESULTADO QUE NÃO É FÍSICA*/
DECLARE @Resultado TABLE
(
 Tabelas nvarchar(max),
 data_sincronizacao date,
 rodovia nvarchar(max),
 elemento nvarchar(max),
 n_foto int

)

 /*DECLARANDO VARIAVEIS PARA MINHA PROCEDURE */
DECLARE @Table_cursor CURSOR 
DECLARE @rodovia nvarchar(MAX)
DECLARE @elemento nvarchar(MAX)
DECLARE @table_name nvarchar(MAX)
DECLARE @database_name  nvarchar(MAX)
DECLARE @sql_insert nvarchar(MAX)

 /*CHAMANDO MEU CURSOR,APONTADANDO ELE PERCORER A TABELA DE REFERENCIA A ONDE ESTÁ ARMAZENADO OS BANCOS E AS TEBALES COM TERMINO "ATTACH" */
SET @Table_cursor = CURSOR FORWARD_ONLY FOR
SELECT Banco, Tabela, Rodovia, Elemento from Dados.dbo.Referencia

 /*LIMPANDO A TABELA "CONSULTA" PARA EVITAR DUPLICAÇÃO */
DELETE FROM Dados.dbo.Consulta

 /*ABRINDO O CURSOR PARA PERCORER TODOS OS DADOS DA TABELA REFERENCIA TRAZENDO DADOS DAS RESPECTIVAS COLUNAS */
OPEN @Table_cursor
FETCH NEXT FROM @Table_cursor INTO @database_name, @table_name, @rodovia, @elemento
WHILE @@FETCH_STATUS = 0
BEGIN
    --PRINT 'Inserindo dados do banco ' + @database_name + ' e tabela ' + @table_name


    SET @sql_insert = CONCAT('INSERT INTO Consulta (Banco, Tabela, Rodovia, Elemento, n_foto, data_sincronizacao) SELECT ''', @database_name, ''', ''', @table_name, ''', ''', @rodovia, ''', ''', @elemento, ''', count(*) as n_foto, FORMAT (GDB_FROM_DATE, ''dd-MM-yyyy'') FROM ', @database_name, '.dbo.' + @table_name, ' GROUP BY FORMAT(GDB_FROM_DATE, ''dd-MM-yyyy'');')

    EXECUTE sp_executesql @sql_insert
    FETCH NEXT FROM @Table_cursor INTO @database_name, @table_name, @rodovia, @elemento
END
CLOSE @Table_cursor;
DEALLOCATE @Table_cursor;

Answer 1

Try to replace the CURSOR for INSERT as follows:

DECLARE @resultado TABLE(
  banco              VARCHAR(MAX),
  tabela             VARCHAR(MAX),
  rodovia            VARCHAR(MAX),
  elemento           VARCHAR(MAX),
  n_foto             INT,
  data_sincronizacao VARCHAR(10)
);

DECLARE @query NVARCHAR(MAX);

SELECT @query = ISNULL(@query + char(10) + 'UNION' + char(10))
              + 'SELECT ''' + r.banco + ''', ''' + r.tabela + ''', ''' + r.rodovia + ''', ''' + r.elemento + ''', COUNT(1), FORMAT(GDB_FROM_DATE, ''dd-MM-yyyy'') FROM ' + r.banco + '.dbo.' + r.tabela + ' GROUP BY FORMAT(GDB_FROM_DATE, ''dd-MM-yyyy'')'
  FROM dados.dbo.referencia r;

INSERT INTO @resultado
EXEC(@query);

-- Insere na tabela consulta
INSERT INTO consulta(banco,
                     tabela,
                     rodovia,
                     elemento,
                     n_foto,
                     data_sincronizacao)
SELECT r.banco,
       r.tabela,
       r.rodovia,
       r.elemento,
       r.n_foto,
       r.data_sincronizacao
  FROM @resultado r;

Answer 2

Looking at your routine you can notice some things that impair performance:

• Fields of the nvarchar type (max)

  In place of nvarchar(max), determine a field size. This way SQL-Server can better calculate the execution plan.

• Fields of the nvarchar type

  If possible, replace with varchar. The fields nvarchar take up twice as much space.

• Delete to clear data

  If there is no need to keep the log and the Identity fields, use Truncate to clear the Query table instead of Delete

• Cursor

  SQL-SERVER will always work better and faster with record sets than with cursor (1 record at a time). From what I have seen of this consultation, this point does not have much to do, as a dynamic implementation of the Inserts is carried out. But the recommendation is for when possible.