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.