PDA

View Full Version : SQL slow query - table update



lasher
25th September 2009, 20:43
Hello,

I use QTableView with QSqlQueryModel to display sql query result,
It works nice but sometimes I have slow query (30-60s, there are few milion rows in database) and I want to update table during query to show partial result. Is is possible using simple code?:)

Thx

gboelter
26th September 2009, 07:48
It works nice but sometimes I have slow query (30-60s, there are few milion rows in database) and I want to update table during query to show partial result.

What about MySql's 'slow query log'?

http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

lasher
26th September 2009, 11:10
Hi,

Thx for reply, but I don't use MySQL, I use PostgrSQL, this query log consists only slow query statements.

I want to update QTableView with QSqlQueryModel, when I execute query (setQuery())
I have to wait till end of this query to get result (sometimes more than 30s), so I want to refresh QTableView (let say every second) and get partial result updated every second till final end of my query.

Greetings!

zverj
21st October 2009, 15:09
Hello,

I use QTableView with QSqlQueryModel to display sql query result,
It works nice but sometimes I have slow query (30-60s, there are few milion rows in database) and I want to update table during query to show partial result. Is is possible using simple code?:)

Thx

Did you found the solution? I'm interested in that too

ChrisW67
22nd October 2009, 00:12
If the long run time is the database server determining what rows to return then there's nothing you can do to display partial results during this time. There are no rows to return and display until after this stage. Make sure you have a good query that makes best use of indexes in the data and only returns columns you need. Full table scans are bad with multi-millions of rows in a table.

If the long run time is predominantly returning the rows after the database engine has selected them then you might have some options. QSqlTableModel already does lazy retrieval of small batches (~256) from large row sets as a view (QTableView) scrolls down. Scrolling through large data sets can be tedious though. You can bundle the entire delay into a single loop after the model select() using canFetchMore() and fetchMore().

If the process is slowed by a network connect to the database server you might find some useful ideas here for asynchronous retrieval (thanks Wysota):
http://blog.wysota.eu.org/index.php/2006/12/26/remote-models/