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

Question

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

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I have a query that when it runs it is taking a while to bring results. And I do not know where to start to improve this query. If anyone can help me I'll be grateful.

The query copies a record that is in a reference table that has two columns (Name all banks and all my bank table) and then merge the result of this table " REFERENCE " to be able to insert into another table " CONSULTATION " to list all banks, tables, and other columns referring to these two columns that 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;

sql-server

asked by anonymous 05.10.2017 / 13:51

2 answers

1

Looking at your routine you can see some things that undermine performance:

Fields of type nvarchar (max)

Instead of nvarchar(max) , determine a size for the field. This way SQL Server can better calculate the execution plan.
Fields of type nvarchar

If possible, replace with varchar . The nvarchar fields occupy twice as much space.
Delete to clear the 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 register sets than with cursor (1 record at a time). From what I saw of this query, this point does not have much to do, because a dynamic execution of the inserts is performed. But the recommendation is for when possible.

05.10.2017 / 21:56

Turn decimal into PHP binary How to return the values of an arraylist in Java

score 1 · Accepted Answer

Try to replace CURSOR with INSERT direct 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;