How can I make a table faster?

Question

I am grabbing data from my SQL server and put them into a table, but the process take so long due to the fact that there are thousands of rows of data. How can I make this process faster?

I am currently using a foreach loop to add new row to the table, even dealing with 20 rows of data, it will take about 2 to 3 seconds. Following is my code, how can I improve it? (the code is a bit long, sorry about that, but I want to show the whole thing in order to tell the whole story)

/*
 * A class that return bestbuy export table
 */
class BestbuyExportTable
{
    // field for the main table
    private DataTable mainTable;

    // field for database connection
    SqlConnection connection;

    /* constructor that initialize fields */
    public BestbuyExportTable()
    {
        mainTable = new DataTable();
        connection = new SqlConnection(Properties.Settings.Default.ConnectionString);
    }

    /* the real thing -> return the table !!! */
    public DataTable getTable()
    {
        // reset table just in case
        mainTable.Reset();

        // add column to table
        addColumn(mainTable, "SKU");                                                 // 1
        addColumn(mainTable, "DEPARTMENT");                                          // 2
        addColumn(mainTable, "SHORT DESCRIPTION");                                   // 3
        addColumn(mainTable, "LONG DESCRIPTION");                                    // 4
        addColumn(mainTable, "UPC (PRIMARY)");                                       // 4
        addColumn(mainTable, "UPC (ADDITIONAL)");                                    // 5
        addColumn(mainTable, "BRAND NAME");                                          // 6
        addColumn(mainTable, "MODEL NO");                                            // 7
        addColumn(mainTable, "MANUFACTURER");                                        // 8
        addColumn(mainTable, "SUPPLIER");                                            // 9
        addColumn(mainTable, "VENDOR PART NO");                                      // 10
        addColumn(mainTable, "UNIT COST");                                           // 11
        addColumn(mainTable, "RETAIL PRICE");                                        // 12
        addColumn(mainTable, "DIMENSION (CM) WIDTH (WITH BOX)");                     // 13
        addColumn(mainTable, "DIMENSION (CM) HEIGHT (WITH BOX)");                    // 14
        addColumn(mainTable, "DIMENSION (CM) LENGTH/DEPTH (WITH BOX)");              // 15
        addColumn(mainTable, "DIMENSION (KG) WEIGHT (WITH BOX)");                    // 16
        addColumn(mainTable, "DIMENSION (CM) WIDTH (NO BOX)");                       // 17
        addColumn(mainTable, "DIMENSION (CM) HEIGHT (NO BOX)");                      // 18
        addColumn(mainTable, "DIMENSION (CM) LENGTH/DEPTH (NO BOX)");                // 19
        addColumn(mainTable, "DIMENSION (KG) WEIGHT (NO BOX)");                      // 20
        addColumn(mainTable, "FRENCH COMPLIANT");                                    // 21
        addColumn(mainTable, "ENERGY STAR");                                         // 22
        addColumn(mainTable, "REFURBISHED");                                         // 23
        addColumn(mainTable, "SOFTWARE PLATFORM");                                   // 24
        addColumn(mainTable, "STREET DATE");                                         // 25
        addColumn(mainTable, "SERIAL NO REQUIRED");                                  // 26
        addColumn(mainTable, "SERIALIZED FORMAT");                                   // 27
        addColumn(mainTable, "SUPPORT MANUFACTURER WARRANTY");                       // 28
        addColumn(mainTable, "SERVICE UNDER MNF WARRANTY");                          // 29
        addColumn(mainTable, "SERVICE OUTSIDE MNF WARRANTY");                        // 30
        addColumn(mainTable, "SUPPLIER CONTACT");                                    // 31
        addColumn(mainTable, "ALWAYS RTV");                                          // 32
        addColumn(mainTable, "RETURN TO VENDOR DEFECTIVE DATE");                     // 33
        addColumn(mainTable, "RETURN TO VENDOR OPEN BOX DAYS");                      // 34
        addColumn(mainTable, "PRODUCT WARRANTY DAYS");                               // 35
        addColumn(mainTable, "PRODUCT WARRANTY COVERAGE");                           // 36
        addColumn(mainTable, "EXTENDED PARTS WARRANTY");                             // 37
        addColumn(mainTable, "RETURN RESTRICTIONS");                                 // 38
        addColumn(mainTable, "EMBARGO DATE");                                        // 39
        addColumn(mainTable, "EXPIRATION DATE/LOT NUMBER");                          // 40
        addColumn(mainTable, "SHELF LIFE");                                          // 41
        addColumn(mainTable, "DATA FLAG");                                           // 42
        addColumn(mainTable, "LESS THAN TRUCKLOAD");                                 // 43
        addColumn(mainTable, "SKU_BESTBUY_CA");                                      // 44

        // local field for inserting data to table
        DataRow row;
        AltText alt = new AltText();    // this is just a class that get the alt text of a product
        string[] skuList = getSKU();

        // add data to each row 
        foreach (string sku in skuList)
        {
            string[] list = getData(sku);
            row = mainTable.NewRow();

            row[0] = sku;                // sku
            row[1] = "104";              // department
            row[2] = alt.getAlt(sku);    // short description 
            row[3] = list[0];            // long description
            row[4] = list[9];            // upc code primary
            row[5] = list[10];           // upc code additional
            row[6] = "Ashlin®";          // brand name
            row[7] = list[1];            // model no
            row[8] = "Ashlin®";          // manufacturer
            row[9] = "234326";           // supplier
            row[10] = sku;               // vendor
            row[11] = (Math.Round((Convert.ToDouble(list[11]) * 0.5775), 2)).ToString();       // unit cost
            row[12] = (Math.Ceiling((Convert.ToDouble(list[11]) * 1.06)) - 0.01).ToString();   // retail price
            row[13] = list[2];                                                 // dimension width (with box)
            row[14] = list[3];                                                 // dimension height (with box)
            row[15] = list[4];                                                 // dimension length (with box)
            row[16] = (Convert.ToDouble(list[5]) / 1000).ToString();           // dimension weight (with box)
            row[17] = list[2];                                                 // dimension width (no box)
            row[18] = list[3];                                                 // dimension height (no box)
            row[19] = list[6];                                                 // dimension length (no box)
            row[20] = list[7];                                                 // dimension weight (no box)
            row[21] = 'Y';              // french compliant
            row[23] = 'N';              // refurbished
            row[24] = 'N';              // software platform
            row[26] = 'N';              // serial no required
            row[28] = 'N';              // support manufacturer warranty
            row[29] = 'Y';              // service under mnf warranty
            row[30] = 'N';              // service outside mnf warranty
            row[31] = 8884274546;       // supplier contact
            row[32] = 'N';              // always rtv
            row[33] = 0;                // return to vendor defective date
            row[34] = 0;                // return to vendor open box days
            row[35] = 365;              // peoduct warranty days
            row[44] = list[8];          // sku bestbuy ca

            mainTable.Rows.Add(row);
        }

        return mainTable;
    }

    /* method that add new column to table */
    private void addColumn(DataTable table, string name)
    {
        // set up column
        DataColumn column = new DataColumn();
        column.ColumnName = name;

        // add column to table
        table.Columns.Add(column);
    }

    /* a method that get all the sku that is active and sell on bestbuy*/
    private string[] getSKU()
    {
        // local field for storing data
        List<string> skuList = new List<string>();

        // connect to database and grab data
        SqlCommand command = new SqlCommand("SELECT SKU_Ashlin FROM master_SKU_Attributes WHERE SKU_BESTBUY_CA is NOT NULL AND Active = \'TRUE\' ORDER BY SKU_Ashlin;", connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            skuList.Add(reader.GetString(0));
        }
        connection.Close();

        return skuList.ToArray();
    }

    /* method that get the data from given sku */
    private string[] getData(string sku)
    {
        // local field for storing data
        List<string> list = new List<string>();
        DataTable table = new DataTable();

        // get the design code from sku
        string design = sku.Substring(0, sku.IndexOf('-'));

        // grab data from design
        // [0] for long description, [1] for model no, [2] for diemsion width (with box), [3] for dimension height (with box), [4] for dimension legth (with box), [5] for dimension weight (with box), [6] for dimension length (no box), [7] for dimension weight(no box)
        //                                                 and (no box)                       and (no box)
        SqlDataAdapter adapter = new SqlDataAdapter("SELECT Extended_Description, Design_Service_Fashion_Name_BESTBUY_CA, Width_cm, Height_cm, Shippable_Depth_cm, Shippable_Weight_grams, Depth_cm, Weight_grams FROM master_Design_Attributes WHERE Design_Service_Code = \'" + design + "\';", connection);
        connection.Open();
        adapter.Fill(table);
        for (int i = 0; i <= 7; i++)
        {
            list.Add(table.Rows[0][i].ToString());
        }
        table.Reset();
        // [8] for SKU_BESTBUY_CA, [9] for upc code primary, [10] for upc code additional, [11] for unit cost and retail price
        adapter = new SqlDataAdapter("SELECT SKU_BESTBUY_CA, UPC_Code_9, UPC_Code_10, Base_Price FROM master_SKU_Attributes WHERE SKU_Ashlin = \'" + sku + "\';", connection);
        adapter.Fill(table);
        for (int i = 0; i <= 3; i++)
        {
            list.Add(table.Rows[0][i].ToString());
        }
        connection.Close();

        return list.ToArray();
      }
   }
}

If the code works, this is more suited in Code Review, I think... — Ian, Jan 29 '16 at 02:00
Possible duplicate to http://stackoverflow.com/questions/18961938/populate-data-table-from-data-reader — Tah, Jan 29 '16 at 02:01
Do you have indexes on the tables you are adding to? Are you caching all the updates in one SQL Statement? Many variables here — Grantly, Jan 29 '16 at 02:03
@Grantly, yes, many variables, the hard thing is that the table I want to return doesn't contain all of the data i have, but I still need to show those unused column — Leon Ma, Jan 29 '16 at 02:23
@LeonMa How many rows are you returning from each queries? You seem to be only using the first one. — ESG, Jan 29 '16 at 02:35
After a cursory look, it seems like this could all be achieved on the SQL Server side. If you think this process is going to increase in size, you hould consider this approach as it will be much faster then a front-end code approach. — Nick.Mc, Jan 29 '16 at 04:22

score 2 · Answer 1 · answered Jan 29 '16 at 02:09

Use using for class with IDisposable With Reader, Adapter and always with SQLConnection One

using(var reader = command.ExecuteReader();)
{
    while (reader.Read())
        {
            skuList.Add(reader.GetString(0));
        }
}
connection.Dispose();

Two

using(var adapter =  new SqlDataAdapter("SELECT Extended_Description, Design_Service_Fashion_Name_BESTBUY_CA, Width_cm, Height_cm, Shippable_Depth_cm, Shippable_Weight_grams, Depth_cm, Weight_grams FROM master_Design_Attributes WHERE Design_Service_Code = \'" + design + "\';", connection))
{
        connection.Open();
        adapter.Fill(table);
        for (int i = 0; i <= 7; i++)
        {
            list.Add(table.Rows[0][i].ToString());
        }
        table.Reset();
        adapter = new SqlDataAdapter("SELECT SKU_BESTBUY_CA, UPC_Code_9, UPC_Code_10, Base_Price FROM master_SKU_Attributes WHERE SKU_Ashlin = \'" + sku + "\';", connection);
        adapter.Fill(table);
        for (int i = 0; i <= 3; i++)
        {
            list.Add(table.Rows[0][i].ToString());
        }
}
        connection.Dispose();

score 1 · Answer 2 · answered Jan 29 '16 at 04:17

Before loading a bunch of data into a DataTable, call BeginLoadData, https://msdn.microsoft.com/en-us/library/system.data.datatable.beginloaddata(v=vs.110).aspx . It will turn off some of the internal constraints and notifications. When you're done loading the data, call EndLoadData.

score 0 · Answer 3 · answered Feb 28 '16 at 15:16

I fixed the problem ( I should answer this post earlier ). It's just we don't want to connect to database or doing request to the server too many times, that is the crucial part that dragged the processing speed due to the fact that its connection and request time is much too longer compare to computer's processing time. Therefore, we want to limit the time we do request to the server.

One slightly better way to improve my example is instead of doing two SELECT queries in getData(string sku) method, I could just combine them using INNER JOIN so that we get rid of half the query, which slightly improve the run time.

However, the best way is to just get all the desired data into one table and get the table so that only one query required (using Fill method in SqlDataAdapter). But getting too many data in a time might cause run time issue, so you might want to divide the times retrieving data (say, retrieve 200 data at a time) even like that we only need to do 50 queries if there are 10,000 of data, which is much much better that doing it for every single of data.

How can I make a table faster?

3 Answers3