Try creating the following indexes (I'm using what I think is something similar to your naming convention for index names):
CREATE INDEX DomainTable$DomainIP$IDNum
ON dbo.DomainTable (DomainIP, IDNum DESC);
CREATE INDEX theDB$idx001
ON dbo.theDB (IDNum DESC)
INCLUDE (theDate, [Status], Rate);
The first index allows SQL Server to do a seek on DomainIP, which will be very quick, and it includes the IDNum column, in descending order, which will eliminate the key lookup operation from your query plan, and eliminate the required descending order sort.
The second index might also allow SQL Server to eliminate the sort - see if it helps or hinders the query execution. If you add a minimal, complete, and verifiable example to your question, I'd be able to test that for you.
Anyway, consider the following modifications to your query:
IF OBJECT_ID(N'tempdb..#statii', N'U') IS NOT NULL
DROP TABLE #statii;
CREATE TABLE #statii
[status] varchar(20) NOT NULL
PRIMARY KEY CLUSTERED
INSERT INTO #statii ([status])
, ('To ReActivate')
, ('To Deactivate')
, ('Deactivate ASAP')
, ('SUSPENDED X')
, ('SUSPENDED Y')
, ('SUSPENDED Z');
SELECT TOP 1 theDB.IDNum
, CONVERT(varchar(30), theDB.theDate, 22)
INNER JOIN DomainTable ON theDB.IDNum = DomainTable.IDNum
INNER JOIN #statii s ON theDB.[status] = s.[status]
WHERE DomainTable.DomainIP = '127.0.0.1'
ORDER BY theDB.IDNum DESC;
A couple of things to note:
instead of using the
WHERE ... IN (...) clause, I'm inserting those values into a temporary table and joining on them. You may not notice a huge increase in performance from this methodology, but as the list of items in the
IN (...) clause gets bigger, the performance difference becomes more and more noticeable. You may want to revert that to the
IN (...) clause if it turns out to hinder performance. I think it's worth testing.
FORMAT - it's slow. Use
CAST where possible.
Use table aliases, as I've done for the
Capitalize keywords, keep data types lower case.
Never 1 use keywords as column names. I'm looking at you,
status. That column should be named
Blah is the name of the table, or the kind of status, or something.
1 - ok, maybe never is a bit harsh. Almost never.