QSqlQuery and bad performance!

[mismael85]

hello every body;
the QSqlQuery have a bad perofrmance when i use it with large tables.
I have a table with more than 31964 record and i exec the following query
SELECT field from table ORDER BY id.
when i use QSqlQuery::next it takes time to move to the next record.
and also when i use QSqlQuery::Last it takes longger time and increase the application used memory in RAM incredibly from 27 MB to 143 MB.
is there a solution or i should use the sqlite headers to do this job?
Thank you,

[wysota]

Well, you are retrieving 32k records into your application, it has to take time. Did you create an index on the `id` field in your table?

[mismael85]

yes i created an index but the database still slow.
but i want to ask if i created an index will i still use the database through the qsqlquery and it handles the index automatically because i did not find any thing handles indexes?

[wysota]

yes i created an index but the database still slow.

Can we see the schema of the table?

but i want to ask if i created an index will i still use the database through the qsqlquery and it handles the index automatically because i did not find any thing handles indexes?

It's the database engine that handles the index, not Qt. Which automatically means you'll get similar overhead when accessing the database from sqlite console.

[mismael85]

Can we see the schema of the table?

CREATE TABLE `b11430`
(
`id` INT,
`nass` TEXT,
`part` INT,
`page` INT
)

It's the database engine that handles the index, not Qt. Which automatically means you'll get similar overhead when accessing the database from sqlite console.

so if i used the following query "SELECT * from table where id = 1" the qsqlquery will use the index to get the record???

[wysota]

CREATE TABLE `b11430`
(
`id` INT,
`nass` TEXT,
`part` INT,
`page` INT
)

There is no index here.

so if i used the following query "SELECT * from table where id = 1" the qsqlquery will use the index to get the record???

If there was an index, yes. But it seems you didn't create one.

[mismael85]

i created an index but the query still slow and here are the schema

CREATE TABLE b11430
-- This table created by SQLite2008 Pro Enterprise Manager
-- Osen Kusnadi - http://www.osenxpsuite.net
-- Created date: 2010-09-24 19:47:33
(
       id INTEGER,
       nass TEXT,
       part INTEGER,
       page INTEGER
);
CREATE UNIQUE INDEX idx ON b11430(id);

CREATE TABLE b11430
-- This table created by SQLite2008 Pro Enterprise Manager
-- Osen Kusnadi - http://www.osenxpsuite.net
-- Created date: 2010-09-24 19:47:33
(
       id INTEGER,
       nass TEXT,
       part INTEGER,
       page INTEGER
);
CREATE UNIQUE INDEX idx ON b11430(id);

To copy to clipboard, switch view to plain text mode 

is there a solution?

[wysota]

Does it get any faster if you omit the "order by" clause?

[mismael85]

No it didn't get any faster.
do you think that if i used the sqlite directly it will be faster?

[wysota]

No it didn't get any faster.

So by saying that it's slow, could you be more specific? How slow it is? Also, what happens if you use a database which has only about half the number of rows of your original db?

do you think that if i used the sqlite directly it will be faster?

I already answered that question.