将C＃类型Int64的参数传递给T-SQL bigint存储过程参数时，性能下降 [英] performance hit when passing argument of C# type Int64 into T-SQL bigint stored procedure parameter

问题描述

当我开始将数据类型 [bigint] 用于存储过程参数时，我注意到应用程序存在严重的性能问题。下面的快速代码的参数数据类型为 [nvarchar]（50）。下面是我更改的一些代码，这个简单的调用来自< 1秒（快速代码）到20秒以上（慢速代码）。是什么导致此问题？如何使用 [bigint] 保持性能？我在.NET 4.0中使用Enterprise Library 5（数据库应用程序块）。

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

（快速）之前：

            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);

之后（慢）：

            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);

推荐答案

您必须检查db中的类型，确保参数的类型与您要查询的列相同（我想这是varchar，而不是bigint）。如果它们不同，则比较将进行转换，并且sql server将不使用索引（无法优化）。

You have to check the type in the db, make sure the type of the parameter is the same as the column you are quering (I guess it is a varchar, not a bigint). If they are different, the compaison will do a convertion, and sql server will not use the indexes (can not optimize).

这篇关于将C＃类型Int64的参数传递给T-SQL bigint存储过程参数时，性能下降的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！