I have a rather large table, ~ 3,5b rows and growing. For each row I have a specific ID which I wish to retrieve faster than currently. The current run-time is 5 minutes the first time I exectue the query, but instant for other queries subsequently. The table is approximately 603.628,430MB and the Index space takes up 406.398,570MB.
A sample row is:
documentID pages sort_id word_bbox page_bbox asfdfdddee23333rtfds 1 1 2030 12 2123 55 0 0 2479 3508 aavfcbu4lobfhlyguicl 1 2 2144 12 2157 45 0 0 2479 3508
The query I wish to execute is:
SELECT p.documentID , p.pages , convert(integer, REPLACE(p.word_id, 'word_1_', '')) as sort_id , p.word , p.word_bbox , p.page_bbox FROM [MY].[DB].[DOCUMENTS] p with (NOLOCK) where p.documentID = 'asfdfdddee23333rtfds'
I suspect that the rather long execution time ~5 minutes is the read operations from the server?
I have created a clustered index on the documentID and a Non-clustered Index as well on the documentID as well (just as a test, I have no reason for this to work).
The execution plan is shown here:
Execution plan XML: https://www.brentozar.com/pastetheplan/?id=SJezWACCu