I have a table with about 250,000 records where one of the columns is of type datetime. If I execute a query like:
select lcdregnumber, lcddateissued from table1
where lcddateissued >= '20140401' and lcddateissued <= '20140831'
and (lcdRegNumber not in (select lcdRegNumber from tblLicenseDetails where lcdExpDate >= dateadd(day, 1, '20140831'))
The query execute successfully and takes less than 5 seconds to display the results. however, if I use the value for the date as a variable the query takes very long to execute, for example up to 6 minutes (I cant work with such a time if I am to run the query from an application).
For example the query i am executing is:
declare @exp datetime
select @exp = '20140430'
select l.lcdregnumber, lcdNumber, l.lcdExpDate
from tblLicenseDetails
where ExpDate <= @exp
and (lcdRegNumber not in (select lcdRegNumber from tblLicenseDetails where lcdExpDate >= dateadd(day, 1, @exp))
A record for lcdRegNumber can appear more than once in the table but with different values for lcdExpdate.
How best can I speed up my query so that if I am to put this code in a stored procedure and execute it from an application, I do not get the 'Timeout expired' error?