CPU usage goes to high when only 20 simultaneous users insert tracking data into geographical table

Question

We have live tracking application in which we are inserting tracking point to one table called Tracking. We have another table called ExistingAddressPointTable where we have delivery address. We are considering particular address as delivered when delivery boy is in 200 meter radius of that address and inserting delivery entry into non spatial table called as Delivery (many to many relationship) table.

We are making an API call after 20-30 seconds of each minute hence device always send 10-15 addresspoint in that period. I implement the solution for this in stored procedure but the problem is even for simultaneous 20 delivery boys session Azure SQL Server CPU usage reaches 100%. I want to support this system for 1000 simultaneous sessions.

We are using Azure SQL Server database in elastic pool having 50 DTUs limit in UAT environment. Our client already have 5 other db of 250 GB each in the same elastic pool. For production also we have 100 DTUs limit.

Any solution to achieve Delivery boy is tracking through different mobile devices?

Technology used: .net core, Web API and Azure SQL Server 2017, spatial indexing on spatial table (Tracking & ExistingAddressPointTable) is already applied. Any help is much much appreciated.

PSB code for reference

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

Why are you using a `WHILE` here? SQL Server excels are set based methods, not iterative ones. Is there a good reason why you getting your server to process things one (agonising) row at a time? — Thom A, May 12 '19 at 18:45
@Larnu I am getting bunch of addresspoint from mobile device. I am iterating those — Dnyaneshwar Shivbhakta, May 12 '19 at 18:46
What do the DMVs and Query Store say about the CPU utilization of this procedure and the queries in it? — David Browne - Microsoft, May 12 '19 at 18:58
@DavidBrowne-Microsoft How to check that.Your guidance would be helpful for me.I checked azure query performance insight,in that I saw Update Existing address point query & inserting delivery query is taking hig dtu usage — Dnyaneshwar Shivbhakta, May 12 '19 at 19:10
But why iterate in the first place? Why not do everything in one statement? — Thom A, May 12 '19 at 19:34
@Larnu can you please guide me how to do that,in this scenario.may be my query is not that much optimized. your help on this would be much appericiable — Dnyaneshwar Shivbhakta, May 12 '19 at 19:40
It’s not obvious that the looping is the main cause of the CPU utilization. It could, for instance, be an indexing problem, which would be just as much of an issue after rewriting as a set-based process. — David Browne - Microsoft, May 12 '19 at 20:05
@DavidBrowne-Microsoft I used Spatial indexing and non-clustered indexing as well on columns which are used in the where condition — Dnyaneshwar Shivbhakta, May 12 '19 at 20:14
Some **indenting and formatting** to make the code more readable wouldn't hurt ! ..... — marc_s, May 12 '19 at 20:54

Leshie · Answer 1 · 2019-05-13T00:02:13.097

Try replacing this:

        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

with this Code:

        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)

If your CPU-usage goes down, the problem was that the server calculated the distance for each row, no matter if isDelivered was 0 or 1. Also address.IsDelivered should have an index.

In general if u have performance problems try to narrow it down to the exact statement by commenting out parts of the code or by logging time between commands.

ExistingAddressPointTable have almost 15 millions spatial record and in future it may have trillions of data.So dont you think fetching almost 1-5 millions record & inserting this record into temp tablewill be performance headache. — Dnyaneshwar Shivbhakta, May 13 '19 at 01:19

CPU usage goes to high when only 20 simultaneous users insert tracking data into geographical table

1 Answers1