c#写的一个小程序，后台数据库是sql2008，打开仅500条记录的单据就要10秒钟

Question

这还是在LAN里测试的结果，用VPN还更慢，现在我把显示单据的代码写出来，求大家帮帮忙看看要怎么样修改？            string QueryPI_str = @"SELECT
                                       *
                                   FROM
                                       tb_pi
                                   WHERE
                                       PI_ID = '" + ThePIID + "'";
            SqlDataReader QueryPI_Dr = DBSetup.GetDR(QueryPI_str);
            QueryPI_Dr.Read();            string QueryDept_str = @"SELECT
                                         *
                                     FROM
                                         tb_dept
                                     WHERE
                                         Dept_ID = '" + QueryPI_Dr["Dept_ID"].ToString() + "'";
            SqlDataReader QueryDept_Dr = DBSetup.GetDR(QueryDept_str);
            QueryDept_Dr.Read();            string QueryUser_str = @"SELECT
                                         *
                                     FROM
                                         tb_user
                                     WHERE
                                         User_ID = '" + QueryPI_Dr["User_ID"].ToString() + "'";
            SqlDataReader QueryUser_Dr = DBSetup.GetDR(QueryUser_str);
            QueryUser_Dr.Read();            this.txtPIEditDate.Text = Convert.ToDateTime(QueryPI_Dr["PI_Date"]).ToShortDateString();
            this.txtPIEditDept.Text = QueryDept_Dr["Dept_Name"].ToString();
            this.txtPIEditUser.Text = QueryUser_Dr["RealName"].ToString();
            this.txtPIEditNO.Text = QueryPI_Dr["PI_NO"].ToString();            QueryUser_Dr.Close();
            QueryDept_Dr.Close();
            QueryPI_Dr.Close();              this.dgvPIEditLine.ColumnCount = 5;
            this.dgvPIEditLine.Columns[0].Name = "商品ID";
            this.dgvPIEditLine.Columns[1].Name = "条形码";
            this.dgvPIEditLine.Columns[2].Name = "商品";
            this.dgvPIEditLine.Columns[3].Name = "帐面数量";
            this.dgvPIEditLine.Columns[4].Name = "盘点数量";
            this.dgvPIEditLine.Columns[0].Visible = false;            string QueryLine_str = @"SELECT
                                  *
                              FROM
                                  tb_pi_line
                              WHERE
                                  PI_ID = '" + ThePIID + "'";
            SqlDataReader QueryLine_Dr = DBSetup.GetDR(QueryLine_str);            int i = 0;
            while (QueryLine_Dr.Read())
            {
                string sqlstr = @"SELECT
                                      *
                                  FROM
                                      tb_goods
                                  WHERE
                                      tb_goods.Goods_ID = '" + Convert.ToString(QueryLine_Dr["Goods_ID"]) + "'";
                SqlDataReader sqldr = DBSetup.GetDR(sqlstr);
                sqldr.Read();                this.dgvPIEditLine.Rows.Add();                this.dgvPIEditLine[0, i].Value = Convert.ToString(QueryLine_Dr["Goods_ID"]);
                this.dgvPIEditLine[1, i].Value = Convert.ToString(sqldr["Goods_Barcode"]);
                this.dgvPIEditLine[2, i].Value = Convert.ToString(sqldr["Goods_Name"]);
                this.dgvPIEditLine[3, i].Value = Convert.ToString(QueryLine_Dr["Acc_Qty"]);
                this.dgvPIEditLine[4, i].Value = Convert.ToString(QueryLine_Dr["Act_Qty"]);                i++;
                sqldr.Close();
            }            QueryLine_Dr.Close();

Answer 1

where后面的查询字段都有索引吧...这里用到太多的DataReader了.呵呵.怎么这么多次?不能一次解决吗?写在一个sql语句里面,或者是写在一个存储过程里面.和你现在差距还是挺大的.建议你优化一下,采用存储过程,建立一下索引.

Answer 2

  SqlDataReader sqldr = DBSetup.GetDR(sqlstr);
估计是红色的函数内部有问题,应该是打开数据库之后没有关闭的原因,多次打开数据库,
占用资源

Answer 3

GetDR是这样的：        public static SqlConnection OpenConn()
        {
            DBConn = new SqlConnection(conn_str);
            if (DBConn.State == ConnectionState.Closed)
            {
                DBConn.Open();
            }
            return DBConn;
        }
        public static SqlDataReader GetDR(string SQLstr)
        {
            DRCom = new SqlCommand(SQLstr, OpenConn());
            SqlDataReader TheDr;            try
            {
                TheDr = DRCom.ExecuteReader();
                return TheDr;
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误:" + ex.Message, "错误提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                if (OpenConn().State == ConnectionState.Open)
                {
                    OpenConn().Close();
                    OpenConn().Dispose();
                }
            }
        }

Answer 4

where后面的字段都是PRIMARY KEY

Answer 5

用临时表吧 ，你这样对SQL的折磨他不罢工已经不错了啊！

Answer 6

现在我修改了小小一点点，但效果不明显            string QueryPI_str = @"SELECT
                                        PI_NO,
                                        (SELECT
                                                Dept_Name
                                        FROM
                                                tb_dept
                                        WHERE
                                                tb_dept.Dept_ID = tb_pi.Dept_ID
                                        ) as Dept_Name,
                                        (SELECT
                                                RealName
                                        FROM
                                                tb_user
                                        WHERE
                                                tb_user.User_ID = tb_pi.User_ID
                                        ) as RealName,
                                        PI_Date
                                   FROM
                                       tb_pi
                                   WHERE
                                       PI_ID = '" + ThePIID + "'";
            SqlDataReader QueryPI_Dr = DBSetup.GetDR(QueryPI_str);
            QueryPI_Dr.Read();            this.txtPIEditDate.Text = Convert.ToDateTime(QueryPI_Dr["PI_Date"]).ToShortDateString();
            this.txtPIEditDept.Text = QueryPI_Dr["Dept_Name"].ToString();
            this.txtPIEditUser.Text = QueryPI_Dr["RealName"].ToString();
            this.txtPIEditNO.Text = QueryPI_Dr["PI_NO"].ToString();            QueryPI_Dr.Close();            this.dgvPIEditLine.ColumnCount = 5;
            this.dgvPIEditLine.Columns[0].Name = "商品ID";
            this.dgvPIEditLine.Columns[1].Name = "条形码";
            this.dgvPIEditLine.Columns[2].Name = "商品";
            this.dgvPIEditLine.Columns[3].Name = "帐面数量";
            this.dgvPIEditLine.Columns[4].Name = "盘点数量";
            this.dgvPIEditLine.Columns[0].Visible = false;            string QueryLine_str = @"SELECT
                                            Goods_ID,
                                            (SELECT
                                                    Goods_Barcode
                                            FROM
                                                    tb_goods
                                            WHERE
                                                    tb_goods.Goods_ID = tb_pi_line.Goods_ID
                                            ) as Goods_Barcode,
                                            (SELECT
                                                    Goods_Name
                                            FROM
                                                    tb_goods
                                            WHERE
                                                    tb_goods.Goods_ID = tb_pi_line.Goods_ID
                                            ) as Goods_Name,
                                            Acc_Qty,
                                            Act_Qty
                              FROM
                                  tb_pi_line
                              WHERE
                                  PI_ID = '" + ThePIID + "'";
            SqlDataReader QueryLine_Dr = DBSetup.GetDR(QueryLine_str);            int i = 0;
            while (QueryLine_Dr.Read())
            {
                this.dgvPIEditLine.Rows.Add();                this.dgvPIEditLine[0, i].Value = Convert.ToString(QueryLine_Dr["Goods_ID"]);
                this.dgvPIEditLine[1, i].Value = Convert.ToString(QueryLine_Dr["Goods_Barcode"]);
                this.dgvPIEditLine[2, i].Value = Convert.ToString(QueryLine_Dr["Goods_Name"]);
                this.dgvPIEditLine[3, i].Value = Convert.ToString(QueryLine_Dr["Acc_Qty"]);
                this.dgvPIEditLine[4, i].Value = Convert.ToString(QueryLine_Dr["Act_Qty"]);                i++;
                
            }            QueryLine_Dr.Close();

Answer 7

你不会是在debug 模式下面作调试吧？

Answer 8

你在 SQL SERVER 的企业管理器或者控制台执行一下你的语句，看看速度是不是也是这么慢？
你的 SQL 数据库机器的配置是怎么样的？使用存储过程可以提高速度，但是不会是秒级别的提高，现在用 10s，做成存储过程他也不会变成  1s 的。感觉是数据库的问题。

Answer 9

开那么多的sqlconnection和reader当然会慢啊

Answer 10

仔细看了看，一个inner join好像就可以搞定了
SELECT  a.Goods_ID,b.Goods_Barcode,b.Goods_Name
 FROM tb_pi_line a INNER JOIN tb_goods b ON a.GoodsID = b.GoodsID
WHERE a.PI_ID = '" + ThePIID + "'";

Answer 11

楼主，以后写SQL,条件里面不要用 pi_id ='" +thepiid + "'
最好参数化
用户输个单引号就

Answer 12

是Release模式下
机器配置不算差，AMD 8450/2G/悍马HA06GT
我在13楼改了一些语句，效果不明显，后来改为dgv绑定数据集：            string QueryLine_str = @"SELECT
                                            Goods_ID as 商品ID,
                                            (SELECT
                                                    Goods_Barcode
                                            FROM
                                                    tb_goods
                                            WHERE
                                                    tb_goods.Goods_ID = tb_pi_line.Goods_ID
                                            ) as 条形码,
                                            (SELECT
                                                    Goods_Name
                                            FROM
                                                    tb_goods
                                            WHERE
                                                    tb_goods.Goods_ID = tb_pi_line.Goods_ID
                                            ) as 商品,
                                            Acc_Qty as 帐面数量,
                                            Act_Qty as 盘点数量
                              FROM
                                  tb_pi_line
                              WHERE
                                  PI_ID = '" + ThePIID + "'";
            
            this.dgvPIEditLine.DataSource = DBSetup.GetDS(QueryLine_str).Tables[0];
改成这样之后，打开单据可以在1秒左右完成，但我还是想一条一条刻录往dgv里添加，不知道要怎么办。另外如果用存储过程的话，那我要用什么来接收存储过程返回的数据呢？是datatable还是dataset?

Answer 13

SELECT A.Goods_ID,B.Goods_BarCode,B.Goods_Name,Acc_Qty FROM tb_pi_line A INNER JOIN tb_Goods B ON A.Goods_ID=B.Goods_ID
    WHERE condition...

Answer 14

string QueryLine_str = @"SELECT
                                            Goods_ID as 商品ID,
                                            (SELECT
                                                    Goods_Barcode
                                            FROM
                                                    tb_goods
                                            WHERE
                                                    tb_goods.Goods_ID = tb_pi_line.Goods_ID
                                            ) as 条形码,
                                            (SELECT
                                                    Goods_Name
                                            FROM
                                                    tb_goods
                                            WHERE
                                                    tb_goods.Goods_ID = tb_pi_line.Goods_ID
                                            ) as 商品,
                                            Acc_Qty as 帐面数量,
                                            Act_Qty as 盘点数量
                              FROM
                                  tb_pi_line
                              WHERE
                                  PI_ID = '" + ThePIID + "'";
            
           DataTable dt =  DBSetup.GetDS(QueryLine_str).Tables[0];            foreach(DataRow  dr in dt.Rows)
           {
               dr["商品ID"]; //这样遍历其中的每一行数据. 
               dr["条形码"];
           } 

Answer 15

DataGridView填数据确实很慢，直接绑定数据源效果要好些，但对上万的记录依然不可接受，VitualMode效果倒很明显，只是本身也不太完善，有些Bug