Loadrunner测试数据库、SQL语句性能
2023-09-11 14:20:55 时间
Action()
{
int rc;
int db_connection; // 数据库连接
int query_result; // 查询结果集 MYSQL_RES
char** result_row; // 查询的数据衕
char *server = "localhost";
char *user = "root";
char *password = "123456";
char *database = "test";
int port = 3306;
int unix_socket = NULL;
int flags = 0;
// 找到libmysql.dll的所在位置.
rc = lr_load_dll("C:\\Program Files\\MySQL\\MySQL Server 5.1\\bin\\libmysql.dll");
if (rc != 0) {
lr_error_message("Could not load libmysql.dll");
lr_abort();
}
// 创建MySQL对象
db_connection = mysql_init(NULL);
if (db_connection == NULL) {
lr_error_message("Insufficient memory");
lr_abort();
}
// 连接到MySQL数据库
rc = mysql_real_connect(db_connection, server, user, password, database, port, unix_socket, flags);
if (rc == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 向数据库插入数据
// 此处的 {ORDER_ID} 是一个参数,简单测试时可以用一个常数代替
lr_save_string (lr_eval_string("INSERT INTO test_data (order_id) VALUES ({ORDER_ID})"),"paramInsertQuery");
rc = mysql_query(db_connection, lr_eval_string("{paramInsertQuery}"));
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 从数据库读取一个数据并显示
rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1");
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
query_result = mysql_use_result(db_connection);
if (query_result == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
// 如果结果集包含多行数据,需要多次调用 mysql_fetch_row 直到返回NULL
result_row = (char **)mysql_fetch_row(query_result);
if (result_row == NULL) {
lr_error_message("Did not expect the result set to be empty");
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
// 保存参数,用于删除这行数据
lr_save_string(result_row[0], "paramOrderID");
lr_output_message("Order ID is: %s", lr_eval_string("{paramOrderID}"));
mysql_free_result(query_result);
// 在事务里更新一行数据,需要用InnoDB引擎
rc = mysql_query(db_connection, "BEGIN"); //启动事务
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 使用 "FOR UPDATE" 锁住要更新的数据行
rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1 FOR UPDATE");
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
query_result = mysql_use_result(db_connection);
if (query_result == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
result_row = (char **)mysql_fetch_row(query_result);
if (result_row == NULL) {
lr_error_message("没有查询到结果");
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
lr_save_string(result_row[0], "paramOrderID");
lr_output_message("Order ID is: %s", lr_eval_string("{paramOrderID}"));
mysql_free_result(query_result);
lr_save_string(lr_eval_string("UPDATE test_data SET status=TRUE, date_used=NOW() WHERE order_id={paramOrderID}"),"paramUpdateQuery");
rc = mysql_query(db_connection, lr_eval_string("{paramUpdateQuery}"));
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
rc = mysql_query(db_connection, "COMMIT"); // 提交事务
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 再次查找数据,应该为空了,因为前面的事务更新了标志
rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1");
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
query_result = mysql_use_result(db_connection);
if (query_result == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
result_row = (char **)mysql_fetch_row(query_result);
if (result_row == NULL) {
lr_output_message("Result set is empty as expected");
mysql_free_result(query_result);
} else {
lr_error_message("Did not expect the result set to contain any rows");
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
// 删除数据
lr_save_string(lr_eval_string("DELETE FROM test_data WHERE order_id = {paramOrderID}"),"paramDeleteQuery");
rc = mysql_query(db_connection, lr_eval_string("{paramDeleteQuery}"));
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 释放MySQL资源
mysql_close(db_connection);
return 0;
}
Action()
{
int rc;
int db_connection; // 数据库连接
int query_result; // 查询结果集 MYSQL_RES
char** result_row; // 查询的数据衕
char *server = "localhost";
char *user = "root";
char *password = "123456";
char *database = "test";
int port = 3306;
int unix_socket = NULL;
int flags = 0;
// 找到libmysql.dll的所在位置.
rc = lr_load_dll("C:\\Program Files\\MySQL\\MySQL Server 5.1\\bin\\libmysql.dll");
if (rc != 0) {
lr_error_message("Could not load libmysql.dll");
lr_abort();
}
// 创建MySQL对象
db_connection = mysql_init(NULL);
if (db_connection == NULL) {
lr_error_message("Insufficient memory");
lr_abort();
}
// 连接到MySQL数据库
rc = mysql_real_connect(db_connection, server, user, password, database, port, unix_socket, flags);
if (rc == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 向数据库插入数据
// 此处的 {ORDER_ID} 是一个参数,简单测试时可以用一个常数代替
lr_save_string (lr_eval_string("INSERT INTO test_data (order_id) VALUES ({ORDER_ID})"),"paramInsertQuery");
rc = mysql_query(db_connection, lr_eval_string("{paramInsertQuery}"));
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 从数据库读取一个数据并显示
rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1");
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
query_result = mysql_use_result(db_connection);
if (query_result == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
// 如果结果集包含多行数据,需要多次调用 mysql_fetch_row 直到返回NULL
result_row = (char **)mysql_fetch_row(query_result);
if (result_row == NULL) {
lr_error_message("Did not expect the result set to be empty");
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
// 保存参数,用于删除这行数据
lr_save_string(result_row[0], "paramOrderID");
lr_output_message("Order ID is: %s", lr_eval_string("{paramOrderID}"));
mysql_free_result(query_result);
// 在事务里更新一行数据,需要用InnoDB引擎
rc = mysql_query(db_connection, "BEGIN"); //启动事务
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 使用 "FOR UPDATE" 锁住要更新的数据行
rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1 FOR UPDATE");
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
query_result = mysql_use_result(db_connection);
if (query_result == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
result_row = (char **)mysql_fetch_row(query_result);
if (result_row == NULL) {
lr_error_message("没有查询到结果");
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
lr_save_string(result_row[0], "paramOrderID");
lr_output_message("Order ID is: %s", lr_eval_string("{paramOrderID}"));
mysql_free_result(query_result);
lr_save_string(lr_eval_string("UPDATE test_data SET status=TRUE, date_used=NOW() WHERE order_id={paramOrderID}"),"paramUpdateQuery");
rc = mysql_query(db_connection, lr_eval_string("{paramUpdateQuery}"));
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
rc = mysql_query(db_connection, "COMMIT"); // 提交事务
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 再次查找数据,应该为空了,因为前面的事务更新了标志
rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1");
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
query_result = mysql_use_result(db_connection);
if (query_result == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
result_row = (char **)mysql_fetch_row(query_result);
if (result_row == NULL) {
lr_output_message("Result set is empty as expected");
mysql_free_result(query_result);
} else {
lr_error_message("Did not expect the result set to contain any rows");
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
// 删除数据
lr_save_string(lr_eval_string("DELETE FROM test_data WHERE order_id = {paramOrderID}"),"paramDeleteQuery");
rc = mysql_query(db_connection, lr_eval_string("{paramDeleteQuery}"));
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 释放MySQL资源
mysql_close(db_connection);
return 0;
}
以小博大外小内大,Db数据库SQL优化之小数据驱动大数据 SQL优化中,有一条放之四海而皆准的既定方针,那就是:永远以小数据驱动大数据。其本质其实就是以小的数据样本作为驱动查询能够优化查询效率,在SQL中,涉及到不同表数据的连接、转移、或者合并,这些操作必须得有个数据集作为“带头”大哥,即驱动数据,而这个驱动数据最好是数据量最小的那一个。
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载
{
int rc;
int db_connection; // 数据库连接
int query_result; // 查询结果集 MYSQL_RES
char** result_row; // 查询的数据衕
char *server = "localhost";
char *user = "root";
char *password = "123456";
char *database = "test";
int port = 3306;
int unix_socket = NULL;
int flags = 0;
// 找到libmysql.dll的所在位置.
rc = lr_load_dll("C:\\Program Files\\MySQL\\MySQL Server 5.1\\bin\\libmysql.dll");
if (rc != 0) {
lr_error_message("Could not load libmysql.dll");
lr_abort();
}
// 创建MySQL对象
db_connection = mysql_init(NULL);
if (db_connection == NULL) {
lr_error_message("Insufficient memory");
lr_abort();
}
// 连接到MySQL数据库
rc = mysql_real_connect(db_connection, server, user, password, database, port, unix_socket, flags);
if (rc == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 向数据库插入数据
// 此处的 {ORDER_ID} 是一个参数,简单测试时可以用一个常数代替
lr_save_string (lr_eval_string("INSERT INTO test_data (order_id) VALUES ({ORDER_ID})"),"paramInsertQuery");
rc = mysql_query(db_connection, lr_eval_string("{paramInsertQuery}"));
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 从数据库读取一个数据并显示
rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1");
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
query_result = mysql_use_result(db_connection);
if (query_result == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
// 如果结果集包含多行数据,需要多次调用 mysql_fetch_row 直到返回NULL
result_row = (char **)mysql_fetch_row(query_result);
if (result_row == NULL) {
lr_error_message("Did not expect the result set to be empty");
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
// 保存参数,用于删除这行数据
lr_save_string(result_row[0], "paramOrderID");
lr_output_message("Order ID is: %s", lr_eval_string("{paramOrderID}"));
mysql_free_result(query_result);
// 在事务里更新一行数据,需要用InnoDB引擎
rc = mysql_query(db_connection, "BEGIN"); //启动事务
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 使用 "FOR UPDATE" 锁住要更新的数据行
rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1 FOR UPDATE");
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
query_result = mysql_use_result(db_connection);
if (query_result == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
result_row = (char **)mysql_fetch_row(query_result);
if (result_row == NULL) {
lr_error_message("没有查询到结果");
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
lr_save_string(result_row[0], "paramOrderID");
lr_output_message("Order ID is: %s", lr_eval_string("{paramOrderID}"));
mysql_free_result(query_result);
lr_save_string(lr_eval_string("UPDATE test_data SET status=TRUE, date_used=NOW() WHERE order_id={paramOrderID}"),"paramUpdateQuery");
rc = mysql_query(db_connection, lr_eval_string("{paramUpdateQuery}"));
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
rc = mysql_query(db_connection, "COMMIT"); // 提交事务
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 再次查找数据,应该为空了,因为前面的事务更新了标志
rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1");
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
query_result = mysql_use_result(db_connection);
if (query_result == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
result_row = (char **)mysql_fetch_row(query_result);
if (result_row == NULL) {
lr_output_message("Result set is empty as expected");
mysql_free_result(query_result);
} else {
lr_error_message("Did not expect the result set to contain any rows");
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
// 删除数据
lr_save_string(lr_eval_string("DELETE FROM test_data WHERE order_id = {paramOrderID}"),"paramDeleteQuery");
rc = mysql_query(db_connection, lr_eval_string("{paramDeleteQuery}"));
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 释放MySQL资源
mysql_close(db_connection);
return 0;
}
Action()
{
int rc;
int db_connection; // 数据库连接
int query_result; // 查询结果集 MYSQL_RES
char** result_row; // 查询的数据衕
char *server = "localhost";
char *user = "root";
char *password = "123456";
char *database = "test";
int port = 3306;
int unix_socket = NULL;
int flags = 0;
// 找到libmysql.dll的所在位置.
rc = lr_load_dll("C:\\Program Files\\MySQL\\MySQL Server 5.1\\bin\\libmysql.dll");
if (rc != 0) {
lr_error_message("Could not load libmysql.dll");
lr_abort();
}
// 创建MySQL对象
db_connection = mysql_init(NULL);
if (db_connection == NULL) {
lr_error_message("Insufficient memory");
lr_abort();
}
// 连接到MySQL数据库
rc = mysql_real_connect(db_connection, server, user, password, database, port, unix_socket, flags);
if (rc == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 向数据库插入数据
// 此处的 {ORDER_ID} 是一个参数,简单测试时可以用一个常数代替
lr_save_string (lr_eval_string("INSERT INTO test_data (order_id) VALUES ({ORDER_ID})"),"paramInsertQuery");
rc = mysql_query(db_connection, lr_eval_string("{paramInsertQuery}"));
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 从数据库读取一个数据并显示
rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1");
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
query_result = mysql_use_result(db_connection);
if (query_result == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
// 如果结果集包含多行数据,需要多次调用 mysql_fetch_row 直到返回NULL
result_row = (char **)mysql_fetch_row(query_result);
if (result_row == NULL) {
lr_error_message("Did not expect the result set to be empty");
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
// 保存参数,用于删除这行数据
lr_save_string(result_row[0], "paramOrderID");
lr_output_message("Order ID is: %s", lr_eval_string("{paramOrderID}"));
mysql_free_result(query_result);
// 在事务里更新一行数据,需要用InnoDB引擎
rc = mysql_query(db_connection, "BEGIN"); //启动事务
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 使用 "FOR UPDATE" 锁住要更新的数据行
rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1 FOR UPDATE");
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
query_result = mysql_use_result(db_connection);
if (query_result == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
result_row = (char **)mysql_fetch_row(query_result);
if (result_row == NULL) {
lr_error_message("没有查询到结果");
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
lr_save_string(result_row[0], "paramOrderID");
lr_output_message("Order ID is: %s", lr_eval_string("{paramOrderID}"));
mysql_free_result(query_result);
lr_save_string(lr_eval_string("UPDATE test_data SET status=TRUE, date_used=NOW() WHERE order_id={paramOrderID}"),"paramUpdateQuery");
rc = mysql_query(db_connection, lr_eval_string("{paramUpdateQuery}"));
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
rc = mysql_query(db_connection, "COMMIT"); // 提交事务
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 再次查找数据,应该为空了,因为前面的事务更新了标志
rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1");
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
query_result = mysql_use_result(db_connection);
if (query_result == NULL) {
lr_error_message("%s", mysql_error(db_connection));
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
result_row = (char **)mysql_fetch_row(query_result);
if (result_row == NULL) {
lr_output_message("Result set is empty as expected");
mysql_free_result(query_result);
} else {
lr_error_message("Did not expect the result set to contain any rows");
mysql_free_result(query_result);
mysql_close(db_connection);
lr_abort();
}
// 删除数据
lr_save_string(lr_eval_string("DELETE FROM test_data WHERE order_id = {paramOrderID}"),"paramDeleteQuery");
rc = mysql_query(db_connection, lr_eval_string("{paramDeleteQuery}"));
if (rc != 0) {
lr_error_message("%s", mysql_error(db_connection));
mysql_close(db_connection);
lr_abort();
}
// 释放MySQL资源
mysql_close(db_connection);
return 0;
}
====================================分割线================================
最新内容请见作者的GitHub页:http://qaseven.github.io/
以小博大外小内大,Db数据库SQL优化之小数据驱动大数据 SQL优化中,有一条放之四海而皆准的既定方针,那就是:永远以小数据驱动大数据。其本质其实就是以小的数据样本作为驱动查询能够优化查询效率,在SQL中,涉及到不同表数据的连接、转移、或者合并,这些操作必须得有个数据集作为“带头”大哥,即驱动数据,而这个驱动数据最好是数据量最小的那一个。
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载
相关文章
- 接口测试常用技能:Jmeter操作数据库
- 接口测试常用技能:Jmeter操作数据库
- python通过ssh连接mysql数据库的注意事项
- 数据库设计中的14个技巧
- 科技云报道:金融级高可用!天翼云TeleDB数据库如何实现容灾双活?
- 数据库新增一条数据,前端页面却看不到?
- 接口测试常用技能:Jmeter操作数据库
- 性能测试:自建数据库与RDS性能对比SQL Server案例排查分析
- NoSQL数据库笔谈(转载)
- 【docker】dbclient远程访问非本机数据库(mysql,oracle,postgres,sqlserver/mssql),测试远程数据库连通性
- ThinkPHP3.2.3新特性之:数据库设置
- django之创建第8-3个项目-数据库数据提取之高级操作
- 《测试驱动数据库开发》—第1章1.4节小结
- 使用PL/SQL将sql脚本数据导入数据库
- 数据库 = filesystem + transcation + dsl + dslengine
- PySe-009-测试辅助示例应用数据库更新语句创建
- 怎样进行数据库性能测试