[第一次发贴]如何提高如下sql的查询效率（C#），在线

Question

现在有如下六张表：1.学科表[Subject]（sub_id,subName）,
2.版本表[Edition](edi_id,ediName)，
3.学科版本关系表[sub_edi_rel](ser_id,sub_id,edi_id),--关系表ID，学科ID，版本ID（因为学科和版本是同级，所以..）
4.册表[Volume](vol_id,volName,)，
5.章表[Chapter](cha_id,chaName,vol_id)，
6.节表[Section](sec_id,secName,cha_id)他们的关系是1是2的父级，2是3是父级，3是4是父级，4是5的父级。
介于层次关系比较多，所以呢查询起来非常慢，请问怎么样提高查询效率呢？
最好提供C#代码和sqlserver代码。希望代码简洁，条理清晰，有注释。最重要的是效率要高。
欢迎大家积极发言。来者感谢之。分数分配不用担心我会尽量做到合理分配，呵呵。

Answer 1

另外，我想说明的是我目前用的是一层层循环的方式来取的值，但是效果非常不好，效率太低了。
同时又必须得一下子全部取出来。因为我要把取出来的数据作为xml串传给别人使用的。所以大家最好不要说让我用一层层加载的方式

Answer 2

再多嘴一句，呵呵，最好写的方法是放到TreeView中，谢了。我已经把treeview转化成xml的代码写好了的。最后生成的xml格式如下（格式仅供参考）<?xml version="1.0" encoding="utf-8" ?><Data>
  <Subject id="1010" value="小学语文">
    <Volume id="56" value="苏教国标版">
    </Volume>
    <Volume id="63" value="苏教版">
      <Chapter id="322" value="第七册">
        <Section id="1411" value="第一单元：诗歌">
        </Section>
        <Section id="1412" value="第二单元：爱国主义教育">
        </Section>
      </Chapter>
      <Chapter id="323" value="第八册">      </Chapter>
    </Volume>
  </Subject>
  <Subject id="1011" value="小学数学" />
</Data>

Answer 3

要查询的数据就是1，2，4，5，6表中的所有ID和Name
查询的条件让我细细说来：
首先查出Subject表中所有数据，然后以subject表中id和为条件，查询他的所有儿子，以此类推。
最终返回的是一个包括所有数据的TreeView树对象
我把我写的一部分代码拿出来示例一下，你们估计就明白了。/// <summary>
        /// 获取学科知识树的方法
        /// </summary>
        /// <returns></returns>        
        public string GetXmlTree()
        {
            TreeView trv = new TreeView();
            string strSql = "select sub_id,sub_name from subject where tier='2'";
            DataTable dt = GetDataTable(strSql);
            if (dt == null) return "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                TreeNode parent = new TreeNode(dt.Rows[i][1].ToString());
                parent.Name = dt.Rows[i][0].ToString();
                string parentID = parent.Name;
                //strSql += "'" + parentID + "'";
                AppendEdition(parent,parentID);
                trv.Nodes.Add(parent);
            }
            return ConvertTreeViewToXml(trv);//这个是把TreeView生成xml的方法，不用管
        } //添加版本
        private void AppendEdition(TreeNode parent, string parentID)
        {
            try
            {
                string sql = strSqls[0];
                sql += "'" + parentID + "'";
                DataTable dt = GetDataTable(sql);
                if (dt == null) return;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    TreeNode child = new TreeNode(dt.Rows[i][1].ToString());
                    child.Name = dt.Rows[i][0].ToString();
                    AppendVolume(child, child.Name);
                    parent.Nodes.Add(child);
                }
            }
            catch (Exception ex)
            {
                throw ;
            }
        }
//..添加册，章，节的方法以和上类似,但是这种效率太低，且十分不好

Answer 4

我要把所有的数据先是转化成TreeView对象显示出来，然后再处理成xml串。
处理生成xml我已经弄好了。关键是生成treeview的方法，想弄个好的。不然数据特大跑不起来。
上面给的xml串只是我生成后的一个样例，实际数据要远远超过于此。

Answer 5

希望能弄出具体的代码，谢了。光说存储过程，我想应该不是那么好实现的。
而且取出所有数据的sql语句对我来说是没意义的，那个很好实现，而我却是要实现层次结构的那种。

Answer 6

试试这样:
把这几个表连接起来并按照 父ID、子ID 的顺序排序作为一个结果表一次把所有内容查询出来
然后一次循环就可以取出数据并构成xml了。

Answer 7

建立索引
建立树结构
http://topic.csdn.net/u/20090911/11/9268a8f2-4742-4f48-b688-fcf10e4b1164.html

Answer 8

嗯。我想要的是自己定义的那种xml格式，而不是用datatable对象生成的xml那种格式。
所以呢， 还是蛮麻烦的。 又要考虑生成效率，又要考虑代码质量。
10楼说的也是有道理的。
意思应该是在取出的datatable里取吧。我晚点没办法再试试。
其实我不仅仅想要思想，还想要更简洁的代码。呵呵。所以还是不介意大家拿出自认为不错的代码，供我们一起学习下。谢了。 

Answer 9

或者查6次把所有表查出来且按照 父ID,子ID 排序
这样就直接从父到子往树上加就可以了，在加的时候判断当父变化时就回到上一级添加新父，然后继续添加子级，这样是不需要多余的数据访问的。

Answer 10

根据我上面提的思路，用Linq To xml写了一个，直接上代码void Test()
{
    List<Subject> Subjects = new List<Subject>() { 
        new Subject(){SubID="1",SubName="小学语文"},
        new Subject(){SubID="2",SubName="小学数学"},
        new Subject(){SubID="3",SubName="中学语文"},
        new Subject(){SubID="4",SubName="中学物理"},
    };//怎么取数据略
    List<Edition> Editions = new List<Edition>() { 
        new Edition(){EdiID="1",EdiName="苏教国标版"},
        new Edition(){EdiID="2",EdiName="苏教版"},
        new Edition(){EdiID="3",EdiName="人教版"},
    };//怎么取数据略
    List<SubEdition> SubEditons = new List<SubEdition>() { 
        new SubEdition(){SEID="101",SubID="1",EdiID="1"}
    };//怎么取数据略
    List<Chapter> Chapters = new List<Chapter>() { 
        new Chapter(){ChapterID="10101",SEID="101",ChapterName="第七册"},
        new Chapter(){ChapterID="10102",SEID="101",ChapterName="第八册"},
        new Chapter(){ChapterID="10901",SEID="109",ChapterName="第N册"}//一条无关的数据
    };//怎么取数据略
    List<Section> Sections = new List<Section>() { 
        new Section(){SectionID="1010101",ChapterID="10101",SectionName="第一单元：诗歌"},
        new Section(){SectionID="1010102",ChapterID="10101",SectionName="第二单元：爱国主义教育"},
    };//怎么取数据略
    XElement root = new XElement("Data", Subjects.Select(
        iSubject => new XElement("Subject", new object[] 
        { 
            new XAttribute("id",iSubject.SubID),
            new XAttribute("value", iSubject.SubName), 
            SubEditons.Where(oSubEdtion => oSubEdtion.SubID == iSubject.SubID).Select(
            iSubEditon => new XElement("SubEdion",new object[]
            {
                new XAttribute("id",iSubEditon.SEID),
                new XAttribute("value",Editions.Find(
                    oEdition=>oEdition.EdiID==iSubEditon.EdiID).EdiName),
                Chapters.Where(oChapter=>oChapter.SEID==iSubEditon.SEID).Select(
                iChapter=>new XElement("Chapter",new object[]
                {
                    new XAttribute("id",iChapter.ChapterID),
                    new XAttribute("value",iChapter.ChapterName),
                    Sections.Where(oSection=>oSection.ChapterID==iChapter.ChapterID).Select(
                    iSection=>new XElement("Section",new object[]
                    {
                        new XAttribute("id",iSection.SectionID),
                        new XAttribute("value",iSection.SectionName)
                    }))
                }))
            })) 
        }))
    );
    root.Save("C:\\Data.xml");
}
    public class Subject
    {
        public string SubID { set; get; }
        public string SubName { set; get; }
    }
    public class Edition
    {
        public string EdiID { set; get; }
        public string EdiName { set; get; }
    }
    public class SubEdition
    {
        public string SEID { set; get; }
        public string SubID { set; get; }
        public string EdiID { set; get; }
    }
    public class Chapter
    {
        public string ChapterID { set; get; }
        public string SEID { set; get; }
        public string ChapterName { set; get; }
    }
    public class Section
    {
        public string SectionID { set; get; }
        public string ChapterID { set; get; }
        public string SectionName { set; get; }
    }

Answer 11

15楼意思，我看了下大体明白是什么意思。
之前你没写代码时我也知道你的意思，可是我用的是05，没有linq的。
我在想用你说的方法，还不是一样的要很多个循环的么？
跟我之前做的唯一的区别就是你说的那种方式是一次取出所有数据，然后在后台处理。
速度肯定是有一定的提升，但是提升的也不是太多。
难道就没有更好的方法了么？

Answer 12

全加索引那就是全文了,一个个去加你没有那么大的索引空间
我的意思是,你的某些字段是否经常用到,且用它做为条件或分组等等等等操作,如果是,那有索引肯定会快的
表很多,若你left join 那不用问,数据肯定很大.

Answer 13

with  name  as ()我相信你们会喜欢的。

Answer 14

很明显像我种情况，常用的字段肯定是主键和名称字段了。
加索引也只能加在主键ID上了。
要么就是连Name也加上，但是后者明显不太合理。
至于你所说的left join视图的问题，我目前正在用这种方式。不然还是得不断的在后台连数据库，就算加了索引，我感觉还是不会太快。