writing table data with 16k rows to file is timing out

Question

I have a feature on my website where an admin can download the contents of a table. The table has about 16ks and only 8 columns.

Here is my C# code

using (MySqlConnection con = new MySqlConnection(constr))
        {

            string query = "SELECT * from wp_portal_users";
            using (MySqlCommand cmd = new MySqlCommand(query))
            {

                cmd.Connection = con;
                con.Open();

                MySqlDataReader sdr = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                if (sdr.HasRows == true)
                {
                    dt.Columns.Add("sysproID");
                    dt.Columns.Add("password");
                    dt.Columns.Add("username");
                    dt.Columns.Add("update_time", typeof(string));
                    dt.Columns.Add("tempPasswordValidity");
                    dt.Columns.Add("tempPassword");
                    dt.Columns.Add("last_login");
                    dt.Columns.Add("login_count");
                    dt.Rows.Add("Syspro ID", "Password", "Username", "Last Updated", "Temp Password Validity", "Temp Password", "Last Login", "Login Count");
                    DataRow dr = null;

                    while (sdr.Read())
                    {
                        dr = dt.NewRow();
                        dr["sysproID"] = sdr["sysproID"].ToString();
                        dr["password"] = sdr["password"].ToString();
                        dr["username"] = sdr["username"].ToString();
                        dr["update_time"] = sdr["update_time"].ToString();
                        dr["tempPasswordValidity"] = sdr["tempPasswordValidity"].ToString();
                        dr["tempPassword"] = sdr["tempPassword"].ToString();
                        dr["last_login"] = sdr["last_login"].ToString();
                        dr["login_count"] = sdr["login_count"].ToString();
                        dt.Rows.Add(dr);
                    }
                }

                string csv = string.Empty;

                foreach (DataRow row in dt.Rows)
                {
                    foreach (DataColumn column in dt.Columns)
                    {
                        //Add the Data rows.
                        csv += row[column.ColumnName].ToString().Replace(",", ";") + ',';
                    }

                    //Add new line.
                    csv += "\r\n";
                }

                //Download the CSV file.
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment;filename=PortalUsers.csv");
                Response.Charset = "";
                Response.ContentType = "application/text";
                Response.Output.Write(csv);
                Response.Flush();
                Response.End();
            }

        }

When this runs on a table with only a few rows it works well. With 16k rows it times out. What can i change to correct this performance issue?

Answer 1

You should not build a datatable in memory just to iterate though the rows (and then throw it away). Datatables are rather slow to build manually.

Instead use your MySqlDataReader to add the column strings to your variable.

Something like this:

            if (sdr.HasRows == true)
            {
                string csv = string.Empty;

                while (sdr.Read())
                {
                    csv += sdr["sysproID"].ToString().Replace(",", ";") + ',';
                    // add other columns

                    csv += "\r\n";
                }
            }
            //Download the CSV file.

Edit:

As @John mentioned in comment, you should use a StringBuilder to build the csv:

            if (sdr.HasRows == true)
            {
                StringBuilder sb = new StringBuilder();

                while (sdr.Read())
                {
                    sb.Append(sdr["sysproID"].ToString().Replace(",", ";"));
                    sb.Append(";");
                    // add other columns

                    sb.AppendLine();
                }
            }
            //Download the CSV file.