有没有办法提高C#随机抽取记录的效率?

Question

请问各位高手,以下代码从数据库中随机抽取一条记录,开始抽取记录时运行还可以,但抽取了二十条记录左右后,就开始变慢,请问有没有办法提高C#随机抽取记录的效率?代码如下：
private void button1_Click(object sender, System.EventArgs e)／／开始从数据库抽取记录，并
                                                              ／／循环显示出来
{         
           conn = new SqlConnection(connstr);
           comm = new SqlCommand("selectcomm", conn);／／存储过程中的代码为：select top 1 *                                                     //* from j_data order by newid()
           
           comm.CommandType = CommandType.StoredProcedure;
           label2.Text = ""; //显示记录
          try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();                                      
                        timer.Interval = 1;
                        timer.Enabled = true;
                        timer.Tick += new EventHandler(TimerOnTick);
                        timer.Start();
                   
                   
                }
                            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "错误");
            }
            finally
            {
                conn.Close();
                button1.Enabled = false;
                button1.Visible = false;
                button2.Visible = true;
                button2.Enabled = true;
                button2.Focus();
            }           
}
  
     
        public void  TimerOnTick(object obj,EventArgs ea)  //
{
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }            SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
            
             
                  while (reader.Read())
                  {
                      
                      string id = reader.GetString(0);
                      string name = reader.GetString(1);
                      string depart = reader.GetString(2);                      Font font = new Font("华文彩云", 70, FontStyle.Regular); //华文彩云, 120pt, style=Bold
                      label2.Font = font;
                      label2.ForeColor = Color.Silver;
                      
                      sid = id;
                      sname = name;
                      sdepart = depart;
                      label2.Text = sname + " " + sdepart; //循环显示，按BUTTON2后停止循环并                                                          ／／显示某人和部门在LABEL2中
                  }
                  reader.Close();
         
}        private void button2_Click(object sender, System.EventArgs e)／／抽取记录
        {
            timer.Enabled = false;
            timer.Stop();            Font font = new Font("宋体_GB2312", 90, FontStyle.Bold); //华文彩云, 120pt, style=Bold
           label2.ForeColor = Color.Red;
            label2.Font = font;
           
            //写抽奖号码到历史数据表和删除原表中奖记录
            try
            {
                UpdateData(sid.Trim(), sdepart.Trim(), sname.Trim());            }
            catch (SqlException ea)
            {
                MessageBox.Show(ea.Message, "提示");
            }
            finally
            {                button1.Visible = true;
                button1.Enabled = true;
                button2.Visible = false;
                button2.Enabled = false;
                button1.Focus();            }
        }
//写抽奖号码到历史表
private void UpdateData(string data,string depart,string name)
{  
conn=new SqlConnection(connstr);
SqlCommand comm=new SqlCommand("updatedata",conn);
comm.CommandType=CommandType.StoredProcedure;

SqlParameter parameterdata=new SqlParameter("@data",SqlDbType.NVarChar,16);
parameterdata.Value=data;
comm.Parameters.Add(parameterdata);            SqlParameter parameterdepart = new SqlParameter("@depart", SqlDbType.NVarChar, 16);
            parameterdepart.Value = depart;
            comm.Parameters.Add(parameterdepart);
 
           SqlParameter paramername=new SqlParameter("@name",SqlDbType.NVarChar,16);
paramername.Value=name;
comm.Parameters.Add(paramername);

if(conn.State==ConnectionState.Closed)
{
conn.Open();
}
SqlTransaction  tran;
tran=conn.BeginTransaction();
            
try
{
comm.Transaction=tran;
comm.ExecuteNonQuery();
tran.Commit();

}
catch(SqlException ea)
{
MessageBox.Show(ea.Message,"错误!");
tran.Rollback();
}
finally
{
               conn.Close();           

} }

Answer 1

首先把数据全部填充到DataSet中。然后随机读取离线的DataSet的记录，这个时候效率会提高许多

Answer 2

嗯,楼上的说的没错,先fill到dataset.再操作dataset.

Answer 3

离线数据集是不错的解决方法,DataSet.Tables[0].select(..) 效率很高.

Answer 4

这么长阿
哪有时间看啊
======================================================
本人大四，下学期找工作了，各位兄弟帮帮忙
我的简历：http://202.118.70.40/winal/introduce.aspx
======================================================