Speed ​​problem when using SQLite in Qt

[sys1]

hello,

1. I made a function(Fx_DB) to access DB using C++ and sqlite (A.cpp, A.h)
2. In the QT program, A.cpp and A.h made above are included.
3. After that, I call the function(Fx_DB) I made in the QT program.

The problem is that speed is not a problem when calling Fx_DB from A.cpp,
but calling Fx_DB from QT program slows the speed (about 1 second -> 17 seconds).

I want to know why.
And the delete operation is also slow.

I attach a part of the source code I used below.

Main sequence of operations

1. Get the "order date" and "order number" from the table A by "customer number".
2. In the table B, it finds the matching "order date" and "order number".
3. Insert the found data in the C table.
   ============================================
   << A.cpp - Fx_DB >>

void Fx_DB(sqlite3* db, int CustomerNo)
{
static bool firstStart = true;
CustomerRelation data;
vector<CustomerRelation> vectorCustomerOrder;
vector<CustomerRelation>::iterator it_co;
int count = 0;
char szString[5][500];
int price[4] = { 0 };
string Orders[9];
string SQL_CREATE_TEMP_TABLE = createTempCustomerOrder;
string SQL_CUSTOMER_ORDER;
string SQL_INSESRT_TEMP;
string SQL_CUSTOMER_RELATATION;
sqlite3_stmt* stmt1 = NULL;
sqlite3_stmt* stmt2 = NULL;
sqlite3_stmt* stmt3 = NULL;

if (firstStart)
{
	executeForward(db, "DROP TABLE TEMP_CUSTOMER_ORDER;");
	executeForward(db, SQL_CREATE_TEMP_TABLE);
	firstStart = false;
}

// !!! slow !!! 
// delete data in table 
if (existTable(db, "TEMP_CUSTOMER_ORDER"))
{
	sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, NULL, NULL);
	executeForward(db, "DELETE FROM TEMP_CUSTOMER_ORDER;");
	sqlite3_exec(db, "END TRANSACTION;", NULL, NULL, NULL);

}

SQL_CUSTOMER_RELATATION = "SELECT * FROM CUSTOMER_RELATION WHERE CUSTOMER_NO = \"" + to_string(CustomerNo) + "\";";
sqlite3_prepare_v2(db, SQL_CUSTOMER_RELATATION.c_str(), -1, &stmt3, NULL);
while (sqlite3_step(stmt3) == SQLITE_ROW)
{
	data.setCustomerNumber(CustomerNo);
	data.setDate(insertStringNull(&stmt3, 1));
	data.setBillNumber(insertStringNull(&stmt3, 2));

	// save data
	vectorCustomerOrder.push_back(data);
}

// use transaction unit 
sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, NULL, NULL);

// !!! very slow !!!
for (it_co = vectorCustomerOrder.begin(); it_co != vectorCustomerOrder.end(); it_co++)
{
	SQL_CUSTOMER_ORDER = "SELECT * FROM MAIN_ORDER WHERE OPER_DT = \"" + it_co->getDate() + "\" AND BILL_NO = \"" \
		+ it_co->getBillNumber() + "\";";

	sqlite3_prepare_v2(db, SQL_CUSTOMER_ORDER.c_str(), -1, &stmt1, NULL);
	if (sqlite3_step(stmt1) == SQLITE_ROW)
	{
		//  insert into temp array
		Orders[0] = insertStringNull(&stmt1, 0);
		Orders[1] = insertStringNull(&stmt1, 1);
		Orders[2] = insertStringNull(&stmt1, 2);
		Orders[3] = insertStringNull(&stmt1, 3);
		price[0] = sqlite3_column_int(stmt1, 4);
		price[1] = sqlite3_column_int(stmt1, 5);
		price[2] = sqlite3_column_int(stmt1, 6);
		price[3] = sqlite3_column_int(stmt1, 7);
		Orders[4] = insertStringNull(&stmt1, 8);
		Orders[5] = insertStringNull(&stmt1, 9);
		Orders[6] = insertStringNull(&stmt1, 10);
		Orders[7] = insertStringNull(&stmt1, 11);
		Orders[8] = insertStringNull(&stmt1, 12);

		// insert data
		SQL_INSESRT_TEMP = "INSERT INTO TEMP_CUSTOMER_ORDER VALUES(\"" + Orders[0] + "\", \"" + Orders[1] + "\", \"" + Orders[2] + "\", \""\
			+ Orders[3] + "\", " + to_string(price[0]) + ", " + to_string(price[1]) + ", " + to_string(price[2]) + ", "\
			+ to_string(price[3]) + ", \"" + Orders[4] + "\", \"" + Orders[5] + "\", \"" + Orders[6] + "\", \""\
			+ Orders[7] + "\", \"" + Orders[8] + "\");";

		executeForward(db, SQL_INSESRT_TEMP);
	}
}
sqlite3_exec(db, "END TRANSACTION;", NULL, NULL, NULL);

cout << "===========================" << endl;

sqlite3_finalize(stmt1);
sqlite3_finalize(stmt2);
sqlite3_finalize(stmt3);

}

===========================================================
<< Qt Program>>

// call function
Fx_DB(db, customerNo);

===========================================================

thank you

[SGaist]

Hi,

If you want people to help find what is going on you have to provide one minimal compilable project for each situation.

You also have to give more details about your environment and setup.

[Christian Ehrlicher]

And use the <code> - tags so the source code is readable.

[JonB]

@sys1 said in Speed ​​problem when using SQLite in Qt:

The problem is that speed is not a problem when calling Fx_DB from A.cpp,
but calling Fx_DB from QT program slows the speed

While you are answering the two posts above :) I just don't understand what you mean/what you are comparing here? What do you mean by calling a function from a .cpp file versus from a Qt program?