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. 硬件设置： 我忘了在这个题目的第一遍中包括硬件.我的错. （编辑：湖南网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
