SQLite usage and performance

Solar2D General Questions/Discussion
#1

I’m implementing a simple sql database for my app, but i’m having a couple of problems

the code for creating the file:

   [lua]

    local path = system.pathForFile(“data.db”,system.ResourcesDirectory)

    local db = sqlite3.open( path )

   local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id, array);]]

   db:exec( tablesetup )

    db:exec(“BEGIN TRANSACTION”)

    for key,value in pairs(dicionario) do

        local insertQuery = [[INSERT INTO test VALUES (’]]…key…[[’,’]]…value…[[’);]]

        db:exec( insertQuery )

    end

    db:exec(“COMMIT”) [/lua]

dicionario is a lua table that serves as a hashtable that i’m building the sql file from. key/id is the hashcode and value/array is an array of words that doesnt have a fixed size, going from 1 to ~20 values. The problem is that the program is not letting me concatenate a table in the query. If I try to put just a single value it goes fine.

Continuing…

I use something like this code to access the data:

[lua]

    for p in db:nrows([[SELECT * FROM test WHERE id=’]]…“aaelms”…[[’;]]) do

         print(p,p.array)

    end [/lua]

I have to acces this data systematically but this way it is taking a looooot of time in my time-critical routine

Is there a smarter way to do this?

 

#2

Hi there,

SQLite (and the SQL specification in general) doesn’t have an array datatype.  You can see SQLite’s datatypes here: http://www.sqlite.org/datatype3.html.  If you want to store multiple values for an object, you could have one table where each row represents an object (and any attributes it has), and another table that holds the multiple values (each row would list the object id and a value associated with that object; the same object id would appear multiple times in this table if it has multiple values).

  • Andrew
#3

yes, I managed the multiple values problem by concatenating the table into a string. But I still have the performance problem

#4

Well, a single read (SELECT) from the database in a frame probably shouldn’t take much time unless your database is truly large.  Are you noticing a performance hit just from doing a single read in a frame?  Or are you doing lots of reads per frame?

Also, when you set up the database, you’d be wise to set up the id as the primary key of the table.  This should cause SQLite (or any SQL database system) to create an index for the table, which should speed up your reading times.

  • Andrew
#5

How large is the dataset you’re dealing with? It might make sense to add an index on your search key.

#6

I,m dealing with a dictionary/hashtable with about 600 000 values. every time I call my main function I have to do ~100 reads.

but the mistake was with the primare key thing.I set the id as the primary key like below and the retrieve time whent to normal:

CREATE TABLE IF NOT EXISTS test (id STRING PRIMARY KEY , array);

thanks for your answers,

#7

Hi there,

SQLite (and the SQL specification in general) doesn’t have an array datatype.  You can see SQLite’s datatypes here: http://www.sqlite.org/datatype3.html.  If you want to store multiple values for an object, you could have one table where each row represents an object (and any attributes it has), and another table that holds the multiple values (each row would list the object id and a value associated with that object; the same object id would appear multiple times in this table if it has multiple values).

  • Andrew
#8

yes, I managed the multiple values problem by concatenating the table into a string. But I still have the performance problem

#9

Well, a single read (SELECT) from the database in a frame probably shouldn’t take much time unless your database is truly large.  Are you noticing a performance hit just from doing a single read in a frame?  Or are you doing lots of reads per frame?

Also, when you set up the database, you’d be wise to set up the id as the primary key of the table.  This should cause SQLite (or any SQL database system) to create an index for the table, which should speed up your reading times.

  • Andrew
#10

How large is the dataset you’re dealing with? It might make sense to add an index on your search key.

#11

I,m dealing with a dictionary/hashtable with about 600 000 values. every time I call my main function I have to do ~100 reads.

but the mistake was with the primare key thing.I set the id as the primary key like below and the retrieve time whent to normal:

CREATE TABLE IF NOT EXISTS test (id STRING PRIMARY KEY , array);

thanks for your answers,