简单的 SQL 查询超时，具体取决于输入参数

问：

我有一个非常奇怪的问题，根据查询参数，从以下代码调用数据库将失败，并显示：

Exception.Message = Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

代码为：

        public IList<ProductCommissionMinimal> FindProductCommissionMinimal(string premiumTypeCode, string sellerId, string eposProductId, string singlePremiumPolicy)
        {
            var sql = @"
SELECT distinct
        CSP.ProductSubtypeId, PC.ProductFamilyId, PC.ProductId
    FROM
        ProductCommission PC,
        CommissionTemplate CT,
        Seller S,
        Product P,
        CoreSystemProduct CSP
    WHERE
        PC.ProductFamilyId = CSP.ProductFamilyId AND PC.ProductId = CSP.ProductId
        AND P.ProductFamilyId = CSP.ProductFamilyId AND P.ProductId = CSP.ProductId
        AND CT.PremiumTypeCd = @premiumTypeId
        AND S.SellerId = @sellerId
        AND CT.CommissionTemplateSeq = PC.CommissionTemplateSeq
        AND CT.StatusCd = 'O'
        AND PC.DistributionId = S.DistributionId
        AND CT.AvailabilityCd <> 'I'
        AND CSP.CoreProductId = @eposProductId";

            var parameters = new Dictionary<string, object>
            {
                { "@premiumTypeId", premiumTypeCode },
                { "@sellerId", sellerId },
                { "@eposProductId", eposProductId },
            };

            if (premiumTypeCode == "1")
            {
                sql = sql + " AND P.IsSinglePremiumOnlyCd = @singlePremiumPolicy";
                parameters["@singlePremiumPolicy"] = singlePremiumPolicy;
            }

            return Query<ProductCommissionMinimal>(sql, parameters);
        }

Query 方法执行一个泛型方法，该方法最终执行 在我们的代码库中，在数百个不同的内联查询中使用了相同的泛型方法，没有问题，所以我认为那里没有任何问题。此通用方法还使用默认命令超时，即未指定 none，因此为 30 秒。但是，此 SQL 查询几乎应立即返回，因此我不认为这只是一个长时间运行的查询。System.Data.Common.DbCommand.ExecuteReader()

我看到的问题是，对于某些参数组合，这工作正常，但对于其他参数组合则存在错误。例如，它似乎因以下原因而出错： premiumTypeCode=5&sellerId=NQ49&eposProductId=UPR3SAA&singlePremiumPolicy=Y 但不是 premiumTypeCode=5&sellerId=NQ49&eposProductId=ABC3SAA&singlePremiumPolicy=Y

这个特定方法的一个独特之处在于，它是我们代码库中唯一使用该列的方法。似乎如果我将该参数值更改几个字母，它就可以正常工作。CoreProductId

我对此失去了理智。任何见解都会有很大帮助。后端数据库为 SQL Server 2016。

编辑我已将查询更新为：

    public IList<ProductCommissionMinimal> FindProductCommissionMinimal(string premiumTypeCode, string sellerId, string eposProductId, string singlePremiumPolicy)
    {
        var sql = @"
SELECT distinct CSP.ProductSubtypeId, PC.ProductFamilyId, PC.ProductId
                    FROM
                        ProductCommission PC
                    INNER JOIN
                           CommissionTemplate CT 
                    ON 
                           CT.CommissionTemplateSeq = PC.CommissionTemplateSeq
                    INNER JOIN
                        Seller S 
                    ON 
                           PC.DistributionId = s.DistributionId
                    INNER JOIN
                        CoreSystemProduct CSP 
                    ON  
                           PC.ProductFamilyId = CSP.ProductFamilyId AND PC.ProductId = CSP.ProductId
                    INNER JOIN
                        Product P 
                    ON 
                           P.ProductId = CSP.ProductId AND P.ProductFamilyId = CSP.ProductFamilyId  
                    WHERE
                        CT.PremiumTypeCd = @premiumTypeId
                        AND S.SellerId = @sellerId       
                        AND CT.StatusCd = 'O'
                        AND PC.DistributionId = S.DistributionId
                        AND CT.AvailabilityCd <> 'I'
                        AND CSP.CoreProductId = @eposProductId";

        var parameters = new Dictionary<string, object>
        {
            { "@premiumTypeId", premiumTypeCode },
            { "@sellerId", sellerId },
            { "@eposProductId", eposProductId },
        };

        if (premiumTypeCode == "1")
        {
            sql = sql + " AND P.IsSinglePremiumOnlyCd = @singlePremiumPolicy";
            parameters["@singlePremiumPolicy"] = singlePremiumPolicy;
        }

        return Query<ProductCommissionMinimal>(sql, parameters);
    }

按照建议。但是，对于某些参数组合，这仍然失败，例如 premiumTypeCode=1&sellerId=TC99&eposProductId=UPO2SAA&singlePremiumPolicy=N