SQL Server 2019. Here's a link to a gist with the xml plan.
Hello, I am having a heck of a time understanding why this query is taking 0.02 seconds to execute when it can't find a record with one of the included statuses. When it finds a record that has one of the included statuses it tends to be much faster. I am guessing that is because the query stops once it finds 1 row that matches.
SELECT TOP 1 IDNum, FORMAT(Date, 'M/d/yy') AS theDate, Status, Rate FROM theDB INNER JOIN DomainTable ON theDB.IDNum = DomainTable.IDNum WHERE DomainIP = '127.0.0.1' AND status IN ( 'Active', 'To ReActivate', 'To Deactivate', 'Deactivate ASAP', 'SUSPENDED', 'SUSPENDED X', 'SUSPENDED Y', 'SUSPENDED Z' ) ORDER BY theDB.IDNum DESC
(DomainIP belongs to DomainTable the others belong to theDB)
In the execution plan the largest cost is the TOP N SORT at 33% There is a clustered Index seek at 29% There is a key lookup on DomainTable that is using 29% The Index seek for the IP on DomainTable is 9%
My questions are:
is there any way to get that TOP N to not be so heavy?
0.02 seconds isn't that slow but also this query is pretty light. So I would like to optimize it as much as possible.
There are only four entries in DomainTable where the IP is 127.0.0.1 so it is essentially taking 0.2 sec to decide which one of those to return and it turns out the answer is none of them because the status isn't right on any of them. Is there not a way to just create an index that keeps all of this information in RAM or something?