Today I was running some tests on my T-SQL Procedures and noticed that for some reason my Select statement was performing in seconds compared to my Procedure which would take minutes to complete. After a few hours I stumbled upon my mistake I had used the date datatype in the procedure parameters when my database table was of type datetime, by switching the variable to a varchar(30) datatype the procedures performance had increased and functioned with the same IO count and time as the select statement.

Incorrect Procedure Statement

CREATE PROCEDURE P_RULE_MULTI_LOCATION
@startDate date,
@endDate date
AS
BEGIN

SELECT * FROM ACTIVITY_LOG WHERE ACTION_TS >= @startDate AND ACTION_TS < @endDate

END

Correct Procedure Statement

CREATE PROCEDURE P_RULE_MULTI_LOCATION
@startDate varchar(30),
@endDate varchar(30)
AS
BEGIN

SELECT * FROM ACTIVITY_LOG WHERE ACTION_TS >= @startDate AND ACTION_TS < @endDate

END

The simple above change enhances the queries performance where it can use both an index and its position when performing the select statement. While the date to datetime comparison does not allow for the index position to be used due to the conversion. It may not make the most sense but if you find your procedure functioning slower than your direct sql query you may want to check your datatypes to make sure that they are the same.