Hello, I am new to SQL to SQLite. To practice I wrote a program that generates a database representing a random graph. I have a performance problem with the code. I do not think that all the code relevant to my question, but I can provide it if needed.

I can open the database in two different ways:

sqlite3_open_v2(NULL, &db, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CR\
    EATE|SQLITE_OPEN_MEMORY, NULL);

sqlite3_open_v2("graph.db", &db, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CR\
    EATE, NULL);

From what I understand, the first way makes the database in memory and the second way makes it on disk. The first way is 172x faster on my computer for my graph database generator.

The problem is that I would like to do the generation in memory and then save to a file. So I read about the SQL VACUUM statement. Here is my full code for saving the database:

int save_database(sqlite3 *db, const char *fn) {
	sqlite3_stmt *stmt;
	if (SQLITE_OK != sqlite3_prepare_v2(db, "VACUUM INTO ?;", -1, &stmt, NULL)) {
		fprintf(stderr, "a: %s\n", sqlite3_errmsg(db));
		return 1;
	}
	if (SQLITE_OK != sqlite3_bind_text(stmt, 1, fn, -1, SQLITE_STATIC)) {
		fprintf(stderr, "b: %s\n", sqlite3_errmsg(db));
		return 1;
	}
	printf("%s\n", sqlite3_expanded_sql(stmt));
	if (SQLITE_OK != sqlite3_step(stmt)) {
		fprintf(stderr, "c: %s\n", sqlite3_errmsg(db));
		return 1;
	};
	sqlite3_finalize(stmt);
	return 0;

I get the response c: no more rows available, but I am not understanding what this means in this context. It doesn't write anything to the file fn. Any help is much appreciated!

no more rows available corresponds to SQLITE_DONE, meaning it completed successfully.

Check the documentation for sqlite3_step(). It will not return SQLITE_OK; if there are no errors, it will either return SQLITE_ROW or SQLITE_DONE.

Any help is much appreciated!

This doesn't answer your question (an anonymous contributor already did that) but it does resolve a memory leak:

int rc = 0;
if (SQLITE_OK != sqlite3_bind_text(stmt, 1, fn, -1, SQLITE_STATIC)) {
    fprintf(stderr, "b: %s\n", sqlite3_errmsg(db));
    rc = 1;
    goto end;
}
printf("%s\n", sqlite3_expanded_sql(stmt));
if (SQLITE_OK != sqlite3_step(stmt)) {
    fprintf(stderr, "c: %s\n", sqlite3_errmsg(db));
    rc = 1;
    goto end;
};
end:
sqlite3_finalize(stmt);
return rc;

"The problem" is that you are returning without finalizing the statement, which leaks it. Having an opened statement can cause you locking-related problems, so it's always good to finalize a statement you don't need any more or to call sqlite3_reset() on longer-lived statements when their immediate use is done and they'll be waiting to be reused later on.

I do not think the command was successful, or maybe I am misunderstanding what VACUUM does. Suppose I open the database in the second way as a file graph.db, and to copy the database I run save_database(db, "graphcopy.db");. In this case, the file graphcopy.db is created successfully on the filesystem with the same contents as the original database.

But in the case that I use the first version of opening the database, where it is located in memory, if I run save_database(db, "graphcopy.db");, then no file gets generated. That is my main issue. I want to VACUUM from an in-memory database into a file to save the work that was done, but no file is being created.