如何加快加载过程 [英] How Can I Speed Up The Loading Process
本文介绍了如何加快加载过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一张excel表，里面有10万条记录，现在我想将这整个Sheeet加载到postgres数据库中，需要花费大约20到30分钟的时间
i希望减少加载时间进入数据库我该怎么办这个建议我
代码是这个
i have an excel sheet which have 1 lakh of record in it now i want to load this whole sheeet into postgres database it take lotup time approx 20 to 30 min
i want to reduce time to load into database how can i do this pls suggest me
code is this
public static DataTable GetDataTableExcel(string strFileName, string Table, string sheet, string expProp)
{
string date = DateTime.Now.ToString();
date = date.Replace("/", "").Replace(":", "").Replace(" ", "").ToString();
string values = string.Empty;
string Column = string.Empty;
string strFilename = System.IO.Path.GetFileNameWithoutExtension(strFileName);
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"" + expProp + ";HDR=Yes;IMEX=1\";");
conn.Open();
string strQuery = "SELECT * FROM [" + sheet + "]";
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
System.Data.DataTable ds = new System.Data.DataTable();
//string DestFileName = "D:\\"+Table;
adapter.Fill(ds);
//DataSet DaSe = new DataSet();
//DaSe.Tables.Add(ds);
//ExportToCSVFastly(DaSe, DestFileName);
//Microsoft.Office.Interop.Excel.Application excelapp = new Microsoft.Office.Interop.Excel.Application();
//string ext = Path.GetExtension(strFileName);
//if (ext.ToLower() == ".xls" || ext.ToLower() == ".xlsx")
//{
// Microsoft.Office.Interop.Excel.Workbook excelBook = excelapp.Workbooks.Open(strFileName);
// String[] excelSheets = new String[excelBook.Worksheets.Count];
// int n = 0;
// foreach (Microsoft.Office.Interop.Excel.Worksheet wSheet in excelBook.Worksheets)
// {
// excelSheets[n] = wSheet.Name;
// n++;
// }
//}
conn.Close();
string Columnname = string.Empty;
for (var i = 0; i < ds.Columns.Count; i++)
{
// if (ds.Columns[i].DataType.ToString() == "System.Int32")
// {
// //Replace(@"([^a-zA-Z0-9_]|^\s)", string.Empty);
// Columnname += ds.Columns[i].ToString().Replace(" ", string.Empty).Replace("#", string.Empty) + " " + "integer"/* + "," + "new_" + ds.Columns[i].ToString().Replace(" ", string.Empty).Replace("#", string.Empty) + " " + "integer"*/ + ",";
// }
// else if (ds.Columns[i].DataType.ToString() == "System.Double")
// {
// Columnname += ds.Columns[i].ToString().Replace(" ", string.Empty).Replace("#", string.Empty) + " " + "character varying(255)"/* + "," + "new_" + ds.Columns[i].ToString().Replace(" ", string.Empty).Replace("#", string.Empty) + " " + "character varying(255)"*/ + ",";
// }
// else
// {
Columnname += ds.Columns[i].ToString().Replace(" ", string.Empty).Replace("#", string.Empty).Replace("?", string.Empty).Replace("@", string.Empty).Replace("&", string.Empty).Replace("$", string.Empty).Replace("%", string.Empty).Replace("!", string.Empty).Replace("/", string.Empty).Replace("-", string.Empty).Replace("~", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty) + " " + "character varying(255)"/* + "," + "new_" + ds.Columns[i].ToString().Replace(" ", string.Empty).Replace("#", string.Empty) + " " + "character varying(255*/ + ",";
// }
// // Column += ds.Columns[i].ToString() + "," +"new_"+ ds.Columns[i].ToString()+",";
}
Columnname = Columnname.TrimEnd(',');
Columnname = Columnname + "," + "pid character varying(255)";
//string date = DateTime.Now.ToString();
//date = date.Replace("/", "").Replace(":", "").Replace(" ", "").ToString();
string sql_Create = "CREATE TABLE " + Table + "(" + Columnname + ")";
obj.ExecuteNonQuery(sql_Create);
//string savefilepath = ConfigurationManager.AppSettings["upload_path"] + DateTime.Now.Day.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Hour + "_" + DateTime.Now.Minute + "_" + DateTime.Now.Second + "_" + DateTime.Now.Millisecond + "_" + DateTime.Now.Ticks + "_Data.csv";
//System.IO.File.Copy(strFileName, savefilepath);
//string vFTPLocation = ConfigurationManager.AppSettings["upload_path"] + Path.GetFileName(savefilepath);
for (var j = 0; j < ds.Columns.Count; j++)
{
Column += ds.Columns[j].ToString().Replace(" ", string.Empty).Replace("@", string.Empty).Replace("!", string.Empty).Replace("?", string.Empty).Replace("#", string.Empty).Replace("$", string.Empty).Replace("^", string.Empty).Replace("%", string.Empty).Replace("&", string.Empty).Replace("*", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty).Replace("=", string.Empty).Replace("+", string.Empty).Replace(":", string.Empty).Replace("'", string.Empty).Replace("[", string.Empty).Replace("]", string.Empty).Replace("{", string.Empty).Replace("}", string.Empty).Replace("`", string.Empty).Replace("~", string.Empty).Replace("<", string.Empty).Replace(">", string.Empty).Replace("|", string.Empty).Trim().Replace("/", string.Empty).Replace("-", string.Empty) + ","/* + "new_" + ds.Columns[j].ToString().Replace(" ", string.Empty).Replace("#", string.Empty) + ","*/;
}
Column = Column.TrimEnd(',');
Column = Column + "," + "pid";
// obj.ExecuteNonQuery("copy " + strFilename + " from '" + vFTPLocation.Replace("\\", "\\\\") + "' csv header");
// obj.ExecuteNonQuery("copy " + strFilename + "(" + Column + ") from '" + vFTPLocation.Replace("\\", "\\\\") + "' csv header");
int counter = 0;
for (int k = 0; k < ds.Rows.Count; k++)
{
counter = counter + 1;
for (int i = 0; i < ds.Columns.Count; i++)
{
string val = ds.Rows[k][i].ToString();
if (val.Contains("'") || val.Contains(@"\"))
{
val = val.Replace("\"", "").Replace("'", "").Replace(@"\", "-").Replace("!", string.Empty).Replace("?", string.Empty).Replace("#", string.Empty).Replace("$", string.Empty).Replace("^", string.Empty).Replace("%", string.Empty).Replace("&", string.Empty).Replace("*", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty).Replace("=", string.Empty).Replace("+", string.Empty).Replace(":", string.Empty).Replace("'", string.Empty).Replace("[", string.Empty).Replace("]", string.Empty).Replace("{", string.Empty).Replace("}", string.Empty).Replace("`", string.Empty).Replace("~", string.Empty).Replace("<", string.Empty).Replace(">", string.Empty).Replace("|", string.Empty).Trim();
}
values += "'" + val.Replace("!", string.Empty).Replace("?", string.Empty).Replace("#", string.Empty).Replace("$", string.Empty).Replace("^", string.Empty).Replace("%", string.Empty).Replace("&", string.Empty).Replace("*", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty).Replace("=", string.Empty).Replace("+", string.Empty).Replace(":", string.Empty).Replace("'", string.Empty).Replace("[", string.Empty).Replace("]", string.Empty).Replace("{", string.Empty).Replace("}", string.Empty).Replace("`", string.Empty).Replace("~", string.Empty).Replace("<", string.Empty).Replace(">", string.Empty).Replace("|", string.Empty).Trim() +"'" + ","/* + "'" + val + "'" + ","*/;
}
Column = Column.TrimEnd(',');
values = values.TrimEnd(',');
values = values + "," + counter;
obj.ExecuteNonQuery("insert into " + Table + "(" + Column + ") values(" + values + ")");
values = string.Empty;
}
return ds;
}
推荐答案
， string .Empty）.Replace（ ％， string .Empty).Replace( ！， string .Empty）.Replace（ /， string .Empty）.Replace（ - ， string .Empty）.Replace（ 〜， string .Empty）.Replace（ （， string .Empty）.Replace（ ） ， s tring .Empty）+ + 字符变化（255） / * +，+new_+ ds.Columns [i] .ToString（）。Replace（，string.Empty）.Replace（＃，string.Empty）++字符变化（255 * / + ，;
// }
/ / //列+ = ds.Columns [i] .ToString（）+，+new _+ ds.Columns [i] .ToString（）+ ，;
}
Columnname = Columnname.TrimEnd（' ）;
Columnname = Columnname + ， + pid字符变化（255）;
// string date = DateTime.Now.ToString（）;
< span class =code-comment> // date = date.Replace（/，）。Replace（：，）。替换（，）。ToString（）;
string sql_Create = CREATE TABLE +表+ （ + Columnname + ）;
obj.ExecuteNonQuery（sql_Create）;
// string savefilepath = ConfigurationManager.AppSettings [upload_path] + DateTime.Now.Day.ToString（ ）+ DateTime.Now.Month.ToString（）+ DateTime.Now.Year.ToString（）+_+ DateTime.Now.Hour +_+ DateTime.Now.Minute +_+ DateTime.Now。 Second +_+ DateTime.Now.Millisecond +_+ DateTime.Now.Ticks +_ Data.csv;
// System.IO.File.Copy（strFileName，savefilepath）;
// < span class =code-comment> string vFTPLocation = ConfigurationManager.AppSettings [upload_path] + Path.GetFileName（savefilepath）;
for （ var j = 0 ; j < ds.Columns.Count; j ++）
{
Column + = ds.Columns [j]。 ToString（）。替换（ ， string .Empty）.Replace（ @， string .Empty）.Replace（ ！， string .Empty）.Replace（ ？， string .Empty）.Replace（ ＃，< span class =code-keyword> string .Empty）.Replace（
", string.Empty).Replace("%", string.Empty).Replace("!", string.Empty).Replace("/", string.Empty).Replace("-", string.Empty).Replace("~", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty) + " " + "character varying(255)"/* + "," + "new_" + ds.Columns[i].ToString().Replace(" ", string.Empty).Replace("#", string.Empty) + " " + "character varying(255*/ + ","; // } // // Column += ds.Columns[i].ToString() + "," +"new_"+ ds.Columns[i].ToString()+","; } Columnname = Columnname.TrimEnd(','); Columnname = Columnname + "," + "pid character varying(255)"; //string date = DateTime.Now.ToString(); //date = date.Replace("/", "").Replace(":", "").Replace(" ", "").ToString(); string sql_Create = "CREATE TABLE " + Table + "(" + Columnname + ")"; obj.ExecuteNonQuery(sql_Create); //string savefilepath = ConfigurationManager.AppSettings["upload_path"] + DateTime.Now.Day.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Hour + "_" + DateTime.Now.Minute + "_" + DateTime.Now.Second + "_" + DateTime.Now.Millisecond + "_" + DateTime.Now.Ticks + "_Data.csv"; //System.IO.File.Copy(strFileName, savefilepath); //string vFTPLocation = ConfigurationManager.AppSettings["upload_path"] + Path.GetFileName(savefilepath); for (var j = 0; j < ds.Columns.Count; j++) { Column += ds.Columns[j].ToString().Replace(" ", string.Empty).Replace("@", string.Empty).Replace("!", string.Empty).Replace("?", string.Empty).Replace("#", string.Empty).Replace("
， string .Empty）.Replace（ ^， string .Empty）.Replace（ ％， string .Empty）.Replace（ &， string .Empty）.Replace（ *， string .Empty）.Replace（ （， string .Empty）.Replace（ ）， string .Empty）.Replace（ =， string .Empty）.Replace（ +， string .Empty）.Replace（ ：， string .Empty）.Replace（ '， string .Empty）.Replace（ [， string .Empty）.Replace（ ]， string .Empty）.Replace（ {， string .Empty）.Replace（ }， string .Empty）.Replace（\"`\", string.Empty).Replace (\"~\", string.Empty ).Replace(\"<\", string.Empty).Replace(\">\", string.Empty).Replace(\"|\", string.Empty).Trim().Replace(\"/\", string.Empty).Replace(\"-\", string.Empty) + \",\"/* + \"new_\" + ds.Columns[j].ToString().Replace(\" \", string.Empty).Replace(\"#\", string.Empty) + \",\"*/;
}
Column = Column.TrimEnd(',');
Column = Column + \",\" + \"pid\";
// obj.ExecuteNonQuery(\"copy \" + strFilename + \" from '\" + vFTPLocation.Replace(\"\\\", \"\\") + \"' csv header\");
// obj.ExecuteNonQuery(\"copy \" + strFilename + \"(\" + Column + \") from '\" + vFTPLocation.Replace(\"\\\", \"\\") + \"' csv header\");
int counter = 0;
for (int k = 0; k < ds.Rows.Count; k++)
{
counter = counter + 1;
for (int i = 0; i < ds.Columns.Count; i++)
{
string val = ds.Rows[k][i].ToString();
if (val.Contains(\"'\") || val.Contains(@\"\\"))
{
val = val.Replace(\"\\"\", \"\").Replace(\"'\", \"\").Replace(@\"\\", \"-\").Replace(\"!\", string.Empty).Replace(\"?\", string.Empty).Replace(\"#\", string.Empty).Replace(\"
", string.Empty).Replace("^", string.Empty).Replace("%", string.Empty).Replace("&", string.Empty).Replace("*", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty).Replace("=", string.Empty).Replace("+", string.Empty).Replace(":", string.Empty).Replace("'", string.Empty).Replace("[", string.Empty).Replace("]", string.Empty).Replace("{", string.Empty).Replace("}", string.Empty).Replace("`", string.Empty).Replace("~", string.Empty).Replace("<", string.Empty).Replace(">", string.Empty).Replace("|", string.Empty).Trim().Replace("/", string.Empty).Replace("-", string.Empty) + ","/* + "new_" + ds.Columns[j].ToString().Replace(" ", string.Empty).Replace("#", string.Empty) + ","*/; } Column = Column.TrimEnd(','); Column = Column + "," + "pid"; // obj.ExecuteNonQuery("copy " + strFilename + " from '" + vFTPLocation.Replace("\\", "\\\\") + "' csv header"); // obj.ExecuteNonQuery("copy " + strFilename + "(" + Column + ") from '" + vFTPLocation.Replace("\\", "\\\\") + "' csv header"); int counter = 0; for (int k = 0; k < ds.Rows.Count; k++) { counter = counter + 1; for (int i = 0; i < ds.Columns.Count; i++) { string val = ds.Rows[k][i].ToString(); if (val.Contains("'") || val.Contains(@"\")) { val = val.Replace("\"", "").Replace("'", "").Replace(@"\", "-").Replace("!", string.Empty).Replace("?", string.Empty).Replace("#", string.Empty).Replace("
\", string.Empty).Replace(\"^\", string.Empty).Replace(\"%\", string.Empty).Replace(\"&\", string.Empty).Replace(\"*\", string.Empty).Replace(\"(\", string.Empty).Replace(\")\", string.Empty).Replace(\"=\", string.Empty).Replace(\"+\", string.Empty).Replace(\":\", string.Empty).Replace(\"'\", string.Empty).Replace(\"[\", string.Empty).Replace(\"]\", string.Empty).Replace(\"{\", string.Empty).Replace(\"}\", string.Empty).Replace(\"`\", string.Empty).Replace(\"~\", string.Empty).Replace(\"<\", string.Empty).Replace(\">\", string.Empty).Replace(\"|\", string.Empty).Trim();
}
values += \"'\" + val.Replace(\"!\", string.Empty).Replace(\"?\", string.Empty).Replace(\"#\", string.Empty).Replace(\"
", string.Empty).Replace("^", string.Empty).Replace("%", string.Empty).Replace("&", string.Empty).Replace("*", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty).Replace("=", string.Empty).Replace("+", string.Empty).Replace(":", string.Empty).Replace("'", string.Empty).Replace("[", string.Empty).Replace("]", string.Empty).Replace("{", string.Empty).Replace("}", string.Empty).Replace("`", string.Empty).Replace("~", string.Empty).Replace("<", string.Empty).Replace(">", string.Empty).Replace("|", string.Empty).Trim(); } values += "'" + val.Replace("!", string.Empty).Replace("?", string.Empty).Replace("#", string.Empty).Replace("
这篇关于如何加快加载过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
