sql-server – SQL Server中的大量数据和性能
|
副标题[/!--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. 硬件配置： 我忘了在这个问题的第一遍中包含硬件.我的错. （编辑：核心网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
