C# と VB.NET の質問掲示板

以下のようなデータテーブルがあります。

OYA	KO	NO
A	B	1
A	B	2
B	C	1
B	C	2
C	D	1
C	D	2
D	E	1
D	E	2
〜
Y	Z	1
Y	Z	2

このデータを取得するのに＜例�@＞のSQLを使用すると
A〜P のデータで6秒
A〜Q のデータで12秒
A〜R のデータで26秒
A〜S のデータで50秒
A〜T のデータで1分２６秒
A〜Z のデータで50分で戻ってこないので中断

＜例�@＞
DECLARE	@OYA VARCHAR(20) = 'A'; 
WITH CTE (OYA, KO, NO)
AS
(
	SELECT	OYA, KO, [NO]
	FROM	TBL spi1
	WHERE	OYA = @OYA
	UNION	ALL
	SELECT	spi2.OYA, spi2.KO, spi2.NO
	FROM	TBL AS spi2
	INNER	JOIN CTE
		ON spi2.OYA = CTE.KO
)
SELECT	DISTINCT *
FROM	CTE

なので、
ダメもとで作ったのが

＜例�A＞
IF OBJECT_ID(N'tempdb..#TBL', N'U') IS NOT NULL DROP TABLE #TBL;
IF OBJECT_ID(N'tempdb..#TABLE', N'U') IS NOT NULL DROP TABLE #TABLE;

SELECT	*
INTO	#TBL
FROM	(
	SELECT	OYA, KO, NO
	FROM	TBL
	) AS ITM;

CREATE TABLE #TABLE
(
	ROWNO	INT IDENTITY(1,1) NOT NULL,
	OYA	VARCHAR(20) NULL,
	KO	VARCHAR(20) NULL,
	NO	INT NULL,
);

DECLARE	@OYA VARCHAR(20) = 'A'; 
INSERT	INTO #TABLE
SELECT	OYA, KO, NO
FROM	(
	SELECT	OYA, KO, NO
	FROM	#TBL
	WHERE	OYA = @OYA
	) AS SPI

DECLARE	@ROWNO INT = 1;
WHILE	(SELECT	COUNT(*) FROM #TABLE WHERE ROWNO = @ROWNO) > 0
BEGIN
	IF OBJECT_ID(N'tempdb..#OYATBLS', N'U') IS NOT NULL DROP TABLE #OYATBLS;
	SELECT	ZNO, OYA, KO, NO
	INTO	#OYATBLS
	FROM	(
		SELECT	ROW_NUMBER() OVER(ORDER BY OYA, KO, NO) AS ZNO, OYA, KO, NO
		FROM	#TBL
		WHERE	OYA = (
			SELECT	KO
			FROM	#TABLE
			WHERE	ROWNO = @ROWNO
			)
		) AS ZITEMS

	DECLARE	@ZNO INT = 1;
	WHILE	(SELECT	COUNT(*) FROM #OYATBLS WHERE ZNO = @ZNO) > 0
	BEGIN
		IF OBJECT_ID(N'tempdb..#OYATBL', N'U') IS NOT NULL DROP TABLE #OYATBL;
		SELECT	OYA, KO, NO
		INTO	#OYATBL
		FROM	(
			SELECT	OYA, KO, NO
			FROM	#OYATBLS
			WHERE	ZNO = @ZNO
			) AS ZITEMS
		IF	(
			SELECT	(
				SELECT	COUNT(*)
				FROM	#TABLE
				WHERE	#TABLE.OYA = #OYATBL.OYA
					AND #TABLE.KO = #OYATBL.KO
					AND #TABLE.NO = #OYATBL.NO
				) AS CNT
			FROM	#OYATBL
			) <= 0
		BEGIN
			INSERT	INTO #TABLE
			SELECT	OYA, KO, NO
			FROM	(
				SELECT	OYA, KO, NO
				FROM	#OYATBL
				) AS ZITEM
		END
		IF OBJECT_ID(N'tempdb..#OYATBL', N'U') IS NOT NULL DROP TABLE #OYATBL;
		SET @ZNO = @ZNO + 1
	END

	IF OBJECT_ID(N'tempdb..#SIN', N'U') IS NOT NULL DROP TABLE #OYATBLS;
	SET	@ROWNO = @ROWNO + 1
END
SELECT	OYA, KO, NO
FROM	#TABLE
IF OBJECT_ID(N'tempdb..#TBL', N'U') IS NOT NULL DROP TABLE #TBL;
IF OBJECT_ID(N'tempdb..#TABLE', N'U') IS NOT NULL DROP TABLE #TABLE;

これを使用すると1秒で処理が完了した。
実際のデータは階層が４５あり、処理時間は３０秒近くかかっている。


例�@のやり方では計算上WITH で作成されるデータは５０００億個を超えるので、
最後に DISTINCT するのではなく途中で 重複データを消しながらデータを
作成していく方法はないのでしょうか？

■No92228 (犬夜叉 さん) に返信
> 例�@のやり方では計算上WITH で作成されるデータは５０００億個を超えるので、
> 最後に DISTINCT するのではなく途中で 重複データを消しながらデータを
> 作成していく方法はないのでしょうか？

階層化の際に [NO] 列は使っていないようなので、
OYA, KO だけで階層問い合わせして、最後に [NO] を逆引きできないでしょうか。

/*
CREATE TABLE TBL ( OYA CHAR(1), KO CHAR(1), [NO] int );

INSERT INTO TBL VALUES
  ('A', 'B', 1), ('A', 'B', 2)
, ('B', 'C', 1), ('B', 'C', 2)
, ('C', 'D', 1), ('C', 'D', 2)
, ('D', 'E', 1), ('D', 'E', 2)
, ('E', 'F', 1), ('E', 'F', 2)
, ('F', 'G', 1), ('F', 'G', 2)
, ('G', 'H', 1), ('G', 'H', 2)
, ('H', 'I', 1), ('H', 'I', 2)
, ('I', 'J', 1), ('I', 'J', 2)
, ('J', 'K', 1), ('J', 'K', 2)
, ('K', 'L', 1), ('K', 'L', 2)
, ('L', 'M', 1), ('L', 'M', 2)
, ('M', 'N', 1), ('M', 'N', 2)
, ('N', 'O', 1), ('N', 'O', 2)
, ('O', 'P', 1), ('O', 'P', 2)
, ('P', 'Q', 1), ('P', 'Q', 2)
, ('Q', 'R', 1), ('Q', 'R', 2)
, ('R', 'S', 1), ('R', 'S', 2)
, ('S', 'T', 1), ('S', 'T', 2)
, ('T', 'U', 1), ('T', 'U', 2)
, ('U', 'V', 1), ('U', 'V', 2)
, ('V', 'W', 1), ('V', 'W', 2)
, ('W', 'X', 1), ('W', 'X', 2)
, ('X', 'Y', 1), ('X', 'Y', 2)
, ('Y', 'Z', 1), ('Y', 'Z', 2)
;
*/

WITH CTE1 AS
(
  SELECT DISTINCT OYA, KO
  FROM TBL
), CTE2 AS (
  SELECT DISTINCT OYA, KO
  FROM CTE1
  WHERE OYA = @OYA
  UNION ALL
  SELECT spi2.OYA, spi2.KO
  FROM CTE1 AS spi2 INNER JOIN CTE2 ON spi2.OYA = CTE2.KO
)
SELECT * FROM TBL WHERE EXISTS(SELECT * FROM CTE2
WHERE TBL.OYA = CTE2.OYA AND TBL.KO = CTE2.KO)

■No92240 (魔界の仮面弁士) に追記
> WPF なのか ASP.NET なのか WinForm なのかわかりませんが、
> 初期状態ではノードを折りたたんでおいて、
> 展開された時に動的に子ノードを構築するようにしてみては如何でしょうか。


WinForm な例。

public partial class Form1 : Form
{
    public interface IEntry
    {
        string Oya { get; }
        string Ko { get; }
        int No { get; }
    }

    private class Entry : IEntry
    {
        public string Oya { get; }
        public string Ko { get; }
        public int No { get; }
        public Entry(string oya, string ko, int no)
        {
            Oya = oya; Ko = ko; No = no;
        }
    }

    private class EntryNode : TreeNode, IEntry
    {
        private readonly IEntry Entry;
        public string Oya { get { return Entry.Oya; } }
        public string Ko { get { return Entry.Ko; } }
        public int No { get { return Entry.No; } }
        public EntryNode(IEntry entry)
        {
            Entry = entry;
            Text = string.Format("{0}-{1}-{2}", Oya, Ko, No);
        }
    }


    private Entry[] Entries = CreateDemo().ToArray();
    private static IEnumerable<Entry> CreateDemo()
    {
        string demo = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        int len = demo.Length;
        for (int i = 1; i < len; i++)
        {
            string oya = demo[i - 1].ToString();
            string ko = demo[i].ToString();
            yield return new Entry(oya, ko, 1);
            yield return new Entry(oya, ko, 2);
        }
    }

    private TreeView tv;
    public Form1()
    {
        InitializeComponent();
        tv = new TreeView() { Dock = DockStyle.Fill };
        Controls.Add(tv);
        tv.BeforeExpand += tv_BeforeExpand;
    }
    private void Form1_Load(object sender, EventArgs e)
    {
        string oya = "A";
        foreach (var rootNode in Entries.Where(x => x.Oya == oya).Select(BuildNode))
        {
            tv.Nodes.Add(rootNode);
        }
    }

    private IEnumerable<Entry> GetChildren(string oya)
    {
        return Entries.Where(x => x.Oya == oya);
    }
    private EntryNode BuildNode(IEntry owner)
    {
        string oya = owner.Oya;
        EntryNode node = (owner as EntryNode) ?? new EntryNode(owner);
        if (GetChildren(node.Ko).Any())
        {
            node.Nodes.Add("***Dummy***");
        }
        return node;
    }

    private void tv_BeforeExpand(object sender, TreeViewCancelEventArgs e)
    {
        var oya = e.Node as EntryNode;
        if (oya == null) { return; }
        var fn = oya.FirstNode;
        if (fn == null) { return; }
        if (!(fn is IEntry))
        {
            fn.Remove();
            foreach (var childNode in Entries.Where(x => x.Oya == oya.Ko).Select(BuildNode))
            {
                oya.Nodes.Add(childNode);
            }
        }
    }
}