代码之家 › 专栏 › 技术社区 › Retrocoder

慢SQL命令

sql-server sql

Retrocoder · 技术社区 · 14 年前

我需要从一个表中获取一些数据(并在途中展开一些xml),然后将其放入另一个表中。由于源表可以有数千条或多条导致超时的记录,所以我决定以100条记录为一批来执行。代码按计划运行,因此分批执行对客户来说是正常的。如果我说源数据库中有200条记录,存储过程运行得非常快,但如果有数千条记录,则需要几分钟。我猜“前100名”只有在所有的记录都查过之后才能进入前100名。我需要在某个时候更改整个代码和存储过程,因为它不可伸缩,但是现在是否有一个快速的解决方案来加快运行速度?

INSERT INTO [deviceManager].[TransactionLogStores] 
SELECT TOP 100 [EventId],
    [message].value('(/interface/mac)[1]', 'nvarchar(100)') AS mac,
    [message].value('(/interface/device) [1]', 'nvarchar(100)') AS device_type,
    [message].value('(/interface/id) [1]', 'nvarchar(100)') AS device_id,
    [message].value('substring(string((/interface/id)[1]), 1, 6)', 'nvarchar(100)') AS store_id,
    [message].value('(/interface/terminal/unit)[1]', 'nvarchar(100)') AS unit,
    [message].value('(/interface/terminal/trans/event)[1]', 'nvarchar(100)') AS event_id,
    [message].value('(/interface/terminal/trans/data)[1]', 'nvarchar(100)') AS event_data,
    [message].value('substring(string((/interface/terminal/trans/data)[1]), 9, 11)', 'nvarchar(100)') AS badge,
    [message].value('(/interface/terminal/trans/time)[1]', 'nvarchar(100)') AS terminal_time,
    MessageRecievedAt_UTC AS db_time
FROM [deviceManager].[TransactionLog]
WHERE EventId > @EventId
--WHERE MessageRecievedAt_UTC > @StartTime AND MessageRecievedAt_UTC < @EndTime
ORDER BY terminal_time DESC

3 回复 | 直到 14 年前

Matt Gibson 14 年前

您需要点菜吗?如果数据没有排序的话,排名靠前的人不需要把整个结果集拿回来就可以把排名靠前的100个数据切掉。如果你真的需要订购,确保你有一个终端时间的索引;如果要花那么长的时间,你可能没有。

另外,确保你有一个关于eventid的索引。

Tomalak 14 年前

你有索引吗 EventId (和) MessageRecievedAt_UTC )?

做 @EventId 和 @StartTime/@EndTime 具有与相应列相同的数据类型?

Ian Henry 14 年前

它看起来像(如果我错了,请纠正我,这是其他构造) message.value() 是一个总是接受相同输入的自定义项。您每N行调用该函数大约10次——这肯定会增加性能负担。如果这是一个确定性函数(我希望是),您可以将这些函数调用的结果缓存到一个临时表中,性能应该会显著提高。

如果不这样做,可能是排序花费的时间最长。在决定如何排序之前,它必须得到完整的结果集。正如马特所说,一个指数可以大大加快这一速度。