如何更快地將資料插入資料庫。經過老師指點，在網路搜尋半天，實驗了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();
}

總結：

使用第一種方法更快。與結論一致。

插入總元組一定情況下，陣列大小從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的繫結型別，大小等，而不是行集。結果出現了各種錯誤，“日期時間欄位溢位”，“對於造型說明無效的字元值” 各種，浪費好多時間，才發現原來是引數設定問題。

也許更好的方式是使用檔案，太麻煩，沒有試。

SQLBulkOperations及陣列作引數的SQL

相關文章