Real天下::码农频道

案发现场

我们先到事故现场视察一下，看看会找到什么线索。这是半个月前新上线WMS网站系统，最近两天频频发生sql查询超时的报错。先看看项目的基本面信息：

Web应用：ASP.NET项目网站，使用PetaPoco ORM
数据库：大微软的SQL Server2012
硬件：阿里云数据库RDS，4核心16G
查询数据：查询订单明细数据，数据总量小于10W行；查询视图 UNOIN出库入库订单数据（ InStock/OutStock），并且JOIN一下几个表：Customer、Supplier、Product、Saler、OrderDetail
系统界面查询出现30秒查询超时的情况，但，SQL查询分析器里面查询视图却是非常正常200ms？

OMG！堂堂的SQL Server + 阿里云数据库，的表现竟然不如Sqlite？！这完全不科学，需要好好分析一下。

索引的问题？

根据过往的经验，就算没有索引裸跑，SQL Server 也不至于那么不“耐操”吧。算了，不就加索引么？早晚是要弄的。

（30 minutes later...）

orderNo、productNo、customerNo、salerNo、orderTime的索引全部搞定！

but，超时，依旧还是超时。:（

ORM的问题？

到底ORM把我的SQL脚本弄成啥样了？立马打开Profiler，截获超时的脚本。嗯，有发现！

exec sp_executesql N'SELECT ROW_NUMBER() OVER (ORDER BY orderDate) peta_rn, * FROM VW_Order_Detail
WHERE (1=1)
AND createDate> @0
AND createDate< @1 ORDER BY orderDate 
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY',N'@0 nvarchar(4000),@1 nvarchar(4000)',@0=N'20210718',@1=N'20210808'

跟我在查询分析器上测试的SQL脚本，多了一个sp_executesql的存储过程。

SELECT ROW_NUMBER() OVER (ORDER BY orderDate) peta_rn, * FROM VW_Order_Detail
WHERE (1=1) 
AND createDate> '20210704'
AND createDate< '20210808' ORDER BY orderDate 
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

在查询分析起跑了一下，带sp_executesql的脚本，果然妥妥的超时。看来，真相，很快就要浮出水面了！

真相大白

用“sp_executesql over time”的关键词搜索了一下，果然stackoverflow上找到了类似的案例。

原来问题出在ORM把传入string 的SqlDbType弄成nvarchar了。然而项目数据库字段类型清一色全都是varchar，sp_executesql执行查询前还要做类型转换，消耗了大量的CPU时间。好比开车挂着手踩油门，车子轰隆隆地响就是跑不快。

解决问题

能够重现问题，并且知道引起的原因。接下来就好办了。不是改数据库字段类型，就是直接对ORM代码开刀！我选择了代价最小的后者。

PetaPoco

然而，PetaPoco却说：“这不是我的锅！SqlDbType的类型映射是System.Data.SqlClient做的！我只是个传话的。”

确实，我在某度查了一下，C#里面string类型的参数，都被映射成nvarchar了，后面我会补充一下mapping的表格。然后，调试了一下PetaPoco的代码，发现还是可以在里面找到解决的办法。以下这是我的解决办法：

找到AddParam的方法
对string类型的参数动一下手脚,让它强行映射成varchar

else if (t == typeof(string))
{
    if (p.GetType().Name == \"SqlParameter\") // parameter SqlDbType: nvarchar => varchar
        p.GetType().GetProperty(\"SqlDbType\").SetValue(p, SqlDbType.VarChar, null);

    if ((value as string).Length + 1 > 4000 && p.GetType().Name == \"SqlCeParameter\")
        p.GetType().GetProperty(\"SqlDbType\").SetValue(p, SqlDbType.NText, null);

    p.Size = Math.Max((value as string).Length + 1, 4000); // Help query plan caching by using common size
    p.Value = value;
}

简单粗暴，但确实解决了问题，围观的大牛们不喜勿喷。到时候用你们自己的办法去解决就好了～

SqlSugar

噢，差点忘了！还有个MQ队列的消费者应用。用的不是PetaPoco而是SqlSugar。

查SqlSugar的文档无果。直接从源码入手，找到了一个配置参数 DisableNvarchar = true。（看来SqlSugar的使用者，曾经有人踩过此坑）

new ConnectionConfig()
{
    ConnectionString = conn,
    DbType = dbType,
    MoreSettings = new ConnMoreSettings { DisableNvarchar = true }
};

加一行代码，搞定～～爽！

总结

原来这么多年来，我用的SQL Server竟然是一个被ORM封印了的版本！不起眼的查询超时，竟然挖出这么个巨坑，而且潜伏多来。这算是今年以来一个非常大的收获了。

附录：SqlDbType与DbType的映射关系

SqlDbType => DbType

SqlDbType | DbType ---|--- SqlDbType.BigInt|DbType.Int64 SqlDbType.Binary|DbType.Binary SqlDbType.Bit| DbType.Boolean SqlDbType.Char| DbType.AnsiStringFixedLength SqlDbType.DateTime|DbType.DateTime SqlDbType.Decimal|DbType.Decimal SqlDbType.Float| DbType.Double SqlDbType.Image| DbType.Binary SqlDbType.Int| DbType.Int32 SqlDbType.Money| DbType.Currency SqlDbType.NChar| DbType.StringFixedLength SqlDbType.NText| DbType.String SqlDbType.NVarChar|DbType.String SqlDbType.Real| DbType.Single SqlDbType.UniqueIdentifier |DbType.Guid SqlDbType.SmallDateTime |DbType.DateTime SqlDbType.SmallInt|DbType.Int16 SqlDbType.SmallMoney|DbType.Currency SqlDbType.Text| DbType.AnsiString SqlDbType.Timestamp|DbType.Binary SqlDbType.TinyInt|DbType.Byte SqlDbType.VarBinary|DbType.Binary SqlDbType.VarChar|DbType.AnsiString SqlDbType.Variant|DbType.Object SqlDbType.Xml| DbType.Xml SqlDbType.Udt| DbType.Object SqlDbType.Structured|DbType.Object SqlDbType.Date| DbType.Date SqlDbType.Time| DbType.Time SqlDbType.DateTime2|DbType.DateTime2 SqlDbType.DateTimeOffset|DbType.DateTimeOffset

DbType => SqlDbType

DbType | SqlDbType ---|--- DbType.AnsiString|SqlDbType.VarChar DbType.Binary| SqlDbType.VarBinary DbType.Byte| SqlDbType.TinyInt DbType.Boolean| SqlDbType.Bit DbType.Currency| SqlDbType.Money DbType.Date| SqlDbType.DateTime DbType.DateTime| SqlDbType.DateTime DbType.Decimal| SqlDbType.Decimal DbType.Double | SqlDbType.Float DbType.Guid| SqlDbType.UniqueIdentifier DbType.Int16 | SqlDbType.SmallInt DbType.Int32| SqlDbType.Int DbType.Int64| SqlDbType.BigInt DbType.Object| SqlDbType.Variant DbType.SByte| (抛异常) DbType.Single| SqlDbType.Real DbType.String| SqlDbType.NVarChar DbType.Time| SqlDbType.DateTime DbType.UInt16| (抛异常) DbType.UInt32| (抛异常) DbType.UInt64| (抛异常) DbType.VarNumeric|(抛异常) DbType.AnsiStringFixedLength|SqlDbType.Char DbType.StringFixedLength|SqlDbType.NChar DbType.Xml| SqlDbType.Xml DbType.DateTime2|SqlDbType.DateTime2 DbType.DateTimeOffset|SqlDbType.DateTimeOffset

SQL Server查询超时，是ORM的锅吗？