Re: [sqlite] Ideas for Searching Names in Contacts

On 25/02/2011, at 2:23 AM, Mohit Sindhwani wrote: > Thank you for your suggestions - I tried the approach using LIKE and it > seemed quite slow (this was on Windows CE).. FTS3/4 seem good to explore. Yes, like '%name%' will be slow since it can't use an index. However, like 'name%' will use an

Re: [sqlite] COUNT() extremely slow first time!

Note sure. Will get back on this as soon as I have played a bit with the analyzer utility! What I meant was "My own estimated x bytes/row" :) x + ID + ~20 characters should make some ~32 bytes :) > Date: Wed, 23 Feb 2011 10:58:00 -0800 > From: fle...@fletchowns.net > To: sqlite-users@sqlite.o

[sqlite] Hugues Geymond/BR/PRS/BTMEU is out of the office this afternoon.

I will be out of the office starting 02/24/2011 and will not return until 02/28/2011. I will respond to your message when I return. If necessary please contact M. Fahloul * The information contained in this electronic

Re: [sqlite] Parallel execution of queries in threads

On Thu, Feb 24, 2011 at 4:52 AM, Maddy wrote: > Hi, > I have 4 "SELECT" queries which are accessing same table. > When I executed these queries concurrently using pthreads in Linux, it took > approximate 10 Seconds. > But same 4 queries executed sequentially it only took 2 seconds. > Total Rows i

Re: [sqlite] complex query

On 24 Feb 2011, at 15:20, Simon Slavin wrote: > On 24 Feb 2011, at 3:18pm, Philip Graham Willoughby wrote: > >> On 24 Feb 2011, at 14:07, Simon Slavin wrote: >> >>> A more flexible way to lay out genealogy databases is as follows: >>> >>> Person: id, name, sex, dob >>> >>> Relationship: id1, i

Re: [sqlite] Ideas for Searching Names in Contacts

On 24/2/2011 1:49 AM, Simon Slavin wrote: > I would not try to make each individual word of a name a row in a table. I > think 'LIKE' is designed almost exactly for your problem: > > > > This means you can search on any component of the name. for insta

Re: [sqlite] complex query

On 24 Feb 2011, at 3:18pm, Philip Graham Willoughby wrote: > On 24 Feb 2011, at 14:07, Simon Slavin wrote: > >> A more flexible way to lay out genealogy databases is as follows: >> >> Person: id, name, sex, dob >> >> Relationship: id1, id2, connection >> >> So you end up with things like >>

Re: [sqlite] complex query

On 24 Feb 2011, at 14:07, Simon Slavin wrote: > > On 24 Feb 2011, at 1:06pm, Josh Marell wrote: > >> I would say that couple should be a 3 column table, with coupleID, >> partner1ID, partner2ID. It looks like right now, you have just coupleID and >> partnerID, that doubles the number of rows yo

Re: [sqlite] Parallel execution of queries in threads

The reason could be caching, swapping or things like that. When you execute 4 queries concurrently in different connections each of them have to create its own memory cache of database pages. Reading data into cache involves syscalls to Linux kernel for reading from disk. Although Linux has its own

Re: [sqlite] complex query

On 24 Feb 2011, at 1:06pm, Josh Marell wrote: > I would say that couple should be a 3 column table, with coupleID, > partner1ID, partner2ID. It looks like right now, you have just coupleID and > partnerID, that doubles the number of rows you have. A more flexible way to lay out genealogy databa

Re: [sqlite] complex query

On 24 Feb 2011, at 13:40, Igor Tandetnik wrote: > Josh Marell wrote: >> I would say that couple should be a 3 column table, with coupleID, >> partner1ID, partner2ID. It looks like right now, you have just coupleID and >> partnerID, that doubles the number of rows you have. > > On the other han

Re: [sqlite] complex query

Josh Marell wrote: > I would say that couple should be a 3 column table, with coupleID, > partner1ID, partner2ID. It looks like right now, you have just coupleID and > partnerID, that doubles the number of rows you have. On the other hand, this would make many queries more complicated. Try gene

Re: [sqlite] complex query

On 24 Feb 2011, at 07:49, Aric Bills wrote: > people (a table of individuals who are somehow interrelated) > fields: uid (among others) > > partners (links individuals [i.e., individual spouses] to couple IDs) > fields: coupleid, personid > > children (links children to their parents' couple

Re: [sqlite] Parallel execution of queries in threads

You don't say how many cores you have but I assume more than 4. If you're just single or dual-core I'd say this isn't too surprising. It's pretty rare that multi-threading gives an N*X performance boost -- especially for CPU or disk bound processes. Simon said most of the reasons but also for

Re: [sqlite] complex query

I would say that couple should be a 3 column table, with coupleID, partner1ID, partner2ID. It looks like right now, you have just coupleID and partnerID, that doubles the number of rows you have. On Thu, Feb 24, 2011 at 7:02 AM, Igor Tandetnik wrote: > Aric Bills wrote: > > Using the results o

Re: [sqlite] complex query

Aric Bills wrote: > Using the results of this query, I'd like to identify cousins. It should be > possible to define two people as cousins if they share a grandparent but > have no parents in common. Defining the query above as the view > "grandparentage", I can come up with a query for people w

Re: [sqlite] Nth row of on sqlite DB

Hei, Try the following sql statements: To get the first n rows : SELECT * FROM LIMIT OFFSET 0 To get the nth entry : SELECT * FROM LIMIT 1 OFFSET Suresh Kavita Raghunathan wrote: > > Hello, > I’ve been adding and deleting rows from the sqlite database. Now the > primary ID is n

Re: [sqlite] COUNT() extremely slow first time!

Yes, Greg, please post this value for this table you use in count query Max On Wed, Feb 23, 2011 at 9:58 PM, Greg Barker wrote: > Is the record size you refer to here the same as the "Average payload per > entry" that sqlite3_analyzer determines for me? > > On Wed, Feb 23, 2011 at 5:09 AM, Max

[sqlite] Auto Reply: sqlite-users Digest, Vol 38, Issue 24

I am on vacation and will not be checking my email until March 2. If you have an urgent issue please contact Ashok Joshi. Ron Cohen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parallel execution of queries in threads

On 24 Feb 2011, at 9:52am, Maddy wrote: > I have 4 "SELECT" queries which are accessing same table. > When I executed these queries concurrently using pthreads in Linux, it took > approximate 10 Seconds. > But same 4 queries executed sequentially it only took 2 seconds. > Total Rows in table are

Re: [sqlite] Documentation seams to be wrong.

On Thu, Feb 24, 2011 at 4:44 AM, Matthew Baynham wrote: > On this page http://www.sqlite.org/c3ref/c_stmtstatus_autoindex.html there > are three Constants, however only 2 exist. > > SQLITE_STMTSTATUS_AUTOINDEX Doesn't seam to exist. > Added in version 3.7.0. You must be using an older version.

[sqlite] Parallel execution of queries in threads

Hi, I have 4 "SELECT" queries which are accessing same table. When I executed these queries concurrently using pthreads in Linux, it took approximate 10 Seconds. But same 4 queries executed sequentially it only took 2 seconds. Total Rows in table are 10. Sample query is "SELECT column1, sum(col

[sqlite] Documentation seams to be wrong.

On this page http://www.sqlite.org/c3ref/c_stmtstatus_autoindex.html there are three Constants, however only 2 exist. SQLITE_STMTSTATUS_AUTOINDEX Doesn't seam to exist. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/

Re: [sqlite] Problem with VACUUM feature

Hi, I fixed the code in such a way that the temp files which are created with below flags are deleted during close. dwFlagsAndAttributes = FILE_ATTRIBUTE_TEMPORARY | FILE_ATTRIBUTE_HIDDEN | FILE_FLAG_DELETE_ON_CLOSE; so.. * Pro

[sqlite] complex query

Apologies for the lengthy message. I'm a SQL newbie trying to model family relationships in SQLite (and no, this isn't homework; it's part of an effort to create a computer-based language lesson on kinship terminology). I've created the following tables: people (a table of individuals who are som

Re: [sqlite] COUNT() extremely slow first time!

Is the record size you refer to here the same as the "Average payload per entry" that sqlite3_analyzer determines for me? On Wed, Feb 23, 2011 at 5:09 AM, Max Vlasov wrote: > Greg, you should also take the record size into account. My hypothesis is > that if your record is comparatively small (s