SQLBulkOperations及数组作参数的SQL

最新推荐文章于 2019-07-26 13:43:13 发布
最新推荐文章于 2019-07-26 13:43:13 发布
阅读量3.8k 收藏 1
点赞数
文章标签： 数据库
如何更快地将数据插入数据库。经过老师指点，在网络搜索半天，实验了SQLBulkOperations及数组作参数的SQL两种方式。[@more@]

以前程序使用ADO，现在转换到ODBC，不熟悉，中间犯了许多错误。先把例子放上来。

const int ARRAY_SIZE = 1000;

参数化数组中每个行对应结构

typedef struct Channel_Trans_Struct
{
SQLCHAR mac_addr[13];
//SQLTIMESTAMP start_time[24];
SQLCHAR start_time[24];
//SQL_TIMESTAMP_STRUCT start_time;
SQLUSMALLINT start_channel_number;
SQLVARCHAR start_channel_name[31];
SQLINTEGER start_channel_name_id;
SQLUSMALLINT start_video_id;
SQLUSMALLINT start_audio_id;
SQLUSMALLINT start_volume;
SQLINTEGER start_frequence;

SQLINTEGER mac_addrInd;
SQLINTEGER start_timeInd;
SQLINTEGER start_channel_numberInd;
SQLINTEGER start_channel_nameInd;
SQLINTEGER start_channel_name_idInd;
SQLINTEGER start_video_idInd;
SQLINTEGER start_audio_idInd;
SQLINTEGER start_volumeInd;
SQLINTEGER start_frequenceInd;
}ChannelStruct;

1.使用参数化SQL

VOID TestControl::TestInsert(int count)
{


DatabseConn conn;
SQLHENV henv;
SQLHDBC hdbc;
HSTMT cursor;

//连接到数据库
conn.ConnectToDatabase(henv,hdbc);
conn.DispatchCursor(hdbc,cursor);


//开始计时
long time1=GetTickCount();//begin time


//int ARRAY_SIZE =10;//批量提交数据量
ChannelStruct ChannelArray[ARRAY_SIZE];
//Table1Struct ChannelArray[ARRAY_SIZE];
SQLUSMALLINT i,ParamsProcessed=0,ParamStatusArray[ARRAY_SIZE]={0};

//保存到数据库
SQLCHAR * Statement = (unsigned char *)"insert into channel_transfer(mac_addr,start_time,start_channel_number,start_channel_name,start_channel_name_id,start_video_id,start_audio_id,start_volume,start_frequence) values(?,?,?,?,?,?,?,?,?)";
//SQLCHAR * Statement = (SQLCHAR*)"insert into Table1(column1,column2) values(?,?)";

//设置SQL_ATTR_PARAM_BIND_TYPE语句以使用行式邦定
//SQLSetStmtAttr(cursor,SQL_ATTR_PARAM_BIND_TYPE,(void*)sizeof(ChannelStruct),0);
SQLSetStmtAttr(cursor,SQL_ATTR_PARAM_BIND_TYPE,(SQLPOINTER)sizeof(ChannelStruct),SQL_IS_INTEGER);

//指定返回参数数组的元素数
SQLRETURN rc = SQLSetStmtAttr(cursor,SQL_ATTR_PARAMSET_SIZE,(SQLPOINTER)(long)ARRAY_SIZE,SQL_IS_INTEGER);
printf("%ld",rc);

//指定返回参数状态的数组
SQLSetStmtAttr(cursor,SQL_ATTR_PARAM_STATUS_PTR,ParamStatusArray,0);

//指定一个SQLUINTEGER值来返回参数号
SQLSetStmtAttr(cursor,SQL_ATTR_PARAMS_PROCESSED_PTR,&ParamsProcessed,0);


long tag = SQL_NTS;
long len = 2;
//以行式绑定参数
int len1= sizeof(SQL_TIMESTAMP_STRUCT);
rc = SQLBindParameter(cursor,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,12,0,&ChannelArray[0].mac_addr,13,&ChannelArray[0].mac_addrInd);//mac_addr
rc=SQLBindParameter(cursor,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_TYPE_TIMESTAMP,23,3,&ChannelArray[0].start_time,24,&ChannelArray[0].start_timeInd);//start_time //用SQL_VARCHAR比SQL_TYPE_TIMESTAMP6884，不知道可否用SQL_CHAR,使用下面一句6500稍快一点
//rc = SQLBindParameter(cursor,2,SQL_PARAM_INPUT,SQL_C_TYPE_TIMESTAMP ,SQL_TYPE_TIMESTAMP ,23,3,&ChannelArray[0].start_time,0,&ChannelArray[0].start_timeInd);//start_time
rc = SQLBindParameter(cursor,3,SQL_PARAM_INPUT,SQL_C_SSHORT,SQL_SMALLINT,5,0,&ChannelArray[0].start_channel_number,0,&ChannelArray[0].start_channel_numberInd);//start_channel_number
rc = SQLBindParameter(cursor,4,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,30,0,&ChannelArray[0].start_channel_name,31,&ChannelArray[0].start_channel_nameInd);//start_channle_name
rc = SQLBindParameter(cursor,5,SQL_PARAM_INPUT,SQL_C_SSHORT,SQL_SMALLINT,5,0,&ChannelArray[0].start_channel_name_id,0,&ChannelArray[0].start_channel_name_idInd);//start_channel_name_id
rc = SQLBindParameter(cursor,6,SQL_PARAM_INPUT,SQL_C_SSHORT,SQL_SMALLINT,5,0,&ChannelArray[0].start_video_id,0,&ChannelArray[0].start_video_idInd);//start_video_id
rc = SQLBindParameter(cursor,7,SQL_PARAM_INPUT,SQL_C_SSHORT,SQL_SMALLINT,5,0,&ChannelArray[0].start_audio_id,0,&ChannelArray[0].start_audio_idInd);//start_audio_id
rc = SQLBindParameter(cursor,8,SQL_PARAM_INPUT,SQL_C_SSHORT,SQL_SMALLINT,5,0,&ChannelArray[0].start_volume,0,&ChannelArray[0].start_volumeInd);//start_volume
rc = SQLBindParameter(cursor,9,SQL_PARAM_INPUT,SQL_C_SHORT,SQL_INTEGER,10,0,&ChannelArray[0].start_frequence,0,&ChannelArray[0].start_frequenceInd);//start_frequnce

SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0);
rc=SQLPrepare(cursor, Statement, SQL_NTS);


for (int j=0;j<100;j++)
{

//循环设置待插入的数据
for ( i=0;i {

sprintf((char *)ChannelArray[i].mac_addr,"%012ld",i);
SYSTEMTIME systime;
GetSystemTime(&systime);
sprintf((char *)ChannelArray[i].start_time,"%d-%02d-%02d %02d:%02d:%02d.%03d",systime.wYear,systime.wMonth,systime.wDay,systime.wHour,systime.wMinute,systime.wSecond,systime.wMilliseconds);

ChannelArray[i].start_channel_number=i%10000;
sprintf((char *)ChannelArray[i].start_channel_name,"频道%ld",i);
//ChannelArray[i].start_channel_name="频道"+i;
ChannelArray[i].start_channel_name_id=i%10000;
ChannelArray[i].start_video_id=4;
ChannelArray[i].start_audio_id=5;
ChannelArray[i].start_volume=50;
ChannelArray[i].start_frequence=555;

ChannelArray[i].mac_addrInd = SQL_NTS;
ChannelArray[i].start_timeInd = SQL_NTS;
ChannelArray[i].start_channel_numberInd=5;
ChannelArray[i].start_channel_nameInd=SQL_NTS;
ChannelArray[i].start_channel_name_idInd=0;
ChannelArray[i].start_video_idInd=0;
ChannelArray[i].start_audio_idInd=0;
ChannelArray[i].start_volumeInd=0;
ChannelArray[i].start_frequenceInd=0;

}

//执行语句
rc = SQLExecute(cursor);//似乎这种方式更快一些，从文档没有看出原因来
CHECK_STMT(cursor);


}
SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);//使用事务批量提交方式更快
//结束计时，统计结果
long time2 = GetTickCount();
printf("n 时间间隔 :%ld :n",time2-time1);
//释放连接 等等资源
conn.ReleaseDatabaseConn();
}

2.使用SQLBulkOperations SQL_ADD

void TestControl::BulkInsert(int count)
{
DatabseConn conn;
SQLHENV henv;
SQLHDBC hdbc;
HSTMT hstmt;

RETCODE retcode;


//连接到数据库
conn.ConnectToDatabase(henv,hdbc);
conn.DispatchCursor(hdbc,hstmt);


//开始计时
long time1=GetTickCount();//begin time


ChannelStruct ChannelArray[ARRAY_SIZE];
//Table1Struct ChannelArray[ARRAY_SIZE];
SQLUSMALLINT i,ParamsProcessed=0,ParamStatusArray[ARRAY_SIZE]={0};

SQLINTEGER nBindOffset = 0;

//SQLSetStmtAttr(hstmt,SQL_ATTR_USE_BOOKMARKS,(SQLPOINTER)SQL_UB_VARIABLE,0);



//保存到数据库
SQLCHAR * Statement = (unsigned char *)"insert into channel_transfer(mac_addr,start_time,start_channel_number,start_channel_name,start_channel_name_id,start_video_id,start_audio_id,start_volume,start_frequence) values(?,?,?,?,?,?,?,?,?)";

retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY, (SQLPOINTER) SQL_CONCUR_ROWVER, 0);


retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER) SQL_CURSOR_DYNAMIC, 0);


//使用行式绑定
SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_BIND_TYPE,(SQLPOINTER)sizeof(ChannelStruct),SQL_IS_INTEGER);
SQLRETURN rc;
//指定返回参数数组的元素数
//SQLRETURN rc = SQLSetStmtAttr(hstmt,SQL_ATTR_PARAMSET_SIZE,(SQLPOINTER)(long)NUM_OF_ROW_EACH_FETCH,SQL_IS_INTEGER);
rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) ARRAY_SIZE, 0);

printf("%ld",rc);

//指定返回参数数组的元素数
//SQLSetStmtAttr(hstmt,SQL_ATTR_PARAMSET_SIZE,(SQLPOINTER)(long)ARRAY_SIZE,SQL_IS_INTEGER);
//指定返回参数状态的数组
//SQLSetStmtAttr(hstmt,SQL_ATTR_PARAM_STATUS_PTR,ParamStatusArray,0);
rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, ParamStatusArray, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_OFFSET_PTR, (SQLPOINTER)&nBindOffset, 0 );


//指定一个SQLUINTEGER值来返回参数号
//SQLSetStmtAttr(hstmt,SQL_ATTR_PARAMS_PROCESSED_PTR,&ParamsProcessed,0);
//先执行查询
retcode = SQLExecDirect(hstmt,(unsigned char*)" select mac_addr,start_time,start_channel_number,start_channel_name,start_channel_name_id,start_video_id,start_audio_id,start_volume,start_frequence from channel_transfer ",SQL_NTS);
//调整游标位置
retcode = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);


SQLBindCol(hstmt,1,SQL_C_CHAR,&ChannelArray[0].mac_addr,11,&ChannelArray[0].mac_addrInd);

rc=SQLBindCol(hstmt,2,SQL_C_CHAR,&ChannelArray[0].start_time,23,&ChannelArray[0].start_timeInd);//start_time //用SQL_VARCHAR比SQL_TYPE_TIMESTAMP6884，不知道可否用SQL_CHAR,使用下面一句6500稍快一点
//rc = SQLBindParameter(cursor,2,SQL_PARAM_INPUT,SQL_C_TYPE_TIMESTAMP ,SQL_TYPE_TIMESTAMP ,23,3,&ChannelArray[0].start_time,0,&ChannelArray[0].start_timeInd);//start_time
rc = SQLBindCol(hstmt,3,SQL_C_SSHORT,&ChannelArray[0].start_channel_number,0,&ChannelArray[0].start_channel_numberInd);//start_channel_number
rc = SQLBindCol(hstmt,4,SQL_C_CHAR,&ChannelArray[0].start_channel_name,31,&ChannelArray[0].start_channel_nameInd);//start_channle_name
rc = SQLBindCol(hstmt,5,SQL_C_SSHORT,&ChannelArray[0].start_channel_name_id,0,&ChannelArray[0].start_channel_name_idInd);//start_channel_name_id
rc = SQLBindCol(hstmt,6,SQL_C_SSHORT,&ChannelArray[0].start_video_id,0,&ChannelArray[0].start_video_idInd);//start_video_id
rc = SQLBindCol(hstmt,7,SQL_C_SSHORT,&ChannelArray[0].start_audio_id,0,&ChannelArray[0].start_audio_idInd);//start_audio_id
rc = SQLBindCol(hstmt,8,SQL_C_SSHORT,&ChannelArray[0].start_volume,0,&ChannelArray[0].start_volumeInd);//start_volume
rc = SQLBindCol(hstmt,9,SQL_C_SHORT,&ChannelArray[0].start_frequence,0,&ChannelArray[0].start_frequenceInd);//start_frequnce

CHECK_STMT(hstmt);


for (int j=0;j<10000;j++)
{

//循环设置待插入的数据
for ( i=0;i {

sprintf((char *)ChannelArray[i].mac_addr,"%012ld",i+4);
SYSTEMTIME systime;
GetSystemTime(&systime);
sprintf((char *)ChannelArray[i].start_time,"%d-%02d-%02d %02d:%02d:%02d.%03d",systime.wYear,systime.wMonth,systime.wDay,systime.wHour,systime.wMinute,systime.wSecond,systime.wMilliseconds);
ChannelArray[i].start_timeInd = SQL_NTS;
/*
ChannelArray[i].start_time.year=systime.wYear;
ChannelArray[i].start_time.month=systime.wMonth;
ChannelArray[i].start_time.day=systime.wDay;
ChannelArray[i].start_time.hour=systime.wHour;
ChannelArray[i].start_time.minute=systime.wMinute;
ChannelArray[i].start_time.second=systime.wSecond;
ChannelArray[i].start_time.fraction=systime.wMilliseconds;// * 1000000;
ChannelArray[i].start_timeInd = 0;
*/
//ChannelArray[i].start_time=dtBegin.Format("%Y-%m-%D %H:%M:%S");
ChannelArray[i].start_channel_number=i%10000+2;
sprintf((char *)ChannelArray[i].start_channel_name,"频道%ld",i);
//ChannelArray[i].start_channel_name="频道"+i;
ChannelArray[i].start_channel_name_id=i%10000;
ChannelArray[i].start_video_id=5;
ChannelArray[i].start_audio_id=5;
ChannelArray[i].start_volume=50;
ChannelArray[i].start_frequence=555;

ChannelArray[i].mac_addrInd = SQL_NTS;

ChannelArray[i].start_channel_numberInd=6;
ChannelArray[i].start_channel_nameInd=SQL_NTS;
ChannelArray[i].start_channel_name_idInd=0;
ChannelArray[i].start_video_idInd=0;
ChannelArray[i].start_audio_idInd=0;
ChannelArray[i].start_volumeInd=0;
ChannelArray[i].start_frequenceInd=0;

}

//执行语句

rc = SQLBulkOperations(hstmt,SQL_ADD);
CHECK_STMT(hstmt);




}

//结束计时，统计结果
long time2 = GetTickCount();
printf("n 时间间隔 :%ld :n",time2-time1);
//释放资源
conn.ReleaseDatabaseConn();
}

总结：

使用第一种方法更快。与http://topic.csdn.net/u/20071224/10/bf2107d5-0a7c-4ed2-92f1-7ce948a937fc.html 结论一致。

插入总元组一定情况下，数组大小从10-100-1000，速度越来越快。

列类型为时间时，绑定参数为时间SQL_TIMESTAMP_STRUCT比使用字符串稍快一点，（也许数据库中使用整型存储时间会更快？）

使用SQLPrepare更好，SQLExecute比SQLExecDirect性能更好，前者专用于执行参数化SQL？

教训：

1.使用一个新函数之前耐心看看API解释，光看例子尝试可能浪费更多时间。SQLBindParameter，SQLBindCol的使用就是教训

2.ODBC中各种类型与SQL中类型，C中类型的对应关系，尤其是时间类型timestamp( 可以对应char,也可以对应timestamp_struct).

3.参数化数组与SQLBulkOperations对statement设置时，设置的参数有不一样的地方，前面设置PARAM的绑定类型，大小等等，后者设置的是行集的绑定类型，大小等等。

4.copy代码，以及参考例子时要注意。由于犯了3的错误。先试验的SQLBulkOperataions后试验参数化数组，对statement设置的是PARAM的绑定类型，大小等，而不是行集。结果出现了各种错误，“日期时间字段溢出”，“对于造型说明无效的字符值” 各种，浪费好多时间，才发现原来是参数设置问题。

也许更好的方式是使用文件，太麻烦，没有试。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/38085/viewspace-1002351/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/38085/viewspace-1002351/

cikonga371352
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
ODBC学习笔记—SQLSetStmtAttr
不上进的程序员
07-07 2099
SQLGetConnectAttr 函数定义： SQLRETURN SQLGetConnectAttr(      SQLHDBC     ConnectionHandle,      SQLINTEGER     Attribute,      SQLPOINTER     ValuePtr,      SQLINTEGER     BufferLength,      SQLIN
SqlBulkCopy(批量复制)使用方法
10-25
SqlBulkCopy提供了一种将数据复制到Sql Server数据库表中高性能的方法。SqlBulkCopy 包含一个方法 WriteToServer，它用来从数据的源复制数据到数据的目的地。 WriteToServer方法可以处理的数据类型有DataRow[]数组，DataTable 和 DataReader。 你可以根据不同的情形使用不同的数据类型。SqlBulkCopy其原理是采用了SQL Server的BCP协议进行数据的批量复制。
Bulk SQL Operations
weixin_30364325的博客
07-17 255
PL/SQL中的批量操作，允许一次操作和处理多行数据，而不是一次处理一行数据。批量操作包括批量的读取数据，批量的绑定处理处理数据:(bulk fetching and bulk binding) Bulk fetching refers to the ability to retrieve a set of rowsfrom the database into PL...
C#中使用SQL的BULK语句，进行大数据量操作
weixin_30718391的博客
07-26 950
下面是C#中如何使用SQL的BULK类语句，对大量数据执行操作。 下面主要是将另外一张表的内容移植到新的临时表中，以便后面操作。 Code stringconnectionString=ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString(); SqlConnectio...
使用可变数组sqlParameter来进行sql参数化查询
weixin_30265103的博客
04-14 193
1 2 3 这周在工作室开始与美工部分合作开发网站，自己写了一个简单的sql参数化查询类 4 5 using System; 6 using System.Collections.Generic; 7 using System.Data; 8 using System.Data.SqlClient; 9 using Syst...
SqlBulkCopy:批量插入SqlServer的利器
weixin_30247781的博客
07-13 114
废话少说，先看效果。295万多条业务数据一次性插入数据库的一个表中，数据大小大约为280M，总耗时38秒。上图， 表的定义是这样的， 如果使用事务，那么时间增长到46s，也就是多了8s。我用SqlBulkCopy的InternalTransaction做的测试， SqlBulkCopy sbc = new SqlBulkCopy(m_dal.ConnectionString, SqlBul...
C# Bulk Operations
Leckun的专栏
04-25 2935
Overcome SqlBulkCopy Limitations with C# Bulk Insw3school.com.cnert, Update, Delete and Merge // Support all type of operations var bulk = new BulkOperation(connection); bulk.BulkInsert(dt); bu
SQL Server错误代码大全及解释（留着备用）
12-15
SQL SERVER 驱动程序错误 描述  HY000 所有绑定列都是只读的。 必须是可升级的列，以使用 SQLSetPos 或 SQLBulkOperations 更改或插入行。 HY000 已检测到一个旧 netlib (%s)。请删除并重新启动应用...
C#中使用SqlBulk百万级数据秒级插入
机械键盘侠博客
07-01 3324
本文转自这篇文章，提供了一种较快的数据插入的思路，转过来做个记录。 #region static void Insert() { Console.WriteLine("使用Bulk插入的实现方式"); Stopwatch sw = new Stopwatch(); DataTable dt = GetTableSchema(); using...
直接执行语句 (ODBC)
lucky_fly的专栏
11-19 870
直接执行语句并且只执行一次 如果语句有参数标记，使用SQLBindParameter要绑定到程序变量的每个参数。 使用数据值填充程序变量，然后设置任何执行时数据参数。 调用SQLExecDirect执行语句。 如果使用执行时数据输入的参数， SQLExecDirect返回 SQL_NEED_DATA。 通过使用分块区发送数据SQLParamData并SQLPutData。...
Kesion cms 路径泄露漏洞
收集盒 Book box
09-09 2697
暴路径bug 1 : http://www.xx .com/user/editor.asp?ChannelID=10000&ID=Content ---------------------------------------------------------------------------------------------- 暴路径bug 2 : 最近看到一个科汛的cm
sql参数数组，及调用。SqlParameter[] Parameters
雨水霂
03-17 6840
1、参数数组： SqlParameter[] Parameters = new SqlParameter[] { new SqlParameter("@ID", ID), new SqlParameter("@Bh", Bh), new SqlParameter("@StuffName", StuffName), new SqlParameter("@Sex", Sex), new SqlP
使用类型参数化数组
杨文飞的博客
05-04 1157
在 Scala 中， 你可以使用 new 来实例化一个类。当你创建一个对象的实例时，你可以使用数值或类型参数。如果使用类型参数，它的作用类似 Java 或 .Net 的 Generic 类型。所不同的是， Scala 使用方括号来指明数据类型参数，而非尖括号。比如： val greetString = new Array[String](3) greetString(0)="Hello" g...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值