none
asp.net页面反应迟钝 RRS feed

  • 问题

  • 我是从数据库中两个表中读取数据出来(返回为dataTable类型),但是经过处理,然后在放在另一个dataTable中,然后绑定到 gridview中的。但是页面显示得很慢,请问有什么解决办法?

    2010年2月3日 1:43

答案

  • 这个很长...大概看了下,主要原因是你在第次循环中都去访问了数据。

    我觉得下面三种方式都对你有帮助。

    1 处理在数据库中进行,但这样你会面临较大的改动。
    2 一次性将循环所需要的所有数据取出来为循环所有,目的是在于减少与数据库之间的往返次数。
    3 如果数据行数多,不想做太多的改动,可以试着在你这个方法都使用一个 connection。第一次取数的打开连接,循环完毕后关闭连接,中间不需要再打开和关闭连接。目的是减少连接打开关闭时间,这只有在数据较多的情况下才会有明显的效果。

    知识改变命运,奋斗成就人生!
    2010年2月3日 3:19
    版主

全部回复

  • 你好!

    数据多吗?

    你从数据库读取数据的时时间是多少,合并的时间是多少,最终显示完成的时间是多少?分别测一下,然后再针对问题分析。

    另外请不要重复发帖。

    知识改变命运,奋斗成就人生!
    2010年2月3日 1:46
    版主
  • 十几个字段,怎么测试这些的?
    2010年2月3日 1:54
  • 比较简单的方式如下:

    DateTime begin = DateTime.Now;

    // your code

    DateTime end= DateTime.Now;

    TimeSpan ts = end - begin;

    double span = ts.Total....

    知识改变命运,奋斗成就人生!
    2010年2月3日 1:59
    版主
  • 读取用了0.12s,处理数据用了12.04s,最后显示用了0.02s
    2010年2月3日 2:28
  • 那问题,明显出在你合并 datatable 的时候,你可以仔细检查一下这部分。若有问题再把你详细的代码发出来。
    知识改变命运,奋斗成就人生!
    2010年2月3日 2:30
    版主
  • 我是用两个dataTable接收数据库返回的数据,从这两个表中逐行读取数据,通过计算处理之后,再添加到两个表上,然后把两个表绑定到gridview中。
    2010年2月3日 2:34
  • 你把合并代码发出来吧,大家帮你分析一下看看能否优化。
    知识改变命运,奋斗成就人生!
    2010年2月3日 2:42
    版主
  • public void BandData()
        {
           
            getFactory();
            getTime();
            getOperate();
            //绑定数据
            ConnectionSql sqlcon = new ConnectionSql();
            //string sql = "select * from FLEXA";
            string sql = "select LINE,f.mac_no as mac_no,mach_date,f.mac_id as mac_id,fl_running as Running,fl_loading as loading,fl_stopped as stopped,fl_wnext,fl_wprevious,fl_wpart ,fl_wswitch,fl_disconnected as disconnected,idle,maintain,no_energy,wait,eating,no_arrange,cline from FLEXA as f left join pmMACH as p on p.mac_no=f.mac_no and p.mac_id=f.mac_id where ";
            sql = sql + "  p.Location " + fac+" and ";
            sql = sql + " fl_running " + range;
            sql = sql + " and mach_date >='" + startTime + "' and mach_date <='" + endTime+"'";
            sql = sql + " order by Running " + sortBy;
            DataTable newdt = new DataTable();
            //新建列
            System.Type column = System.Type.GetType("System.String");
            DataColumn n1 = new DataColumn("Line", column);
            DataColumn n2 = new DataColumn("Machine", column);
            DataColumn n3 = new DataColumn("Date", column);
            DataColumn n4 = new DataColumn("Running", column);
            DataColumn n5 = new DataColumn("Loading", column);
            DataColumn n6 = new DataColumn("Stopped", column);
            DataColumn n7 = new DataColumn("Wait next", column);
            DataColumn n8 = new DataColumn("Wait previous", column);
            DataColumn n9 = new DataColumn("Wait part", column);
            DataColumn n10 = new DataColumn("Wait switch", column);
            DataColumn n11 = new DataColumn("Disconnected", column);
            DataColumn n12 = new DataColumn("Idle", column);
            DataColumn n13 = new DataColumn("Maintain", column);
            DataColumn n14 = new DataColumn("No_energe", column);
            DataColumn n15 = new DataColumn("Wait", column);
            DataColumn n16 = new DataColumn("Eating", column);
            DataColumn n17 = new DataColumn("No_arrange", column);
            DataColumn ttleff = new DataColumn("TTL Eff", column);
            DataColumn mceff = new DataColumn("M/C Eff", column);
            newdt.Columns.Add(n1);
            newdt.Columns.Add(n2);
            newdt.Columns.Add(n3);
            newdt.Columns.Add(n4);
            newdt.Columns.Add(n5);
            newdt.Columns.Add(n6);
            newdt.Columns.Add(n7);
            newdt.Columns.Add(n8);
            newdt.Columns.Add(n9);
            newdt.Columns.Add(n10);
            newdt.Columns.Add(n11);
            newdt.Columns.Add(n12);
            newdt.Columns.Add(n13);
            newdt.Columns.Add(n14);
            newdt.Columns.Add(n15);
            newdt.Columns.Add(n16);
            newdt.Columns.Add(n17);
            newdt.Columns.Add(ttleff);
            newdt.Columns.Add(mceff);
            DataTable dt = sqlcon.executeTest(sql);
            //end1 = DateTime.Now;
            //TimeSpan t1 = end1 - begin;
            //System.Console.Write("时间:" +t1);
            if (dt.Rows.Count > 0)
            {
                //获取总行数,用于画饼图
                totalRow = dt.Rows.Count;
                //各阶段数值的个数
                //取数据绑定到 显示平均值的table 中
                float sum_running = 0, sum_loading = 0, sum_stopped = 0, sum_wnext = 0, sum_wprevious = 0, sum_wpart = 0, sum_switch = 0, sum_disconnected = 0, sum_idle = 0;
                float sum_maintain = 0, sum_no_energy = 0, sum_wait = 0, sum_eating = 0, sum_no_arrange = 0,sum_cline=0,sum_eff=0;
                string ave_ttl="",ave_mc="";
                string mac_no = "",mach_date="";
                int mac_id;
                foreach (DataRow row in dt.Rows)
                {
                    DataRow newdtaRow = newdt.NewRow();
                    //确定的值先赋值给newdt
                    newdtaRow["Line"] = row["LINE"].ToString();
                    newdtaRow["Machine"] = row["mac_no"].ToString();;
                    newdtaRow["Date"] =Convert.ToDateTime(row["mach_date"]).ToShortDateString() ;
                    newdtaRow["Running"] = row["Running"].ToString();
                    newdtaRow["Loading"] = row["loading"].ToString();
                    newdtaRow["Stopped"] = row["stopped"].ToString();
                    newdtaRow["Wait next"] = row["fl_wnext"].ToString();
                    newdtaRow["Wait previous"] = row["fl_wprevious"].ToString();
                    newdtaRow["Wait part"] =row["fl_wpart"].ToString() ;
                    newdtaRow["Wait switch"] = row["fl_wswitch"].ToString();
                    newdtaRow["Disconnected"] = row["disconnected"].ToString();
                    newdtaRow["Idle"] = row["idle"].ToString();
    
    
                    float running = 0, loading = 0, stopped = 0, wnext = 0, wprevious = 0, wpart = 0, _switch = 0, disconnected = 0, idle = 0, maintain = 0, no_energy = 0, wait = 0, eating = 0, no_arrange = 0,cline=0;
                    mac_no = row["mac_no"].ToString();
                    mach_date = Convert.ToDateTime(row["mach_date"]).ToShortDateString();
                    mac_id = Convert.ToInt16(row["mac_id"]);
                    running = Convert.ToSingle(row["Running"].ToString());
                    loading = Convert.ToSingle(row["loading"].ToString());
                    stopped = Convert.ToSingle(row["stopped"].ToString());
                    wnext = Convert.ToSingle(row["fl_wnext"].ToString());
                    wprevious = Convert.ToSingle(row["fl_wprevious"].ToString());
                    wpart = Convert.ToSingle(row["fl_wpart"].ToString());
                    _switch = Convert.ToSingle(row["fl_wswitch"].ToString());
                    disconnected = Convert.ToSingle(row["disconnected"].ToString());
                    idle = Convert.ToSingle(row["idle"].ToString());
                   
                    //查询保养,停电,停气,吃饭,PMC没安排时间
                    string _sql="select mac_no, [maintain]=sum(case sorts when 's03' then total end),[no_energy]=sum(case sorts when 's12' then total end),";
                    _sql=_sql+"[wait]=sum(case sorts when 's07' then total end),[eating]=sum(case sorts when 's11' then total end),";
                    _sql=_sql+"[noarrange]=sum(case sorts when 's13' then total end),[changeover]=sum(case sorts when 's10' then total end) ";
                    _sql=_sql+"from pmdown where mac_no='"+mac_no+"' and workdate='"+mach_date+"' and mac_id='"+mac_id+"' group by mac_no";
                    ConnectionSql conn=new ConnectionSql ();
                    DataTable temp=conn.executeTest(_sql);
                    if (temp.Rows.Count > 0)
                    {
                        if (temp.Rows[0]["maintain"].ToString() != "")
                        {
                            maintain = Convert.ToSingle(temp.Rows[0]["maintain"].ToString()) * 100 / (60 * 24);
                            if (maintain > 16.67)
                            {
                                maintain = 16.67f;
                            }
                        }
                        if (temp.Rows[0]["no_energy"].ToString() != "")
                        {
                            no_energy = Convert.ToSingle(temp.Rows[0]["no_energy"].ToString()) * 100 / (60 * 24);
                        }
                        if (temp.Rows[0]["wait"].ToString() != "")
                        {
                            wait = Convert.ToSingle(temp.Rows[0]["wait"].ToString()) * 100 / (60 * 24);
                        }
                        if (temp.Rows[0]["eating"].ToString() != "")
                        {
                            eating = Convert.ToSingle(temp.Rows[0]["eating"].ToString()) * 100 / (60 * 24);
                        }
                        if (temp.Rows[0]["noarrange"].ToString() != "")
                        {
                            no_arrange = Convert.ToSingle(temp.Rows[0]["noarrange"].ToString()) * 100 / (60 * 24);
                        }
                        if (temp.Rows[0]["changeover"].ToString() != "")
                        {
                            cline = Convert.ToSingle(temp.Rows[0]["changeover"].ToString()) * 100 / (60 * 24);
                        }
                    }
                    temp.Dispose();
                    newdtaRow["Maintain"] =maintain;
                    newdtaRow["No_energe"] =no_energy;
                    newdtaRow["Wait"] =wait;
                    newdtaRow["Eating"] =eating;
                    newdtaRow["No_arrange"] =no_arrange;
                 
                    //统计总和
                    if(disconnected!=100 && (disconnected+no_arrange+running)<=100)
                    {
                         sum_eff=sum_eff+100;
                         sum_running = sum_running + running;
                         sum_loading = sum_loading + loading;
                         sum_stopped = sum_stopped + stopped;
                         sum_wnext = sum_wnext + wnext;
                         sum_wprevious = sum_wprevious + wprevious;
                         sum_wpart = sum_wpart + wpart;
                         sum_switch = sum_switch + _switch;
                         sum_disconnected = sum_disconnected + disconnected;
                         sum_idle = sum_idle + idle;
                    }
                    if(disconnected<=50)
                    {
                        sum_maintain = sum_maintain + maintain;
                        sum_no_arrange = sum_no_arrange + no_arrange;  
                    }
                    sum_no_energy = sum_no_energy + no_energy;
                    sum_wait = sum_wait + wait;
                    sum_eating = sum_eating + eating;
                    sum_cline = sum_cline + cline;
                    //计算ttl_eff,mc_eff的值
                    float ttl_eff=0,mc_eff=0;
                    if(disconnected>50 && disconnected!=0)
                    {
                        if (disconnected == 100)
                        {//分母为零时
                            ttl_eff = 0;
                        }
                        else
                        {
                            ttl_eff = (running + loading) * 100 / (100 - disconnected);
                        }
                    }
                    else if(100-disconnected-maintain-cline!=0)
                    {
                        ttl_eff=(running+loading)*100/(100-disconnected-maintain-cline);
                    }
                     if(disconnected>50 && disconnected!=0)
                    {
                        if (disconnected == 100)
                        {//分母为零时
                            mc_eff = 0;
                        }
                        else
                        {
                            mc_eff = (running + loading) * 100 / (100 - disconnected);
                        }
                    }
                    else if(100-disconnected-maintain-cline-no_energy-no_arrange-wait-eating!=0)
                    {
                        mc_eff=(running+loading)*100/(100-disconnected-maintain-cline-no_energy-no_arrange-wait-eating);
                    }
                    newdtaRow["TTL EFF"] =ttl_eff;
                    newdtaRow["M/C EFF"] = mc_eff;
                    newdt.Rows.Add(newdtaRow);
                    //获取饼图数据 
                    if (ttl_eff < 20)
                    {
                        small20++;
                    }
                    else if (ttl_eff >= 20 && ttl_eff < 40)
                    {
                        bw20and40++;
                    }
                    else if (ttl_eff >= 40 && ttl_eff < 60)
                    {
                        bw40and60++;
                    }
                    else if (ttl_eff >= 60 && ttl_eff < 80)
                    {
                        bw60and80++;
                    }
                    else large80++;
                }
                // 设置显示格式,保留2位小数
                string s = sum_running.ToString("F2");
                //新创建一个表averageData
                DataTable averageDt = new DataTable("averageData");
                //定义列类型
                System.Type columnType = System.Type.GetType("System.Single");
                System.Type columnType1 = System.Type.GetType("System.Decimal");
                System.Type No1Tpye = System.Type.GetType("System.String");
                //定义列字段  
                DataColumn c1 = new DataColumn("type", No1Tpye);
                DataColumn c2 = new DataColumn("Running", No1Tpye);
                DataColumn c3 = new DataColumn("Loading", No1Tpye);
                DataColumn c4 = new DataColumn("Stopped", No1Tpye);
                DataColumn c5 = new DataColumn("Wait next", No1Tpye);
                DataColumn c6 = new DataColumn("Wait previous", No1Tpye);
                DataColumn c7 = new DataColumn("Wait part", No1Tpye);
                DataColumn c8 = new DataColumn("Wait switch", No1Tpye);
                DataColumn c9 = new DataColumn("Disconnected", No1Tpye);
                DataColumn c10 = new DataColumn("Idle", No1Tpye);
                DataColumn c11 = new DataColumn("Maintain", No1Tpye);
                DataColumn c12 = new DataColumn("No_energe", No1Tpye);
                DataColumn c13 = new DataColumn("Wait", No1Tpye);
                DataColumn c14 = new DataColumn("Eating", No1Tpye);
                DataColumn c15 = new DataColumn("No_arrange", No1Tpye);
                DataColumn c16 = new DataColumn("TTL EFF", No1Tpye);
                DataColumn c17 = new DataColumn("M/C EFF", No1Tpye);
                //添加入表
                averageDt.Columns.Add(c1);
                averageDt.Columns.Add(c2);
                averageDt.Columns.Add(c3);
                averageDt.Columns.Add(c4);
                averageDt.Columns.Add(c5);
                averageDt.Columns.Add(c6);
                averageDt.Columns.Add(c7);
                averageDt.Columns.Add(c8);
                averageDt.Columns.Add(c9);
                averageDt.Columns.Add(c10);
                averageDt.Columns.Add(c11);
                averageDt.Columns.Add(c12);
                averageDt.Columns.Add(c13);
                averageDt.Columns.Add(c14);
                averageDt.Columns.Add(c15);
                averageDt.Columns.Add(c16);
                averageDt.Columns.Add(c17);
                //添加行数据
                DataRow averageRow = averageDt.NewRow();
                //IFormatProvider provider = "C2";
                averageRow["type"] = "平均值";
                averageRow["Running"] = (sum_running / (sum_eff/100)).ToString("F2") + "%";
                averageRow["Loading"] = (sum_loading / (sum_eff/100)).ToString("F2") + "%";
                averageRow["Stopped"] = (sum_stopped / (sum_eff/100)).ToString("F2") + "%";
                averageRow["Wait next"] = (sum_wnext / (sum_eff/100)).ToString("F2") + "%";
                averageRow["Wait previous"] = (sum_wprevious / (sum_eff/100)).ToString("F2") + "%";
                averageRow["Wait part"] = (sum_wpart / (sum_eff/100)).ToString("F2") + "%";
                averageRow["Wait switch"] = (sum_switch / (sum_eff/100)).ToString("F2") + "%";
                averageRow["Disconnected"] = (sum_disconnected / (sum_eff/100)).ToString("F2") + "%";
                averageRow["Idle"] = (sum_idle / (sum_eff/100)).ToString("F2") + "%";
                averageRow["Maintain"] = (sum_maintain / (sum_eff/100)).ToString("F2") + "%";
                averageRow["No_energe"] = (sum_no_energy / (sum_eff/100)).ToString("F2") + "%";
                averageRow["Wait"] = (sum_wait /(sum_eff/100)).ToString("F2") + "%";
                averageRow["Eating"] = (sum_eating / (sum_eff/100)).ToString("F2") + "%";
                averageRow["No_arrange"] = (sum_no_arrange / (sum_eff/100)).ToString("F2") + "%";
                ave_ttl=((sum_running+sum_loading)*100/(sum_eff-sum_disconnected-sum_maintain-sum_cline)).ToString("F2") + "%";
                ave_mc = ((sum_running + sum_loading) * 100 / (sum_eff - sum_disconnected - sum_maintain - sum_cline - sum_no_energy - sum_no_arrange - sum_wait - sum_eating)).ToString("F2") + "%";
    
                averageRow["TTL EFF"] =ave_ttl;
                averageRow["M/C EFF"] = ave_mc;
    
                averageDt.Rows.Add(averageRow);
                //end2 = DateTime.Now;
                //TimeSpan t2 = end2 - begin;
                this.reportGridview1.Visible = true;
                this.reportGridview1.DataSource = newdt;
                this.reportGridview1.DataBind();
                this.averageGridview.Visible = true;
                this.averageGridview.DataSource = averageDt;
                this.averageGridview.DataBind();
                //查看饼图
                this.ShowPie.NavigateUrl = "~/flexa/drawPie.aspx?total="+totalRow+"&count1="+small20+"&count2="+bw20and40+"&count3="+bw40and60+"&count4="+bw60and80+"&count5="+large80;
            }   
            else
            {
                dt.Dispose();
                //查询无数据时
                this.tipLabel.Text = "該廠在此查詢范圍內無數據!";
                this.reportGridview1.Visible = false;
                this.averageGridview.Visible = false;
            }
          
        }
    2010年2月3日 2:54
  • 这个很长...大概看了下,主要原因是你在第次循环中都去访问了数据。

    我觉得下面三种方式都对你有帮助。

    1 处理在数据库中进行,但这样你会面临较大的改动。
    2 一次性将循环所需要的所有数据取出来为循环所有,目的是在于减少与数据库之间的往返次数。
    3 如果数据行数多,不想做太多的改动,可以试着在你这个方法都使用一个 connection。第一次取数的打开连接,循环完毕后关闭连接,中间不需要再打开和关闭连接。目的是减少连接打开关闭时间,这只有在数据较多的情况下才会有明显的效果。

    知识改变命运,奋斗成就人生!
    2010年2月3日 3:19
    版主
  • 我把数据处理放在数据库中进行了,果然快很多,但是那个查询语句搞了半天才写成,(*^__^*) 嘻嘻……,谢谢你。
    2010年2月4日 1:57