我的原始解决方案(在我完成数据的实际计算之前)是让我的应用程序将记录插入到我的客户查询的同一个表中.显然,这很快就会崩溃和烧毁,因为查询插入了大量记录的表是不可能的.

我的第二个解决方案是使用2个数据库,一个用于应用程序接收的数据,另一个用于客户端就绪数据.

我的应用程序将接收数据,将其分成大约100k条记录并批量插入到临时表中.在大约100k记录之后,应用程序将动态创建另一个具有与以前相同的模式的临时表,并开始插入该表.它将在作业表中创建一个记录,其中包含具有100k记录的表的名称,并且sql Server端的存储过程会将数据从登台表移动到客户端就绪生产表,然后删除我的应用程序创建的表临时表.

除具有jobs表的登台数据库外,两个数据库都具有相同模式的5个表的相同集合.登台数据库在大量记录所在的表上没有完整性约束,密钥,索引等.如下所示,表名是SignalValues_staging.目标是让我的应用程序尽快将数据打入sql Server.即时创建表以便可以轻松迁移的工作流程非常有效.

以下是我的临时数据库中的5个相关表,以及我的jobs表：

我编写的存储过程处理从所有登台表中移动数据并将其插入生产中.下面是我的存储过程中从登台表插入到生产中的部分：

-- Signalvalues jobs table.
SELECT *,ROW_NUMBER() OVER (ORDER BY JobId) AS 'RowIndex'
INTO #JobsToProcess
FROM 
(
    SELECT JobId,ProcessingComplete,SignalValueStagingTableName AS 'TableName',(DATEDIFF(SECOND,(SELECT last_user_update
                              FROM sys.dm_db_index_usage_stats
                              WHERE database_id = DB_ID(DB_NAME())
                                AND OBJECT_ID = OBJECT_ID(SignalValueStagingTableName)),GETUTCDATE())) SecondsSinceLastUpdate
    FROM SignalValueJobs
) cte
WHERE cte.ProcessingComplete = 1
   OR cte.SecondsSinceLastUpdate >= 120

DECLARE @i INT = (SELECT COUNT(*) FROM #JobsToProcess)

DECLARE @jobParam UNIQUEIDENTIFIER
DECLARE @currentTable NVARCHAR(128) 
DECLARE @processingParam BIT
DECLARE @sqlStatement NVARCHAR(2048)
DECLARE @paramDefinitions NVARCHAR(500) = N'@currentJob UNIQUEIDENTIFIER,@processingComplete BIT'
DECLARE @qualifiedTableName NVARCHAR(128)

WHILE @i > 0
BEGIN

    SELECT @jobParam = JobId,@currentTable = TableName,@processingParam = ProcessingComplete
    FROM #JobsToProcess 
    WHERE RowIndex = @i 

    SET @qualifiedTableName = '[Database_Staging].[dbo].['+@currentTable+']'

    SET @sqlStatement = N'

        --Signal values staging table.
        SELECT svs.* INTO #sValues
        FROM '+ @qualifiedTableName +' svs
        INNER JOIN SignalMetaData smd
            ON smd.SignalId = svs.SignalId  


        INSERT INTO SignalValues SELECT * FROM #sValues

        SELECT DISTINCT SignalId INTO #uniqueIdentifiers FROM #sValues

        DELETE c FROM '+ @qualifiedTableName +' c INNER JOIN #uniqueIdentifiers u ON c.SignalId = u.SignalId

        DROP TABLE #sValues
        DROP TABLE #uniqueIdentifiers

        IF NOT EXISTS (SELECT TOP 1 1 FROM '+ @qualifiedTableName +') --table is empty
        BEGIN
            -- processing is completed so drop the table and remvoe the entry
            IF @processingComplete = 1 
            BEGIN 
                DELETE FROM SignalValueJobs WHERE JobId = @currentJob

                IF '''+@currentTable+''' <> ''SignalValues_staging''
                BEGIN
                    DROP TABLE '+ @qualifiedTableName +'
                END
            END
        END 
    '

    EXEC sp_executesql @sqlStatement,@paramDefinitions,@currentJob = @jobParam,@processingComplete = @processingParam;

    SET @i = @i - 1
END

DROP TABLE #JobsToProcess

我使用sp_executesql,因为登台表的表名是来自jobs表中记录的文本.

这个存储过程使用我从this dba.stackexchange.com post学到的技巧每2秒运行一次.

我不能解决的问题是执行插入生产的速度.我的应用程序创建临时登台表,并以极快的速度填充记录.插入到生产中无法跟上表的数量,并且最终会有数千个表中的多余表.我能够跟上传入数据的唯一方法是删除生产SignalValues表上的所有键,索引,约束等.我接下来面临的问题是该表最终会有如此多的记录,因此无法查询.

我已经尝试使用[Timestamp]作为分区列来分区表无济于事.任何形式的索引都会减慢插入速度,使得它们无法跟上.另外,我需要提前几年创建数千个分区(每分钟一小时？).我无法弄清楚如何在飞行中创建它们

我尝试通过向名为TimestampMinute的表添加计算列来创建分区,其值为INSERT,DATEPART(MINUTE,GETUTCDATE()).还是太慢了.

我已经尝试按照this Microsoft article将它变成一个内存优化表.也许我不明白该怎么做,但是MOT使插入速度变慢了.

我检查了存储过程的执行计划,发现(我认为？)最密集的操作是

SELECT svs.* INTO #sValues
FROM '+ @qualifiedTableName +' svs
INNER JOIN SignalMetaData smd
    ON smd.SignalId = svs.SignalId

对我来说这没有意义：我已经在存储过程中添加了挂钟记录,否则证明了这一点.

在时间记录方面,上述特定语句在100k记录上执行约300ms.

该声明

INSERT INTO SignalValues SELECT * FROM #sValues

在100k记录上执行2500-3000ms.从表中删除受影响的记录,符合：

DELETE c FROM '+ @qualifiedTableName +' c INNER JOIN #uniqueIdentifiers u ON c.SignalId = u.SignalId

再花300ms.

我怎样才能让它更快？ sql Server能否每天处理数十亿条记录？

如果相关,则为sql Server 2014 Enterprise x64.

硬件配置：

我忘了在这个问题的第一遍中包含硬件.我的错.

我将在这些陈述中加注：我知道由于我的硬件配置,我正在失去一些性能.我已经尝试了很多次,但是由于预算,C级,行星的对齐等等,不幸的是我无法做更好的设置.服务器在虚拟机上运行,​​我甚至无法增加内存,因为我们根本没有.

这是我的系统信息：

存储通过iSCSI接口连接到VM服务器到NAS盒(这会降低性能). NAS盒在RAID 10配置中有4个驱动器.它们是4TB WD WD4000FYYZ旋转磁盘驱动器,具有6GB / s SATA接口.服务器只配置了一个数据存储,因此tempdb和我的数据库位于同一个数据存储中.

最大DOP为零.我应该将其更改为常量值还是让sql Server处理它？我读到RCSI：我是否认为RCSI的唯一好处是行更新？永远不会有任何这些特定记录的更新,它们将被插入和选择. RCSI还能让我受益吗？

我的tempdb是8mb.基于以下jyao的答案,我将#sValues更改为常规表以完全避免tempdb.虽然表现差不多.我会尝试增加tempdb的大小和增长,但考虑到#sValues的大小或多或少总是大小相同,我预计不会有太大的收益.

我已经采取了我在下面附上的执行计划.此执行计划是临时表的一次迭代–100k记录.查询的执行相当快,大约2秒,但请记住,SignalValues表上没有索引,而INSERT的目标SignalValues表中没有记录.