SQL Server Dapper parameter not working [closed]-CodePudding

When using Dapper SQL Server, the parameter settings not work.

FooNo、yyyyMMdd、Point are Char(8), Char(8), Char(4).

not work

    var parameters1 = new
    {
        FooNo = "00007829",
        yyyyMMdd = "20210812",
        Point = "001A"
    };
    var result = this.OpenedConn.Query(sql1, parameters1).ToList();

work

    sql1 = sql1.Replace("@FooNo", $@"'00007829'");
    sql1 = sql1.Replace("@yyyyMMdd", $@"'20210812'");
    sql1 = sql1.Replace("@Point", $@"'001A'");
    var result = this.OpenedConn.Query(sql1).ToList();

SQL

SELECT            COUNT([番号]) AS 登録数
FROM              [dbo].[Foo]
WHERE             ([施工先番号] = @FooNo) AND ([回収日] = @yyyyMMdd) AND ([ポイント] = @Point)

What is the correct way to assign parameters with dapper?

CodePudding user response：

You are already using parameters correctly, in the first version that doesn't work. You mention that the error is a timeout. This, combined with the fact that the inline literals version works, makes me conclude that the problem here is parameter sniffing and query plan reuse.

Context: when a query plan is generated for a query, it gives a lot of weight to the parameters being used that time - in terms of understanding the data distributions. This is usually correct, but it has problems if the data is heavily skewed. Internally at Stack Overflow, we actually call this (lovingly) the "Jon Skeet" problem - by which I mean: users like Jon Skeet (and, to avoid false modesty, myself) have very different amounts of data on our profiles to most users. This can lead to problems:

if a query plan gets generated for a high volume user, the generated query may not work well for most "normal" users
if a query plan gets generated for a "normal" user, the generated query may not work well for high volume users

This manifests in slower-than-expected queries (often dramatically slower), with the observation that they work fine when executed directly: for the reason that we're forcing a different query plan!

The good new is, the solution is usually simple: "optimize for unknown"

By which I mean: add OPTION(OPTIMIZE FOR UNKNOWN) after your existing query. This helps reduce the bias that atypical data can have in terms of skewing the query plan towards atypical data.

SELECT            COUNT([番号]) AS 登録数
FROM              [dbo].[Foo]
WHERE             ([施工先番号] = @FooNo) AND ([回収日] = @yyyyMMdd) AND ([ポイント] = @Point)
OPTION (OPTIMIZE FOR UNKNOWN)