faster sqlite_query?

[gcue]

hello.

i am trying to generate a unique record_id for an existing sqlite database

here's how i am doing it but it takes 30 seconds to go through each record to make sure it doesn't already exists..

Func Record_ID_Generate($sql_db)


Local $aRow, $hQuery, $record_id, $record_found = False


For $x = 1 To 10
$i = Random(48, 83, 1)
$record_id &= Chr($i + ($i > 57) * 7)
Next


_SQLite_Open($sql_db)


;~  _SQLite_QuerySingleRow(-1, "SELECT * FROM aLog WHERE Record_ID='" & $record_id & "'", $aRow)
_SQLite_Query(-1, "SELECT * FROM aLog WHERE Record_ID='" & $record_id & "'", $hQuery)
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
Debug("found")
$record_found = True
WEnd


If $record_found = True Then
Debug("not found")
Record_ID_Generate($sql_db)
EndIf


_SQLite_Close()


Return $record_id


EndFunc   ;==>Record_ID_Generate

i tried sqlite_query and sqlite_querysinglerow.. both take the same amount of time.

any ideas?

thank you in advance!

 

[KaFu]

No need to generate a unique record ID, SQLite does this automagically , try to access "RowID".

[jchd]

To give advice it's important to have precisions about your database schema.

Please download SQLite Expert free edition (link below) open the DB, hit DDL with the DB selected (not one table) and copy/paste what is displayed.

Anyhow, I find it suspect that your record_id is a string and not an integer.

[gcue]

i changed it to alphanumeric just so i can get more variations... pretty large database..

so how do i use rowid to make sure its a unique entry?  is it visible?

[jchd]

Impossible to say anything sensible unless looking at the exact schema.

[gcue]

schema is very simple...

just one table with multiple fields

[jchd]

But how did you create this table? Is record_id a primary key? Do you have any index which the queries you feel "slow" can use to speed up the search?

[gcue]

this table is created with CREATE TABLE.  not sure what you mean by primary key.  index?  is that some sort of cache?  i do not

[jchd]

Can you actually answer questions?

What is of interest here is the content of your create table statement!

Also following the advice about SQLite Expert would dramatically help.

[JohnQSmith]

Can you actually answer questions?

 

Since @gcue doesn't appear to have a large working knowledge of databases and @jchd's attitude is bigger than his patience...

@gcue

If you don't have the sqlite3 executable, you can get it from http://sqlite.org.

From the command line, run:

sqlite3 yourdatabasename

Then when you get the "sqlite>" prompt, type and enter:

.schema

You should end up with something similar to the following

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

Copy and paste that in a reply and @jhcd might a little more accommodating.

 

[jchd]

Perhaps but if he didn't define a primary key, using a full-ledged DB manager will make creating one a 5-second no-fuss operation.

[gcue]

thank you so much - i never would have guessed what jchd was talking about =)

http://imgur.com/FKu9V4U

looks like there's no schema on it.

i tried it on other databases and i did schema.. would this be the reason why its slow?  if so, how can i schema it?

thanks again for your help

[JohnQSmith]

If there is no schema, then it's really not a database; it's just a file with a .sqlite extension.

How large is this "logs.sqlite" file and are you sure that it was located in the "sqlitebrowser_200_b1_win" folder that you tried opening it from? If you run sqlite3 against a file that doesn't exist, it will create a new file in the current location with the filename provided.

Edit:  Also, like @jchd said, try opening it in SQLite Expert or Database Browser for SQLite. They're much friendlier than the command line.

Edited September 5, 2014 by JohnQSmith

[KaFu]

Every table has a schema. Somewhere in your script past these lines and copy the console results:

Local $hQuery, $aRow
_SQLite_Query(-1, "SELECT * FROM sqlite_master;", $hQuery)
While _SQLite_FetchData($hQuery, $aRow, False, False) = $SQLITE_OK ; Read Out the next Row
    for $i = 0 to UBound($aRow)-1
        ConsoleWrite($aRow[$i] & @tab)
    Next
    ConsoleWrite(@CRLF)
WEnd
_SQLite_QueryFinalize($hQuery)

[gcue]

If there is no schema, then it's really not a database; it's just a file with a .sqlite extension.

How large is this "logs.sqlite" file and are you sure that it was located in the "sqlitebrowser_200_b1_win" folder that you tried opening it from? If you run sqlite3 against a file that doesn't exist, it will create a new file in the current location with the filename provided.

Edit:  Also, like @jchd said, try opening it in SQLite Expert or Database Browser for SQLite. They're much friendlier than the command line.

 

its 20MB

the reason its in that folder was because i copied it locally so i can do the command line thing

my apologies i put logs.sqlite instead of log.sqlite.. here's teh schema

http://imgur.com/JjfSQqt

thanks for your help guys

[KaFu]

Hmm, I still don't get what your aiming at... doesn't "SELECT RowID, * FROM aLog;" work for you? Because every record has a unique ID in RowID.

[gcue]

so if i have 100 records and i delete record 57 does record 58-100 automatically decrease by 1 - ie record 100 becomes 99?

Edited September 5, 2014 by gcue

[JohnQSmith]

No need to generate a unique record ID, SQLite does this automagically , try to access "RowID".

 

Hmm, I still don't get what your aiming at... doesn't "SELECT RowID, * FROM aLog;" work for you? Because every record has a unique ID in RowID.

 

Thanks for that. I didn't know about the automatic ROWID. 

[KaFu]

Nope, record 100 stays record 100 and the next added will become 101, even if 57 is missing.

Edited September 5, 2014 by KaFu

[JohnQSmith]

so if i have 100 records and i delete record 57 does record 58-100 automatically decrease by 1 - ie record 100 becomes 99?

 

No. It's an autoincrement field that remembers the last value. So, if you start from a new database and add 100 records and then delete all of them, the next ROWID will be 101.  See here... http://www.sqlite.org/autoinc.html

Edit: After reading down on that page, this is not "exactly" true. Quoting from there...

If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. If the table is initially empty, then a ROWID of 1 is used. If the largest ROWID is equal to the largest possible integer (9223372036854775807) then the database engine starts picking positive candidate ROWIDs at random until it finds one that is not previously used. If no unused ROWID can be found after a reasonable number of attempts, the insert operation fails with an SQLITE_FULL error. If no negative ROWID values are inserted explicitly, then automatically generated ROWID values will always be greater than zero.

Which sounds pretty much exactly like what you are trying to do.

Edited September 5, 2014 by JohnQSmith