.net dataTable对象的处理性能

今天做了个试验, 试验使用DataTable对象的过滤性能与手工比较性能.

背景: 一个表中有80W条记录, 有两个字段field1, field2, 其中distinct field1有30W条记录,相当于第个field1对应有3条field2记录, 程序的目的是field1相同时合并field2, 即最终输出为:

field10: field20;field21;field22...

...

即最终输出30多W行, 第一列是field1,第二列是field1对应的不同field2值的合并串. 使用了下面两种方法,结果大为吃惊:

>ConsoleApplication1.exe
loop1:2008-12-18 17:46:08
begin:2008-12-18 17:46:44
00:00:00.4720000
Filter:2008-12-18 17:46:44
begin:2008-12-18 17:47:33
00:00:43.7490000

程序均未算上从DB取数据的时间,只算了处理数据的时间, 发现DataTable的filter性能差得实在太远,估计有60倍的差距. 当然上面的数据中field1的记录数也太多,过滤次数太多, 这也可能是个原因.

不过以后还是使用下面的方法, 简单易理解, 性能也好, 少用select()过滤了,除非数据量非常少.

 1class Program
 2    {
 3        static OdbcConnection con = new OdbcConnection("dsn=odbc_xxx;uid=XXX;pwd=XXX");
 4
 5        static void Main(string[] args)
 6        {
 7            con.Open();
 8
 9
            Loop1();

            Filter();

            con.Close();
        }

        static void Loop1()
        {

            Console.WriteLine("loop1:" + DateTime.Now.ToString());

            string sql = "select field1,field2 from dm_pmart.xdy_coach_eachone group by 1,2 order  by 1";
            OdbcCommand cmd = new OdbcCommand(sql, con);
            OdbcDataAdapter da = new OdbcDataAdapter(cmd);
            DataSet dsLoop = new DataSet();
            da.Fill(dsLoop);

            Console.WriteLine("begin:" + DateTime.Now.ToString());
            DateTime dt = DateTime.Now;

            using (StreamWriter sw = File.CreateText("loop1.txt"))
            {
                string str = "";
                string lastCoach = "";
                foreach (DataRow row in dsLoop.Tables[0].Rows)
                {
                    if (row[0].ToString().Equals(lastCoach))
                        str += ";" + row[1].ToString();
                    else
                    {
                        if (str != "")
                            sw.WriteLine(lastCoach + ":" + str);

                        lastCoach = row[0].ToString();
                        str = row[1].ToString();
                    }
                }
            }

            Console.WriteLine(DateTime.Now.Subtract(dt).ToString());
        }

        static void Filter()
        {
            Console.WriteLine("Filter:" + DateTime.Now.ToString());

            string sql = "select field1 from dm_pmart.xdy_coach_eachone group by 1";
            OdbcCommand cmd = new OdbcCommand(sql, con);
            OdbcDataAdapter da = new OdbcDataAdapter(cmd);
            DataSet dsCoach = new DataSet();
            da.Fill(dsCoach);

            sql = "select field1,field2 from dm_pmart.xdy_coach_eachone group by 1,2";
            da.SelectCommand.CommandText = sql;
            DataSet dsAll = new DataSet();
            da.Fill(dsAll);

            Console.WriteLine("begin:" + DateTime.Now.ToString());
            DateTime dt = DateTime.Now;
            using (StreamWriter sw = File.CreateText("filter.txt"))
            {

                foreach (DataRow row in dsCoach.Tables[0].Rows)
                {
                    //DataView dv = new DataView(dsAll.Tables[0], "field1='" + row[0].ToString() + "'", "", DataViewRowState.CurrentRows);
                    string str = "";
                    foreach (DataRow dr in dsAll.Tables[0].Select("field1='" + row[0].ToString() + "'"))
                    {
                        str += ";" + dr[1].ToString();
                    }
                    sw.WriteLine(row[0].ToString() + ":" + str);
                }
            }

            Console.WriteLine(DateTime.Now.Subtract(dt).ToString());

        }
    }