Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Speed ​​problem when using SQLite in Qt



  • 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


  • Lifetime Qt Champion

    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.


  • Lifetime Qt Champion

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



  • @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?


Log in to reply