- PR -

SQLについて

«前のページへ 1|2
投稿者投稿内容
忠犬
大ベテラン
会議室デビュー日: 2006/05/01
投稿数: 109
投稿日時: 2009-03-04 17:09
要件について、もう一度確認したいのですが。

次のようにしたいのでは？

ケース１
未採番の場合・・・nullで返るので、1（最小値）とする

ケース２
n番まで採番されており、空き番号がない場合・・・n+1とする

ケース３
n番まで採番されており、空き番号がある場合・・・空きのうち、最小値


ケース３で、例えば1〜3、7〜10が空きになっていた場合、採用された方法では、1ではなく7が返されます。

それでokなのでしょうか？
明智重蔵
大ベテラン
会議室デビュー日: 2005/09/05
投稿数: 127
投稿日時: 2009-03-04 22:53
これでいいでしょう

コード:
select coalesce(max(seq), 0) + 1 as gap
  from (select seq, Row_Number() over(order by seq) as Rank
          from SeqTbl) d
 where seq = Rank;


参考リンク
分析関数の衝撃5（総集編）
http://codezine.jp/article/detail/1594
あかり
常連さん
会議室デビュー日: 2009/02/18
投稿数: 38
投稿日時: 2009-03-05 08:36
忠犬様、明智重蔵様回答ありがとうございます。

忠犬様ご指摘ありがとうございます。
>ケース３で、例えば1〜3、7〜10が空きになっていた場合、採用された方法では、1ではなく7が返されます。

OKではないです。失礼いたしました。

明智重蔵様
参考サイト拝見させていただきました。
動作も確認取れましたありがとうございました。

皆様に教えていただいた方法を試して、速度を計測してみたいと思います。
計測いたしましたら、またご報告申し上げます。
あかり
常連さん
会議室デビュー日: 2009/02/18
投稿数: 38
投稿日時: 2009-03-05 16:25
お疲れ様です。

SQLの計測を行った結果をご報告いたします。
テスト環境は4998件のレコードを準備
歯抜けは2991,3980です。
VB.NETのfor-next構文で1000回実行した時の実行時間を計測します。
それぞれ5回実行し中間時間をサンプリングする。

今回測定したのは、
NO1.
【SQL】連番の歯抜けの検索の改良型として、
忠犬様に提示していただいたSQL
select min(seq+1) as gap
from (select seq from seqtbl
union
select 0 as seq from seqtbl) as x
where (seq+1) not in(select seq from seqtbl)

NO2.
明智重蔵様のSQL
select coalesce(max(seq), 0) + 1 as gap
from (select seq, Row_Number() over(order by seq) as Rank
from SeqTbl) d
where seq = Rank;

NO3.
私の作成したNULLをストアドのIF文でチェックするSQL

ALTER PROCEDURE [dbo].[Get_Insert_Key]
@keyvalue integer OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @keyvalue = MIN(key + 1)
FROM Table_A
WHERE (key + 1) NOT IN ( SELECT key FROM Table_A);

IF @keyvalue IS NULL
BEGIN
SET @keyvalue = 1
END
END

以上を測定いたしました。
TEST1
プログラム中にコンスタント値としてNO1.のSQLを持ちExecuteScalarを実行した場合。
00:00:05.8254892
が中間的な速度でした。

TEST2
NO1.のSQLをストアドに登録し、ExecuteScalarを実行した場合。
00:00:05.8229156
が中間的な速度でした。

TEST3
NO1.のSQLをストアドに登録し、OUTPUTを作成、ExecuteNonQueryを実行しOUTPUTを取得する場合。
00:00:05.8401467
が中間的な速度でした。

TEST4
NO2.のSQLをストアドに登録し、ExecuteScalarを実行した場合。
00:00:02.3820686
が中間的な速度でした。

TEST5
NO2.のSQLをストアドに登録し、OUTPUTを作成、ExecuteNonQueryを実行しOUTPUTを取得する場合。
00:00:02.3951854
が中間的な速度でした。

TEST6
NO3.のSQLをストアドに登録し、OUTPUTを作成、ExecuteNonQueryを実行しOUTPUTを取得する場合。
00:00:07.0363274
が中間的な速度でした。

以上です。
１．ストアドもプログラムからのSQL発行もほぼ速度的な違いはありませんでした。

２．ストアドの実行方法は、OUTPUTパラメータを準備するため、ExecuteNonQueryを実行しOUTPUTを取得の方法は遅くなる。

３．ストアド中の判定文は遅い。

４．集計関数は非常に早い。

よっしー
大ベテラン
会議室デビュー日: 2007/05/17
投稿数: 143
投稿日時: 2009-03-05 18:01
いまさらですが。。

引用:

あかりさんの書き込み (2009-03-04 14:35) より:
>せっかくなので、スカラ値@keyvalueを返すストアドファンクションの方が便利ではないでしょうか？

とはどういうことでしょうか？


パラメータを利用しないで、こんな感じでINSERT文などにそのまま組み込めるということでした。
INSERT INTO tbl
(
key
, a
, b
, c
)
VALUES
(
dbo.GetKey()
, 'aaa'
, 'bbb'
, 'ccc'
)

中のGetKey()で難しいことを行っても、外側はシンプルになります。
忠犬
大ベテラン
会議室デビュー日: 2006/05/01
投稿数: 109
投稿日時: 2009-03-05 23:25
「採番」列に、インデクスを適切に定義しているでしょうか？

母体データが数千件なのに、数秒かかるというのは、テーブルスキャンが発生しているように感じます。

特に、「SQL Server 2005」とのことのようなので、Oracleでいう「分析関数」を使用した場合、インデクスのアクセスのみで結果を得られるはずなのですけどね。
あかり
常連さん
会議室デビュー日: 2009/02/18
投稿数: 38
投稿日時: 2009-03-06 09:06
おはようございます。
よっしー様、忠犬様回答ありがとうございます。

よっしー様
EXECUTEを使用しなくてもストアドを実行できるということですよね、知りませんでした。
１項目の戻り値であればとても有効ですね、勉強になりましたありがとうございます。


忠犬様
インデックスは使用しています。
母体データが数千件ですが、１０００回実行だとしても遅いのでしょうか？
For i As Integer = 1 To 1000
Try
Using cmd As SqlCommand = New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "test3"
cmd.Connection = cn
key = cmd.ExecuteScalar()
'Console.WriteLine(key.ToString)
End Using
Catch ex As Exception

End Try
Next
実際のロジックです。
忠犬
大ベテラン
会議室デビュー日: 2006/05/01
投稿数: 109
投稿日時: 2009-03-06 10:46
引用:

母体データが数千件ですが、１０００回実行だとしても遅いのでしょうか？


失礼、完全に見落としていました。
«前のページへ 1|2

スキルアップ／キャリアアップ（JOB@IT）