加入收藏 | 设为首页 | 会员中心 | 我要投稿 上饶站长网 (https://www.0793zz.com.cn/)- 数据库平台、视觉智能、智能搜索、决策智能、迁移!
当前位置: 首页 > 站长学院 > MsSql教程 > 正文

sql-server – SQL Server中的大量数据和性能

发布时间:2021-05-17 06:29:35 所属栏目:MsSql教程 来源:网络整理
导读:我编写了一个带有SQL Server后端的应用程序,用于收集和存储大量的记录.我已经计算出,在最高峰时,平均记录量大约在每天30-40亿(运营20小时)的大道上. 我的原始解决方案(在我完成数据的实际计算之前)是让我的应用程序将记录插入到我的客户查询的同一个表中.显然
副标题[/!--empirenews.page--]

我编写了一个带有SQL Server后端的应用程序,用于收集和存储大量的记录.我已经计算出,在最高峰时,平均记录量大约在每天30-40亿(运营20小时)的大道上.

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

我的第二个解决方案是使用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.

硬件配置:

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

(编辑:上饶站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

推荐文章
    热点阅读