SQLでin 区を先にOrderしとくといいことがあるらしいので実験してみた①
目次
こんにちは。私は現在SQLのパフォーマンスチューニングが難しくてしんどい毎日をおくっています。
そんなある日同僚からある情報をゲットしました。
なんでも
「INで指定する項目は先にソートしとくと性能が向上する」
的なこと。ぜんぜん知らない情報だったから「うそでっしゃろ」といったんだけど、
実際にはやくなったぜい。とのこと。
理屈がまるで理解できなかったのでためしにやって見ることにした。
つまり、どゆこと?
ようするに、こんな感じのこと
たとえば
— yamada yuki (@couraeg) 2020年2月18日
select distinct col1 from hugatbl
where fuga in (1,3,7,4)
より
select distinct col1 from hugatbl
where fuga in (1,3,4,7)
のが速い。コストは変わらないんだが。
inの指定を適当に並べるんじゃなくて、ソートしてからならべてみ。という形。
先に結果から
差異なし。なかった。データパターンによるかもしれないで、もうちょっと検証してみる予定。
仮説的ななにか。
対象のSQLはこんな形。
SELECT --RANDUM DISTINCT string_group FROM dbo.sample1 WHERE string_value in( @ソートする子たち。 )
適当にぐぐっても、SQLServerのリファレンスを漁っても特にそれらしき記述は見当たらなかった。
1.なんとなくDISTINCTの処理に影響してるんじゃないかなーとは思っている。
DISTINCTするために、ソートを行うが先にソートしINを使った実行結果は、DISTINCT用にソートしなくてもある程度並んでいて、
ソートするんだけど計算が早く終わった?というようなそんな雑な説。(個人的にはこっちだと思っている。)
2.ソートの方向をINDEXと合わせているので「2 , 10, 20」と指定されていたら「2」を処理した後は2より前のツリーは見る必要がなさそう。
「10」のあとはさらになさそう。という感じで内部でうまいことやってる説。なくもない気がするけど、こーいった話ならもっとググったらヒットする気がするんだ。
だからこっちはないんじゃないかなーという風におもっている。
検証環境
テーブル構成
gyazo.com
SSMSつかってるとDDL参照するより ALT+F1 たたいたほうが早かったので。
PRIMARY KEYは Seq。
int_value、string_value それぞれ単独にIndexもつくっておいた。
検証方法
SSMS v18.4 を利用する。クライアント統計より平均を確認する。
初回の読み込みはキャッシュがどうたらなので読み飛ばす。
データ
gyazo.com
こんなのりで「65536」レコード用意
gyazo.com
insert ゴニョゴニョしているのは、まとめてぶちこむとSQLServerがエラーはくので、
1万レコードずつぐらい順次発行した。
※そういえばよく、このInsertつくる数式つくるし。なんならSQLはくSQLも書くよなぁ。。。関係ない話だけど。
抽出レコード
100レコードほど選出。RAND()を使用して適当に選抜
gyazo.com
SQL
RANKでばらしただけ版
SELECT --RANDUM DISTINCT string_group FROM dbo.sample1 WHERE string_value in( 'A15248', 'A64261', 'A453', 'A2048', 'A2849', 'A65221', 'A33118', 'A40893', 'A4646', 'A39000', 'A54145', 'A62087', 'A51953', 'A200', 'A57296', 'A26603', 'A51251', 'A11736', 'A34673', 'A25247', 'A12676', 'A2307', 'A14853', 'A52231', 'A48490', 'A47098', 'A62271', 'A48098', 'A37082', 'A24875', 'A34056', 'A48482', 'A30644', 'A60290', 'A62841', 'A41690', 'A22644', 'A13343', 'A16431', 'A765', 'A18763', 'A65238', 'A21191', 'A46067', 'A2165', 'A6133', 'A5280', 'A19706', 'A30949', 'A1491', 'A45748', 'A45229', 'A12036', 'A12248', 'A41194', 'A36729', 'A46322', 'A3545', 'A15876', 'A40214', 'A26364', 'A15458', 'A61826', 'A33099', 'A25222', 'A9335', 'A27770', 'A23440', 'A61849', 'A26165', 'A60885', 'A40923', 'A7052', 'A62381', 'A9846', 'A13357', 'A28753', 'A29100', 'A25523', 'A13274', 'A53818', 'A3297', 'A47652', 'A7943', 'A38928', 'A18387', 'A45783', 'A17175', 'A34165', 'A55673', 'A27634', 'A63874', 'A53210', 'A39694', 'A8005', 'A40405', 'A48884', 'A33200', 'A57162', 'A41524' )
min sorted版
SELECT --min sorted DISTINCT string_group FROM dbo.sample1 WHERE string_value in( 'A11736', 'A12036', 'A12248', 'A12676', 'A13274', 'A13343', 'A13357', 'A14853', 'A1491', 'A15248', 'A15458', 'A15876', 'A16431', 'A17175', 'A18387', 'A18763', 'A19706', 'A200', 'A2048', 'A21191', 'A2165', 'A22644', 'A2307', 'A23440', 'A24875', 'A25222', 'A25247', 'A25523', 'A26165', 'A26364', 'A26603', 'A27634', 'A27770', 'A2849', 'A28753', 'A29100', 'A30644', 'A30949', 'A3297', 'A33099', 'A33118', 'A33200', 'A34056', 'A34165', 'A34673', 'A3545', 'A36729', 'A37082', 'A38928', 'A39000', 'A39694', 'A40214', 'A40405', 'A40893', 'A40923', 'A41194', 'A41524', 'A41690', 'A45229', 'A453', 'A45748', 'A45783', 'A46067', 'A46322', 'A4646', 'A47098', 'A47652', 'A48098', 'A48482', 'A48490', 'A48884', 'A51251', 'A51953', 'A52231', 'A5280', 'A53210', 'A53818', 'A54145', 'A55673', 'A57162', 'A57296', 'A60290', 'A60885', 'A6133', 'A61826', 'A61849', 'A62087', 'A62271', 'A62381', 'A62841', 'A63874', 'A64261', 'A65221', 'A65238', 'A7052', 'A765', 'A7943', 'A8005', 'A9335', 'A9846' )