SELECT *,
     CASE WHEN start_of_value = 1 THEN ''
     ELSE SUBSTRING(CI_NUM, start_of_value, 5) END AS [5charsyouwant]
FROM ( VALUES('asdfasf-ABCDE-asdfasdfsfasf'),('ss-TREWQ-asdfasfsf'),
     ('asdfa-12-ABCDE-kindatricky'),('nothingtoseehere'),
 ...

So I guess it's doing a non-clustered index seek?  When you go that route, you have to be careful of the "tipping point", where SQL falls back to scanning the...

100% agree with Phil.  Get everything in one read and save/cache it for your app however you need to.  Db read round trips (from your app to the db server...

Btw, I'd stronbly encourage you to use:
and SQLDateTime < '2018-07-03T08:00:00 AM' 
rather than:
and SQLDateTime <= '2018-07-03T07:59:00 AM'

As both of us have stated, run a CHECKDB to get an idea of the scope of the problem.  One error or 100 errors?

I forgot to mention before...


select *
from items
where isnull(@items, '') = '' OR
    exists(select 1 from DBO.Splitstrings_moden(@items, ',') where Item = item_no)