varchar 列に nvarchar 渡しても convert_implicit しなくなってた

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

f:id:odashinsuke:20191006113548j:plain

一番上のプランの動作変わってんなーってとこ。
一番上は、昔は [var] 列が convert_implicit で nvarchar になってテーブルスキャンになってたけど、実行プランの xml みたら、GetRangeThroughConvert 使って範囲検索になってた。

SQL Server 2012 の環境あったから、同じクエリ試したけどやっぱ変わってた。
(Microsoft SQL Server 2012 - 11.0.5569.0 (X64))
f:id:odashinsuke:20191006115026p:plain

どのバージョンから変わったのかまでは調べてないけど、たまには色々試してみないとダメやね~。

テーブルを条件にいれた Index Scan なプランを取得する

必要になったので書くつもりが、良いの見つけたのでちょっとだけ改変。
元ネタ: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'[テーブル名]'

09/10(火) SQLWorld★大阪#51 開催します #sqlworld

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 で bit型(bool) をリテラルで指定する場合の対処法

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 でリテラルをパラメータ化したくない場合は DirectValue か N 使おう

前回 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:実行プラン変わる可能性あるし

LambdicSql で共通な select from を使った union

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
*/
  }
}

x64 な OSで Any CPU(32bit優先) な アプリを Friendly + nunit3-console でテストする

何も気にせず動かしたら、こんなエラーが出た。

Codeer.Friendly.FriendlyOperationException : プラットフォームターゲットがテスト対象とテストプロセスで異なります。合わせてください。

アプリが x86 で、nunit3-console が x64 やでーって感じっぽい。

nunit3-console.exe に x86 で動かすオプションあるからそれ指定でOKー。
Console Command Line · nunit/docs Wiki · GitHub

nunit3-console.exe --x86 Hoge.dll

結構前やけど、多分同じ感じなんちゃうかなー?
Friendly×Nunit3×Consoleの注意点 - Qiita