@IT会議室は、ITエンジニアに特化した質問・回答コミュニティ「QA@IT」に生まれ変わりました。ぜひご利用ください。
- PR -

データベースからある範囲のデータを取り出す方法について

投稿者投稿内容
nodera
大ベテラン
会議室デビュー日: 2003/09/08
投稿数: 200
投稿日時: 2004-02-17 12:56
Webでよくありますようにデータベースに格納されているデータを、
一覧形式かつページ単位で表示したいと考えています。

この場合、データベースからデータを抽出する際にどのような方法が一般的、また効率のよい方法であるか質問させてください。

# 質問の中心が.NETではなくSQLにありますが、どこが適当な投稿場所か分からなかったのと、
# ここは投稿・閲覧件数が多いため、皆さんに甘えさせてもらい投稿させてもらいました。
# それでも不適切だった場合、ごめんなさい。

ちなみに環境は
Windows2003Server
IIS6
SQLServer2000
ASP.NET(VS.NET2003)
を想定しています。

考えられる一番単純な方法は、データベースから対象とするテーブルの結果セットを全て取得し、いま表示しようとしているページ数×1ページあたりの行数を、結果セットの前方から回してカウントしていき、表示対象とするレコードが現れたらそれを一覧としてレスポンスするというやり方が考えられます。
しかし、これでは結果セットで得られるデータが多くなればなるほど、時間がかかりサーバーリソースも無駄にしてしまいます。

続いて考えられる方法は、SQLのSELECT文でTOP句を使用して目的とするレコードだけ抽出するやり方です。
具体的には次のサイトに記述されているような方法です。
http://www.sqlpassj.org/bunkakai/web/series/ado/04.aspx#l4_5

この方法では処理時間も短く、サーバーリソースもそれほど消費しないと考えられますが、TOP句に変数を利用できないという問題点があります。

いまやろうとしていることのひとつに、
「ユーザー要求により1ページあたりの表示行数が変更できるようにする」
というものがあります。

しかしクエリーをストアドプロシージャで利用する場合、TOP句に変数を使用することができないため、SQL文を文字列結合で組みたてる必要が発生してしまいます。
これでできることはできますが、ストアドプロシージャによるコンパイル済みクエリーの実行という利点が失われてしまいます。

このような場合、文字列によるSQL文の組み立て以外に方法があるのか教えてください。
(それとも皆さんもこのような方法でやっているのでしょうか?)

# 実はもう一点尋ねようとしていたことがありましたが、
# 上記サイトを読んだら解法がなんとなくわかったため省略。
# その質問というのは複数のキーでソートしていた場合に、あるページのレコードだけを取り出す方法は?
# というものでしたが、ソートを逆転させた結果セットから
# さらにTOPを取り出す方法でいけそう。。たぶん。。まだ試してないですが
ゆうじゅん
ぬし
会議室デビュー日: 2004/01/16
投稿数: 347
投稿日時: 2004-02-17 13:13
SqlDataAdapterクラスのFillメソッドに読み込み開始位置と最大レコード件数を設定して
読み込んでいます。

TABLE1の9レコード目から最大15件読み込む場合

myDataAdapter.Fill(myDataSet,9,15,"TABLE1");
nodera
大ベテラン
会議室デビュー日: 2003/09/08
投稿数: 200
投稿日時: 2004-02-17 16:47
ゆうじゅんさん返答ありがとうございます。

なるほど、Fillメソッドにはそういった引数をとることができるんですね。
どれどれ・・・と「プログラミングADO.NET」という書籍を眺めたところ、しっかり自分で該当箇所に赤線引っ張ってありました(汗 こんな使い方あったの既にわすれてる。

しかし、この書籍やMSDNを再度確認してみると、効率化、パフォーマンスといった点で要求を満たすことができないことがわかりました。
このメソッドは、全てのレコードを一度結果として取得し、必要な部分をDataSetに入れているようです。たぶん自分の記述した単純な方法(のようなこと)を内部的にやってくれてるだけだと思われます。

他にもこういうやり方もあるよ、っていう意見がありましたら皆さんよろしくお願いします。
Jitta
ぬし
会議室デビュー日: 2002/07/05
投稿数: 6267
お住まい・勤務地: 兵庫県・海手
投稿日時: 2004-02-17 17:13
 SQL Serverはちょっとよくわからないのですが、Oracleの場合、ROWNUMという仮列があるので、それを使います。

SELECT * FROM (SELECT * FROM ... ORDER BY 1)
WHERE ROWNUM BETWEEN ? AND ?

インラインビューを使うのは、行番号が決まるのがORDER BY句の後だからです。

<<<<<
自分のページに書いている内容と違っていたので修正(^^;

[ メッセージ編集済み 編集者: Jitta 編集日時 2004-02-17 17:30 ]

引用:

Clusterさんの書き込み (2004-02-17 17:31) より:

残念ながら、SQL ServerにはRowNumはありません。


 では、ROW_NUMBERという「行番号を振る関数」・・・も無いんだろうな。。。次はSQL Server(MSDE)対応なのにどうしよう.....

[ メッセージ編集済み 編集者: Jitta 編集日時 2004-02-17 17:36 ]
Cluster
ぬし
会議室デビュー日: 2003/03/06
投稿数: 289
お住まい・勤務地: 大阪
投稿日時: 2004-02-17 17:31
引用:

Jittaさんの書き込み (2004-02-17 17:13) より:
 SQL Serverはちょっとよくわからないのですが、Oracleの場合、ROWNUMという仮列があるので、それを使います。




残念ながら、SQL ServerにはRowNumはありません。
私の知る限りでは、SQL ServerではTOP句を使う方法しか知りません。
永井和彦
ぬし
会議室デビュー日: 2002/07/03
投稿数: 276
お住まい・勤務地: 東京都
投稿日時: 2004-02-17 17:57
引用:

続いて考えられる方法は、SQLのSELECT文でTOP句を使用して目的とするレコードだけ抽出するやり方です。
具体的には次のサイトに記述されているような方法です。
http://www.sqlpassj.org/bunkakai/web/series/ado/04.aspx#l4_5



上記サイトの方法でいいんではないでしょうか。RDBMSの特徴を活かした、具体的な良いTipだと思います。

引用:

この方法では処理時間も短く、サーバーリソースもそれほど消費しないと考えられますが、TOP句に変数を利用できないという問題点があります。



ここはよく分からないのですが……「ここには変数を入れられない」というのもあるものなんですね……

引用:

いまやろうとしていることのひとつに、
「ユーザー要求により1ページあたりの表示行数が変更できるようにする」
というものがあります。

しかしクエリーをストアドプロシージャで利用する場合、TOP句に変数を使用することができないため、SQL文を文字列結合で組みたてる必要が発生してしまいます。



このユーザー要求はとりあえず関係無いですよね?内側のTOP句に使う数字は常に総件数とページ数に引っ張られて変化しますし。

#途中で主旨から離れて迷走していたため、後半部分を削除しました。

[ メッセージ編集済み 編集者: 永井和彦 編集日時 2004-02-17 18:19 ]
nodera
大ベテラン
会議室デビュー日: 2003/09/08
投稿数: 200
投稿日時: 2004-02-17 18:03
引用:

Clusterさんの書き込み (2004-02-17 17:31) より:
引用:

Jittaさんの書き込み (2004-02-17 17:13) より:
 SQL Serverはちょっとよくわからないのですが、Oracleの場合、ROWNUMという仮列があるので、それを使います。




残念ながら、SQL ServerにはRowNumはありません。
私の知る限りでは、SQL ServerではTOP句を使う方法しか知りません。




Jittaさん、Clusterさん、返答ありがとうございます。
前述した「プログラミングADO.NET」という書籍でも同様のことが記述されていました。
SQLServerのTOP句は、Olacleはサポートされておらず同じような機能としてrownumがあるようです。また、ソート順を使ったクエリの結果から特定のページを取り出すにはrownumを使用するときにちょっとした裏技(う、ウラワザなんだ)を使う必要があるようです。
サンプルとして掲載されていたコードはほぼJittaさんの書いてくれたものと同じようなコードでした。
SQLServerで使用できないのは残念ですが、情報ありがとうございます。
きくちゃん
ぬし
会議室デビュー日: 2003/08/01
投稿数: 854
お住まい・勤務地: 都内某所
投稿日時: 2004-02-17 18:28
noderaさん、こんばんは。

引用:

しかしクエリーをストアドプロシージャで利用する場合、TOP句に変数を使用することができないため、SQL文を文字列結合で組みたてる必要が発生してしまいます。


プロシージャの代わりに TABLE 型を返すユーザ定義関数を使用して、
SELECT TOP n .... FROM MY_FUNCTION(....)
というようなSQLを組み立てて発行すれば良いような気がしますが、如何でしょうか。

スキルアップ/キャリアアップ(JOB@IT)