Re: [sqlite] sqlite Query Optimizer

[Prakash Premkumar]

Thanks for your reply Simon.
I have read through those links.

Does sqlite implement the pointers in the System R Algorithm ? Like
assigning selectivity factors for predicates in where clause?
Link to System R Algorithm:
https://www.cs.duke.edu/courses/spring03/cps216/papers/selinger-etal-1979.pdf

Thanks a lot for your time.

Regards
Prakash

On Wed, Oct 15, 2014 at 5:36 PM, Simon Slavin  wrote:

>
> On 15 Oct 2014, at 12:54pm, Prakash Premkumar 
> wrote:
>
> > I'm trying to understand the sqlite select query optimizer. It works by
> > assigning costs to each relation in FROM clause.
>
> That is only a little bit of how it works.  Have you read these ?
>
> 
> 
>
> Simon.
> ___
> 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] Search query alternatives.

[Michael Falconer]

Thanks Stephen,

good point, I was just after some general results, I do take your point
about caching etc. However it is logical to apply UNION ALL if appropriate
in preference to UNION which in this context is a bit lazy. I'm a bit
annoyed I didn't pick up on it myself, but thankful to Igor for reminding
me of the difference in the two statements.


On 16 October 2014 11:14, Stephen Chrzanowski  wrote:

> Careful with the timing.  You may be looking at OS memory caching the
> result set instead of pulling from the drive.  For best bets, either re-run
> both queries several times, ditch the longest and shortest times, then take
> the mean or average times and do the comparison that way.
>
> On Wed, Oct 15, 2014 at 7:54 PM, Michael Falconer <
> michael.j.falco...@gmail.com> wrote:
>
> > Igor,
> >
> > Nice one. A quick test using 10 lookup trees and the same search
> criteria:
> > *Showing rows 0 - 29 (30 total, Query took 0.4838 sec)*
> >
> > Now with UNION ALL replacing UNION:
> > *Showing rows 0 - 29 (30 total, Query took 0.2050 sec)*
> >
> > You weren't kidding about cheaper were you? LOL
> >
> >
> > On 16 October 2014 10:30, Michael Falconer  >
> > wrote:
> >
> > > Yes, I agree with the general sentiment. It is not exactly the design I
> > > would have chosen either, but it is what exists. Design change is
> > occurring
> > > but for the moment we are stuck with the current schema.
> > >
> > > If you cannot change the schemata to be more suitable, then your
> > >> demonstrated queries are very near as good as it gets. I can think of
> > other
> > >> ways to achieve the same, but nothing that would be more efficient to
> a
> > >> tangible level. Maybe someone else here can come up with something a
> bit
> > >> more tight.
> > >>
> > > You are preaching to the converted I'm afraid, but I appreciate the
> > points
> > > you made. The quoted paragraph probably answers most of my questions
> > > really, except for this from Igor:
> > >
> > > Unless you do expect duplicates and need to eliminate them, use UNION
> ALL
> > >> - it's much cheaper (this is assuming you insist on keeping multiple
> > >> tables).
> > >>
> > > Cheaper because it drops the operation to remove the duplicate records
> > > from the result set? I'm assuming. I'll give that a try.
> > >
> > > On 16 October 2014 10:05, RSmith  wrote:
> > >
> > >>
> > >> On 2014/10/16 00:05, Michael Falconer wrote:
> > >>
> > >>> Hi all,
> > >>>
> > >>> first off I must start with an apology. I know I'm sort of doing the
> > >>> wrong//...
> > >>>
> > >>
> > >> No need to apologise, this flies quite close to the central theme.
> > >> Whether you are using SQLite or any other SQL RDBMS, this is horrible
> DB
> > >> design and it is so for precisely the reasons you are asking the
> > question.
> > >> Tables should not be dynamic: The simple rule of thumb being - if you
> > >> cannot tell (or at least accurately predict) before-hand exactly how
> > many
> > >> tables will be in the final system, then you are doing it wrong.  Why
> > not
> > >> have one set of tables and in stead of preceding each of them with a
> > >> , simply add a column that can host the  as a simple
> > value,
> > >> which will immediately make your life very much easier and get the SQL
> > >> engine to do the work you are now trying to compensate for in a
> > lop-sided
> > >> manual kind of way.
> > >>
> > >> I can only imagine the amount of code you invested into your system to
> > >> track and deal with these dynamic sets of tables, so I know changing
> it
> > >> will seem like a rather large undertaking, but really it will mostly
> > >> involve removing loads of code to end up with a small set of simple
> > queries
> > >> that does all the work for you.
> > >>
> > >> The only reason I can imagine this sort of breakdown useful is if your
> > >> tree tables are all really really huge, like Gigabytes, and so there
> > might
> > >> be some efficiency to be gained from splitting it up, but I doubt this
> > is
> > >> the case.
> > >>
> > >> Just imagine the ease of those search queries you demonstrated... one
> > >> query, no UNIONs, a single set of joins and an extra where-clause
> > check...
> > >> Add to that increased efficiency at finding results (run one query in
> > stead
> > >> of 50).  Easy-mode. As a proof-of-concept, just make an SQLite DB, one
> > set
> > >> of tables as per your list, add column(s) for prefixing as needed,
> > import
> > >> all the tables from some large set of them (specifying additionally
> the
> > >> column for the prefix) and ten run search queries on them. I am
> willing
> > to
> > >> bet on the fact it will be much quicker in addition to being much
> > simpler.
> > >>
> > >> If you cannot change the schemata to be more suitable, then your
> > >> demonstrated queries are very near as good as it gets. I can think of
> > other
> > >> ways to achieve the same, but nothing that would be more efficient to
> a
> > >> tangible level. Maybe someone else here can c

Re: [sqlite] Search query alternatives.

[Stephen Chrzanowski]

Careful with the timing.  You may be looking at OS memory caching the
result set instead of pulling from the drive.  For best bets, either re-run
both queries several times, ditch the longest and shortest times, then take
the mean or average times and do the comparison that way.

On Wed, Oct 15, 2014 at 7:54 PM, Michael Falconer <
michael.j.falco...@gmail.com> wrote:

> Igor,
>
> Nice one. A quick test using 10 lookup trees and the same search criteria:
> *Showing rows 0 - 29 (30 total, Query took 0.4838 sec)*
>
> Now with UNION ALL replacing UNION:
> *Showing rows 0 - 29 (30 total, Query took 0.2050 sec)*
>
> You weren't kidding about cheaper were you? LOL
>
>
> On 16 October 2014 10:30, Michael Falconer 
> wrote:
>
> > Yes, I agree with the general sentiment. It is not exactly the design I
> > would have chosen either, but it is what exists. Design change is
> occurring
> > but for the moment we are stuck with the current schema.
> >
> > If you cannot change the schemata to be more suitable, then your
> >> demonstrated queries are very near as good as it gets. I can think of
> other
> >> ways to achieve the same, but nothing that would be more efficient to a
> >> tangible level. Maybe someone else here can come up with something a bit
> >> more tight.
> >>
> > You are preaching to the converted I'm afraid, but I appreciate the
> points
> > you made. The quoted paragraph probably answers most of my questions
> > really, except for this from Igor:
> >
> > Unless you do expect duplicates and need to eliminate them, use UNION ALL
> >> - it's much cheaper (this is assuming you insist on keeping multiple
> >> tables).
> >>
> > Cheaper because it drops the operation to remove the duplicate records
> > from the result set? I'm assuming. I'll give that a try.
> >
> > On 16 October 2014 10:05, RSmith  wrote:
> >
> >>
> >> On 2014/10/16 00:05, Michael Falconer wrote:
> >>
> >>> Hi all,
> >>>
> >>> first off I must start with an apology. I know I'm sort of doing the
> >>> wrong//...
> >>>
> >>
> >> No need to apologise, this flies quite close to the central theme.
> >> Whether you are using SQLite or any other SQL RDBMS, this is horrible DB
> >> design and it is so for precisely the reasons you are asking the
> question.
> >> Tables should not be dynamic: The simple rule of thumb being - if you
> >> cannot tell (or at least accurately predict) before-hand exactly how
> many
> >> tables will be in the final system, then you are doing it wrong.  Why
> not
> >> have one set of tables and in stead of preceding each of them with a
> >> , simply add a column that can host the  as a simple
> value,
> >> which will immediately make your life very much easier and get the SQL
> >> engine to do the work you are now trying to compensate for in a
> lop-sided
> >> manual kind of way.
> >>
> >> I can only imagine the amount of code you invested into your system to
> >> track and deal with these dynamic sets of tables, so I know changing it
> >> will seem like a rather large undertaking, but really it will mostly
> >> involve removing loads of code to end up with a small set of simple
> queries
> >> that does all the work for you.
> >>
> >> The only reason I can imagine this sort of breakdown useful is if your
> >> tree tables are all really really huge, like Gigabytes, and so there
> might
> >> be some efficiency to be gained from splitting it up, but I doubt this
> is
> >> the case.
> >>
> >> Just imagine the ease of those search queries you demonstrated... one
> >> query, no UNIONs, a single set of joins and an extra where-clause
> check...
> >> Add to that increased efficiency at finding results (run one query in
> stead
> >> of 50).  Easy-mode. As a proof-of-concept, just make an SQLite DB, one
> set
> >> of tables as per your list, add column(s) for prefixing as needed,
> import
> >> all the tables from some large set of them (specifying additionally the
> >> column for the prefix) and ten run search queries on them. I am willing
> to
> >> bet on the fact it will be much quicker in addition to being much
> simpler.
> >>
> >> If you cannot change the schemata to be more suitable, then your
> >> demonstrated queries are very near as good as it gets. I can think of
> other
> >> ways to achieve the same, but nothing that would be more efficient to a
> >> tangible level. Maybe someone else here can come up with something a bit
> >> more tight.
> >>
> >>
> >>  thing here as this question is NOT related to sqlite. It is a general
> SQL
> >>> question but I ask it here because I have great respect for the answers
> >>> and
> >>> discussions I have seen on this forum over many years. I rarely post
> >>> myself
> >>> as there are always several contributors who beat me to the answer and
> >>> often their response is far better than mine would have been. I'm not a
> >>> code leper, I don't need the actual SQL just the method really, though
> a
> >>> short code example would be well received for illustration.
> >>>
> >>> It's about a sea

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Igor,

Nice one. A quick test using 10 lookup trees and the same search criteria:
*Showing rows 0 - 29 (30 total, Query took 0.4838 sec)*

Now with UNION ALL replacing UNION:
*Showing rows 0 - 29 (30 total, Query took 0.2050 sec)*

You weren't kidding about cheaper were you? LOL


On 16 October 2014 10:30, Michael Falconer 
wrote:

> Yes, I agree with the general sentiment. It is not exactly the design I
> would have chosen either, but it is what exists. Design change is occurring
> but for the moment we are stuck with the current schema.
>
> If you cannot change the schemata to be more suitable, then your
>> demonstrated queries are very near as good as it gets. I can think of other
>> ways to achieve the same, but nothing that would be more efficient to a
>> tangible level. Maybe someone else here can come up with something a bit
>> more tight.
>>
> You are preaching to the converted I'm afraid, but I appreciate the points
> you made. The quoted paragraph probably answers most of my questions
> really, except for this from Igor:
>
> Unless you do expect duplicates and need to eliminate them, use UNION ALL
>> - it's much cheaper (this is assuming you insist on keeping multiple
>> tables).
>>
> Cheaper because it drops the operation to remove the duplicate records
> from the result set? I'm assuming. I'll give that a try.
>
> On 16 October 2014 10:05, RSmith  wrote:
>
>>
>> On 2014/10/16 00:05, Michael Falconer wrote:
>>
>>> Hi all,
>>>
>>> first off I must start with an apology. I know I'm sort of doing the
>>> wrong//...
>>>
>>
>> No need to apologise, this flies quite close to the central theme.
>> Whether you are using SQLite or any other SQL RDBMS, this is horrible DB
>> design and it is so for precisely the reasons you are asking the question.
>> Tables should not be dynamic: The simple rule of thumb being - if you
>> cannot tell (or at least accurately predict) before-hand exactly how many
>> tables will be in the final system, then you are doing it wrong.  Why not
>> have one set of tables and in stead of preceding each of them with a
>> , simply add a column that can host the  as a simple value,
>> which will immediately make your life very much easier and get the SQL
>> engine to do the work you are now trying to compensate for in a lop-sided
>> manual kind of way.
>>
>> I can only imagine the amount of code you invested into your system to
>> track and deal with these dynamic sets of tables, so I know changing it
>> will seem like a rather large undertaking, but really it will mostly
>> involve removing loads of code to end up with a small set of simple queries
>> that does all the work for you.
>>
>> The only reason I can imagine this sort of breakdown useful is if your
>> tree tables are all really really huge, like Gigabytes, and so there might
>> be some efficiency to be gained from splitting it up, but I doubt this is
>> the case.
>>
>> Just imagine the ease of those search queries you demonstrated... one
>> query, no UNIONs, a single set of joins and an extra where-clause check...
>> Add to that increased efficiency at finding results (run one query in stead
>> of 50).  Easy-mode. As a proof-of-concept, just make an SQLite DB, one set
>> of tables as per your list, add column(s) for prefixing as needed, import
>> all the tables from some large set of them (specifying additionally the
>> column for the prefix) and ten run search queries on them. I am willing to
>> bet on the fact it will be much quicker in addition to being much simpler.
>>
>> If you cannot change the schemata to be more suitable, then your
>> demonstrated queries are very near as good as it gets. I can think of other
>> ways to achieve the same, but nothing that would be more efficient to a
>> tangible level. Maybe someone else here can come up with something a bit
>> more tight.
>>
>>
>>  thing here as this question is NOT related to sqlite. It is a general SQL
>>> question but I ask it here because I have great respect for the answers
>>> and
>>> discussions I have seen on this forum over many years. I rarely post
>>> myself
>>> as there are always several contributors who beat me to the answer and
>>> often their response is far better than mine would have been. I'm not a
>>> code leper, I don't need the actual SQL just the method really, though a
>>> short code example would be well received for illustration.
>>>
>>> It's about a search performed on multiple tables. However the structure
>>> of
>>> this database is somewhat unorthodox. It contains genealogical data and
>>> this is clustered into trees. Each tree has 8 tables, there can be as
>>> many
>>> as 100 trees but most installations of the associated application
>>> software
>>> contain between 5 - 50 trees. These 8 tables contain a family trees data
>>> and are named:
>>> addresses
>>> connections
>>> events
>>> family
>>> person
>>> repositories
>>> sources
>>> texts
>>>
>>> The  changes and is unique for each tree.
>>>
>>> There are extensive search options o

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Yes, I agree with the general sentiment. It is not exactly the design I
would have chosen either, but it is what exists. Design change is occurring
but for the moment we are stuck with the current schema.

If you cannot change the schemata to be more suitable, then your
> demonstrated queries are very near as good as it gets. I can think of other
> ways to achieve the same, but nothing that would be more efficient to a
> tangible level. Maybe someone else here can come up with something a bit
> more tight.
>
You are preaching to the converted I'm afraid, but I appreciate the points
you made. The quoted paragraph probably answers most of my questions
really, except for this from Igor:

Unless you do expect duplicates and need to eliminate them, use UNION ALL -
> it's much cheaper (this is assuming you insist on keeping multiple tables).
>
Cheaper because it drops the operation to remove the duplicate records from
the result set? I'm assuming. I'll give that a try.

On 16 October 2014 10:05, RSmith  wrote:

>
> On 2014/10/16 00:05, Michael Falconer wrote:
>
>> Hi all,
>>
>> first off I must start with an apology. I know I'm sort of doing the
>> wrong//...
>>
>
> No need to apologise, this flies quite close to the central theme. Whether
> you are using SQLite or any other SQL RDBMS, this is horrible DB design and
> it is so for precisely the reasons you are asking the question.  Tables
> should not be dynamic: The simple rule of thumb being - if you cannot tell
> (or at least accurately predict) before-hand exactly how many tables will
> be in the final system, then you are doing it wrong.  Why not have one set
> of tables and in stead of preceding each of them with a , simply
> add a column that can host the  as a simple value, which will
> immediately make your life very much easier and get the SQL engine to do
> the work you are now trying to compensate for in a lop-sided manual kind of
> way.
>
> I can only imagine the amount of code you invested into your system to
> track and deal with these dynamic sets of tables, so I know changing it
> will seem like a rather large undertaking, but really it will mostly
> involve removing loads of code to end up with a small set of simple queries
> that does all the work for you.
>
> The only reason I can imagine this sort of breakdown useful is if your
> tree tables are all really really huge, like Gigabytes, and so there might
> be some efficiency to be gained from splitting it up, but I doubt this is
> the case.
>
> Just imagine the ease of those search queries you demonstrated... one
> query, no UNIONs, a single set of joins and an extra where-clause check...
> Add to that increased efficiency at finding results (run one query in stead
> of 50).  Easy-mode. As a proof-of-concept, just make an SQLite DB, one set
> of tables as per your list, add column(s) for prefixing as needed, import
> all the tables from some large set of them (specifying additionally the
> column for the prefix) and ten run search queries on them. I am willing to
> bet on the fact it will be much quicker in addition to being much simpler.
>
> If you cannot change the schemata to be more suitable, then your
> demonstrated queries are very near as good as it gets. I can think of other
> ways to achieve the same, but nothing that would be more efficient to a
> tangible level. Maybe someone else here can come up with something a bit
> more tight.
>
>
>  thing here as this question is NOT related to sqlite. It is a general SQL
>> question but I ask it here because I have great respect for the answers
>> and
>> discussions I have seen on this forum over many years. I rarely post
>> myself
>> as there are always several contributors who beat me to the answer and
>> often their response is far better than mine would have been. I'm not a
>> code leper, I don't need the actual SQL just the method really, though a
>> short code example would be well received for illustration.
>>
>> It's about a search performed on multiple tables. However the structure of
>> this database is somewhat unorthodox. It contains genealogical data and
>> this is clustered into trees. Each tree has 8 tables, there can be as many
>> as 100 trees but most installations of the associated application software
>> contain between 5 - 50 trees. These 8 tables contain a family trees data
>> and are named:
>> addresses
>> connections
>> events
>> family
>> person
>> repositories
>> sources
>> texts
>>
>> The  changes and is unique for each tree.
>>
>> There are extensive search options offered to users as well as simple name
>> searching and it is one of the best features of the app. It works pretty
>> well, so it ain't broke in any sense, we just wonder if there is a better
>> way to perform this search in SQL. Is there a general technique which is
>> superior either in speed, efficiency or load bearing contexts?
>>
>> I am sure you can see one of the pitfalls here is the exponential growth
>> of
>> such a search query as both total n

Re: [sqlite] Search query alternatives.

[RSmith]

On 2014/10/16 00:05, Michael Falconer wrote:

Hi all,

first off I must start with an apology. I know I'm sort of doing the wrong//...


No need to apologise, this flies quite close to the central theme. Whether you are using SQLite or any other SQL RDBMS, this is 
horrible DB design and it is so for precisely the reasons you are asking the question.  Tables should not be dynamic: The simple 
rule of thumb being - if you cannot tell (or at least accurately predict) before-hand exactly how many tables will be in the final 
system, then you are doing it wrong.  Why not have one set of tables and in stead of preceding each of them with a , simply 
add a column that can host the  as a simple value, which will immediately make your life very much easier and get the SQL 
engine to do the work you are now trying to compensate for in a lop-sided manual kind of way.


I can only imagine the amount of code you invested into your system to track and deal with these dynamic sets of tables, so I know 
changing it will seem like a rather large undertaking, but really it will mostly involve removing loads of code to end up with a 
small set of simple queries that does all the work for you.


The only reason I can imagine this sort of breakdown useful is if your tree tables are all really really huge, like Gigabytes, and 
so there might be some efficiency to be gained from splitting it up, but I doubt this is the case.


Just imagine the ease of those search queries you demonstrated... one query, no UNIONs, a single set of joins and an extra 
where-clause check... Add to that increased efficiency at finding results (run one query in stead of 50).  Easy-mode. As a 
proof-of-concept, just make an SQLite DB, one set of tables as per your list, add column(s) for prefixing as needed, import all the 
tables from some large set of them (specifying additionally the column for the prefix) and ten run search queries on them. I am 
willing to bet on the fact it will be much quicker in addition to being much simpler.


If you cannot change the schemata to be more suitable, then your demonstrated queries are very near as good as it gets. I can think 
of other ways to achieve the same, but nothing that would be more efficient to a tangible level. Maybe someone else here can come up 
with something a bit more tight.



thing here as this question is NOT related to sqlite. It is a general SQL
question but I ask it here because I have great respect for the answers and
discussions I have seen on this forum over many years. I rarely post myself
as there are always several contributors who beat me to the answer and
often their response is far better than mine would have been. I'm not a
code leper, I don't need the actual SQL just the method really, though a
short code example would be well received for illustration.

It's about a search performed on multiple tables. However the structure of
this database is somewhat unorthodox. It contains genealogical data and
this is clustered into trees. Each tree has 8 tables, there can be as many
as 100 trees but most installations of the associated application software
contain between 5 - 50 trees. These 8 tables contain a family trees data
and are named:
addresses
connections
events
family
person
repositories
sources
texts

The  changes and is unique for each tree.

There are extensive search options offered to users as well as simple name
searching and it is one of the best features of the app. It works pretty
well, so it ain't broke in any sense, we just wonder if there is a better
way to perform this search in SQL. Is there a general technique which is
superior either in speed, efficiency or load bearing contexts?

I am sure you can see one of the pitfalls here is the exponential growth of
such a search query as both total number of trees and indeed user search
criteria increase. For each criteria component, and there are quite a few,
the appropriate tables must be queried and results joined.

Searches return records of individuals meeting the entered search criteria
so the query focuses on the person table as it's anchor, performs searches
on required other tables in the tree and joins the results to the person
data. The results from each tree search are then UNION'ed to provide the
result set. Here is a contrived example of the SQL query code which should
make things clearer.

The user enters simple search criteria - any person with a last name
containing 'mac' and a first name containing the character 'a'. This is the
resulting query (generated by php code), which searches a small 4 family
tree installation.

(SELECT humo1_person.*, event_kind, event_event, address_place, address_zip
 FROM humo1_person
 LEFT JOIN humo1_events ON pers_gedcomnumber=event_person_id
 LEFT JOIN humo1_addresses ON pers_gedcomnumber=address_person_id
 WHERE pers_lastname LIKE '%mac%'
   AND (pers_firstname LIKE '%a%'
   OR (event_kind='name' AND event_event LIKE '%a%')

Re: [sqlite] Search query alternatives.

[Igor Tandetnik]

On 10/15/2014 6:05 PM, Michael Falconer wrote:

addresses
connections
events
family
person
repositories
sources
texts


Personally, I'd have one set of tables, each with an extra column 
containing . Why do you need a separate set of tables, only to 
UNION them on every request? Instead of sharding by table, shard by row, 
using "prefix" column as a discriminator.



(SELECT humo1_person.*, event_kind, event_event, address_place, address_zip
UNION
 (SELECT humo2_person.*, event_kind, event_event, address_place,


Unless you do expect duplicates and need to eliminate them, use UNION 
ALL - it's much cheaper (this is assuming you insist on keeping multiple 
tables).

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Search query alternatives.

[Michael Falconer]

Hi all,

first off I must start with an apology. I know I'm sort of doing the wrong
thing here as this question is NOT related to sqlite. It is a general SQL
question but I ask it here because I have great respect for the answers and
discussions I have seen on this forum over many years. I rarely post myself
as there are always several contributors who beat me to the answer and
often their response is far better than mine would have been. I'm not a
code leper, I don't need the actual SQL just the method really, though a
short code example would be well received for illustration.

It's about a search performed on multiple tables. However the structure of
this database is somewhat unorthodox. It contains genealogical data and
this is clustered into trees. Each tree has 8 tables, there can be as many
as 100 trees but most installations of the associated application software
contain between 5 - 50 trees. These 8 tables contain a family trees data
and are named:
addresses
connections
events
family
person
repositories
sources
texts

The  changes and is unique for each tree.

There are extensive search options offered to users as well as simple name
searching and it is one of the best features of the app. It works pretty
well, so it ain't broke in any sense, we just wonder if there is a better
way to perform this search in SQL. Is there a general technique which is
superior either in speed, efficiency or load bearing contexts?

I am sure you can see one of the pitfalls here is the exponential growth of
such a search query as both total number of trees and indeed user search
criteria increase. For each criteria component, and there are quite a few,
the appropriate tables must be queried and results joined.

Searches return records of individuals meeting the entered search criteria
so the query focuses on the person table as it's anchor, performs searches
on required other tables in the tree and joins the results to the person
data. The results from each tree search are then UNION'ed to provide the
result set. Here is a contrived example of the SQL query code which should
make things clearer.

The user enters simple search criteria - any person with a last name
containing 'mac' and a first name containing the character 'a'. This is the
resulting query (generated by php code), which searches a small 4 family
tree installation.

(SELECT humo1_person.*, event_kind, event_event, address_place, address_zip
FROM humo1_person
LEFT JOIN humo1_events ON pers_gedcomnumber=event_person_id
LEFT JOIN humo1_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%')
  )
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo2_person.*, event_kind, event_event, address_place,
address_zip
FROM humo2_person LEFT JOIN humo2_events ON
pers_gedcomnumber=event_person_id
LEFT JOIN humo2_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%') )
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo3_person.*, event_kind, event_event, address_place,
address_zip
FROM humo3_person LEFT JOIN humo3_events ON
pers_gedcomnumber=event_person_id
LEFT JOIN humo3_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%')
  )
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo4_person.*, event_kind, event_event, address_place,
address_zip
FROM humo4_person
LEFT JOIN humo4_events ON pers_gedcomnumber=event_person_id
LEFT JOIN humo4_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%')
  )
GROUP BY pers_gedcomnumber
)
ORDER BY pers_lastname ASC , pers_firstname ASC LIMIT 0,30

Any thoughts? Suggestions? Missiles? Good approach, bad approach, or
completely off the grid? I do use sqlite quite a bit, but not on this
particular project.


-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsubscription

[Klaas V]

Venkatarangan MJ wrote:

>How do I unsubscribe from the group as I have stopped using SQLITE from quite 
>some time now?
>Thanks.
>Regards
>Rangan.

Send a message with subject or body 'help' to sqlite-users-requ...@sqlite.org

This is written in the header of each digest; I don't know about single 
messages.
We're sorry to see one of us go as a user of the greatest RDBMS on planet Earth 
:-|

As they say in Italy:in bocca al lupo 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SSL Rating on SQLite.org

[Jungle Boogie]

Hello All/Richard,

Unrelated to databases, sql, and sqlite but I noticed the poor rating on 
sqlite.org of an F:

https://www.ssllabs.com/ssltest/analyze.html?d=sqlite.org

It would be nice to have a better rating!

On the positive side, oracle.com doesn't even have an SSL for the homepage.

--
inum: 883510009027723
sip: jungleboo...@sip2sip.info
xmpp: jungle-boo...@jit.si
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issue when copying data from one sqlite database to another

[Pontus Bergsten]

Thanks for your suggestions.
@Mikael I do believe that write caching was disabled on target. However, 
enabling lazy data write and data caching didn't make much of a difference.
@Ketil I specified the pragmas before copying by executing the sql statements 
"PRAGMA Dest.journal_mode = OFF" and "PRAGMA synchronous = OFF" on the database 
connection containing the "Main" and the attached "Dest" database. The queries 
seemed to be accepted by sqlite. However, it didn't make much of a difference.

@Richard The embedded operating system is RTOS-32 (including RTFiles-32 and 
RTUSB-32) from On time.
I find it strange that enabling the file data cache, and the setting of the 
synchronous pragma didn't affect performance. However, from the thread 
profiling, it can be seen that a "INSERT INTO ... FROM  ... WHERE"  statement 
usually have a couple of continuous segments with fairly good CPU utilization 
for about 10 to 50 ms, that is followed by long periods, 100 to 200 ms, 
containing only sporadic activity ~10 us triggered by the USB driver.
In the current implementation the data is transferred in chunks using separate 
"INSERT INTO ... FROM  ... WHERE" statements. The reason for this was to 
display some progress to the user. Next, I will try to transfer data data in 
larger chunks and see it makes any difference.
Any other ideas are most welcome!
Regards,
/Pontus
 



 Från: Richard Hipp 
 Till: Pontus Bergsten ; General Discussion of SQLite 
Database  
 Skickat: onsdag, 15 oktober 2014 0:29
 Ämne: Re: [sqlite] Performance issue when copying data from one sqlite 
database to another
   




On Tue, Oct 14, 2014 at 5:24 PM, Pontus Bergsten  
wrote:

 When the application is executed on Windows on a desktop computer, the copying 
works fine and the performance is fairly ok, even when saving to USB. However, 
when the same code is executed on the embedded system, the copying of data is 
extremely slow, even though the CPU load is very moderate.



That sounds like a file-system problem to me.  What is your embedded OS?

-- 
D. Richard Hipp
d...@sqlite.org

  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[James K. Lowden]

On Tue, 14 Oct 2014 18:21:27 -0400
Ross Altman  wrote:

> Yeah, that's actually a really good point. Oh well, I guess I'll just
> have to hope that people decide to use the database responsibly...
> haha

You can advertise your database with the tagline, "Please compute
responsibly".  

The first rule of security is that there's no security without physical
security.  If you don't control the physical thing itself -- usually
hardware, but in this case a file -- then you don't control what
can be done with it.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Stephen Chrzanowski]

I've got three options, two of which require an internet connection, one
part time, the other full time.  The third option has the constraint on the
size of the data in question.

- Have your preference of a resultant hash check in a plain text file
sitting somewhere on your web server.  The application pokes the server at
each run to verify the hash against the physical hash of the DB.  If a
missmatch is found, force a download.  This will make sure your end users
have the most up to date data, as well as protect your primary criteria of
keeping the data 'read-only' at application run, however, an internet
connection would be required at least for the initial check.

- Have your application query a database on your server via web or socket
protocols instead of relying on the flat file.  This becomes bandwidth
heavy, and the end user machine requires an internet connection for the
life of your applications running lifespan on the client computer

- If on Windows (I'm not sure if other OS compilers have the capabilities)
Build the database into your software via a resource file.
-- Depending on the IDE, you can have the compiler automatically create a
resource file that'll be put directly into your compiled code, and it'll
build that resource file on a full build, or, on an if-needed basis.
-- Run an MD5 (Or whatever your pref is) against the physical file being
built into the EXE
-- At run time, if the database doesn't exist or if the MD5 check fails,
dump the resource file back out to the physical file.
- The downside of this is that you'll be retransmitting the application
each time, BUT, it'll guarantee that your data is consistent based on the
build of your application.
- Implementation of the checks and balances would be new code, but, only
need to be built for the applications startup.  Once the physical file is
placed down and is valid, your existing routines will work (Provided you're
closing the DB properly, writing out the database file, then re-opening the
database against the same variables)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsubscription

[jose isaias cabrera]

"Venkatarangan MJ" wrote...

How do I unsubscribe from the group as I have stopped using SQLITE from 
quite some time now?


Thanks.

Regards
Rangan.


Look at the bottom of this email and click on the last link.  I think you 
can figure it out the rest.


jic 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite Query Optimizer

[Simon Slavin]

On 15 Oct 2014, at 12:54pm, Prakash Premkumar  wrote:

> I'm trying to understand the sqlite select query optimizer. It works by
> assigning costs to each relation in FROM clause.

That is only a little bit of how it works.  Have you read these ?




Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite Query Optimizer

[Prakash Premkumar]

Hi,

I'm trying to understand the sqlite select query optimizer. It works by
assigning costs to each relation in FROM clause. As far as I understand, it
primarily uses the logarithmic estimate of the number of rows in the
relation.

Query optimization algorithms like IBM System R algorithm assigns weight to
predicates in WHERE clause in addition to the number of rows.
Can that be implemented for sqlite /what are other optimizations that are
available in other databases like postgres,MySQL that can be ported to
SQLite.

Thanks for sharing your ideas.

Regards
Prakash
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unable to prepare a statement

[Dan Kennedy]

On 10/15/2014 07:19 AM, Sam Carleton wrote:

When I use the SQLite Manager, I am able to run this query just fine:

 UPDATE EventNode
SET IsActive = 1
  WHERE EventNodeId IN (SELECT w.EventNodeId
  FROM EventNode as w, EventNode as m on
m.objectId = 'a09f0f8a-a37c-44c2-846f-16a59b1c34c1'
  WHERE w.lft BETWEEN m.lft AND m.rgt )

But when I try to prepare the same statement to be used with my C++ code:

 const char * updateString =
 "UPDATE EventNode "
"SET IsActive = @isActive "
  "WHERE EventNodeId IN (SELECT w.EventNodeId "
  "FROM EventNode AS w, EventNode AS m ON
m.objectId = @objectId "
 "WHERE w.lft BETWEEN m.lft AND m.rgt)";

I get an error where sqlite3_errmsg() returns: no such table: EventNode

Now the code that is opening the DB is in a base class which is used other
places to access the EventNode table, so I am a bit mystified as to what
exactly is going on.  Any thoughts?


Perhaps it's opening a different database file.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users