来週 10/19(土) に、.NET Conf関西 2019 - connpass で
.NET + SQL Server Tips というタイトルでお話しします。
Tips なので既に知ってるという方も居られるかもしれませんが、
何人の方にはへーっと思って頂ければと思っています。
満席間近みたいなので、参加しようと思っている方はお早めに~。
来週 10/19(土) に、.NET Conf関西 2019 - connpass で
.NET + SQL Server Tips というタイトルでお話しします。
Tips なので既に知ってるという方も居られるかもしれませんが、
何人の方にはへーっと思って頂ければと思っています。
満席間近みたいなので、参加しようと思っている方はお早めに~。
varchar と nvarchar の列持つテーブルにそれぞれ index つけて適当にデータ突っ込んで試したら、動作変わってた。
create table [Hoge] ( [var] varchar(20) not null, [nvar] nvarchar(20) not null ) create index [Hoge_var] on [Hoge] ([var]) create index [Hoge_nvar] on [Hoge] ([nvar]) ; with cte as (select 1 as cnt union all select cnt + 1 from cte where cnt < 100000) insert into [Hoge] ([var], [nvar]) select cnt, 100000-cnt from cte option (MAXRECURSION 0)
Azure SQL DB
(Microsoft SQL Azure (RTM) - 12.0.2000.8)
declare @nvar nvarchar(20) = N'111' declare @var varchar(20) = '111' select * from [Hoge] where [var] = @nvar select * from [Hoge] where [nvar] = @nvar select * from [Hoge] where [var] = @var select * from [Hoge] where [nvar] = @var
一番上のプランの動作変わってんなーってとこ。
一番上は、昔は [var] 列が convert_implicit で nvarchar になってテーブルスキャンになってたけど、実行プランの xml みたら、GetRangeThroughConvert 使って範囲検索になってた。
SQL Server 2012 の環境あったから、同じクエリ試したけどやっぱ変わってた。
(Microsoft SQL Server 2012 - 11.0.5569.0 (X64))
どのバージョンから変わったのかまでは調べてないけど、たまには色々試してみないとダメやね~。
必要になったので書くつもりが、良いの見つけたのでちょっとだけ改変。
元ネタ:Clustered Index Scan のプランを抽出する at SE の雑記
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp) select * from ( SELECT RO.C.value(N'@LogicalOp','varchar(20)') as LogicalOp , text , query_plan , creation_time --, RO.C.value(N'@EstimateRows','numeric(20,5)') as EstimateRows , min_elapsed_time , max_elapsed_time , min_logical_reads , max_logical_reads , min_logical_writes , max_logical_writes , RO.C.value(N'(./sp:IndexScan/sp:Object/@Table)[1]', 'nvarchar(100)') as TableName FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle) CROSS APPLY sys.dm_exec_sql_text(sql_handle) CROSS APPLY query_plan.nodes(N'//sp:RelOp') as RO(C) WHERE RO.C.value(N'@LogicalOp','varchar(20)') like '%Index Scan' ) data_ where TableName = N'[テーブル名]'
SqlWorld :: SQLWorld★大阪#51 開催します!
32回目の平日夜開催で、前回同様 ハンズオン 形式で行う予定です。
【日時】
2019年09月10日(火曜日) 19:00~21:00
【イベント概要】
SQLWorld、今回で33回目の平日夜開催~。今回も、みんなで SQL を書いてみようというハンズオン企画です!ブラウザがあれば参加出来るようにしていますので、iPad 等のタブレットでも大丈夫です。
【会場】
フェンリル株式会社さま大阪本社 http://www.fenrir-inc.com/
〒530-0011 大阪府大阪市北区大深町 3番1号 グランフロント大阪タワーB(オフィス)
【参加費】
無料
【持ち物】
パソコン/タブレット (DB のインストールは不要です。)
【参加可能人数】
13 人
お題に沿って、SQL を書いてみようという勉強会です。是非ご参加を~。
開催回数は増えていっていますが、続き物というわけでは無いので初めて参加される方でもお気軽にどぞー。
LambdicSql でリテラルをパラメータ化したくない場合は DirectValue か N 使おう - お だ のスペース
で、DirectValue 使おうねーって書いたけど、bit型のカラム(.NET の型だと bool) は DirectValue をそのまま使うとエラーなクエリが吐かれます。
using LambdicSql; using System; using System.Data.SqlClient; using static LambdicSql.SqlServer.Symbol; public class table { public int foo { get; set; } public int bar { get; set; } public bool IsValid { get; set; } } public class DB { public table table { get; set; } } class Program { static void Main(string[] args) { var query = Db<DB>.Sql(db => Select(new table { foo = db.table.foo, bar = db.table.bar }). From(db.table). Where(db.table.IsValid == true.DirectValue())); var qs = query.Build(typeof(SqlConnection)).Text; Console.WriteLine(qs); /* SELECT table.foo AS foo, table.bar AS bar FROM table WHERE table.IsValid = True */ } }
なクエリが吐かれてこのまま実行すると、
列名 'True' が無効です。
となってしまいます。
bit (Transact-SQL) - SQL Server | Microsoft Docs
bit 型の場合、0 (false) か 0以外 (true) という扱いなので、取り合えず 0 か 1 を渡したら良さげです。
※文字列 'TRUE'、'FALSE' でもクエリエラーにはなりませんが、前回も書いた暗黙の型変換(convert_implicit)が発生します。
では、table.IsValid == 1.DirectValue()
と書きたいけどこうすると bool型 == int型 になってコンパイルエラーとなります。
なので、1.DirectValue() の結果を bool にキャストします。これも int型 を bool型にそのままキャスト出来ないのでobject型を経由します。
class Program { static void Main(string[] args) { var query = Db<DB>.Sql(db => Select(new table { foo = db.table.foo, bar = db.table.bar }). From(db.table). Where(db.table.IsValid == (bool)(object)1.DirectValue())); var qs = query.Build(typeof(SqlConnection)).Text; Console.WriteLine(qs); /* SELECT table.foo AS foo, table.bar AS bar FROM table WHERE table.IsValid = 1 */ } }
めんどくさー。
前回 LambdicSql ネタ書いたので、小ネタを何個か。
LambdicSql でリテラル値を使うと勝手にパラメータ化されます。
が、パラメータ化したくない時もあります。*1
using LambdicSql; using System; using System.Data.SqlClient; using static LambdicSql.SqlServer.Symbol; public class table { public int foo { get; set; } public int bar { get; set; } public string 状態 { get; set; } } public class DB { public table table { get; set; } } class Program { static void Main(string[] args) { var query = Db<DB>.Sql(db => Select(new table { foo = db.table.foo, bar = db.table.bar }). From(db.table). Where(db.table.状態 == "有効")); var qs = query.Build(typeof(SqlConnection)).Text; Console.WriteLine(qs); /* SELECT table.foo AS foo, table.bar AS bar FROM table WHERE table.状態 = @p_0 */ } }
[状態] の条件はリテラルで "有効" としているけど、@p_0 でパラメータ化されちゃう。
これを防ぐには DirectValue 使おうねー。
class Program { static void Main(string[] args) { var query = Db<DB>.Sql(db => Select(new table { foo = db.table.foo, bar = db.table.bar }). From(db.table). Where(db.table.状態 == "有効".DirectValue())); var qs = query.Build(typeof(SqlConnection)).Text; Console.WriteLine(qs); /* SELECT table.foo AS foo, table.bar AS bar FROM table WHERE table.状態 = '有効' */ } }
はい、パラメータ化されなくなった。
でも、nvarchar、nchar だと、暗黙の型変換(convert_implicit)が起きてパフォーマンスに悪影響?
そんなときは、N 使おうねー。
class Program { static void Main(string[] args) { var query = Db<DB>.Sql(db => Select(new table { foo = db.table.foo, bar = db.table.bar }). From(db.table). Where(db.table.状態 == N("有効"))); var qs = query.Build(typeof(SqlConnection)).Text; Console.WriteLine(qs); /* SELECT table.foo AS foo, table.bar AS bar FROM table WHERE table.状態 = N'有効' */ } }
暗黙の型変換のドキュメント
Data type conversion (Database Engine) - SQL Server | Microsoft Docs
パフォーマンスに影響するよーの記事
DO’s&DONT’s #2: 絶対にやらなければいけないこと – データ型を一致させる – Microsoft SQL Server Japan Support Team Blog
SQL Server のチューニングについてまとめてみる - その6 - ( CONVERT_IMPLICIT、暗黙の型変換の怖さを知ろう ) - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo
*1:実行プラン変わる可能性あるし
GitHub - Codeer-Software/LambdicSql
GitHub - Codeer-Software/LambdicSql.SqlServer
を使ったサンプル。
元ネタ
SELECT foo, bar FROM table WHERE foo = 1 AND bar = 2 UNION ALL SELECT foo, bar FROM table WHERE foo = 3 AND baz = 4
select - from は共通してて、union all する時の where を変更したいみたいな感じ。
install-package LambdicSql.SqlServer install-package System.Data.SqlClient
して、
using LambdicSql; using System; using System.Data.SqlClient; using static LambdicSql.SqlServer.Symbol; public class table { public int foo { get; set; } public int bar { get; set; } } public class DB { public table table { get; set; } } class Program { static void Main(string[] args) { var selectfrom = Db<DB>.Sql(db => Select(new table { foo = db.table.foo, bar = db.table.bar }).From(db.table)); var query = selectfrom + Db<DB>.Sql(db => Where(db.table.foo == 1 && db.table.bar == 2)) + Db<DB>.Sql(_ => Union(All())) + selectfrom + Db<DB>.Sql(db => Where(db.table.foo == 3 && db.table.bar == 4)); var qs = query.Build(typeof(SqlConnection)).Text; Console.WriteLine(qs); /* SELECT table.foo AS foo, table.bar AS bar FROM table WHERE table.foo = @p_0 AND table.bar = @p_1 UNION ALL SELECT table.foo AS foo, table.bar AS bar FROM table WHERE table.foo = @p_2 AND table.bar = @p_3 */ } }