ちょっとベンチ

バックエンドSQLserverフロントエンドAccessVBA VS VB.NET
‘VBA
Public Function Queryspeedtest()
Dim T1 As Double
Dim T2 As Double
T1 = Timer
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.ConnectionString = “OLEDB接続文字”
cn.Open
rs.cursorlocation=adUseClient
rs.Open “select A.code,B.codename from TableA as A inner join TableB as B on(A.code=B.code)”, cn, adOpenStatic, adLockOptimistic

rs.Close
cn.Close

T2 = Timer
Debug.Print T2 – T1
End Function

‘VB.NET
Public Shared Function Queryspeedtest()
Dim SW = New System.Diagnostics.Stopwatch()
SW.Start()

Dim cn = New System.Data.OleDb.OleDbConnection(OLEDB接続文字)
cn.Open()
Dim cmd = New System.Data.OleDb.OleDbCommand(
“select A.code,B.codename from TableA as A inner join TableB as B on(A.code=B.code)”, cn
)
Dim DT = New DataTable(“test”)
Dim adp = New System.Data.OleDb.OleDbDataAdapter(cmd)
adp.Fill(DT)
adp.Dispose()
cn.Close()

SW.Stop()
Debug.WriteLine(SW.Elapsed)
End Function

tableAは20万件、TableBは1万件弱のマスターファイル
TableAのレコードのcodeからTableBのcodenameを得るよくあるクエリ
もちろん主キーは両方あり

単純なselect column from tableは若干VBが勝利
上記のクエリはVBA 0.4s NET 0.8s

倍とはいえ差はコンマ数秒なのでまあいいかと思うがdatagridviewでtextbox表示とかcomboboxに入れたりするとちょっと・・
Accessフォームの方が速い、VB.NETではちょっと工夫が要りそうな

vbaでcursolocationがクライアントの時はcursoltype、locktypeによる差はない
サーバサイドでは最速と覚えていたadopenforwardonlyよりadopendynamicの方がなぜか早かった

ADODBとADO.NETはまったく別物なので比較するなと言われそうだけど・・
でもVSのエディタはすばらしい、VBAエディターが残念に思えるほど

datatable のクエリ

が遅い

sqlserverテーブルのシンプルな選択クエリ。
フォームに表示が遅いのではなく デザイナーで作成途中の確認の段階で遅い。

accessから移行中のクエリだがaccessのリンクテーブルのクエリの方がはるかに速い

ADODBのデータソースレス接続の様にコードにクエリを書いてdataadapter作成したほうが速いのか?

ODBC接続のaccessクエリに ADO.NETが速さで劣る?

どこか間違えているのかも、明日落ち着いて見直そう

新元号

新元号は令和
Accessには和暦はあるのでそのうちupdateあるでしょう
SQLserverとVB.NETにはない → 失礼、VB.NETにはあります
SQLserverのスカラー関数を
前略
DECLARE @ResultVar nvarchar(10),@VarYear int,@VarDate nvarchar(10),@VarDateInt int

select @VarYear=cast(format(@paramDate,’yyyy’) as int)
select @VarDate=format(@paramDate,’MM/dd’)
select @VarDateInt=cast(format(@paramDate,’yyyyMMdd’) as int)

— Add the T-SQL statements to compute the return value here
select @ResultVar=(case
when @VarDateint>20190430 then
concat(‘R’,format((@VarYear-2018),’00’),’/’,@VarDate) –令和
when @VarDateInt>19890107 then
concat(‘H’,format((@varyear-1988),’00’),’/’,@VarDate) –平成
when @VarDateInt>19261224 then
concat(‘S’,format((@varyear-1925),’00’),’/’,@VarDate) –昭和
when @VarDateInt>19120729 then
concat(‘T’,format((@varyear-1911),’00’),’/’,@VarDate) –大正
when @VarDateInt>18680124 then
concat(‘M’,format((@varyear-1867),’00’),’/’,@VarDate) –明治
else ‘0’
end
)
— Return the result of the function
RETURN @ResultVar
以下略
これで良いはず
SQLserverにあればVSはなんとかなるでしょう
VB.NETには和暦あります。省略型はないですが。

複数ステップのOLEDBエラー nvarchar(max) 改行

SQLserverテーブルのnvrachar(max)のカラムに対して
ODBC接続のレコードセット取って

Str=”文字” & vbCrLf
rs!カラム=Str
rs.update
など更新や追加行うと複数ステップの・・・エラー

OLEDB接続では起きない
nvarchar(max)をmaxを500など変更すれば起きない
cn.execute “insert into・・・values・・・” では起きない

サロゲートペア SQLserverの場合

以前もMySQLで書いたけど
AccessからSQLServerへサロゲート文字保存について

AccessはVBエディターに表示はできないけどテーブル、フォームに表示保存可能
SQLserverも保存できる

接続文字を書いてexecuteメソッドで
insert into Table(column) values(“サロゲートペア文字”)
とVBAから更新クエリ発行すると保存できない

OLEDBはだめ
oledbはhttps://docs.microsoft.com/ja-jp/sql/connect/oledb/features/utf-16-support-in-oledb-driver-for-sql-server?view=sql-server-2017
難しいけどだめだと書いてあるようです

ODBCならといろいろ試したけどこれも今のところだめです
ODBCからリンクテーブルにしてフォーム作って入力すると可能なのだがなぜ?
接続文字列にオプションか何かあるのかな?引き続き要調査

OLE DB

詳しくはわかりませんが何年か前から
Microsoft OLE DB Provider for SQL Server(SQLOLEDB) 
はもうサポートしないから新規開発に使用するのはやめてほしい
とMicrosoftがアナウンスしているが今でも使えています
でも突然消えると困るので調べてみると
代わりに
Microsoft OLE DB Driver for SQL Server(MSOLEDBSQL) 
をリリースしてるようです
問題はSQLOLEDBはOSに付属してるので何も考えずに良かったけどMSOLEDBは別途インスト必要らしいです
挙動も少し違うようで安全性は向上する、計算列の操作が今まで甘かったけど今度は厳しく行きますよ という感じのことがアナウンスされてた
今日開発機にインストして使ってみましたがaccdbは特に問題なく動きました。
OSは64bitですがAccessが32なのでx86をインストだろうと思ったらX64でした
接続文字もSQLOLEDBをMSOLEDBSQLに書き換えるだけで良いようです
アプリ配布先にはインストされてないのでパッケージ作るとき困るけどSQLOLEDB消えてから考えよう

update case文とトリガー

テーブルにupdateトリガーを仕込んで更新日時カラムをcurrent_timestampにupdateさせている状態で

update TableName set TargetColumn=case when OtherColumn=’abc’ then ‘D’ else TargetColumn end

で更新すると更新日時カラムは全レコード現在時刻に変更される

更新クエリの前に

Alter table TableName disable trigger TriggerName

クエリの後に

Alter table TableName enable trigger TriggerName

トリガーは楽な反面、仕込んでいるのを忘れてしまう
今日見直すとこの更新クエリ結局全部更新?
where句でupdate の方が断然速い
なんでもcase で条件分岐すれば良いというわけではないらしい

VBAからSQL ServerのIdentity取得

これは時々必要になるのですが
scope_identity() , ident_current(‘Table’) , @@Identity
の3つの方法があるが他のセッションの影響を受けない scope_identity() が推奨と検索すると出てきます。

しかしVBAからADO ADODBでレコードセットを取って
前略
cn.execute “insert into table・・・”
rs.open “select scope_identity() as ID”,cn,adopendynamic,adlockoptimistic
としても取得してくれないのでしかたなく
rs.open “select ident_current(‘Table’) as ID”,cn,adopendynamic,adlockoptimistic
で取得していました

cursorlocationがserverサイドでdynamicを要求した場合だけ取得できないのに気がつきました
ほかの組み合わせは大丈夫のようです
しかしこのレコードセットのcursortypeは何を要求しようと結局forwardonlyに調整されます
テーブルやPK等で自動調節?
ならばなぜdynamic要求の場合だけXなのでしょう。動的だから?

集計クエリ カウント

重複は除外してカウント、重複あっても全カウント 両方出したいとき
SQLserver、MySQLなら
select GroupColumn,Count(distinct TargetColumn),Count(TargetColumn) from Table group by GroupColumn
と簡単に出る

ACCESSでは?

このクエリはACCESSではできないようなのでいろいろ試したが今のところクエリ一文ではできていない
一度クエリでカウントして保存してさらにこれをソースにカウントしたカラムをカウントする のイメージで….

select GroupColumn as Group1,Count(TargetColumn) as Count1 from Table group by GroupColumn,TargetColumn
これをQ_1で保存して
select Group1,Count(Count1) as CountDistinct from Q_1 group by Group1
これでdistinct countができる

もう一つ全カウント
select GroupColumn as Group1,Count(TargetColumn) as CounAll from Table group by GroupColumn
のクエリQ_2を保存して

Q_1、Q_2 の連結クエリを作成する  の2段構え
もっといい方法があるのかもしれないが今のところこれしか思いつかない

バックエンドがSQLserverやMySQLならACCESSから一番上のパススルークエリの方が楽かな?

string_agg と for xml path

before Sqlserver2017
select
replace((select Column as [data()] from Table for xml path()),’ ‘,’,’) as CSVcolumn

Sqlserver2017
select
string_agg(Column,’,’) as CSVcolumn from Table

before Sqlserver2017
select
Z.PKcolumn
,Z.columnA
,Z.columnB
,row_number() over (order by Z.columnB asc) Seq
,replace((select Z.columnD as [data()] from Table where Z.PKcolumn=PKcolumn for xml path(”)),’ ‘,’,’) Member
from Table as Z
group by PKcolumn,columnA,columnB

Sqlserver2017
select
Z.PKcolumn
,Z.columnA
,Z.columnB
,row_number() over (order by Z.columnB asc) Seq
,string_agg(Z.columnD,’,’) Member
from Table as Z
group by PKcolumn,columnA,columnB