Reducing delay when reading from database

Ankit.Jain

Hi,

I am currently using a database to gain some value.
The db is in the form: (LocationIndex, Value, Timestamp,SomeOtherUselessStuff)

So, I am running the query of "select LocationIndex, Values from table where timestamp=someTimestamp" resulting in a (LocationIndex, Value) based records.

The problem is that my database (SQLite) size is 1.2GB. Time taken for iterating (using query.next()) for about 256 tuple result is 2.3 to 2.7 secs. This is not exactly acceptable especially since I am advertising the use of MySQL as well (expecting bigger delay there).

I realized that executing the query itself costs barely anything (about 20,000 nano secs). The delay is with me iterating using a while loop over the condition while(query.next()) {}. I realized that even after commenting everything inside the loop. the delay is present.

I am not sure of how the query execution works. Hence I was wondering if there is a go around for reducing this delay.

Thanks

JonB

@Ankit.Jain

since I am advertising the use of MySQL as well (expecting bigger delay there).

Not sure why you expect MySQL to be slower, but anyway....

Try the bare query directly in MySQL Workbench (or whatever you have similar for SQLite). You need to discover just how long your query takes outside of any Qt fetching of records. If it's "long", can you add an index on timestamp column? I don't know how "database (SQLite) size is 1.2GB" relates to rows, just how many rows are there? If you're waiting for 256 out of 1,000,000 rows, say, with no indexing, it's going to take a while...

kshegunov

What are the (SQLite) types of LocationIndex and Value, just out of curiosity?

Ankit.Jain

Sorry for the delayed response. Had a long weekend off.
@JonB
I expect MySQL to be slower because there is another level (to and from server) added. I haven't had the opportunity to test it because I dont have access to one right now.

Running the same query in SQLiteStudio, the query takes similar time (2 to 2.5 secs), but I keep getting a status of "[09:09:49] Query finished in 0.006 second(s).". Because of this, I am not sure how this is calculated though.

I do not have rights for asking changes in the database. But just out of curiosity, how do you add an index to a column?

The number of tuples is : 16,777,216

@kshegunov

Location Index isn't exactly one field, its 3 fields : (X,Y,Z) or (Lat,Long,Alt). I test for lat long / cartesian on input. Lat,Long,Alt are double values. While value is double as well.

The thing is that the query itself doesn't take long, the reading loop does. This makes me suspect if the query is being run multiple times.
Also,
I found that if I do something like:

queryResult.exec(query);
QElapsedTimer elapsedTimer;
elapsedTimer.start();
queryResult.next();
qDebug() << "Time Elapsed: " << elapsedTimer.nsecsElapsed();

Output is about 3000ns [2716ns]

While,

QElapsedTimer eTime;
eTime.start();
queryResult.exec(query);
qDebug() << "Generate heat map for Car Query Elapsed:" << eTime.nsecsElapsed();

Outputs 307306 ns

While:

QElapsedTimer eTime;
eTime.start();
while (queryResult.next())
{
}
qDebug() << "Elapsed:" << eTime.nsecsElapsed();

Outputs 2617937433 (2.6 secs). I have also had instances of 2.2 secs, 2.4 secs etc during my testing. P.S, I once put an iterator in the loop, the value iterated up-to 256.

Coming back to the question, why is the time taken for executing the query so small compared to viewing the result of the same.

sierdzio

@Ankit.Jain said in Reducing delay when reading from database:

I expect MySQL to be slower because there is another level (to and from server) added. I haven't had the opportunity to test it because I dont have access to one right now.

SQLite is renowned for it's slowness, I think you might be surprised by the results once you do test it. In any case, if you do benchmark it, please share the results, it's an interesting topic.

kshegunov

@Ankit.Jain said in Reducing delay when reading from database:

I do not have rights for asking changes in the database. But just out of curiosity, how do you add an index to a column?

If you don't have an index on the field(s) in the where clause, you'd always get those problems, no matter what database engine you choose. Make sure you have indexed the timestamp you're selecting by. On a related note, the (X, Y, Z) tuple should've been expanded in separate columns.

@sierdzio said in Reducing delay when reading from database:

SQLite is renowned for it's slowness

Well, I haven't noticed that, but I must admit that I've used SQLite for only rather trivial tasks, so I might haven't encountered it.

JonB

@Ankit.Jain said in Reducing delay when reading from database:

Running the same query in SQLiteStudio, the query takes similar time (2 to 2.5 secs), but I keep getting a status of "[09:09:49] Query finished in 0.006 second(s).". Because of this, I am not sure how this is calculated though.

If it takes similar time in SQLiteStudio, it's not surprising it takes similar from Qt.

I do not have rights for asking changes in the database. But just out of curiosity, how do you add an index to a column?

I have never used SQLite. But CREATE INDEX in SQL creates an index. No idea whether SQLite will or will not take advantage of such an index if it exists, though...