Troubleshooting low performance with SqlParameter (index seek/scan issue)

設計一表格如下：

CREATE TABLE [dbo].[IC_TESTA](

    [NAME] [varchar](100) not NULL,

    [SHORT_NAME] [varchar](100) not NULL

);

CREATE NONCLUSTERED INDEX IC_TESTA_I01 ON IC_TESTA( NAME,SHORT_NAME );

1.  Table Scan

因為條件沒有指定index的第一個欄位：NAME， 導致plan作Table Scan.

SELECT * FROM IC_TESTA WHERE --NAME = '01' and SHORT_NAME='Y';

2.  Index Seek (最佳情況)

SELECT * FROM IC_TESTA WHERE NAME = '01' and SHORT_NAME='Y';

3.  Index Scan

SELECT * FROM IC_TESTA WHERE NAME =01 and SHORT_NAME='Y';

因為在指定NAME欄位值時用的是數字，在Sql Server會對NAME這個欄位做Convert為數字型態，導致無法做到Index Seek，而是Index Scan。

至於Index Seek/Index Scan的比較，請參考以下文章。
淺談Index Seek和Scan

4.  接下來回到C#的SqlParameter，在使用SqlParameter時如果沒有指定類型，就有可能會吃了CONVERT_IMPLICIT這計悶虧。

String sql = @" SELECT * FROM IC_TESTA WHERE NAME = @NAME"; var sqlParams = new SqlParameter[]{     new SqlParameter("@NAME", "JB") }; var dt = getData(sql, sqlParams); //Get data

請參考MSDN：SqlParameter.DbType 屬性

SqlParameter的SqlDbType預設為NVarChar，所以實際轉換的Sql如下，的確有CONVERT_IMPLICIT的成本。

5.  比較嚴謹的寫法，應該要加上指定SqlDbType。

var sqlParams = new SqlParameter[]{                 new SqlParameter("@NAME", SqlDbType.VarChar)             }; sqlParams[0].Value = "JB";

實際Sql，有正確指定為VARCHAR型別。

6.  Reference
[轉載] Index Scan vs Seek in SQL Server
如何解讀 SQL Server 的圖型式執行計畫
Convert Implicit and the related performance issues with SQL Server
Troubleshooting SQL index performance on varchar
在Sql Server中， NVARCHAR及VARCHAR的區別