I have a windows service developed in .net / c#.
It frequently updates a table with location data stored in SQL server. The update SQL query is etremely slow, somwehere between 0.5-1.5 sec. In this table there are not more than 1500 records.
Would you be so kind to help me to find what I miss? There should be sg with this update, I have others that work perfectly and fast.
Thanks a lot!
Table:
CREATE TABLE [LastPosition] (
[lastposition_id] INTEGER IDENTITY NOT NULL,
[unit_id] INTEGER NOT NULL,
[date_time] DATETIME NOT NULL,
[valid] TINYINT NOT NULL,
[lat] REAL NOT NULL,
[lon] REAL NOT NULL,
[angle] SMALLINT NOT NULL,
[speed] SMALLINT NOT NULL,
[digit] TINYINT NOT NULL,
[altitude] SMALLINT NULL,
[sat] TINYINT NULL,
[extrainfo] NVARCHAR(255) NOT NULL,
[geoinfo] TINYINT NULL,
CONSTRAINT LastPositionPrimaryKey PRIMARY KEY NONCLUSTERED(lastposition_id));
Query:
exec sp_executesql N'UPDATE [LastPosition]
SET [LastPosition].[Unit_id]=@up_Unit_id,
[LastPosition].[Date_Time]=@up_Date_Time,
[LastPosition].[Valid]=@up_Valid,
[LastPosition].[Lat]=@up_Lat,
[LastPosition].[Lon]=@up_Lon,
[LastPosition].[Angle]=@up_Angle,
[LastPosition].[Speed]=@up_Speed,
[LastPosition].[Digit]=@up_Digit,
[LastPosition].[Altitude]=@up_Altitude,
[LastPosition].[Sat]=@up_Sat,
[LastPosition].[ExtraInfo]=@up_ExtraInfo,
[LastPosition].[GeoInfo]=@up_GeoInfo
WHERE [LastPosition].[LastPosition_id] = @0',N'@up_Unit_id int,@up_Date_Time datetime,@up_Valid int,@up_Lat float,@up_Lon float,@up_Angle int,@up_Speed int,@up_Digit int,@up_Altitude int,@up_Sat int,@up_ExtraInfo nvarchar(13),@up_GeoInfo int,@0 int',@up_Unit_id=1164,@up_Date_Time='2022-09-19 10:21:33.457',@up_Valid=1,@up_Lat=49,612609999999997,@up_Lon=15,922759999999999,@up_Angle=127,@up_Speed=0,@up_Digit=255,@up_Altitude=138,@up_Sat=19,@up_ExtraInfo=N'Kkt löueléket',@up_GeoInfo=27,@0=1111
UPDATE: There are no any views on this table. There are no any triggers. I don't have connection issues.
Query plan:
