调用mysql的api
mysql_query(&mysql, sql.c_str());
我的查询语句sql如下
select * from (select * from SHSE_000905_min order by updatetime desc limit 500) a ORDER BY updatetime;
500条数据,
从2800个表中,每个表提取500行数据,每行数据有七个字段。这样总共需要1分多钟,正常吗,还是算慢的?
感觉保存在txt里再提取相同的数据量会比mysql快哎。
另外用mysql_use_result 和mysql_store_result都试了 差不多。
请教还有没有办法更快些,或者如果不需要其他功能确实还是txt文件比较快?
mysql_query(&mysql, sql.c_str());
我的查询语句sql如下
select * from (select * from SHSE_000905_min order by updatetime desc limit 500) a ORDER BY updatetime;
500条数据,
从2800个表中,每个表提取500行数据,每行数据有七个字段。这样总共需要1分多钟,正常吗,还是算慢的?
感觉保存在txt里再提取相同的数据量会比mysql快哎。
另外用mysql_use_result 和mysql_store_result都试了 差不多。
请教还有没有办法更快些,或者如果不需要其他功能确实还是txt文件比较快?
MYSQL_ROW m_row;
MYSQL_RES *m_res = NULL; // 数据结果集
time_t t1, t2;
t1 = time(NULL);
string sql1 = "select * from (select * from ";
string sql2 = " order by updatetime desc limit ";
string sql3 = ") a ORDER BY updatetime;";
string sql;
dataMin data;
for (int i = 0; symList[i] != NULL; i++){
//cout << i << endl;
string sqltb = symList[i];
int id = sqltb.find(".");
sqltb[id] = '_';
sql = sql1 + sqltb + "_min" + sql2 + to_string(MinInputSz) + sql3;
mysql_query(&mysql, sql.c_str());
m_res = mysql_use_result(&mysql);
while (m_row = mysql_fetch_row(m_res)){
strcpy_s(data.updatetime, sizeof(data.updatetime) / sizeof(char), m_row[0]);
char *buff = nullptr;
char symbuff[16];
strcpy_s(symbuff, symList[i]);
strcpy_s(data.exid, strtok_s(symbuff, ".", &buff));
strcpy_s(data.instid, strtok_s(NULL, ".", &buff));
sscanf_s(m_row[1], "%lf", &data.open);
sscanf_s(m_row[2], "%lf", &data.high);
sscanf_s(m_row[3], "%lf", &data.low);
sscanf_s(m_row[4], "%lf", &data.close);
sscanf_s(m_row[5], "%lf", &data.volume);
sscanf_s(m_row[6], "%lf", &data.amount);
dataM[symList[i]].push_back(data);
memset(&data, 0, sizeof(data));
}
/*
m_res = mysql_store_result(&mysql);//很慢 该用
if (m_res == NULL){
isErrorMysql();
continue;
}
int fieldCnt = mysql_num_fields(m_res);
while (m_row = mysql_fetch_row(m_res)){
strcpy_s(data.updatetime, sizeof(data.updatetime) / sizeof(char), m_row[0]);
char *buff = nullptr;
char symbuff[16];
strcpy_s(symbuff, symList[i]);
strcpy_s(data.exid, strtok_s(symbuff, ".", &buff));
strcpy_s(data.instid, strtok_s(NULL, ".", &buff));
sscanf_s(m_row[1], "%lf", &data.open);
sscanf_s(m_row[2], "%lf", &data.high);
sscanf_s(m_row[3], "%lf", &data.low);
sscanf_s(m_row[4], "%lf", &data.close);
sscanf_s(m_row[5], "%lf", &data.volume);
sscanf_s(m_row[6], "%lf", &data.amount);
//dataD[symList[i]].push_back(data);
memset(&data, 0, sizeof(data));
}
*/
mysql_free_result(m_res);
}
t2 = time(NULL);
printf("运行时间是: %ld", t2 - t1);
return;
}
这是为什么?
代码如下: MYSQL_ROW m_row;
MYSQL_RES *m_res = NULL; // 数据结果集
time_t t1, t2;
t1 = time(NULL);
string sql1 = "select * from (select * from ";
string sql2 = " order by updatetime desc limit ";
string sql3 = ") a ORDER BY updatetime;";
string sql;
dataMin data;
for (int i = 0; symList[i] != NULL; i++){
//cout << i << endl;
string sqltb = symList[i];
int id = sqltb.find(".");
sqltb[id] = '_';
sql = sql1 + sqltb + dType + sql2 + "500" + sql3;
//sql = sql1 + "shse_600519_min"+ sql2 + "500" + sql3;
mysql_query(&mysql, sql.c_str());
m_res = mysql_store_result(&mysql);
while (m_row = mysql_fetch_row(m_res)){
}
mysql_free_result(m_res);
}
t2 = time(NULL);
printf("运行时间是: %ld", t2 - t1);
这是重复查询一个表,运行时间是4秒。
MYSQL_ROW m_row;
MYSQL_RES *m_res = NULL; // 数据结果集
time_t t1, t2;
t1 = time(NULL);
string sql1 = "select * from (select * from ";
string sql2 = " order by updatetime desc limit ";
string sql3 = ") a ORDER BY updatetime;";
string sql;
dataMin data;
for (int i = 0; symList[i] != NULL; i++){
//cout << i << endl;
string sqltb = symList[i];
int id = sqltb.find(".");
sqltb[id] = '_';
//sql = sql1 + sqltb + dType + sql2 + "500" + sql3;
sql = sql1 + "shse_600519_min"+ sql2 + "500" + sql3;
mysql_query(&mysql, sql.c_str());
m_res = mysql_store_result(&mysql);
while (m_row = mysql_fetch_row(m_res)){
}
mysql_free_result(m_res);
}
t2 = time(NULL);
printf("运行时间是: %ld", t2 - t1);[/code]
这是查询i个表(总共近3000个表),运行时间70多秒.
我的表结构都是相同的,数据量差不多的。