大家好!今天分享测试小程序,感兴趣的可以看看。
我现在有104万条数据,需要添加到数据库中,我想问问大家,应该怎么插入?需要多长时间?
按我之前的做法,一条一条添加不就行了!但是时间呢需要多少?估计要1个小时以上,这个主要是说明一条一条添加大数据太慢了。
那么下面看看我的测试小程序:
首先数据库
--***************指向当前要使用的数据库 use master go ----判断当前数据库是否存在 if exists (select * from sysdatabases where name=‘test‘) drop database test --删除数据库 go --创建数据库 create database test on primary ( --数据库文件的逻辑名 name=‘test_data‘, --数据库物理文件名(绝对路径) filename=‘D:\DB\test_data.mdf‘, --数据库文件初始大小 size=2048MB, --数据文件增长量 filegrowth=512MB ) --创建日志文件 log on ( name=‘test_log‘, filename=‘D:\DB\test_log.ldf‘, size=2048MB, filegrowth=512MB ) go --*****管理表*******指向当前要使用的数据库 use test go --创建表 if exists(select * from sysobjects where name=‘TableB‘) drop table TableB go create table TableB ( value1 varchar(50) not null, -- 一个值 value2 varchar(50) not null, -- 一个值 ) go ----添加测试数据 insert into TableB(value1,value2)values(‘funiyi_01‘,‘funiyi_11‘) insert into TableB(value1,value2)values(‘funiyi_21‘,‘funiyi_21‘) ------查询表里面的数据 select * from TableB go
这个是程序的主页面:
部分C#程序代码展示:
/// <summary> /// txt文件生成 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btn_txt_Click(object sender, EventArgs e) { dataNum = 720000; System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch(); timer.Start(); StringBuilder sb = new StringBuilder(); try { for (int i = 0; i < dataNum; i++) { sb.Append(i.ToString() + ‘,‘ + i.ToString() + ‘\n‘); //数据列用逗号分割,数据行用换行符分割 } //new一个有全路径的 流写入对象, StreamWriter streamWriter = new StreamWriter(Application.StartupPath + $"\\{dataNum}.txt", false); streamWriter.Write(sb); //把文本字符串,通过流的方式写入 txt中 streamWriter.Close(); //关闭流写入对象 } catch (Exception) { throw; } finally { sb = null; GC.Collect(); } timer.Stop(); this.label2.Text = $"txt添加{dataNum}条数据时间:" + timer.ElapsedMilliseconds.ToString() + "ms"; }
/// <summary> /// txt BulkInsert /// txt文件大数据 保存SQL数据库 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void S_Click(object sender, EventArgs e) { dataNum =720000; System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch(); timer.Start(); //****************************数据表+++++++文件路径 ++++++++++++++++++++++++++++++++++++++++ 字段终止符 ++++++++++++行终止符 +++++++++++ 批量大小 string sqlBI = "BULK INSERT TableB FROM ‘" + Application.StartupPath + "\\" + dataNum + ".txt‘ WITH (FIELDTERMINATOR = ‘,‘,ROWTERMINATOR=‘\n‘,BATCHSIZE =720000)"; int iNum = SQLHelperAdvance.ExecuteNonQuery(sqlBI); //传送720000,iNum=1440000; timer.Stop(); this.label2.Text = $"txt SQL添加{dataNum}条数据时间:" + timer.ElapsedMilliseconds.ToString() + "ms"; }
/// <summary> /// 基本 Insert Into /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void bt_InsertInto_Click(object sender, EventArgs e) { System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch(); timer.Start(); sql = GetSql(); int iNum = SQLHelperAdvance.ExecuteNonQuery(sql); timer.Stop(); this.label2.Text = $"SQL添加{dataNum}条数据时间:" + timer.ElapsedMilliseconds.ToString() + "ms"; } /// <summary> /// 获取SQL执行字符串 /// </summary> /// <returns></returns> private string GetSql() { dataNum = 140000; sb = new StringBuilder(); try { for (int i = 0; i < dataNum; i++) { sb.Append("insert into TableB(value1,value2) values(‘" + i + "‘,‘" + i + "‘)"); } return sb.ToString(); } catch (Exception) { throw; } finally { sb = null; GC.Collect(); } }
/// <summary> /// 搜寻txt /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void lab_search_Click(object sender, EventArgs e) { string[] files = Directory.GetFiles(Application.StartupPath, "*.TXT"); //文件全路径列表 textBox1.Text = "找到txt文件:" + files.Length +"\r\n"; foreach (string file in files) { textBox1.Text += Path.GetFileName(file) + "\r\n"; //new DirectoryInfo(@"c:\1").GetFiles("*.txt").ToList().ForEach(fi => fi.MoveTo(@"c:\2\" + fi.Name)); //移动文件 //++++++++++++++获取指定路径下的 txt文件全路径,转List,++++++++ List中的每个元素执行操作,把每个txt文件,剪切到另一个文件夹中 new DirectoryInfo(Application.StartupPath).GetFiles("*.txt").ToList().ForEach(fi => fi.MoveTo("D:\\data\\" + fi.Name)); } } private void btn_txtDe_Click(object sender, EventArgs e) { string path = Environment.CurrentDirectory; string pattern = "*.txt"; string[] strFileName = Directory.GetFiles(path, pattern); foreach (var item in strFileName) { File.Delete(item); } }
private ExcelReport report; /// <summary> /// 直接创建,不需要模板 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btn_ExcelSheetNew_Click(object sender, EventArgs e) { report = new ExcelReport(); //report.CreateWorksheet(01.ToString()); for (int i = 20; i >= 1; i--) { report.CreateWorksheet("段号" + i.ToString()); //注意:最后创建的表索引是1,老的表索引依次加大 } report.Show(); //显示对象 report.ChangeCurrentWorkSheet(1); //改变当前的工作表,如果选择的表不存在,就使用之前的 report.WriteTextToReport(1, 1, "funiyi816+1"); report.ChangeCurrentWorkSheet(2); //改变当前的工作表,如果选择的表不存在,就使用之前的 report.WriteTextToReport(1, 1, "funiyi816+2"); report.SaveAs(Application.StartupPath + "\\001", ExcelSaveFormat.xlsx); report.Close(); //关闭对象 }
/// <summary> /// 通过模板打开 单个表格 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btn_ExcelSingleSheet_Click(object sender, EventArgs e) { dataNum = 720000; //(新版本2007之后)最大行1048576,XFD (老版本97-2003)最大行65536,IV List<TableB> tableBList = new List<TableB>(); for (int i = 0; i < dataNum; i++) { tableBList.Add(new TableB() { Value1 = i.ToString(), Value2 = i.ToString() }); } System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch(); timer.Start(); report = new ExcelReport(Application.StartupPath + "\\Template\\Template.xlsx"); //创建对象 report.ChangeCurrentWorkSheet(1); //改变当前的工作表,如果选择的表不存在,就使用之前的 report.Show(); //显示对象 report.WriteListToReport(2, 1, tableBList, true); report.SaveAs(Application.StartupPath + $"\\{dataNum}", ExcelSaveFormat.xlsx); //保存文件 report.Close(); //关闭对象 timer.Stop(); this.label2.Text = $"EXCEL添加{dataNum}条数据时间:" + timer.ElapsedMilliseconds.ToString() + "ms"; }
/// <summary> /// 多个表格 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btn_ExcelMultipleSheet_Click(object sender, EventArgs e) { dataNum = 720000; //(新版本2007之后)最大行1048576,XFD (老版本97-2003)最大行65536,IV List<TableB> tableBList = new List<TableB>(); for (int i = 0; i < dataNum; i++) { tableBList.Add(new TableB() { Value1 = i.ToString(), Value2 = i.ToString() }); } System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch(); timer.Start(); //string str = Application.StartupPath + "\\Template\\Template.xlsx"; report = new ExcelReport(Application.StartupPath + "\\Template\\Template.xlsx"); //创建对象 report.ChangeCurrentWorkSheet(1); //改变当前的工作表 report.Show(); //显示对象 report.WriteListToReport(2, 1, tableBList, true); report.ChangeCurrentWorkSheet(2); //改变当前的工作表 report.WriteListToReport(2, 1, tableBList, true); report.ChangeCurrentWorkSheet(3); //改变当前的工作表 report.WriteListToReport(2, 1, tableBList, true); report.SaveAs(Application.StartupPath + $"\\{dataNum}", ExcelSaveFormat.xlsx); //保存文件 report.Close(); //关闭对象 timer.Stop(); this.label2.Text = $"EXCEL添加{dataNum}条数据时间:" + timer.ElapsedMilliseconds.ToString() + "ms"; }
好的,就分享到这来吧,又要上班了。欢迎大家进入我们的群一起交流,学习