MDBitz Development Resources

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.

Recently at work I have been working with Database Tables that have been denormalized to “enhance” their performance. As part of the denormilization process table columns where converted from int to the varchar data type. This change lead me to wonder if there is a performance gain from using varchar over int.

After doing some research on this subject it turns out that int columns provide both performance and data integrity.

1. varchar takes longer to compare
   Due to the varchar type allowing the storage of character data a varchar type takes longer to compare then an int value that uses only numeric values
2. varchar removes type check
   Perhaps the greatest reason for not changing the type to varchar is that if you are expecting the column’s value to be an integer then the int column will do a type check that the value inserted is an integer. However by making the column a varchar this check is removed and your column is open for invalid character data that could cause failure to any sql scripts that do conversions.

To wrap things up, lets keep it simple if you need to save a value in a table that is an integer then use the int data type.