[sqlite] query performance question

Hi everybody, after reading some parts of the documentation and numerous Google searches, I still have no solution for fixing my slow query. I would really appreciate if you could point me in the right direction. The basis is a food database (~38 megabyte total size) that contains

Re: [sqlite] query performance question

Quoth Mail , on 2010-10-05 11:43:29 +0200: > SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP > = ? AND (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK = > t1.ZFOOD) ) > ?) There should not need to be a DISTINCT when talking about a primary key. They will

Re: [sqlite] query performance question

Quoth Drake Wilson , on 2010-10-05 03:24:01 -0700: > > My current task is to get the number of foods that belong to each > > group and have at least one weight data related to them. > > That suggests something like: > > SELECT g.Z_PK AS "group", COUNT(f.Z_PK) AS "count" >

Re: [sqlite] When do I need SQLITE_THREADSAFE?

On Tue, Oct 5, 2010 at 4:55 AM, Jay A. Kreibich wrote: > On Mon, Oct 04, 2010 at 07:25:05PM -0700, Dustin Sallings scratched on the > wall: > > > The main difference between =1 and =2 is that =2 assumes you more or > less know what you're doing and will either lock a database

[sqlite] disk IO error after windows resumes from sleep

I have a question about recovering from SQLITE_IOERR? We are using sqlite v3.6.15 on windows where the databases may be accessed across a network (I am aware of the caveats here). On a windows vista/win7 machine or a laptop which goes into sleep mode, when it resumes and the application tries to

[sqlite] Slow operation

Hey guys. My apologies in advance if this is a slightly mundane question. I'm running this code from a scripting language bound to SQLite: %r = db.query("SELECT * FROM userTable", 0); %i = 0; db.query("BEGIN TRANSACTION", 0); while (%i < db.numRows(%r)) {

Re: [sqlite] disk IO error after windows resumes from sleep

Quoth Serena Lien , on 2010-10-05 11:46:18 +0100: > On a windows vista/win7 machine or a laptop which goes into sleep mode, when > it resumes and the application tries to open a database on a networked > drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I

Re: [sqlite] Slow operation

Quoth Ian Hardingham , on 2010-10-05 11:52:36 +0100: > Hey guys. My apologies in advance if this is a slightly mundane question. (Please don't start new threads by replying to random messages. The resultant header information indicates falsely that your email is part of the

[sqlite] Errors after upgrade from 3.6.22 to 3.7.2

Hi. After upgrading from SQLite 3.6.22 to 3.7.2 an application that I work on generated a lot of SQLite related errors. I think these errors have been traced to a change made in the winAccess(...) function. Specifically the "return SQLITE_IOERR_ACCESS" line that was added. Removing the

[sqlite] Incomplete PRAGMA table_info() documentation

Hello, PRAGMA table_info() doc describes only 4 columns in the output while there are actually 6 in SQLite 3.5.9 http://www.sqlite.org/pragma.html#pragma_table_info The last number is especially interesting. Please, CC. -- anatoly t. ___ sqlite-users

Re: [sqlite] Slow operation

Many thanks Drake, all of your points were highly pertinent. I'll stop lazily replying to threads and changing the subject! I indeed see that my approach was pretty bafflingly bad in highsight. I tend to do most "logic" in the scripting language as opposed to in SQLite commands as it's

Re: [sqlite] Slow operation

Quoth Ian Hardingham , on 2010-10-05 12:16:11 +0100: > Your query, > > UPDATE userTable SET playedInfIds = '' > > Still took two seconds actually... but significantly better than what I > was doing. You're doing this only once rather than once per row, right? On a table with

Re: [sqlite] Slow operation

I typed the command into my console - not doing it once per row. Doing it again, it was more like one second. No other SQLite commands should have been happening near the time of execution. I'm not entirely sure what "schema" means in this context. The definiton of userTable is: CREATE

Re: [sqlite] disk IO error after windows resumes from sleep

In response to your question - by "always continues to fail" I mean that yes, after delaying and retrying, even when the file should be accessible, I still get SQLITE_IOERR returned from sqlite3_open_v2. If my application exits and restarts, it will try to call sqlite3_open_v2 again on the same

Re: [sqlite] disk IO error after windows resumes from sleep

On 5 Oct 2010, at 11:46am, Serena Lien wrote: > On a windows vista/win7 machine or a laptop which goes into sleep mode, when > it resumes and the application tries to open a database on a networked > drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I don't > have a problem with

Re: [sqlite] query performance question

Mail wrote: > My current task is to get the number of foods that belong to each group and > have at least one weight data related to them. > > The query I am using for this is: > SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP = ? AND > (SELECT COUNT(*) FROM

Re: [sqlite] query performance question

Thank you very much! Your query is exactly what I was looking for. If I understand you correctly, the main bottleneck is the count in the inner select - is that correct? (disregarding the overall loop for all groups) Am 05.10.2010 um 12:24 schrieb Drake Wilson: > Quoth Mail ,

Re: [sqlite] disk IO error after windows resumes from sleep

Yes - that is indeed what I am doing (your 3rd scenario) - but I have tried to close the connection first (and ignore any errors closing it) before retrying to open. As you say, the database object I am trying to close may not be valid so I am not able to close it first, and therefore unable to

Re: [sqlite] query performance question

On 5 Oct 2010, at 10:43am, Mail wrote: > SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP = ? AND > (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK = t1.ZFOOD) ) > ?) Did you miss out 'AS' from your FROM clause between ZFFFOOD and t0 ? And again in the sub-SELECT ?

Re: [sqlite] query performance question

Simon Slavin wrote: > On 5 Oct 2010, at 10:43am, Mail wrote: > >> SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP = ? AND >> (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK = >> t1.ZFOOD) ) > ?) > > Did you miss out 'AS' from your FROM clause

Re: [sqlite] disk IO error after windows resumes from sleep

Could this also be because you never closed the database handle? So Sqlite thinks it's still open? First time you get an errror do an sqlite3_close() on the old handle. That may solve your problem. Next thing, you should register your app to receive the PBT_APMSUSPEND

Re: [sqlite] disk IO error after windows resumes from sleep

Closing the handle before going to sleep sounds like a really sensible thing to do which I hadn't heard of before, I will definitely try that! Thanks for the quick responses from you all, Serena. On Tue, Oct 5, 2010 at 1:16 PM, Black, Michael (IS) wrote: > Could this

Re: [sqlite] disk IO error after windows resumes from sleep

On 5 Oct 2010, at 1:11pm, Serena Lien wrote: > Yes - that is indeed what I am doing (your 3rd scenario) - but I have tried > to close the connection first (and ignore any errors closing it) before > retrying to open. As you say, the database object I am trying to close may > not be valid so I am

Re: [sqlite] query performance question

On 5 Oct 2010, at 1:14pm, Igor Tandetnik wrote: > Simon Slavin wrote: >> On 5 Oct 2010, at 10:43am, Mail wrote: >> >>> SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP = ? AND >>> (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK = >>> t1.ZFOOD) ) > ?)

Re: [sqlite] disk IO error after windows resumes from sleep

I duplicated your problem. Without the "FIX" defined this behaves badly after wakeup. But just adding the retry fixed it on my testing. #include #include #include #include "sqlite3.h" int main() { sqlite3 *db; int rc; remove("l:\test.db"); rc=sqlite3_open("l:/mike/test.db",); if (rc !=

Re: [sqlite] Slow operation

Quoth Ian Hardingham , on 2010-10-05 12:27:38 +0100: > CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL > UNIQUE, password TEXT NOT NULL, email TEXT, key TEXT, status TEXT, date > TEXT, playedFor INTEGER, totalScore FLOAT DEFAULT 0, totalRecord TEXT >

Re: [sqlite] Slow operation

Thanks again Drake, I'll investigate those alternatives. On 05/10/2010 13:52, Drake Wilson wrote: > Quoth Ian Hardingham, on 2010-10-05 12:27:38 +0100: >> CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL >> UNIQUE, password TEXT NOT NULL, email TEXT, key

[sqlite] Inserting images (gif) in Blob field (with php)

I searched quiet around the internet and cannot found an solution. What I'm currently using is get the file contents and then use base64_encode to store them in database. But that just blows the size up. I saw that the firefox database (places.sqlite) store the favicons directly in database. I

Re: [sqlite] Inserting images (gif) in Blob field (with php)

On 5 Oct 2010, at 2:06pm, Artur Reilin wrote: > I searched quiet around the internet and cannot found an solution. > > What I'm currently using is get the file contents and then use > base64_encode to store them in database. But that just blows the size up. > I saw that the firefox database

Re: [sqlite] Inserting images (gif) in Blob field (with php)

Quoth Artur Reilin , on 2010-10-05 15:06:57 +0200: > What I'm currently using is get the file contents and then use > base64_encode to store them in database. But that just blows the size up. > I saw that the firefox database (places.sqlite) store the favicons > directly in

Re: [sqlite] Slow operation

On Tue, Oct 05, 2010 at 04:08:41AM -0700, Drake Wilson scratched on the wall: > Quoth Ian Hardingham , on 2010-10-05 11:52:36 +0100: > > I'm running this code from a scripting language bound to SQLite: > > > > %r = db.query("SELECT * FROM userTable", 0); > > %i = 0; >

Re: [sqlite] Slow operation

Hey Jay, thanks for your feedback. I am indeed using (several) delineated lists. I would very much appreciate your input into how bad a decision this is. So, I basically need to find entrys of Table B that do not appear in that list. Obviously, it would be better to have a playedInf table

Re: [sqlite] Inserting images (gif) in Blob field (with php)

> Using base64 will allow you to take binary data (arbitrary 1s and 0s) and > store it in a text field. However, SQLite has BLOB fields, which can be > used to store data in its original binary form, and will indeed take up > fewer bytes than encoding it in base64. > > So you need to look at the

[sqlite] Help registering custom tokenizer

I am currently working on writing a custom tokenizer for use with a FTS3 indexed database. I believe I have written the new tokenizer module correctly and am just missing something with registering the tokenizer with the database. To verify this I copied the simple_tokenizer1.c to a new file

Re: [sqlite] When do I need SQLITE_THREADSAFE?

On Tue, Oct 05, 2010 at 12:44:59PM +0200, Zaher Dirkey scratched on the wall: > On Tue, Oct 5, 2010 at 4:55 AM, Jay A. Kreibich wrote: > > > On Mon, Oct 04, 2010 at 07:25:05PM -0700, Dustin Sallings scratched on the > > wall: > > > > > > The main difference between =1 and =2 is

Re: [sqlite] errors after sqlite3_step

Analysis and possible solution to getting unexpected SQLITE_CANTOPEN errors from sqlite3_step In pagerSharedLock, there is a test for the journal file acessability immediatly followed by an attempt to open it. If the journal file goes away between these two actions, sqlite3OsOpen will return

[sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

Hi, My SQLite is 3.7.2. I have a table like this: CREATE TABLE [newsd] ( [id] INTEGER PRIMARY KEY AUTOINCREMENT, [date] INTEGER NOT NULL, [title] TEXT NOT NULL, [yhfgdfhd] NONE, CONSTRAINT "fg" UNIQUE ([yhfgdfhd]) ON CONFLICT IGNORE ) so column [yhfgdfhd] is UNIQUE, and [title] is NOT

Re: [sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

On Tue, Oct 5, 2010 at 3:56 PM, Paweł Salawa wrote: > Hi, > > My SQLite is 3.7.2. > > I have a table like this: > > CREATE TABLE [newsd] ( > [id] INTEGER PRIMARY KEY AUTOINCREMENT, > [date] INTEGER NOT NULL, [title] TEXT NOT NULL, > [yhfgdfhd] NONE, > CONSTRAINT "fg"

Re: [sqlite] Help registering custom tokenizer

I did some more testing, and it is actually the code that register's the tokenizer, not the code that creates the table that is seg faulting. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Travis Orr Sent: October 5, 2010

Re: [sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

On Tue, Oct 05, 2010 at 09:56:52PM +0200, Pawe?? Salawa scratched on the wall: > Hi, > > My SQLite is 3.7.2. > > I have a table like this: > > CREATE TABLE [newsd] ( > [id] INTEGER PRIMARY KEY AUTOINCREMENT, > [date] INTEGER NOT NULL, [title] TEXT NOT NULL, > [yhfgdfhd] NONE, >

Re: [sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

> Maybe you should remove the "ON CONFLICT IGNORE" clause at the end of the > uniqueness constraint... You're right, of course. Shame on me for missing it :( Shame on me! -- Paweł Salawa pawelsal...@gmail.com ___ sqlite-users mailing list

Re: [sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

On 5 Oct 2010, at 9:40pm, Paweł Salawa wrote: >> Maybe you should remove the "ON CONFLICT IGNORE" clause at the end of the >> uniqueness constraint... > > You're right, of course. > Shame on me for missing it :( Shame on me! We all make mistakes like that. It's really useful to have someone