Results 1 to 11 of 11

Thread: how to optimize QSqlQuery?

  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 optimize QSqlQuery?

    Hello Everyone,

    I am working with QSqlQuery to fetch data from database tables.
    Somehow i find it is quite slow for me.

    What i am doing is i am fetching data from table around 15539502 rows.
    which itself take 40-45 seconds., which is fine.
    But when i am traversion this much amount of data it takes around 7-10 minutes.
    which is quite long time.

    Qt Code:
    1. QString query("SELECT * FROM DATA_TABLE_1 (TICK_DATE BETWEEN :date_to AND :date_from) ORDER BY TICK_DATE ASC, TICK_TIME ASC");
    2. QSqlQuery sql_query(m_ServerDB);
    3. sql_query.prepare(query);
    4. sql_query.setForwardOnly(true);
    5. bool ok = sql_query.exec();
    6.  
    7. if(ok &&
    8. sql_query.size() > 0) {
    9. // Do calculations
    10. }
    To copy to clipboard, switch view to plain text mode 

    is there any way to doing the same with multiple threads to save time?
    or anyidea to solve my problem.?
    Last edited by karankumar1609; 9th April 2014 at 11:18.
    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
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,363
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: how to optimize QSqlQuery?

    First of all you are executing the query twice. First in line #2 and then in line #4.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  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 optimize QSqlQuery?

    m_ServerDB is the object of QSqlDatabase then how can it gets executed?
    without even having a query?
    please correct me if i am wrong?
    Last edited by karankumar1609; 9th April 2014 at 11:08.
    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
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,363
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: how to optimize QSqlQuery?

    Quote Originally Posted by karankumar1609 View Post
    m_ServerDB is the object of QSqlDatabase then how can it gets executed?
    without even having a query?
    please correct me if i am wrong?
    No, it's ok, especially after you have edited your original post. Anyway, unless you show us your "do calculations" part there is not much we can help you with.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  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 optimize QSqlQuery?

    Qt Code:
    1. if(ok &&
    2. sql_query.size() > 0) {
    3. QSqlRecord record = sql_query.record();
    4. unsigned char index_tick_date = record.indexOf("TICK_DATE");
    5. unsigned char index_tick_time = record.indexOf("TICK_TIME");
    6. unsigned char index_open = record.indexOf("OPEN");
    7. unsigned char index_high = record.indexOf("HIGH");
    8. unsigned char index_low = record.indexOf("LOW");
    9. unsigned char index_close = record.indexOf("CLOSE");
    10. unsigned char index_volume = record.indexOf("VOLUME");
    11.  
    12. int row = sql_query.size();
    13.  
    14. int date_prev = -1;
    15. int time_prev = -1, time_weekly_prev = -1, time_monthly_prev = -1;
    16.  
    17. float open_daily_prev, open_weekly_prev, open_monthly_prev;
    18. float high_daily_prev, high_weekly_prev, high_monthly_prev;
    19. float low_daily_prev, low_weekly_prev, low_monthly_prev;
    20. float close_daily_prev, close_weekly_prev, close_monthly_prev;
    21. float volume_daily_prev, volume_weekly_prev, volume_monthly_prev;
    22.  
    23. bool init_daily, init_weekly, init_monthly;
    24. unsigned char minutes_counter = 0;
    25.  
    26. init_daily = init_weekly = init_monthly = true;
    27.  
    28. while(sql_query.next()) {
    29. QString date_str = sql_query.value(index_tick_date).toDate().toString("yyyyMMdd");
    30. int date = date_str.toInt();
    31.  
    32. QString time_str = sql_query.value(index_tick_time).toTime().toString("hhmm");
    33. int time = time_str.toInt();
    34. float open = sql_query.value(index_open).toFloat();
    35. float high = sql_query.value(index_high).toFloat();
    36. float low = sql_query.value(index_low).toFloat();
    37. float close = sql_query.value(index_close).toFloat();
    38. float volume = sql_query.value(index_volume).toInt();
    39.  
    40. if(time_prev == -1) {
    41. time_prev = time;
    42. time_monthly_prev = time;
    43. time_weekly_prev = time_str.left(2).toInt() * 100;
    44. }
    45.  
    46. if(date_prev == -1)
    47. date_prev = date;
    48.  
    49. if(init_daily) {
    50. init_daily = false;
    51. open_daily_prev = open_weekly_prev = open_monthly_prev = open;
    52. high_daily_prev = high_weekly_prev = high_monthly_prev = high;
    53. low_daily_prev = low_weekly_prev = low_monthly_prev = low;
    54. close_daily_prev = close_weekly_prev = close_monthly_prev = close;
    55. volume_daily_prev = volume_weekly_prev = volume_monthly_prev = close;
    56. }
    57.  
    58. // Daily checks
    59. // one minute resolution data
    60. {
    61. if(time == time_prev) {
    62. high_daily_prev = ::max(high, high_daily_prev);
    63. low_daily_prev = ::min(low, low_daily_prev);
    64. close_daily_prev = close;
    65. volume_daily_prev += volume;
    66. } else if(time > time_prev || date > date_prev) {
    67. // Process Data Here
    68. // DoSomething()
    69. createOHLCData(date_prev, time_prev, open_daily_prev, high_daily_prev, low_daily_prev, close_daily_prev, id_element_price[INDEX_DAILY]);
    70. createVolumeData(date_prev, time_prev, volume_daily_prev, id_element_volume[INDEX_DAILY]);
    71.  
    72. // Weekly checks
    73. // One hour resolution data
    74. {
    75. // 60 minutes in one hour
    76. if(((time_weekly_prev/100) < (time/100)) ||
    77. date > date_prev ||
    78. init_weekly) {
    79. if(!init_weekly) {
    80. // Process weekly data here
    81. // DoSomething()
    82. createOHLCData(date_prev, time_weekly_prev, open_weekly_prev, high_weekly_prev, low_weekly_prev, close_weekly_prev, id_element_price[INDEX_WEEKLY]);
    83. createVolumeData(date_prev, time_weekly_prev, volume_weekly_prev, id_element_volume[INDEX_WEEKLY]);
    84.  
    85. minutes_counter = 0;
    86. time_weekly_prev = time_str.left(2).toInt() * 100;
    87. } else { init_weekly = false; }
    88.  
    89. open_weekly_prev = open_daily_prev;
    90. high_weekly_prev = high_daily_prev;
    91. low_weekly_prev = low_daily_prev;
    92. close_weekly_prev = close_daily_prev;
    93. volume_weekly_prev = volume_daily_prev;
    94. } else {
    95. high_weekly_prev = ::max(high_daily_prev, high_weekly_prev);
    96. low_weekly_prev = ::min(low_daily_prev, low_weekly_prev);
    97. close_weekly_prev = close_daily_prev;
    98. volume_weekly_prev += volume_daily_prev;
    99. }
    100. }
    101.  
    102. // Monthly checks
    103. // One day resolution
    104. {
    105. // 60 minutes in one hour
    106. if(date > date_prev ||
    107. init_monthly) {
    108. if(!init_monthly) {
    109. // Process monthly data here
    110. // DoSomething()
    111. createOHLCData(date_prev, time_monthly_prev, open_monthly_prev, high_monthly_prev, low_monthly_prev, close_monthly_prev, id_element_price[INDEX_MONTHLY]);
    112. createVolumeData(date_prev, time_monthly_prev, volume_monthly_prev, id_element_volume[INDEX_MONTHLY]);
    113. date_prev = date;
    114. time_monthly_prev = time;
    115. } else { init_monthly = false; }
    116.  
    117. open_monthly_prev = open_daily_prev;
    118. high_monthly_prev = high_daily_prev;
    119. low_monthly_prev = low_daily_prev;
    120. close_monthly_prev = close_daily_prev;
    121. volume_monthly_prev = volume_daily_prev;
    122. } else {
    123. high_monthly_prev = ::max(high_daily_prev, high_monthly_prev);
    124. low_monthly_prev = ::min(low_daily_prev, low_monthly_prev);
    125. close_monthly_prev = close_daily_prev;
    126. volume_monthly_prev += volume_daily_prev;
    127. }
    128. }
    129.  
    130. open_daily_prev = open;
    131. high_daily_prev = high;
    132. low_daily_prev = low;
    133. close_daily_prev = close;
    134. volume_daily_prev = volume;
    135. }
    136.  
    137. time_prev = time;
    138.  
    139. }
    140. }
    141.  
    142. if(row >= 1) {
    143. createOHLCData(date_prev, time_prev, open_daily_prev, high_daily_prev, low_daily_prev, close_daily_prev, id_element_price[INDEX_DAILY]);
    144. createVolumeData(date_prev, time_prev, volume_daily_prev, id_element_volume[INDEX_DAILY]);
    145.  
    146. createOHLCData(date_prev, time_weekly_prev, open_weekly_prev, high_weekly_prev, low_weekly_prev, close_weekly_prev, id_element_price[INDEX_WEEKLY]);
    147. createVolumeData(date_prev, time_weekly_prev, volume_weekly_prev, id_element_volume[INDEX_WEEKLY]);
    148.  
    149. createOHLCData(date_prev, time_monthly_prev, open_monthly_prev, high_monthly_prev, low_monthly_prev, close_monthly_prev, id_element_price[INDEX_MONTHLY]);
    150. createVolumeData(date_prev, time_monthly_prev, volume_monthly_prev, id_element_volume[INDEX_MONTHLY]);
    151. }
    152. }
    To copy to clipboard, switch view to plain text mode 

    here is the calculation under sql query execution.. this part doesn't help me much...
    hope you will find the lack..
    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
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,363
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: how to optimize QSqlQuery?

    Did you measure which part takes the longest time in each iteration?
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  7. #7
    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 optimize QSqlQuery?

    yes i have done function profiling and it says that iteration QSqlQuery multiple number of times and doing calculations after that takes longer time.
    but calculations are necessary.

    can't we do the same with multiple threads to save my time?
    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.

  8. #8
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,363
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: how to optimize QSqlQuery?

    You can use threads if your calculations for iteration A do not require results of calculation for any previous step.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


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

    Default Re: how to optimize QSqlQuery?

    Simple speed rules for larger data sets:
    • Don't select more data than your require. "SELECT *" is almost always more than you require and causes more IO/network traffic as a result. It has negative maintainability effects as well.
    • Don't iterate if an SQL set operation can do the job. Often these are faster even if temporary tables are required.
    • Know what indexes might speed execution. If the limited data you are selecting all comes from an index then the engine may not need to read the actual table at all.


    I assume that createOHLCData() and createVolumeData() create records in other tables, so you potentially have millions of SQL INSERT/UPDATE transactions being executed by this loop. You do not say which database is underlying but if it is Sqlite make sure you are doing the entire process inside a database transaction: http://sqlite.org/faq.html#q19

    Since you have the data in a relational database you probably should consider using the set processing power of SQL to do the work. Record by record iteration should only be used as a last resort on large record volumes.

  10. #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 optimize QSqlQuery?

    Thnx ChrisW67,

    I am using mysql database.
    Actually i am using "Select *" because all data is required. createOHLCData() and createVolumeData() functions do not create entry in tables. they are just creating entry in a structure which is just a small operation.
    And next record insertion is linked with previous record. Feeling quite helpless.
    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. #11
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,460
    Thanked 271 Times in 266 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: how to optimize QSqlQuery?

    But the functions createOHLCData() and createVolumeData() are called 3 times each in one loop iteration. Loop has 15539502 iterations. 0,1 ms saved in one iteration gives 1553 seconds. If You can show us this functions. Maybe it is a problem with memory management with big cantainers.

Similar Threads

  1. Replies: 2
    Last Post: 9th April 2013, 09:15
  2. Replies: 1
    Last Post: 18th July 2011, 12:12
  3. Optimize QGraphicsItem update
    By paolom in forum Qt Programming
    Replies: 10
    Last Post: 21st March 2011, 15:29
  4. How to optimize qt libs for embedded x86?
    By yangyunzhao in forum Installation and Deployment
    Replies: 2
    Last Post: 30th June 2009, 09:47
  5. Optimize Qt Library
    By Svaths in forum Qt for Embedded and Mobile
    Replies: 11
    Last Post: 15th July 2007, 22:25

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.