SQLSERVERのパーティションテーブルつかってみた

目次

確認環境

SQL Server Express Edition For AWS RDS
SSMS(SQL Server Management Studio 18)
Python3 3.6.0

パーティション

詳しいことはこいつだ。公式が最強なのだ
docs.microsoft.com

とりあえずテーブルを作るよ！

CREATE TABLE partition_sample(
	ID int,
	RELATION_ID int,
	START_DATE datetime2(0),
	VALUE1 int,
	VALUE2 int,
	VALUE3 int,
	VALUE4 int
);

IDという列名はよろしくないような気もしつつ。サンプルだからまぁいいかの精神。
列名は適当だけど、START_DATE が「datetime2(0)」型ってのはポイントなので、ちょっとだけ意識してくれるとうれしい。

データを放り込むよ。

効果を実感するには10年分ぐらいデータがほしい。年数が重要になる。重要なのだ。
ちまちまINSERT分を打ち込むのは非常にめんどうなのでPythonでさらっとしあげよう。

import datetime
start = datetime.date(1900, 1, 1)
for i in range(365 * 10):
  print('insert into partition_sample values (' + str(i) + ',1,\'' +  str(start) + '\',1,1,1,1);')
  start = start + datetime.timedelta(days=1)

Paiza.IOさんでも利用してたたけばPrintされます。全実行しちゃいましょう。
※やってみたらPaizaさん4年分ぐらいでテキストはくのやめちゃったから、適当に分割してつかーさい。

件数にしたらこんなもんで。
gyazo.com
毎日4年分でも１５００件ぐらい。

パーティション設定するよ！

※先にもつくれそうなんだけど、データいれてからのほうがマップ作るの楽なので、
データ入れた後にしよう。

gyazo.com
つくったテーブルを右クリックして、ストレージ->パーティションの作成を選ぶ
gyazo.com
ウィザードを適当にポチポチしていく。
gyazo.com
START_DATEを選ぶよ。
gyazo.com
関数名も適当に
gyazo.com
パーティションマップもこんな具合でよいでしょう。からのすぐに実行で完了です。
gyazo.com

本当にできてるか、テーブルのプロパティをみてみる。
gyazo.com
うん。できてるっぽい。いい感じに7分割。初めて作った割にはまともにできたと思う。

適当にSQL発行してみてパーティションを味わう。

SELECT * FROM 
partition_sample
WHERE
	ID = 1
;

とかいいながら、パーティションきかないぱてぃーん。その１。
パーティション列を指定してないのでききまてん。実行計画にもこのとおり。
gyazo.com

SELECT * FROM partition_sample
WHERE
	ID = 1 AND
	START_DATE BETWEEN CAST('1900/01/01' AS datetime2(0)) AND CAST('1900/01/31' AS datetime2(0))
;

こんな感じできちんと指定してあげると、パーティションが有効になる。
gyazo.com

SELECT * FROM partition_sample
WHERE
	ID = 1 AND
	START_DATE BETWEEN CAST('1900/01/01' AS datetime2(1)) AND CAST('1900/01/31' AS datetime2(1))
;

微妙にちがうこちらはNGぱってぃーん。パーティション列は厳密に型をみているようで。
datetime2の有効少数桁までみてるので、要注意。

SELECT * FROM partition_sample
WHERE
	ID = 1 AND
	START_DATE < CAST('1902/01/31' AS datetime2(1))
;

これもよろしくない。パーティションはきっちり区切って使おう。

なんでもつかえばいいってわけじゃないし、良い感じで分散されている日付列なんかが適当になってくるだろうし、
調整は必要だけど「強い」機能だと思います。ます。パーティション。使ったことなければ使てみてね。