[sqlite] Slow Query with LEFT OUTER JOIN
Hello everyone, Hoping that I could get some help with a performance problem. Using version 3.5.2 Here are the tables: CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT) CREATE TABLE Keywords4Objects (ObjectId INTEGER, KeywordId INTEGER) CREATE TABLE Keywords (KeywordId INTEGER PRIMARY KEY, Keyword TEXT NOT NULL COLLATE NOCASE UNIQUE) The Query: SELECT DISTINCT o.ObjectId, o.Name FROM Objects o LEFT OUTER JOIN Keywords4Objects ok ON ok.ObjectId = o.ObjectId LEFT OUTER JOIN Keywords k ON k.KeywordId = ok.KeywordId WHERE k.Keyword LIKE 'abc' OR o.Name LIKE 'abc' Initially, I was just using a join, but objects without keywords were not getting picked up. When I switched to Left Outer Joins. I got the objects that don't have keywords, but the query is unbearably slow. It went from being instantaneous to over a minute. Any ideas? Thanks in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long update times
The blob sizes are about 24KB. I downloaded the sqlite3_analyzer, is there any documentation on this? It's just an executable and when I dbl click I get a console window that disappears. Also, the time I gave was wrong, it was closer to 55 seconds. I set the synchronous flag to 0 (NONE) and the time decreased to 30 seconds. Problem was that the synch flag did not stick. When I reopened the database, the flag was set to 2 (FULL) Thanks again for the help On Thu, Jun 19, 2008 at 7:21 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On Jun 19, 2008, at 9:17 AM, Jason Tudor wrote: > > > Hello Everyone, > > > > I was hoping that someone could explain why my updates are taking so > > long. > > My schema is as follows: > > > > CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT, Type > > INTEGER, > > Lock INTEGER, Data BLOB 3DDataId INTEGER DEFAULT NULL) > > > > My table has 12,243 rows. > > > > when I execute the following: "UPDATE Objects SET Lock = 1" > > > > I wait for about 20 seconds. I would think that the update should > > be much > > faster. > > I tried wrapping the statement with a transaction, but no luck. > > > I routinely do such operations in milliseconds. Dunno what you are > doing wrong. What hardware are you running on? How big are the BLOBs > in your table. Can you post the output from running the > sqlite3_analyzer utility (available from > http://www.sqlite.org/download.html) > on your database file so that we have a better idea of what kind of > data we are dealing with? > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The APress SQLite Book
> First, the index is completely unusable. I'll second that, worst index that I have ever tried to use. On Wed, Jun 18, 2008 at 11:05 PM, Roger Binns <[EMAIL PROTECTED]> wrote: > Stefan Arentz wrote: > > First, the index is completely unusable. > > From the few authors I have spoken to, they don't make the index > themselves. The publisher does it using someone else and you end up > with junk, and they bill the author for it! > > It is a miracle any computer books are any good. I suggest the > following two links for background: > > http://philip.greenspun.com/wtr/dead-trees/story.html > > http://www.xaprb.com/blog/2008/06/15/what-is-it-like-to-write-a-technical-book/ > > Roger > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Long update times
Hello Everyone, I was hoping that someone could explain why my updates are taking so long. My schema is as follows: CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT, Type INTEGER, Lock INTEGER, Data BLOB 3DDataId INTEGER DEFAULT NULL) My table has 12,243 rows. when I execute the following: "UPDATE Objects SET Lock = 1" I wait for about 20 seconds. I would think that the update should be much faster. I tried wrapping the statement with a transaction, but no luck. Thanks for your help. -Tudor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Regular Expressions and sqlite3_create_function
I am trying to enable the REGEXP operator. I have read on the forums that you must use the sqlite3_create_function. I would like to use the boost regular expressions library under the hood. Question is, does anyone have an example using this function (sqlite3_create_function) to override regexp()? TIA T ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why attach databases?
Thanks for the quick feedback, you all have good points. The data transfer example that Sam provided is the most compelling to me. I was initially thinking that I could attach every database I had with the same schema and execute queries without specifying database names. It's good to know the limitations and uses. Thanks again TUD On Feb 7, 2008 2:39 PM, Nicolas Williams <[EMAIL PROTECTED]> wrote: > Given the restrictions on views and triggers (they cannot make reference > to tables from more than one database) I'd say "stay away from ATTACH > where possible" -- don't create new uses of it without good reason. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why attach databases?
I have been playing around with attaching databases. I'm not sure what the point is. Assume that I have two databases with the same schema, say db1 and db2, and I attach them to one connection. In order to get the count from a particular table, I could do the following: SELECT COUNT id FROM main.table UNION SELECT COUNT id FROM db2.table I would then step through the result and add the two values to get a total count. If I have two separate connections, I could run two queries and add the results. Is there a speed difference? In general, what is the benefit of attaching databases verses maintaining multiple connections? TIA TUD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Encryption?
Is there any encryption functionality built into SQLite? Also, can I use extensions other than .db for SQLite database filtes? Thanks Tudor
