Jump to content
Sign in to follow this  
gcue

faster sqlite_query?

Recommended Posts

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!

 

Share this post


Link to post
Share on other sites
KaFu
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
jchd

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
gcue

schema is very simple...

just one table with multiple fields

Share this post


Link to post
Share on other sites
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?


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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.

 


Whenever someone says "pls" because it's shorter than "please", I say "no" because it's shorter than "yes".

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 by JohnQSmith

Whenever someone says "pls" because it's shorter than "please", I say "no" because it's shorter than "yes".

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 by gcue

Share this post


Link to post
Share on other sites
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.  :thumbsup:


Whenever someone says "pls" because it's shorter than "please", I say "no" because it's shorter than "yes".

Share this post


Link to post
Share on other sites
KaFu

Share this post


Link to post
Share on other sites
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 by JohnQSmith

Whenever someone says "pls" because it's shorter than "please", I say "no" because it's shorter than "yes".

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×