sql-server - 当只有 20 个同时用户将跟踪数据插入地理表时，CPU 使用率会很高

Question

我们有实时跟踪应用程序，其中我们将跟踪点插入到一个名为Tracking. 我们有另一个表ExistingAddressPointTable，我们有送货地址。当送货员在该地址的 200 米半径范围内时，我们正在考虑将特定地址交付，并将送货条目插入称为Delivery（多对多关系）表的非空间表中。

我们在每分钟 20-30 秒后进行 API 调用，因此设备在此期间始终发送 10-15 个地址点。我在存储过程中实现了这个解决方案，但问题是即使同时 20 个交付男孩会话 Azure SQL Server CPU 使用率达到 100%。我想支持这个系统同时进行 1000 个会话。

我们在 UAT 环境中使用具有 50 个 DTU 限制的弹性池中的 Azure SQL Server 数据库。我们的客户在同一个弹性池中已经有 5 个其他 250 GB 的数据库。对于生产，我们也有 100 个 DTU 的限制。

任何实现Delivery男孩的解决方案是通过不同的移动设备进行跟踪？

使用的技术：.net core、Web API 和 Azure SQL Server 2017，空间表 ( Tracking& ExistingAddressPointTable) 上的空间索引已应用。非常感谢任何帮助。

PSB 代码供参考

CREATE Procedure [dbo].[InsertTrackingPoint]
    @points AddressPointType READONLY,
    @name NVARCHAR(150),
    @isDisConnected BIT,
    @jobId BIGINT     -- with current scenario of implementation.This is not required
AS 
BEGIN
Declare @routeId bigint;
Declare @point nvarchar(700);
DECLARE @totalRecords INT
DECLARE @I INT
DECLARE @trackingId bigint
Declare @deliveryBoyId bigint;
DECLARE @route As TABLE(Id int Identity,RouteId bigint,JobId bigint);
Select Top 1 @deliveryBoyId=Id from DeliveryPerson with(Nolock) where --Take DeliveryPerson Id FROM DeliveryPerson Table to insert breadcrumb against particular Id
(DeliveryPerson.Name LIKE @name) OR ( DeliveryPerson.Email LIKE @name ) AND IsActive=1
Insert into @route
Select Id As RouteId,JobId from Route With(Nolock) where CarrierID=@deliveryBoyId AND JobId in( Select Id From Job where EndDate>=Convert(date,GETDATE())AND IsActive=1) 
AND TotalAddressPoint>0
order by Id desc --Inserting all the active jobs assigned to that carrier and also checking if addresspoint count is greater than 0.It means we are taking those Jobs whose addressPoint count is greater than 0.
Declare @J INT;
DECLARE @totalRouteCount INT;
SET @J=1;--Iterator variable
SELECT @totalRecords = COUNT(Point) FROM @points--taking count of all the addresspoints coming from Mobile device.
print @totalRecords
print @totalRouteCount
SELECT @totalRouteCount=Count(*) FROM @route --taking count of all the active Jobs/Routes of Jobs assigned to carrier
print @totalRouteCount
DECLARE @addressPoint geography;
DECLARE @speed bigint;
DECLARE @direction nvarchar(100);
DECLARE @capturedFrom nvarchar(100);
DECLARE @accuracy decimal;
DECLARE @isDisconnectedPoint bit
SET @I=1;
WHILE (@I <= @totalRecords)
BEGIN
Select @addressPoint=GEOGRAPHY::STGeomFromText (points.Point,4326),@speed=points.Speed,@direction=points.Direction,
@capturedFrom=points.CapturedFrom,@accuracy=points.Accuracy ,
@isDisconnectedPoint= points.IsDisconnect,@accuracy=points.Accuracy from @points points where points.Id=@I 
Insert into Tracking(CarrierId,Point,Speed,Direction,CapturedFrom,CapturedAt,Accuracy,IsDisconnect,CreatedOn,IsActive) --inserting mobile addresspoint to Tracking table.
Values(@deliveryBoyId,
@addressPoint,
(@speed*2.237)
,@direction,
@capturedFrom   ,
CONVERT(VARCHAR(23), (Select points.CapturedAt from @points points where points.Id=@I), 121),
@accuracy,
@isDisconnectedPoint
,GETDATE()
,1
)
SELECT TOP 1 @trackingId=Id from Tracking order by Id desc
if(@totalRouteCount>0)
BEGIN
SET @j=1;
Insert into Delivery(AddressPointId,TrackingId,RouteId,JobId,CreatedOn,IsActive)
Select (address.Id),@trackingId,rJob.RouteId,rJob.JobId,GetDate(),1
FROM ExistingAddressPointTable address JOIN @route rJob ON rJob.RouteId=address.RouteJobID 
where address.point.STDistance(@addressPoint)<200 
AND address.IsDelivered=0--Non spatial table but still this is 2nd highest CPU usage query.Frankly speaking,I dont know why.

update addres
set addres.IsDelivered =1
from ExistingAddressPointTable addres inner join @routeJob rout on addres.RouteId = rout.RouteId
where addres.point.STDistance(@addressPoint)<200 --Table have 15 millions record now.In azure server this query takes huge CPU spike
AND addres.IsDelivered=0
END
SET @I=@I+1;
END
IF(@isDisConnected=1)--Updating delivery boy status to deactivate mode to his last  tracking record.
BEGIN
Select TOP 1 @trackingId=Id from Tracking where DeliveryBoyId=@deliveryBoyId AND IsActive=1 Order by Id desc
Update Tracking set IsDisconnect=1 where Id=@trackingId
END
END

标签： sql-serversqlperformancesqlgeography

Answer 1

尝试替换这个：

        Insert into Delivery(AddressPointId,TrackingId,RouteId,JobId,CreatedOn,IsActive)
        Select (address.Id),@trackingId,rJob.RouteId,rJob.JobId,GetDate(),1
        FROM ExistingAddressPointTable address JOIN @route rJob ON rJob.RouteId=address.RouteJobID 
        where address.point.STDistance(@addressPoint)<200 
        AND address.IsDelivered=0--Non spatial table but still this is 2nd highest CPU usage query.Frankly speaking,I dont know why.

        update addres
        set addres.IsDelivered =1
        from ExistingAddressPointTable addres inner join @routeJob rout on addres.RouteId = rout.RouteId
        where addres.point.STDistance(@addressPoint)<200 --Table have 15 millions record now.In azure server this query takes huge CPU spike
        AND addres.IsDelivered=0

使用此代码：

        Select address.Id,@trackingId as TrackingId,rJob.RouteId,rJob.JobId,GetDate() as CreatedOn,1 as IsActive, address.point
        into #temp
        FROM ExistingAddressPointTable address JOIN @route rJob ON rJob.RouteId=address.RouteJobID 
        AND address.IsDelivered=0

        delete from #temp
        where point.STDistance(@addressPoint)>=200 

        Insert into Delivery(AddressPointId,TrackingId,RouteId,JobId,CreatedOn,IsActive)
        Select Id, TrackingId, RouteId, JobId, CreatedOn, IsActive
        from #temp as Temp

        update addres
        set addres.IsDelivered =1
        from ExistingAddressPointTable addres
        where Id in (Select Id from #temp)

如果您的 CPU 使用率下降，问题是服务器计算了每一行的距离，无论 isDelivered 是 0 还是 1。此外 address.IsDelivered 应该有一个索引。

通常，如果您遇到性能问题，请尝试通过注释掉部分代码或记录命令之间的时间来将其缩小到确切的语句。