Results 1 to 5 of 5

Thread: Slow insert with Transaction?!

  1. 6th October 2010, 19:03 #1
    codeman
    codeman is offline Intermediate user
    Join Date
    Apr 2009
    Posts
    206
    Thanks
    34
    Thanked 2 Times in 2 Posts

    Default Slow insert with Transaction?!

    Hello friends,

    in my app I have to insert many rows into a mssql table, but surprisingly it take many time to finish. I have to wait for example for 1000 records nearly 3 minutes, I think that is to much isn´t it.

    my code for the insert block looks like this:
    Qt Code: 
    1. QFile myreadfile("inputdata.txt");
    2.     if (!myreadfile.open(QIODevice::ReadOnly | QIODevice::Text))
    3. 	{
    4.         qDebug() << "Fehler beim Öffnen der Datei:" << "inputdata.txt";
    5. 	}
    6. 	else
    7. 	{
    8. 		QSqlDatabase db_mydatabase = QSqlDatabase::database("MSSQL_DB");
    9. 		QString qstr_wholeFile = myreadfile.readAll();
    10. 		QStringList qstrL_WholeFile = qstr_wholeFile.split("\n");
    11. 		qDebug() <<"Transaction :" <<  db_mydatabase.transaction();
    12. 		 qDebug() << "Is DB Open ?: " << db_mydatabase.isOpen();
    13.  
    14. 		 bool sql_ok = true;
    15. 		 qDebug() << "Start insert: " << QDateTime::currentDateTime().toString(Qt::ISODate) << endl;
    16. 		 QSqlQuery q_myquery(db_mydatabase);
    17. 		 q_myquery.prepare("INSERT INTO tbl_mytable ([col1],[col2],[col3],[col4],"
    18. 							 "[col5],[col6],"
    19. 						  "[col7],[col8],[col9],[col10],[col11],[col12],"
    20. 						  "[col13],[col14],[col15],[col16],[col17],"
    21. 						  "[col18],[col19],[col20],[col21],[col22],[col23],[col24],[col25],[col26],"
    22. 						  "[col27],[col28],[col29],[col30],[col31],[col32]) "
    23. 						"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,"
    24. 								"?, ?, ?, ?, ?, ?, ?, ?, ?, ?,"
    25. 								"?, ?, ?, ?, ?, ?, ?, ?, ?, ?,"
    26. 								"?, ?)");
    27.  
    28. 		int i(0);
    29. 		 QStringList::const_iterator constIterator;
    30. 		 for (constIterator = qstrL_WholeFile.constBegin(); constIterator != qstrL_WholeFile.constEnd();++constIterator)
    31. 		 {
    32. 			 if(!(*constIterator).isEmpty())
    33. 			 {
    34.  
    35. 				 QStringList myline = (*constIterator).split(";");
    36. 				 q_myquery.addBindValue(myline.at(0).toInt());
    37. 				 q_myquery.addBindValue(myline.at(1).toInt());
    38. 				 q_myquery.addBindValue(myline.at(2).toInt());
    39. 				 q_myquery.addBindValue(3);
    40. 				 q_myquery.addBindValue(myline.at(3).toInt());
    41. 				 q_myquery.addBindValue(myline.at(4).toInt());
    42. 				 q_myquery.addBindValue(0);
    43. 				 q_myquery.addBindValue(myline.at(5).toInt());
    44. 				 q_myquery.addBindValue(myline.at(6).toInt());
    45. 				 q_myquery.addBindValue(QDateTime::currentDateTime().toString(Qt::ISODate));
    46. 				 q_myquery.addBindValue(myline.at(7));
    47. 				 q_myquery.addBindValue(myline.at(8));
    48. 				 q_myquery.addBindValue(myline.at(9));
    49. 				 q_myquery.addBindValue(myline.at(10).toDouble());
    50. 				 q_myquery.addBindValue(myline.at(11));
    51. 				 q_myquery.addBindValue(myline.at(12));
    52. 				 q_myquery.addBindValue(myline.at(13).toInt());
    53. 				 q_myquery.addBindValue(myline.at(14));
    54. 				 q_myquery.addBindValue(myline.at(15));
    55. 				 q_myquery.addBindValue(myline.at(16));
    56. 				 q_myquery.addBindValue(myline.at(17));
    57. 				 q_myquery.addBindValue(myline.at(18));
    58. 				 q_myquery.addBindValue(myline.at(19));
    59. 				 q_myquery.addBindValue(myline.at(20));
    60. 				 q_myquery.addBindValue(myline.at(21));
    61. 				 q_myquery.addBindValue(myline.at(22));
    62. 				 q_myquery.addBindValue(myline.at(23));
    63. 				 q_myquery.addBindValue(0);
    64. 				 q_myquery.addBindValue(0);
    65. 				 q_myquery.addBindValue(0);
    66. 				 q_myquery.addBindValue(0);
    67. 				 q_myquery.addBindValue(0);
    68. 				 sql_ok = q_myquery.exec();
    69.  
    70. 			}
    71.  
    72. 		 }
    73. 		 q_myquery.clear();
    74. 		 if(sql_ok)
    75. 		 {
    76. 			 sql_ok = db_mydatabase.commit();
    77. 		 }
    78.  
    79. 		 if(!sql_ok)
    80. 		 {
    81. 				 qDebug() <<"Query lastError :"<< q_myquery.lastError();
    82. 				 qDebug() << "lastError().text()"<<q_myquery.lastError().text();
    83. 				 db_mydatabase.rollback();
    84. 		 }
    85. 	 }
    86.  
    87. Do I make some mistakes or why it take soo long for insert??
    To copy to clipboard, switch view to plain text mode 
    Reply With Quote Reply With Quote
  2. 6th October 2010, 22:46 #2
    ChrisW67
    ChrisW67 is offline Guru
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Slow insert with Transaction?!

    Where is the delay? Is it in executing 1000 inserts and all the associated parameter binding, committing the transaction, or rolling back the transaction? Is the connection to the database local or across a network? Does temporarily switching to an Sqlite database change the speed? You can use the QTime::start() and QTime::elapsed() methods to time longer running events like these.

    You can avoid some work in the loop by coding the constant column values (0, 3, and possibly the current time) into the query.

    BTW: The way you are capturing the return from QSqlQuery::exec() you will only capture the result of the last SQL statement and commit/rollback on the basis of that. Are you sure that you are not executing 999 failing SQL statements?
    Reply With Quote Reply With Quote
  3. 19th October 2010, 14:44 #3
    Yakuza_King
    Yakuza_King is offline Beginner
    Join Date
    Oct 2010
    Location
    Berlin, Germany
    Posts
    4
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Slow insert with Transaction?!

    I have a same problem with a SQLite-DB.
    When I rollback or commit a transaction, the system keeps busy for 3-4 minutes.
    The whole application is frozen at this time.
    I'm using a local database.

    I think the rollback or commit statement starts a new thread for it's execution. So the function will finish it's work, but before the function returns it waits for the database-thread to finish.
    Reply With Quote Reply With Quote
  4. 19th October 2010, 23:43 #4
    ChrisW67
    ChrisW67 is offline Guru
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Slow insert with Transaction?!

    Without more information or an example displaying the issue we cannot really help. If you have inserted or updated 100 million records it is going to take some time to commit or rollback no matter what.
    Reply With Quote Reply With Quote
  5. 20th October 2010, 10:38 #5
    saa7_go
    saa7_go is offline Intermediate user
    Join Date
    Jun 2010
    Location
    Salatiga, Indonesia
    Posts
    160
    Thanks
    11
    Thanked 32 Times in 29 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows
    Wiki edits
    1

    Default Re: Slow insert with Transaction?!

    Quote Originally Posted by Yakuza_King View Post
    I have a same problem with a SQLite-DB.
    When I rollback or commit a transaction, the system keeps busy for 3-4 minutes.
    The whole application is frozen at this time.
    I'm using a local database.
    Maybe, this SQLite FAQ explains your problem.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. MySQL and transaction problem in Qt 4.6.2 on Windows XP SP3
    By nuntawat in forum Qt Programming
    Replies: 1
    Last Post: 11th March 2010, 17:42
  2. Unable to commit transaction
    By cydside in forum Qt Programming
    Replies: 6
    Last Post: 23rd July 2009, 07:47
  3. SQLite - QSqlDatabase::transaction()
    By whitefurrows in forum Qt Programming
    Replies: 6
    Last Post: 5th May 2009, 16:06
  4. QTextEdit slow to insert text
    By thomaspu in forum Qt Programming
    Replies: 4
    Last Post: 10th January 2008, 12:05
  5. QDatabase::transaction()
    By sunil.thaha in forum Qt Programming
    Replies: 2
    Last Post: 3rd February 2006, 13:15

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Qt is a trademark of The Qt Company.