Update Statistics With Full Scan in SQL Server We all know to keep auto statistics update ON for a SQL server databases little overkill the performance and query may or may not use correct statistics and may choose the non-optimal execution plan. This may slow down the performance of complex queries to be run faster. Also, it is not a good idea to rebuild fragmented indexes every day in a busy production environment where thousands of query already running and there is no window for database maintenance task. When you will run index rebuild operation on a busy database you may encounter Query Timeout error for other running queries on a larger database. So the alternative and best solution are to update statistics with a full scan. Assuming you already knowing update statistics with sp_updatestats only update statistics a sample of records of the table and with full scan, it will scan the entire table. DECLARE @tablename varchar(80),@shemaname varchar(80) DECLARE @SQL AS NVARCHAR(200) DECLARE TblName_cursor CURSOR FOR SELECT t.name,s.name FROM sys.tables t join sys.schemas s on s.schema_id=t.schema_id OPEN TblName_cursor FETCH NEXT FROM TblName_cursor INTO @tablename,@shemaname WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'UPDATE STATISTICS '+@shemaname+'.[' + @TableName + '] WITH FULLSCAN ' EXEC sp_executesql @statement = @SQL FETCH NEXT FROM TblName_cursor INTO @tablename,@shemaname END CLOSE TblName_cursor DEALLOCATE TblName_cursor Instead of updating statistics for each table we can use below script to update statistics for all tables of a database

What is the alternative in MySQL for this kind of code?

Bhuvneshwar

My system was done in T-SQL and now I'm trying to convert it to MySQL, since MySQL does not have Table variable, how can it be changed? I don't know if a should use a temp Table or a cursor, my searches told me that I shpuld avoid cursors since there's always a better set treatment instead of looping over all the rows.

    ALTER TRIGGER [dbo].[COLETA]
   ON  [dbo].[AquanetColetaDados]
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @CURSOR TABLE
    (
        ROWID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
        ID BIGINT,
        DATAREGISTRO DATETIME,
        VALOR FLOAT,
        COD_PONTO INT,
        COD_ESTACAO INT,
        TP_PT INT,
        QTDEDIASHIST INT,
        FUSO INT
    )

     INSERT INTO @CURSOR
     (
        ID,
        DATAREGISTRO,
        VALOR,
        COD_PONTO,
        COD_ESTACAO,
        TP_PT,
        QTDEDIASHIST,
        FUSO
     )
    SELECT 
        I.ID,
        I.DATAREGISTRO,         
        CASE AD.COLUNA
             WHEN 2 THEN I.COLUNA1
             WHEN 3 THEN I.COLUNA2
             WHEN 4 THEN I.COLUNA3
             WHEN 5 THEN I.COLUNA4
             WHEN 6 THEN I.COLUNA5
             WHEN 7 THEN I.COLUNA6
             WHEN 8 THEN I.COLUNA7
             WHEN 9 THEN I.COLUNA8
             WHEN 10 THEN I.COLUNA9
             WHEN 11 THEN I.COLUNA10
        END AS VALOR,
        AD.COD_PONTO_COLETA,
        I.CODESTACAO,
        P.TP_PT,
        ISNULL(P.QTDE_DIAS_HIST,0),
        U.FUSO_NORMAL
    FROM INSERTED I
    INNER JOIN AQUASAVE.DBO.ARQUIVO_COLETOR A
        ON A.CODIGO=I.CODESTACAO
    INNER JOIN AQUASAVE.DBO.ARQ_COLET_DET AD
        ON AD.COD_ARQUIVO=I.CODESTACAO
    INNER JOIN AQUASAVE.DBO.PONTO_COLETA P 
        ON P.COD_PON_COL = AD.COD_PONTO_COLETA   
    INNER JOIN AQUANET.DBO.FLAG_ALARM F
        ON F.FLAG_SUSPENDE_COLETA=0
    INNER JOIN [AQUASAVE].[DBO].UF U
        ON U.CODIGO = A.UF
    WHERE   A.FLAG_SUSPENDE_LEITURA=0
            AND P.STATUS=1
            AND P.FLAG_PRI_LEITURA=0
            AND P.FLAG_SUSPENDE_COLETA_PONTO=0
    ORDER BY I.DATAREGISTRO,AD.COD_PONTO_COLETA
END