Application uses Entity Framework to create/run query.
SELECT [t].[sbt_assetid], [t].[sbt_messagetimestampmst], [t].[sbt_transid], [t].[CurrentRow], [t].[etl_timestampUTC]
, [t].[sbt_battery], [t].[sbt_cargostate], [t].[sbt_city], [t].[sbt_country], [t].[sbt_extpwr], [t].[sbt_geoname]
, [t].[sbt_geotypename], [t].[sbt_heading], [t].[sbt_idleduration], [t].[sbt_idlegap], [t].[sbt_idlestatus], [t].[sbt_latitude]
, [t].[sbt_longitude], [t].[sbt_messagetype], [t].[sbt_movementstate], [t].[sbt_mtsn], [t].[sbt_quality], [t].[sbt_skyfencestatus]
, [t].[sbt_speed], [t].[sbt_state], [t].[sbt_tetherstate], [t].[sbt_tirestate]
FROM [sb].[TrailerLocations] AS [t]
WHERE ((((((((((((([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_0) AND ([t].[CurrentRow] = 1)))
OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_1) AND ([t].[CurrentRow] = 1)))
OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_2) AND ([t].[CurrentRow] = 1)))
OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_3) AND ([t].[CurrentRow] = 1)))
OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_4) AND ([t].[CurrentRow] = 1)))
OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_5) AND ([t].[CurrentRow] = 1)))
OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_6) AND ([t].[CurrentRow] = 1)))
OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_7) AND ([t].[CurrentRow] = 1)))
OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_8) AND ([t].[CurrentRow] = 1)))
OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_9) AND ([t].[CurrentRow] = 1)))
OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_10) AND ([t].[CurrentRow] = 1)))
OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_11) AND ([t].[CurrentRow] = 1)))
OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_12) AND ([t].[CurrentRow] = 1))
Typically runs < 3 seconds but several times a day exceeds 30 secs whereupon app times out (dev has said need sub 30 sec response times). Table has ~60 million rows. There are 7126 different "assets" that can be passed in as one of 12 parameters .
PK Index on [sbt_assetid] ASC, [sbt_messagetimestampmst] ASC, [sbt_transid], ASC (maybe change sbt_messagetimestampmst to DESC?)
NC Index on [sbt_assetid] ASC, [CurrentRow] DESC
Stats updated automatically (last update yesterday).
https://www.brentozar.com/pastetheplan/?id=SyjnVJcHA
Asked by Sergio Pacheco
(13 rep)
Jun 14, 2024, 05:07 PM
Last activity: Jun 20, 2024, 01:46 PM
Last activity: Jun 20, 2024, 01:46 PM