Performance falls when passing an argument of type C # Int64 to a stored procedure parameter of a large T-SQL value

Question

I notice serious performance issues with my application when I started using the [bigint] data type for my stored procedure parameters. The parameter data type for the quick code is below [nvarchar](50) . Below is the code I changed and this simple call went from <1 second (fast code) to over 20 seconds (slow code). What could be causing this problem? How can I use [bigint] but maintain performance? I am using Enterprise Library 5 (Database Application Block) with .NET 4.0.

To (fast):

  Database db = DatabaseFactory.CreateDatabase("APP"); DbCommand cmd = db.GetStoredProcCommand("sp_test"); db.AddInParameter(cmd, "@crud_command", DbType.String, "read"); db.AddInParameter(cmd, "@network_login", DbType.String, "abc231"); db.AddInParameter(cmd, "@id_filter", DbType.String, id_filter); DataSet ds = db.ExecuteDataSet(cmd); 

After (slow):

  Database db = DatabaseFactory.CreateDatabase("APP"); DbCommand cmd = db.GetStoredProcCommand("sp_test"); db.AddInParameter(cmd, "@crud_command", DbType.String, "read"); db.AddInParameter(cmd, "@network_login", DbType.String, "abc231"); db.AddInParameter(cmd, "@id_filter", DbType.Int64, Convert.ToInt64(id_filter)); DataSet ds = db.ExecuteDataSet(cmd); 

Answer 1

You should check the type in db, make sure that the type of the parameter matches the type of the column you are requesting (I assume it is varchar, not bigint). If they differ from each other, the companion will do the conversion, and the sql server will not use indexes (cannot optimize).