c# - sql連線工具 - sql server連線




SQL查詢在從C#運行時超時,在SQL Server Management Studio中速度很快 (2)

三件事情要看,按照優先順序:

  1. 避免使用AddWithValue()函數 ,因為當ADO.Net猜測列類型錯誤時,可能會產生災難性的性能影響。 做你必須能夠為每個參數設置一個明確的數據庫類型
  2. 看看選項重建
  3. 研究未知的優化 。 只有在別人失敗之後才能做到這一點。

我有一個C#程序,執行SQL查詢,使用下面列出的代碼。 我一直在使用這個代碼一段時間,直到有一天沒有問題。

我傳遞一個查詢字符串到SQL,其中包括字符串,這是股票標識符的列表。 前幾天我跑了它,查詢超時,如果我放了它,將運行一個多小時。 我花了幾天試圖調試這個。 在我原來的查詢中,有大約900個標識符。

我試過改變一切我能想到的,我得到的結果我無法解釋。

例如:

  1. 該查詢與一個股票列表一起工作,但是與字符串數量和總長度不同的另一個列表長度相同

  2. 它與一個列表一起使用,但不以相反的順序使用相同的列表

  3. 有一個列表,如果只有900個標識符,但是不存在899或901,那麼它就起作用,而且我可以包含或排除不同的標識符,並得到相同的結果,所以對於其中一個標識符來說,這並不是什麼好東西。

在每種情況下,我捕獲了正在被我的程序傳遞的查詢字符串,並將其複製到SQL Server Management Studio中,並且在任何情況下查詢都在1秒鐘內運行。

我已經閱讀了一切,我可以在這個和其他論壇關於在SQL Server管理工作室查詢,但從程序運行超時,但這似乎是不同的,我可以找到它失敗的情況下,類似的情況下,工作。

我將不勝感激關於我在哪裡可能會看到可能發生的事情的建議。

using (SqlConnection conn = new SqlConnection(_connectString))
{
    conn.Open();

    using (SqlCommand cmd = new SqlCommand(queryString, conn))
    {
        cmd.Parameters.Clear();
        cmd.CommandTimeout = _timeout;

        SqlParameter param;

        if (parms != null)
        {
            foreach (string parm in parms.Keys)
            {
                param = cmd.Parameters.AddWithValue(parm, parms[parm]);
            }
        }

        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            QueryResult record = new QueryResult();
            record.Fields = new List<object>();

            for (int i = 0; i < returnColumns; ++i)
            {
                object value = reader.GetValue(i);

                if (value == DBNull.Value)
                    record.Fields.Add(null);
                else
                    record.Fields.Add(value);
            }

            result.Add(record);
        }

        reader.Close();
    }

    conn.Close();
}

這是我的查詢。 在這個版本中,我包含了65個股票,它不起作用(<= 64的工作)。

select
    distinct a.Cusip
,   d.Value_ / f.CumAdjFactor as split_adj_val

from qai.prc.PrcScChg a

join qai.dbo.SecMapX b
    on a.Code = b.venCode
    and b.VenType = 1
    and b.exchange = 1
    and b.Rank = (select Min(Rank) from qai.dbo.SecMapX where VenCode = a.Code and VenType = 1 and Exchange = 1)

join qai.dbo.SecMapX b2
    on b2.seccode = b.seccode
    and b2.ventype = 40
    and b2.exchange = 1
    and b2.Rank = (select Min(Rank) from qai.dbo.SecMapX where SecCode = b.SecCode and VenType = 40 and Exchange = 1)

join qai.dbo.SecMapX b3
    on b3.seccode = b.seccode
    and b3.ventype = 33
    and b3.exchange = 1
    and b3.Rank = (select Min(Rank) from qai.dbo.SecMapX where SecCode = b.SecCode and VenType = 33 and Exchange = 1)

join qai.dbo.DXLSecInfo c
    on b2.VenCode = c.Code

join qai.dbo.DXLAmData d
    on c.Code = d.Code
    and d.Date_ = @Date
    and d.Item = 6

left JOIN qai.dbo.DS2Adj f 
    ON f.InfoCode = b3.VenCode
    AND f.AdjType = 2
    and f.AdjDate <= @Date
    and ( f.EndAdjDate >= @Date or f.EndAdjDate is null )

where 
    a.cusip in ('00101J10', '00105510', '00120410', '00130H10', '00206R10',
    '00282410', '00287Y10', '00289620', '00724F10', '00817Y10', '00846U10',
    '00915810', '00936310', '00971T10', '01381710', '01535110', '01741R10',
    '01849010', '02000210', '02144110', '02209S10', '02313510', '02360810',
    '02553710', '02581610', '02687478', '03027X10', '03073E10', '03076C10',
    '03110010', '03116210', '03209510', '03251110', '03265410', '03741110',
    '03748R10', '03783310', '03822210', '03948310', '04621X10', '05276910',
    '05301510', '05329W10', '05333210', '05348410', '05361110', '05430310',
    '05493710', '05722410', '05849810', '06050510', '06405810', '06738310',
    '07181310', '07373010', '07588710', '07589610', '08143710', '08467070',
    '08651610', '09062X10', '09247X10', '09367110', '09702310', '09972410')

你還沒有發布你的查詢,但只是基於它的動態參數列表和參數的數量是如何構建的,我將猜測並說它與參數嗅探有關 - 請參閱:

http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx

這個問題的基本思想是為一組特定的參數創建一個最優的查詢執行計劃,這對另一個集合是非常不理想的。

有幾種方法來解決參數嗅探問題(謝天謝地,其中許多在SQL Server 2008中打開)。

你可以:

  1. 重構您的查詢
  2. WITH RECOMPILE添加到您的存儲過程/ option (recompile)到您的查詢
  3. optimize for unknown / option (optimize for...您的proc / query option (optimize for...
  4. 其他?




ado.net