SQL Server stored procedure extremely slow

Question

I'm using a cursor on my 1700 million records table. It is really slow, let's say 30 round per second. I have no idea where to start.

It transfers all data from 1 table into multiple tables having data for each stream per year.

Here is the code:

CREATE PROCEDURE [dbo].[Importer2]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @LogId bigint;
    DECLARE @SensorID int;
    DECLARE @ValueNumeric decimal(12,4);
    DECLARE @ValueString nvarchar(20);
    DECLARE @DateAdded datetime;
    DECLARE @Status_ NVARCHAR(10)
    DECLARE @SQLString_ NVARCHAR(MAX)
    DECLARE @Year_  NVARCHAR(4)
    DECLARE @TableName_ NVARCHAR(100) --= '[ScadaData].[dbo].[2016_123456]'
    DECLARE @TableNameOnly_ NVARCHAR(100)
    DECLARE @ValueStringTMP_ NVARCHAR(20)
    DECLARE @Measure_ datetime

    DECLARE @ImporterCursor AS CURSOR;

    SET @Status_ = 'OK'

    SELECT @LogId = Value 
    FROM dbo.AppSettings  
    WHERE dbo.AppSettings.Setting = 'LastImportedId';

    SET @ImporterCursor = CURSOR FAST_FORWARD FOR
        SELECT * 
        FROM dbo.Logs l 
        WHERE l.LogID > @LogId;

    OPEN @ImporterCursor;

    FETCH NEXT FROM @ImporterCursor INTO @LogId, @SensorID, @ValueNumeric, @ValueString, @DateAdded;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (@DateAdded IS NOT null)
        BEGIN
            SET @TableName_ = '[ScadaData].[dbo].[Y' + CONVERT(varchar,YEAR(@DateAdded)) + 'S' + CONVERT(varchar,@SensorID) + ']'
            SET @TableNameOnly_ = 'Y' + CONVERT(varchar,YEAR(@DateAdded)) + 'S' + CONVERT(varchar,@SensorID)

            --table does not exists. Create one
            IF NOT EXISTS (SELECT 1 FROM ScadaData.dbo.sysobjects 
                           WHERE xtype = 'U' AND name = @TableNameOnly_)
            BEGIN
                SET @SQLString_ = 'CREATE TABLE ' + @TableName_ + '(
                    [LogID] [bigint] IDENTITY(1,1) NOT NULL,
                    [ValueNumeric] [decimal](12, 4) NULL,
                    [ValueString] [nvarchar](20) NULL,
                    [DateAdded] [datetime2](7) NULL DEFAULT (GETUTCDATE()),
                    CONSTRAINT [PK_Logs' + @TableNameOnly_ + '] PRIMARY KEY CLUSTERED ([LogID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY];
                    CREATE NONCLUSTERED INDEX [DateAddedDESC_' + @TableNameOnly_ + '] ON [dbo].[' + @TableNameOnly_ + '] ([DateAdded] DESC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);'

            EXEC sp_executesql @SQLString_
        END

        --Insert or Update if ValueNumeric is sent
        IF @ValueNumeric IS NOT NULL
        BEGIN
            SET @SQLString_ =               'DECLARE @ValueNumericTmp_  DECIMAL(12,4);'
            SET @SQLString_ = @SQLString_ + 'DECLARE @LogID             BIGINT;'
            SET @SQLString_ = @SQLString_ + 'SELECT TOP 1 @ValueNumericTmp_ = ValueNumeric, @LogID = LogID FROM ' + @TableName_ + ' ORDER BY DateAdded DESC;'
            SET @SQLString_ = @SQLString_ + 'IF (@ValueNumericTmp_ = ' + CONVERT(varchar, @ValueNumeric) + ')'
            SET @SQLString_ = @SQLString_ + '   UPDATE ' + @TableName_ + ' SET DateAdded = ''' + CONVERT(varchar,@DateAdded,121) + ''' WHERE LogID = @LogID;'
            SET @SQLString_ = @SQLString_ + 'ELSE'
            SET @SQLString_ = @SQLString_ + '   INSERT INTO ' + @TableName_ + ' (ValueNumeric, ValueString, DateAdded) VALUES (' + CONVERT(varchar,@ValueNumeric) +', ' + ISNULL(''''  + @ValueString + '''','NULL') + ', GETUTCDATE());'

            EXEC (@SQLString_)
        END

        --Insert or Update if ValueString is sent
        IF @ValueString IS NOT NULL
        BEGIN
            SET @SQLString_ =               'DECLARE @ValueStringTMP_   NVARCHAR(20);'
            SET @SQLString_ = @SQLString_ + 'DECLARE @LogID             BIGINT;'
            SET @SQLString_ = @SQLString_ + 'SELECT TOP 1 @ValueStringTMP_ = ValueString, @LogID = LogID FROM ' + @TableName_ + ' ORDER BY DateAdded DESC;'
            SET @SQLString_ = @SQLString_ + 'IF (@ValueStringTMP_ = ''' + @ValueString + ''')'
            SET @SQLString_ = @SQLString_ + '   UPDATE ' + @TableName_ + ' SET DateAdded = ''' + CONVERT(varchar,@DateAdded,121) + ''' WHERE LogID = @LogID;'
            SET @SQLString_ = @SQLString_ + 'ELSE'
            SET @SQLString_ = @SQLString_ + '   INSERT INTO ' + @TableName_ + ' (ValueNumeric, ValueString, DateAdded) VALUES (' + CONVERT(varchar,@ValueNumeric) +', ''' + @ValueString + ''', GETUTCDATE());'

            EXEC sp_executesql @SQLString_
        END
    END

    UPDATE dbo.AppSettings 
    SET dbo.AppSettings.[Value] = CAST(@LogId AS VARCHAR (50)) 
    WHERE dbo.AppSettings.Setting = 'LastImportedId';

   FETCH NEXT FROM @ImporterCursor INTO @LogId, @SensorID, @ValueNumeric, @ValueString, @DateAdded;
    END

    CLOSE @ImporterCursor;
    DEALLOCATE @ImporterCursor;
END

Answer 1

You have a lot of problems.
As it was already mentioned, the first red flag is a cursor.
However, sometimes you can't support logic without cursors and it is your case.

Secondly, you use dynamic SQL. It is also contributing to performance.

BUT, your biggest problem is application design.
You MUST NOT create individual table for each Sensor from your Log.

The easy solution is to create ONLY ONE "tbl_Sensors" table with an additional column SensorID and reuse it 1700 times instead of recreating 1700 tables.