iT邦幫忙

13

【C#、SQL】 : 省略count判斷資料有無,直接查詢1 or Not返回boolean結果(select top 1 1)

c#

這對大大們應該是基本的概念,但最近還是會看到
"判斷表格是否存在指定的資料,會先查完資料,再判斷是否數量大於1"情況
其實可以少一步count動作,並且借助資料庫語法提升效能。

舉例

現在有一個使用者資料,想要判斷指定名稱的使用者存不存在

UserID Name
1 阿翰
2 阿明

"會先查完資料,再判斷是否數量大於1"寫法

void Main()
{
	using (var conn = this.Connection)//替換成自己的connection
	{
		conn.Open();
		using (var tx = conn.BeginTransaction()) 
		{
			conn.Execute(@"
				--測試資料
				CREATE TABLE #T
				    ([UserID] int, [Name] nvarchar(2))
				;
				    
				INSERT INTO #T
				    ([UserID], [Name])
				VALUES
				    (1, N'阿翰'),
				    (2, N'阿明')
				;			
			", transaction: tx);

			bool IsUserExist(string name)
			{
				//先查完資料,再判斷資料是不是存在
				return conn.Query(@"
					--查詢
					select *
					from #T
					where name = @name			
				", new { name }, transaction: tx).Count()>0;
			}
			var queryExist = IsUserExist("阿明"); 
			var queryNonExist = IsUserExist("阿香"); 

			tx.Rollback();
		}
	}
}

其實可以改成select top 1 1方式 (Oracle可以用rownum=1)
優化原理:

  1. 因為只判斷存不存在資料,使用top 1避免返回多筆資料情況、優化效能
  2. select 1,當返回資料為1的時候,C#指定型態為bool會自動判斷if (value = 1) =true else = false
void Main()
{
    //..略
    bool IsUserExist(string name)
    {
        //返回類型是bool,結果是1時候,會自動轉成true,null或其他狀態會返回false
        return conn.Query<bool>(@"
            --查詢
            select top 1 1
            from #T
            where name = @name			
        ", new { name },transaction:tx).SingleOrDefault();
    }
    var queryExist = IsUserExist("阿明"); //因為有資料所以返回true
    var queryNonExist = IsUserExist("阿香"); //因為沒有資料返回false
    //..略
}

補充關於效能方面的profiler計畫,以下是使用全表掃描情況

先建立二十萬筆資料做測試

--測試資料
CREATE TABLE T
([UserID] int, [Name] nvarchar(2))
;

DECLARE @cnt INT = 0;
BEGIN TRANSACTION;
WHILE @cnt < 100000
BEGIN
	INSERT INTO T ([UserID], [Name])VALUES (1, N'阿翰'),(2, N'阿明') ; 
    SET @cnt = @cnt + 1;
END;
commit;

top 1 1用法

count方式用法

可以觀察到兩個用法,在where掃描的情況下是一樣的(資料表掃描)
但是在top用法在select成本,使用top運算式,該成本不到總成本1%
而count用法在select成本,使用資料流彙總,該成本佔總成本13%

IO比較,都沒用到IO,因為資料已經從資料表掃描篩選出來了
TOP用法 : 0
count用法 : 0

CPU比較,top勝
TOP用法:0.0000001
count用法:0.0600005

至於select * from table在C#再轉count版本就不測試了(這寫法有點糟糕 XDD)

補充

使用ORM套件Dapper,.NET版本4.6.1
編輯器LINQPad,資料庫:SQL-Server


假如大大們有更好作法,或是錯誤地方麻煩告知,感恩。


0
小魚
iT邦高手 1 級 ‧ 2018-09-04 19:38:19

你說那個執行計畫是SSMS自己會顯示的嗎?
那我要好好研究一下...

暐翰 iT邦大師 1 級‧ 2018-09-04 19:38:52 檢舉

會,原生自帶的

小魚 iT邦高手 1 級‧ 2018-09-04 19:48:09 檢舉

這一篇對我來說比較大的收穫是這個
/images/emoticon/emoticon01.gif

暐翰 iT邦大師 1 級‧ 2018-09-04 19:54:40 檢舉

/images/emoticon/emoticon12.gif

0
pcw
iT邦研究生 2 級 ‧ 2018-09-05 08:52:46

有用SELECT TOP 1,但沒想到還可以用SELECT TOP 1 1
感謝。/images/emoticon/emoticon12.gif

暐翰 iT邦大師 1 級‧ 2018-09-05 08:55:25 檢舉

/images/emoticon/emoticon12.gif

pcw iT邦研究生 2 級‧ 2018-09-05 13:08:25 檢舉

SQLITE不支援TOP,要改用SELECT 1 FROM XX LIMIT 1;

4
Homura
iT邦高手 1 級 ‧ 2018-09-05 09:41:08

Select Top 1 1好用
以前都是判斷傳回來的count
覺得自己好笨/images/emoticon/emoticon02.gif

暐翰 iT邦大師 1 級‧ 2018-09-05 09:57:24 檢舉

現在資料庫太聰明

Select Top 1 1 這招我常在用XD..

Homura iT邦高手 1 級‧ 2018-09-05 11:17:23 檢舉

一堆神手/images/emoticon/emoticon06.gif

1
神Q超人
iT邦新手 2 級 ‧ 2018-09-05 13:21:39

還以為TOP 1 1MSSQL的隱藏功能XD,
沒想到是聰明的邏輯來判斷!
感謝大大分享/images/emoticon/emoticon12.gif

暐翰 iT邦大師 1 級‧ 2018-09-05 14:48:16 檢舉

/images/emoticon/emoticon12.gif

我要留言

立即登入留言