如何更快地制作表格
本文关键字：表格 何更快 | 更新日期: 2023-09-27 18:32:19
我正在从我的SQL服务器中获取数据并将它们放入表中，但是由于有数千行数据，该过程需要很长时间。如何使此过程更快？
我目前正在使用 foreach 循环向表中添加新行，即使处理 20 行数据，也需要大约 2 到 3 秒。以下是我的代码，如何改进？（代码有点长，很抱歉，但我想展示整个事情以讲述整个故事）
/*
* 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();
}
}
}
使用 using 表示具有IDisposable的类与Reader，Adapter，永远与SQLConnection一
using(var reader = command.ExecuteReader();)
{
while (reader.Read())
{
skuList.Add(reader.GetString(0));
}
}
connection.Dispose();
二
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();
在将一堆数据加载到 DataTable 之前，调用 BeginLoadData， https://msdn.microsoft.com/en-us/library/system.data.datatable.beginloaddata（v=vs.110）.aspx 。它将关闭一些内部约束和通知。加载完数据后，调用 EndLoadData。
我解决了这个问题（我应该早点回答这篇文章）。只是我们不想过多次连接到数据库或向服务器发出请求，这是拖累处理速度的关键部分，因为它的连接和请求时间与计算机的处理时间相比太长了。因此，我们希望限制向服务器请求的时间。
改进我的示例的一种稍微更好的方法是，而不是在getData（字符串SKU）方法中执行两个SELECT查询，我可以使用INNER JOIN将它们组合在一起，以便我们摆脱一半的查询，从而稍微改善运行时间。
但是，最好的方法是将所有所需的数据放入一个表中并获取该表，以便只需要一个查询（使用 SqlDataAdapter 中的 Fill 方法）。但是一次获取太多数据可能会导致运行时问题，因此您可能希望划分检索数据的时间（例如，一次检索 200 个数据），即使像这样，如果有 10,000 个数据，我们只需要执行 50 个查询，这比对每个数据执行要好得多。