Results 1 to 17 of 17

Thread: How to achieve fastest database insertion

  1. #1
    Join Date
    Feb 2013
    Location
    India
    Posts
    153
    Thanks
    27
    Thanked 18 Times in 18 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    MacOS X Unix/X11 Windows Symbian S60 Maemo/MeeGo

    Exclamation How to achieve fastest database insertion

    Hello Everyone,

    I am working with large database, and i have to insert 12000 queries per second.
    I have seen a MySql database ENGINE (tokudb) through which fast insertion can be achieved, and now i am using it.
    But the problem is when i insert query in my database using QSqlQuery it takes approx(80 ms) which is the slowest insertion.

    Anyone know how to achieve fastest insertion using Qt?
    Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.

  2. #2
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,464
    Thanked 272 Times in 267 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: How to achieve fastest database insertion

    First off all make many inserts in one transaction. Default is one insert - one transaction and this is a problem.
    Show how you do it.

  3. #3
    Join Date
    Feb 2013
    Location
    India
    Posts
    153
    Thanks
    27
    Thanked 18 Times in 18 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    MacOS X Unix/X11 Windows Symbian S60 Maemo/MeeGo

    Default Re: How to achieve fastest database insertion

    I am inserting using this code

    Qt Code:
    1. QTime timex;
    2. timex.start();
    3.  
    4. // static int i = 0;
    5. QString query = TableQueries::insertDataQuery();
    6. qDebug() << ++i;
    7.  
    8. qDebug() << "::> " << timex.elapsed();
    9. db_query.prepare(query);
    10. qDebug() << ":::> " << timex.elapsed();
    11.  
    12. int pos = 0;
    13. db_query.bindValue(pos++, Id);
    14. db_query.bindValue(pos++, date);
    15. db_query.bindValue(pos++, time);
    16. db_query.bindValue(pos++, value);
    17.  
    18. qDebug() << "::::> " << timex.elapsed();
    19. bool exec = db_query.exec();
    20. qDebug() << ":::::> " << timex.elapsed();
    To copy to clipboard, switch view to plain text mode 

    And the frequency of my data is around 12000 records/Second.
    Then how can transaction be achieved?
    Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.

  4. #4
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,464
    Thanked 272 Times in 267 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: How to achieve fastest database insertion

    A problem is that You runs this code for every record. Change this code and run it for group of records (ie. every 100 records) something like this :
    Qt Code:
    1. QString query = TableQueries::insertDataQuery();
    2. db.transaction();//begin of transaction
    3. db_query.prepare(query);
    4. bool exec = false;
    5. for( int i = 0; i < Id_list.size(); i++ )
    6. {
    7. int pos = 0;
    8. db_query.bindValue(pos++, Id_list.at(i));
    9. db_query.bindValue(pos++, date_list.at(i));
    10. db_query.bindValue(pos++, time_list.at(i));
    11. db_query.bindValue(pos++, value_list.at(i));
    12. exec = db_query.exec();
    13. if( !exec )
    14. break;
    15. }
    16. if( exec )
    17. db.commit();
    18. else
    19. db.rollback();
    To copy to clipboard, switch view to plain text mode 
    Remember that this is some idea not working code.

  5. #5
    Join Date
    Feb 2013
    Location
    India
    Posts
    153
    Thanks
    27
    Thanked 18 Times in 18 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    MacOS X Unix/X11 Windows Symbian S60 Maemo/MeeGo

    Default Re: How to achieve fastest database insertion

    Hii Lesiok,

    Thanks for your solution, I have tried your solution but somehow its not sufficient for me.

    It inserts 120 records in 107millisecond, which means ~1200 records in one second.
    But my live feed will be ~12000 records per second.
    I am searching for something which can do more faster insertion.
    I am using tokudb which claim to insert million records in a second, but somehow i dont know why my database not do the same for me.

    Any idea about that?
    Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.

  6. #6
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,464
    Thanked 272 Times in 267 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: How to achieve fastest database insertion

    It depends on many conditions. As you can see a single INSERT lasted 80 ms and 120 in one transaction 107 ms. And what if You increase the number of INSERT statements in a transaction? What will be the difference between a program compiled in debug mode and release? The database is on the same or on another computer?
    I think that with speed of tokudb is like with car manufacturers. Car manufacturers indicate that the fuel consumption of 5 liters per 100 km. They forget only add that in the laboratory that the reality on the road have no relation and the car on the road consumes 6.5 liters per 100 km

  7. The following user says thank you to Lesiok for this useful post:

    karankumar1609 (19th November 2013)

  8. #7
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,555
    Thanks
    13
    Thanked 1,581 Times in 1,510 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: How to achieve fastest database insertion

    You achieve a million inserts a second with multiple processors running multiple insert threads against a database engine on the same machine, running entirely in RAM, over a persistent low latency connection, into a table with no indexes, constraints or foreign keys, and with code that is optimised within an inch of its life. Even then you probably round the result up to the nearest good marketing value. In short the figure is unrealistic.

    This code:
    Qt Code:
    1. #include <QCoreApplication>
    2. #include <QSqlDatabase>
    3. #include <QSqlQuery>
    4. #include <QSqlError>
    5. #include <QDate>
    6. #include <QTime>
    7. #include <QDebug>
    8.  
    9. int main(int argc, char **argv) {
    10. QCoreApplication app(argc, argv);
    11.  
    12. QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    13. db.setHostName("localhost");
    14. db.setDatabaseName("test");
    15. db.setUserName("test");
    16. db.setPassword("");
    17. if (db.open()) {
    18. // Set up a test table
    19. QSqlQuery qry;
    20. if (qry.exec("CREATE TEMPORARY TABLE atable(id INTEGER, d DATE, t TIME, v INTEGER)")) {
    21. QTime stopwatch;
    22. QDate testDate = QDate::currentDate();
    23. QTime testTime = QTime::currentTime();
    24.  
    25. db.transaction();
    26. stopwatch.start();
    27. qry.prepare("INSERT INTO atable VALUES(?, ?, ?, ?)");
    28. for (int i = 0; i < 10000; ++i) {
    29. qry.bindValue(0, i);
    30. qry.bindValue(1, testDate);
    31. qry.bindValue(2, testTime);
    32. qry.bindValue(3, i);
    33. if (!qry.exec())
    34. qDebug() << qry.lastError();
    35. }
    36. qDebug() << stopwatch.elapsed();
    37. db.commit();
    38. }
    39. else
    40. qDebug() << qry.lastError();
    41. }
    42. else
    43. qDebug() << db.lastError();
    44. return 0;
    45. }
    To copy to clipboard, switch view to plain text mode 
    Does 10000 inserts in 2.8 seconds over a 100Mbps LAN to a remote server.
    Does 10000 inserts in 0.29 seconds over a local socket. (This Linux MySQL machine is faster than the other, but not 10 times faster)
    Does 10000 inserts in 0.37 seconds over the loopback network interface. This scales to 29.8 and 38.1 seconds respectively for 1e6 records.

    You do not, for example, want to be establishing a new connection for each insert. You do not want to be preparing a query for each insert: the query does not change, only the values. The mechanics of committing a transaction to disk are likely to take at least as a long as a complete rotation of the disk platter possibly two, or about a millisecond, so you cannot afford to commit often. network delays are likely to be a large component. You avoid doing type conversions if at all possible.


    How long do you have to sustain 12000 records per second? What are the actual data types?
    Last edited by ChrisW67; 18th November 2013 at 22:28.

  9. The following 2 users say thank you to ChrisW67 for this useful post:

    karankumar1609 (19th November 2013), Momergil (5th January 2014)

  10. #8
    Join Date
    Feb 2013
    Location
    India
    Posts
    153
    Thanks
    27
    Thanked 18 Times in 18 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    MacOS X Unix/X11 Windows Symbian S60 Maemo/MeeGo

    Default Re: How to achieve fastest database insertion

    Thanks for your solutions,

    I have used the following data types in create table command.

    Qt Code:
    1. CREATE TEMPORARY TABLE atable(id INTEGER, name varchar(255), date varchar(15), time varchar(15), value INTEGER)
    To copy to clipboard, switch view to plain text mode 

    My speed of insertion of 12000 records is 250millisecond, and it remains constant.
    My current database contains 9630512 rows.

    i have to work with live data and insert it into database.
    Quite puzzeled how to create a better structure to capture the live data and store it.

    If you have any idea about that, please share with me , i will do the rest.
    Thanks again.
    Last edited by karankumar1609; 19th November 2013 at 06:49.
    Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.

  11. #9
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,555
    Thanks
    13
    Thanked 1,581 Times in 1,510 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: How to achieve fastest database insertion

    If you are inserting 12000 rows in 0.25 seconds then you already met your stated target. What is the problem?

  12. #10
    Join Date
    Feb 2013
    Location
    India
    Posts
    153
    Thanks
    27
    Thanked 18 Times in 18 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    MacOS X Unix/X11 Windows Symbian S60 Maemo/MeeGo

    Default Re: How to achieve fastest database insertion

    Sorry for the wrong post , that was 1200 records per 250 millisecond.
    And 12000 records per ~800(MINIMUM) millisecond.
    The problem is i do not want to get stuck 800 millisecond while inserting records, because the live data comes at a very high frequency.
    Last edited by karankumar1609; 19th November 2013 at 08:29.
    Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.

  13. #11
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,555
    Thanks
    13
    Thanked 1,581 Times in 1,510 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: How to achieve fastest database insertion

    It would help if you answered some of the outstanding questions.

    Quote Originally Posted by Lesiok
    The database is on the same or on another computer?
    Quote Originally Posted by Me
    How long do you have to sustain 12000 records per second? What are the actual data types?
    Where is the bottleneck and what have to done to isolate it? There are physical limits to how much data you can push over a network but that is irrelevant if your database server is overloaded.

  14. #12
    Join Date
    Feb 2013
    Location
    India
    Posts
    153
    Thanks
    27
    Thanked 18 Times in 18 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    MacOS X Unix/X11 Windows Symbian S60 Maemo/MeeGo

    Default Re: How to achieve fastest database insertion

    The database is on the same or on another computer?
    Yes the database is on the same computer on which i am working.

    How long do you have to sustain 12000 records per second? What are the actual data types?
    I will get 12000 records per second for 5 hours minimum.
    and my current speed of insertion on 12000 records is approx 800 millisecond.
    Data types used by table is VARCHAR and INT.
    Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.

  15. #13
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,464
    Thanked 272 Times in 267 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: How to achieve fastest database insertion

    Maybe a solution is to write the samples to a regular file and upload them to the database in the other thread. In this way, You are immune to changes in the instantaneous performance of the server. For example, one minute records are going to the first file. After a minute, the records are going to a new file and the previous one is pushed to the database. And so on...

  16. #14
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,555
    Thanks
    13
    Thanked 1,581 Times in 1,510 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: How to achieve fastest database insertion

    You are receiving 12000 records in 1.0 second.
    You are inserting 12000 records in 0.8 seconds.
    Seems to me to me are keeping up just fine although I do wonder why your figure is 3 times longer than mine (Especially given your super-duper MySql backend vs my general MyISAM tables).

    Here are some more questions to answer for your own benefit:
    Where is the bottleneck? You still have not identified that. If you marshal the data into the query without actually executing it: how long does it take? What portion of the total time is this?
    Does this data need to be stored in an RDBMS at all?
    Is text the most efficient method of storing a date or time?
    Can data be aggregrated on-the-fly and only the aggregrate data stored?
    Can Lesiok's approach work for your application?
    "We can't solve problems by using the same kind of thinking we used when we created them." -- Einstein
    If you are posting code then please use [code] [/code] tags around it - makes addressing the problem easier.

  17. The following user says thank you to ChrisW67 for this useful post:

    karankumar1609 (20th November 2013)

  18. #15
    Join Date
    Feb 2013
    Location
    India
    Posts
    153
    Thanks
    27
    Thanked 18 Times in 18 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    MacOS X Unix/X11 Windows Symbian S60 Maemo/MeeGo

    Default Re: How to achieve fastest database insertion

    Hello ChrisW67

    I have implemented the same code as yours and its the total time of insertion of record into the table.
    Where is the bottleneck? You still have not identified that. If you marshal the data into the query without actually executing it: how long does it take? What portion of the total time is this?
    Problem is why my code takes ~0.8 second for insertion.
    Before execution of query it hardly takes ~0-1 millisecond.
    ~0.8 second is the total time

    Qt Code:
    1. QTime time;
    2. time.start();
    3.  
    4. QSqlQuery query;
    5. database_manager_.transaction();
    6. for(int i = 0; i < queue.size(); ++i) {
    7. ...
    8. ...
    9. query.exec();
    10. }
    11. database_manager_.commit();
    12.  
    13. qDebug() << ">> " << time.elapsed();
    To copy to clipboard, switch view to plain text mode 

    Does this data need to be stored in an RDBMS at all?
    Yes data need to be stored in RDBMS

    Is text the most efficient method of storing a date or time?
    I will change these variables from string to date, time. It dosn't matter to me.

    Can data be aggregrated on-the-fly and only the aggregrate data stored?
    Data will be aggregated on-the-fly

    Can Lesiok's approach work for your application?
    Yes it works, but execution time is little high, Should i need any compiler optimization?
    Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.

  19. #16
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,464
    Thanked 272 Times in 267 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: How to achieve fastest database insertion

    Quote Originally Posted by karankumar1609 View Post
    Yes it works, but execution time is little high, Should i need any compiler optimization?
    What does it mean "execution time is little high" ? Maybe your computer is too weak for this job or misconfigured ?

  20. The following user says thank you to Lesiok for this useful post:

    karankumar1609 (20th November 2013)

  21. #17
    Join Date
    Feb 2013
    Location
    India
    Posts
    153
    Thanks
    27
    Thanked 18 Times in 18 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    MacOS X Unix/X11 Windows Symbian S60 Maemo/MeeGo

    Default Re: How to achieve fastest database insertion

    Thanks for your help i have changed the way i am inserting data into table.
    Now i have to insert 300 records per second which is quite less as compared to 12000.
    Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.

Similar Threads

  1. How to achieve Alt+Tab effect ?
    By y.s.bisht in forum Newbie
    Replies: 0
    Last Post: 11th November 2011, 14:22
  2. How can i achieve an auto-resizing QStackedWidget?
    By truefusion in forum Qt Programming
    Replies: 2
    Last Post: 17th May 2010, 03:31
  3. How to achieve showAll ?
    By Gopala Krishna in forum Qt Programming
    Replies: 5
    Last Post: 5th October 2007, 15:26
  4. How to achieve Uniformity...!!!
    By deepusrp in forum Qt Programming
    Replies: 9
    Last Post: 7th May 2007, 10:11
  5. Insertion of unicode characters into database oracle through pro c
    By hemananda choudhuri in forum Qt Programming
    Replies: 1
    Last Post: 8th January 2007, 11:42

Tags for this Thread

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
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.