sql 语句执行流程:剖析 – 编译 – 执行
问题
当执行 10000 次插入操作时,sql 语句须要被“剖析 – 编译 – 执行”10000 次,这是比拟低效的;
- 示例:
char insert_sql[128] = {0};
for (int i=0; i<10000; ++i)
{sprintf(insert_sql, "insert into bindtest values(%d, %d)", i, i*100);
ret = sqlite3_exec(ppdb, insert_sql, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{printf("insert fail\n");
break;
}
sqlite3_free(errmsg);
}
尝试解决
用绑定机制,能够使 sql 语句只被解析一次,之后的操作应用第一次生成的执行打算,以此带来更高的执行效率。
应用步骤:
配备
int sqlite3_prepare(
sqlite3 *db, /* Database handle */
const char *zSql, /* SQL statement, UTF-8 encoded */
int nByte, /* Maximum length of zSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
const char **pzTail /* OUT: Pointer to unused portion of zSql */
);
绑定数据
int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
int sqlite3_bind_blob64(sqlite3_stmt*, int, const void*, sqlite3_uint64,
void(*)(void*));
int sqlite3_bind_double(sqlite3_stmt*, int, double);
int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
int sqlite3_bind_null(sqlite3_stmt*, int);
int sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void*));
int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*));
int sqlite3_bind_text64(sqlite3_stmt*, int, const char*, sqlite3_uint64,
void(*)(void*), unsigned char encoding);
int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
int sqlite3_bind_pointer(sqlite3_stmt*, int, void*, const char*,void(*)(void*));
int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);
int sqlite3_bind_zeroblob64(sqlite3_stmt*, int, sqlite3_uint64);
执行
int sqlite3_step(sqlite3_stmt* mStmt);
如果循环操作(绑定)时
int sqlite3_finalize(sqlite3_stmt *pStmt);
开释
sqlite3_finalize(stmt);
- 示例:
sqlite3_stmt *pStmt = NULL;
char insert[] = "insert into bindtest values(?, ?)";
sqlite3_prepare(ppdb, insert, sizeof(insert), &pStmt, NULL);
// 2. 绑定数据
for (int i=0; i<10000; ++i)
{sqlite3_bind_int(pStmt, 1, i); // 留神这里,绑定从 1 开始!!!sqlite3_bind_int(pStmt, 2, i * 100);
sqlite3_step(pStmt); // 执行插入
sqlite3_reset(pStmt); // 重置
}
// 3. 开释
sqlite3_finalize(pStmt);
编程试验
#include <stdio.h>
#include <sqlite3.h>
#include <time.h>
int main()
{
// 1. 关上数据库
sqlite3 *ppdb = NULL;
int ret = sqlite3_open(":memory:", &ppdb);
if (ret != SQLITE_OK)
{printf("open fail\n");
return -1;
}
// 2. 执行 sql 语句
// 2.1 创建表格 create table apitest(id int, number int);
const char *create_sql = "create table if not exists bindtest(id int, number int)";
char *errmsg = NULL;
ret = sqlite3_exec(ppdb, create_sql, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{printf("%s\n", errmsg);
}
sqlite3_free(errmsg);
// 2.2 数据直接插入 ==============================
struct timeval start;
struct timeval end;
char insert_sql[128] = {0};
mingw_gettimeofday(&start, NULL);
for (int i=0; i<10000; ++i)
{sprintf(insert_sql, "insert into bindtest values(%d, %d)", i, i*100);
ret = sqlite3_exec(ppdb, insert_sql, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{printf("insert fail\n");
break;
}
sqlite3_free(errmsg);
}
mingw_gettimeofday(&end, NULL);
printf("nsert directly: %ld\n", end.tv_usec - start.tv_usec);
// 2.3 数据绑定插入 ==============================
sqlite3_stmt *pStmt = NULL;
char insert[] = "insert into bindtest values(?, ?)";
mingw_gettimeofday(&start, NULL);
sqlite3_prepare(ppdb, insert, sizeof(insert), &pStmt, NULL);
for (int i=0; i<10000; ++i)
{sqlite3_bind_int(pStmt, 1, i);
sqlite3_bind_int(pStmt, 2, i * 100);
sqlite3_step(pStmt);
sqlite3_reset(pStmt);
}
sqlite3_finalize(pStmt);
mingw_gettimeofday(&end, NULL);
printf("bind directly: %ld\n", end.tv_usec - start.tv_usec);
// 3. 敞开数据库
ret = sqlite3_close(ppdb);
if (ret != SQLITE_OK)
{printf("close fail\n");
return -1;
}
return 0;
}
输入:
nsert directly: 59873
bind directly: 27925