Re: [sqlite] Improve query performance using a join
On Thu, Aug 7, 2014 at 5:51 AM, Paul Dillon paul.dil...@gmail.com wrote: 1. Will moving these 8 query fields to a smaller table improve query performance when joined to the larger table? My logic is that this small table would only be about 5% the size of the full table, so the full table scan to service the query might be faster. An alternative that could allow you to cheaply evaluate whether having the smaller 8-column table would be to index those 8 columns. See 1.7 Covering Indices from http://www.sqlite.org/queryplanner.html. Yes, you'd duplicate the data, and your DB would grow by 5% from your own admission, but then SQLite would be able to route queries selecting from that 8-column subset to the index and not read the table at all, and read the full table for other queries, transparently for you (analyze, to get good stats, which will take a long time, and then verify using explain query plan http://www.sqlite.org/eqp.html whether queries access the index, the table, or both). If you adjust your cache size to match or exceed the index size, you might be able to avoid disk IO altogether later, if allof your queries stayed within that subset, although it sounded like you load everything upfront which implies you don't query much later. The advantage of the above it that it requires very little effort from you, just a create index basically, to test that idea and see if it helps. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] After ANALYZE all has become slow
using putty
Linux SRVXXX 2.6.26-2-686 #1 SMP Mon Aug 30 07:01:57 UTC 2010 i686
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
You have new mail.
Last login: Fri Aug 8 08:22:45 2014 from trantor.ia.lan
hal9000@SRVXXX :~$ clear
hal9000@SRVXXX :~$ python
Python 2.5.2 (r252:60911, Jan 24 2010, 14:53:14)
[GCC 4.3.2] on linux2
Type help, copyright, credits or license for more information.
import sqlite3
cn = sqlite3.connect(':memory:')
sqlite3.version
'2.3.2'
sqlite3.sqlite_version
'3.5.9'
cn.cursor().execute(SELECT sqlite_version();).fetchall()
[(u'3.5.9',)]
for i in range(20):
print cn.cursor().execute('SELECT
sqlite_compileoption_get(?);',(i,)).fetchall()
sqlite3.OperationalError: no such function: sqlite_compileoption_get
File stdin, line 1
sqlite3.OperationalError: no such function: sqlite_compileoption_get
On Fri, Aug 8, 2014 at 3:47 AM, Keith Medcalf kmedc...@dessus.com wrote:
Giuseppe,
What version of the sqlite3 library is python using?
python
Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit (Intel)] on
win32
Type help, copyright, credits or license for more information.
import sqlite3
cn = sqlite3.connect(':memory:')
sqlite3.version
'2.6.0'
sqlite3.sqlite_version
'3.8.6'
cn.cursor().execute('select sqlite_version();').fetchall()
[(u'3.8.6',)]
for i in range(20):
...cn.cursor().execute('select
sqlite_compileoption_get(?);',(i,)).fetchall()
...
[(u'DEFAULT_LOCKING_MODE=0',)]
[(u'ENABLE_COLUMN_METADATA',)]
[(u'ENABLE_FTS3',)]
[(u'ENABLE_FTS3_PARENTHESIS',)]
[(u'ENABLE_FTS4',)]
[(u'ENABLE_LOAD_EXTENSION',)]
[(u'ENABLE_MEMORY_MANAGEMENT',)]
[(u'ENABLE_MEMSYS5',)]
[(u'ENABLE_RTREE',)]
[(u'ENABLE_STAT4',)]
[(u'MAX_SCHEMA_RETRY=50',)]
[(u'SOUNDEX',)]
[(u'SYSTEM_MALLOC',)]
[(u'TEMP_STORE=2',)]
[(u'THREADSAFE=1',)]
[(None,)]
[(None,)]
[(None,)]
[(None,)]
[(None,)]
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Thursday, 7 August, 2014 09:26
To: General Discussion of SQLite Database
Subject: Re: [sqlite] After ANALYZE all has become slow
On 7 Aug 2014, at 3:24pm, Giuseppe Costanzi giuseppecosta...@gmail.com
wrote:
I have finished running ANALYZE and SQLite commands are taking longer
only when I use python db api
such as
[...]
however I have done what you have pointed out me and in effects the
speed of execution
from the shell is best after having performed ANALYZE
as I would be me expected [snip]
I have also noticed that, using python db api2 if I perform a query
recalling a view on the database
this it is slower if I directly write the query in the script of python
For example if I perform [...]
and this independently from ANALTYZE but after run ANALYZER the
situation worsens
Very good. If you had a situation where the Shell Tool got slower on
ANALYZE then it would definitely be an error in SQLite and we could
investigate. But you are reporting a situation where the Shell Tool gets
faster but Python gets slower.
This makes me think that the fault is with your Python library. I don't
know enough about Python to help. I am going to hope that someone
familiar with Python sees this and can help you.
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] After ANALYZE all has become slow
On Fri, 8 Aug 2014 11:14:29 +0200, Giuseppe Costanzi giuseppecosta...@gmail.com wrote: sqlite3.sqlite_version '3.5.9' Version 3.5.9 is more than 6 years old. A lot of optimizations were introduced since that version. Please upgrade and try again. -- Groet, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] After ANALYZE all has become slow
The default installation on my VPS running the current release of CentOS is
pretty old as well. Unfortunately it is kind of difficult to upgrade the
version of SQLite (and/or Python) on some Linux distributions because there are
other system dependencies on the versions of Python. For example on CentOS
there are dependencies between Python version and the package manager (yum in
the case of CentOS) making such upgrades sometimes very problematic.
Linux X 3.15.4-x86_64 #1 SMP Mon Jul 7 08:42:36 EDT 2014 x86_64 x86_64
x86_64 GNU/Linux
# python
Python 2.6.6 (r266:84292, Jan 22 2014, 09:42:36)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2
Type help, copyright, credits or license for more information.
import sqlite3
cn = sqlite3.connect(':memory:')
sqlite3.version
'2.4.1'
sqlite3.sqlite_version
'3.6.20'
for i in range(20):
... cn.cursor().execute('select sqlite_compileoption_get(?);',(i,)).fetchall()
...
Traceback (most recent call last):
File stdin, line 2, in module
sqlite3.OperationalError: no such function: sqlite_compileoption_get
Also, the version of the interface module (sqlite3.py / _sqlite3.so) between
python and sqlite3 (sqlite3.so) is quite old as well (compared to the current
versions of python and sqlite3 -- though the interface has not significantly
changed in how it implements most connection/cursor operations).
Is the command line shell of sqlite3 you are using in your testing the same
version of SQLite as the shared object being used by python?
# sqlite3
SQLite version 3.6.20
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite
If they are different, that might explain the different results between the two
depending on the versions. There have been many changes in sqlite since
version 3.6.20 which might affect performance of actually running a query,
depending on the version of the command shell in use.
If they are the same version then the time taken to execute a select at the
command line tool interface and retrieve the results should not be
significantly different than what you see using from python calls. cursor()
methods .execute(...) corresponds to sqlite3_prepare / sqlite3_bind (if
parameters are given); .fetchone() corresponds to a single sqlite3_step and the
sqlite3_value calls necessary to retrieve a row; and, .fetchall() is simply
.fetchone() in a loop until no more rows are available and accumulates the
retrieved row tuples as a list (all executed in C code, not in python code, so
it should be relatively the same efficiency as the sqlite3 shell tool is).
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of Giuseppe Costanzi
Sent: Friday, 8 August, 2014 03:14
To: General Discussion of SQLite Database
Subject: Re: [sqlite] After ANALYZE all has become slow
using putty
Linux SRVXXX 2.6.26-2-686 #1 SMP Mon Aug 30 07:01:57 UTC 2010 i686
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
You have new mail.
Last login: Fri Aug 8 08:22:45 2014 from trantor.ia.lan
hal9000@SRVXXX :~$ clear
hal9000@SRVXXX :~$ python
Python 2.5.2 (r252:60911, Jan 24 2010, 14:53:14)
[GCC 4.3.2] on linux2
Type help, copyright, credits or license for more information.
import sqlite3
cn = sqlite3.connect(':memory:')
sqlite3.version
'2.3.2'
sqlite3.sqlite_version
'3.5.9'
cn.cursor().execute(SELECT sqlite_version();).fetchall()
[(u'3.5.9',)]
for i in range(20):
print cn.cursor().execute('SELECT
sqlite_compileoption_get(?);',(i,)).fetchall()
sqlite3.OperationalError: no such function: sqlite_compileoption_get
File stdin, line 1
sqlite3.OperationalError: no such function: sqlite_compileoption_get
On Fri, Aug 8, 2014 at 3:47 AM, Keith Medcalf kmedc...@dessus.com
wrote:
Giuseppe,
What version of the sqlite3 library is python using?
python
Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit
(Intel)] on win32
Type help, copyright, credits or license for more information.
import sqlite3
cn = sqlite3.connect(':memory:')
sqlite3.version
'2.6.0'
sqlite3.sqlite_version
'3.8.6'
cn.cursor().execute('select sqlite_version();').fetchall()
[(u'3.8.6',)]
for i in range(20):
...cn.cursor().execute('select
sqlite_compileoption_get(?);',(i,)).fetchall()
...
[(u'DEFAULT_LOCKING_MODE=0',)]
[(u'ENABLE_COLUMN_METADATA',)]
[(u'ENABLE_FTS3',)]
[(u'ENABLE_FTS3_PARENTHESIS',)]
[(u'ENABLE_FTS4',)]
[(u'ENABLE_LOAD_EXTENSION',)]
[(u'ENABLE_MEMORY_MANAGEMENT',)]
[(u'ENABLE_MEMSYS5',)]
[(u'ENABLE_RTREE',)]
[(u'ENABLE_STAT4',)]
[(u'MAX_SCHEMA_RETRY=50',)]
[(u'SOUNDEX',)]
[(u'SYSTEM_MALLOC',)]
[(u'TEMP_STORE=2',)]
[(u'THREADSAFE=1',)]
[(None,)]
[(None,)]
[(None,)]
[(None,)]
[(None,)]
[sqlite] Need help on SQLite In-Memory Mode
Hi dear sqlite users, I am using SQLite In-Memory Database in my application. My application is written in C#. I am trying to create an In-Memory Database that can be opened by multiple connections as descripted in this link. What it is the right format of connection string in this case? I have tried SQLiteConnection connection = new SQLiteConnection(Data Source=file::memory:?cache=shared); SQLiteConnection connection = new SQLiteConnection(Data Source=file://:memory:;cache=shared); None of these worked. Thanks in advance! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite merge
hello Simon, thank you so much for your reply , i'm very interesting about close this connection under iOS because i work together with the guys that do this app. our problem is do this : APP working -- call backup function -close sqlite- download sqite - reopen sqlite - APP working best regards Paolo 2014-08-07 18:25 GMT+02:00 Simon Slavin slav...@bigfraud.org: On 7 Aug 2014, at 2:34pm, Paolo Combi paolo.co...@ipratico.it wrote: i have 1 sqlite file on my ipad and when i download this file i find some other version of this file . *.sqlite *.sqlite-shm *-sqlite-wal i see if i use a firefox plugin this file will be merge together and i want to do the same things with php . From your question, I conclude that you are trying to copy the database while at least one program has it open, which would be a very bad thing to do. When there are no connections to the database, only the original database file should exist. See section 2.2 and 2.3 of http://www.sqlite.org/tempfiles.html It is possible that your iPad application is not closing the database file when it quits, or when it is shifted into the background. If you are the programmer of this app please followup and I'm happy to discuss the correct way to do this under iOS in more detail. If you are not the programmer of this app you might find it useful to pass this message along to them. 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] sqlite merge
On 8 Aug 2014, at 8:11am, Paolo Combi paolo.co...@ipratico.it wrote: thank you so much for your reply , i'm very interesting about close this connection under iOS because i work together with the guys that do this app. our problem is do this : APP working -- call backup function -close sqlite- download sqite - reopen sqlite - APP working To close a connection to a SQLite database file you should Make sure all prepared statements for that connection have had sqlite3_finalize() run on them and returned SQLITE_OK . Then make sure your connection has had sqlite3_close_v2() (or perhaps just sqlite3_close()) run on it and returned SQLITE_OK . Don't just execute the functions, check to see the values they return. If they're not SQLITE_OK produce an error message and don't run the backup function. Once you have closed all connections to a database, SQLite itself will delete the .wal and .shm files. If those files still exist, there is something wrong. If it is simple, check to see whether the -wal file still exists, and if it does, produce an error message and don't run the backup function. The most frequent problem with this under iOS is that the database file was in a folder where the application does not have enough privileges to delete a file. If this happens, read the documentation for iOS working folders again. Once you have found the correct folder to keep the database file in this will work correctly without further problems. When you have finished copying the database file you can, of course, use SQLite to open it again. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite merge
On Fri, Aug 8, 2014 at 9:11 AM, Paolo Combi paolo.co...@ipratico.it wrote: APP working -- call backup function -close sqlite- download sqite - reopen sqlite - APP working Do you know about http://www.sqlite.org/backup.html? Just in case... Not quite your problem, but related a little. Hope this helps. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] After ANALYZE all has become slow
well,
after an intense session of debug, we have come to understand the why
of these decreases of performance.
In practice in two tables on 40 they missed two indexes on a total of 50.
Now adversity has wanted these two indexes to be used more or less by
a lot of interrogations.
Restored these the problem it is practically resolved.
sqlite and python never disappoint us.
It stays us the doubt however of how come such problem is amplified
after having performed ANALYZE.
p.s.
I arrange with Keith that is difficult to change versions on you linux
OS, we use debian 5 as server.
I believe even that the panic doesn't often, help to resolve the problems...
you excuse for the trouble
regars
beppe
On Fri, Aug 8, 2014 at 2:09 PM, Keith Medcalf kmedc...@dessus.com wrote:
The default installation on my VPS running the current release of CentOS is
pretty old as well. Unfortunately it is kind of difficult to upgrade the
version of SQLite (and/or Python) on some Linux distributions because there
are other system dependencies on the versions of Python. For example on
CentOS there are dependencies between Python version and the package manager
(yum in the case of CentOS) making such upgrades sometimes very problematic.
Linux X 3.15.4-x86_64 #1 SMP Mon Jul 7 08:42:36 EDT 2014 x86_64 x86_64
x86_64 GNU/Linux
# python
Python 2.6.6 (r266:84292, Jan 22 2014, 09:42:36)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2
Type help, copyright, credits or license for more information.
import sqlite3
cn = sqlite3.connect(':memory:')
sqlite3.version
'2.4.1'
sqlite3.sqlite_version
'3.6.20'
for i in range(20):
... cn.cursor().execute('select
sqlite_compileoption_get(?);',(i,)).fetchall()
...
Traceback (most recent call last):
File stdin, line 2, in module
sqlite3.OperationalError: no such function: sqlite_compileoption_get
Also, the version of the interface module (sqlite3.py / _sqlite3.so) between
python and sqlite3 (sqlite3.so) is quite old as well (compared to the current
versions of python and sqlite3 -- though the interface has not significantly
changed in how it implements most connection/cursor operations).
Is the command line shell of sqlite3 you are using in your testing the same
version of SQLite as the shared object being used by python?
# sqlite3
SQLite version 3.6.20
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite
If they are different, that might explain the different results between the
two depending on the versions. There have been many changes in sqlite since
version 3.6.20 which might affect performance of actually running a query,
depending on the version of the command shell in use.
If they are the same version then the time taken to execute a select at the
command line tool interface and retrieve the results should not be
significantly different than what you see using from python calls. cursor()
methods .execute(...) corresponds to sqlite3_prepare / sqlite3_bind (if
parameters are given); .fetchone() corresponds to a single sqlite3_step and
the sqlite3_value calls necessary to retrieve a row; and, .fetchall() is
simply .fetchone() in a loop until no more rows are available and accumulates
the retrieved row tuples as a list (all executed in C code, not in python
code, so it should be relatively the same efficiency as the sqlite3 shell
tool is).
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of Giuseppe Costanzi
Sent: Friday, 8 August, 2014 03:14
To: General Discussion of SQLite Database
Subject: Re: [sqlite] After ANALYZE all has become slow
using putty
Linux SRVXXX 2.6.26-2-686 #1 SMP Mon Aug 30 07:01:57 UTC 2010 i686
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
You have new mail.
Last login: Fri Aug 8 08:22:45 2014 from trantor.ia.lan
hal9000@SRVXXX :~$ clear
hal9000@SRVXXX :~$ python
Python 2.5.2 (r252:60911, Jan 24 2010, 14:53:14)
[GCC 4.3.2] on linux2
Type help, copyright, credits or license for more information.
import sqlite3
cn = sqlite3.connect(':memory:')
sqlite3.version
'2.3.2'
sqlite3.sqlite_version
'3.5.9'
cn.cursor().execute(SELECT sqlite_version();).fetchall()
[(u'3.5.9',)]
for i in range(20):
print cn.cursor().execute('SELECT
sqlite_compileoption_get(?);',(i,)).fetchall()
sqlite3.OperationalError: no such function: sqlite_compileoption_get
File stdin, line 1
sqlite3.OperationalError: no such function: sqlite_compileoption_get
On Fri, Aug 8, 2014 at 3:47 AM, Keith Medcalf kmedc...@dessus.com
wrote:
Giuseppe,
What version of the sqlite3 library is python using?
python
Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit
[sqlite] What am I doing wrong?
I am to list the film title and the leading actor for all of the films 'Julie
Andrews' played in. The following is structure of the tables used:
movie(id, title, yr, director, budget, gross);
actor(id, name);
casting(movieid, actorid, ord). The column name ord has a value of 1 if the
actor is in starring role.
My attempt at a solution, produces all the movie titles but the actors' names
are all for Julie Andrews:
titlename10Julie AndrewsDarling LiliJulie AndrewsDespicable MeJulie AndrewsDuet
for OneJulie AndrewsHawaiiJulie AndrewsLittle Miss MarkerJulie AndrewsMary
PoppinsJulie AndrewsRelative ValuesJulie AndrewsS.O.B.Julie AndrewsShrek the
ThirdJulie AndrewsStar!Julie AndrewsThe Americanization of EmilyJulie
AndrewsThe Pink Panther Strikes AgainJulie AndrewsThe Princess DiariesJulie
AndrewsThe Princess Diaries 2: Royal EngagementJulie AndrewsThe Sound of
MusicJulie AndrewsThe Tamarind SeedJulie AndrewsThoroughly Modern MillieJulie
AndrewsTooth FairyJulie AndrewsTorn CurtainJulie AndrewsVictor VictoriaJulie
Andrews
The SQL code for the above is given below:
SELECT distinct m1.title, a1.name FROM casting c1
JOIN movie m1 on m1.id=c1.movieid
JOIN actor a1 on a1.id=c1.actorid
WHERE (a1.name='Julie Andrews' and c1.ord=1) or ('Julie Andrews' IN (
SELECT a2.name FROM actor a2
WHERE c1.ord1 and a2.id=a1.id));
Why don't I get the name of the star Dudley Moore for 10, Steve Carrell for
Despicable Me, etc.? I should appreciate your help in finding the appropriate
solution. Thanks and be well.
Errol Emden
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong?
On 8-8-2014 20:35, Errol Emden wrote:
I am to list the film title and the leading actor for all of the films 'Julie
Andrews' played in. The following is structure of the tables used:
movie(id, title, yr, director, budget, gross);
actor(id, name);
casting(movieid, actorid, ord). The column name ord has a value of 1 if the
actor is in starring role.
My attempt at a solution, produces all the movie titles but the actors' names
are all for Julie Andrews:
titlename10Julie AndrewsDarling LiliJulie AndrewsDespicable MeJulie AndrewsDuet
for OneJulie AndrewsHawaiiJulie AndrewsLittle Miss MarkerJulie AndrewsMary
PoppinsJulie AndrewsRelative ValuesJulie AndrewsS.O.B.Julie AndrewsShrek the
ThirdJulie AndrewsStar!Julie AndrewsThe Americanization of EmilyJulie
AndrewsThe Pink Panther Strikes AgainJulie AndrewsThe Princess DiariesJulie
AndrewsThe Princess Diaries 2: Royal EngagementJulie AndrewsThe Sound of
MusicJulie AndrewsThe Tamarind SeedJulie AndrewsThoroughly Modern MillieJulie
AndrewsTooth FairyJulie AndrewsTorn CurtainJulie AndrewsVictor VictoriaJulie
Andrews
The SQL code for the above is given below:
SELECT distinct m1.title, a1.name FROM casting c1
JOIN movie m1 on m1.id=c1.movieid
JOIN actor a1 on a1.id=c1.actorid
WHERE (a1.name='Julie Andrews' and c1.ord=1) or ('Julie Andrews' IN (
SELECT a2.name FROM actor a2
WHERE c1.ord1 and a2.id=a1.id));
Why don't I get the name of the star Dudley Moore for 10, Steve Carrell for
Despicable Me, etc.? I should appreciate your help in finding the appropriate
solution. Thanks and be well.
Errol Emden
This should list all movies with 'Julie Andrews' in a role
(starring, or not):
SELECT distinct m1.title, a1.name FROM casting c1
JOIN movie m1 on m1.id=c1.movieid
JOIN actor a1 on a1.id=c1.actorid
WHERE a1.name='Julie Andrews'
Can you explain why you added the part that i erased from the query?
Because, basically adding
'she should be starring' (ord=1)
or
'she should have a role not starring'
(c1.ord1 and a2.id=a1.id)
will make things unnecessarily complicated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong?
On 8 Aug 2014, at 7:35pm, Errol Emden eem...@hotmail.com wrote: I am to list the film title and the leading actor for all of the films 'Julie Andrews' played in. The following is structure of the tables used: movie(id, title, yr, director, budget, gross); actor(id, name); casting(movieid, actorid, ord). The column name ord has a value of 1 if the actor is in starring role. All the lead roles of all stars in all movies SELECT leadActor.name, movie.title FROM casting JOIN actor AS leadActor ON leadActor.id = casting.actorid JOIN movie ON movie.id = casting.movieid WHERE casting.ord = 1 All the lead roles of all stars in all movies Julie Andrews has been in: SELECT leadActor.name, movie.title FROM casting JOIN actor AS leadActor ON leadActor.id = casting.actorid JOIN movie ON movie.id = casting.movieid JOIN actor AS selectedActor ON selectedActor.id = casting.actorid WHERE casting.ord = 1 AND selectedActor.name = 'Julie Andrews' If would be more efficient to find out Julie's actorid first, and use that for filtering instead of her name. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong?
On 8 Aug 2014, at 7:59pm, Simon Slavin slav...@bigfraud.org wrote: SELECT leadActor.name, movie.title FROM casting JOIN actor AS leadActor ON leadActor.id = casting.actorid JOIN movie ON movie.id = casting.movieid JOIN actor AS selectedActor ON selectedActor.id = casting.actorid WHERE casting.ord = 1 AND selectedActor.name = 'Julie Andrews' Apologies. This does not actually do what you want. Your main table should actually be movie for this one. JOIN once to find the leadActor, and JOIN again to make sure that Julie Andrews is in it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong?
On Aug 8, 2014, at 8:35 PM, Errol Emden eem...@hotmail.com wrote:
I am to list the film title and the leading actor for all of the films
'Julie Andrews' played in.
And another one, for diversity’s sake…
Assuming a slightly different data model:
with
AndrewsMovie
as
(
select movie_cast.movie_id
frommovie_cast
joinperson
on person.id = movie_cast.person_id
where person.name = 'Andrews, Julie (I)'
)
selectmovie.year,
movie.title,
person.name,
movie_cast.attribute
from movie_cast
join movie
onmovie.id = movie_cast.movie_id
join person
onperson.id = movie_cast.person_id
where movie_cast.attribute like '%1'
and exists
(
select 1
fromAndrewsMovie
where AndrewsMovie.movie_id = movie_cast.movie_id
)
order by movie.year,
movie.title;
Sample for 2010:
2010|20 to 1 (2005) {Our All Time Favourite Films (#9.11)}|Newton,
Bert|[Himself - Host] 1
2010|Breakfast (2000) {(2010-05-24)}|Stayt, Charlie|[Himself - Presenter] 1
2010|The Daily Show (1996) {Julie Andrews (#15.12)}|Stewart, Jon (I)|[Himself
- Host] 1
2010|The Late Late Show with Craig Ferguson (2005) {(#6.80)}|Ferguson, Craig
(I)|[Himself - Host] 1
2010|The Oprah Winfrey Show (1986) {(2010-10-28)}|Winfrey, Oprah|[Herself -
Host] 1
2010|Despicable Me (2010)|Carell, Steve|(voice) [Gru] 1
2010|Shrek Forever After (2010)|Myers, Mike (I)|(voice) [Shrek] 1
2010|Tooth Fairy (2010/I)|Johnson, Dwayne (I)|[Derek] 1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In-memory DB slower than disk-based?
Yes Stephen, your description of the locking when writing is pretty much exactly what I'm sure is happening. A couple clarifications though: I have only 20 or 30 threads on the server side. It's the client that has 100 or more threads. And I'm opening all connections to the DB when the server first starts up (one per server thread), not per connection. My thinking was that, in the exact scenario you described, an in-memory database should in theory be faster. Because a thread that locks due to writing should finish its write and be 'done' much faster. In theory. I guess not in practice. -Vern -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephen Chrzanowski Sent: Thursday, August 07, 2014 5:30 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] In-memory DB slower than disk-based? It smells like that you're running into general timing issues, especially if you're consistent with how long it takes to put info into the database. If we were to take your scenario of 100 threads of writing to memory, bashing the hell out of the database for writes, the first thread is going to lock the database, the other 99 threads are going to get put on hold for a period of time. The first thread is going to release, then *when* the waiting threads start making write requests, one is going to get the lock, the others are going to get put on hold again. Since hardware is fast enough, you may find yourself in a situation where all 100 threads are actually counting down and waiting to write to the database. If we start writing to the platter/ssd, you might actually see a shorter time out since the delays between the waiting periods become spread out. I'm not sure if there is anything that broadcasts to other threads that writing can resume. Transactions may also cause problems as well for timing since it can take a long time to write data out. The other thing I just thought about is that if the database is locked while trying to open a new connection, you may run into a 1-2 second delay before you can start doing ANYTHING with the database since it is technically in a read-only state. Are you opening/closing connections to the database at every iteration in every thread? (Meaning you start the testing process, threads are launched to open a connection, you do your work, you close the connection, destroy the thread, then repeat as needed) On Thu, Aug 7, 2014 at 2:53 PM, Jensen, Vern vern.jen...@cshs.org wrote: Real hardware. 27” iMac (native BootCamp running Win 7) with 3.4 GHz Intel Core i7 quad-core processor. And my mistake: 24 GB of RAM, not 20. Anytime I’ve looked at the app while it’s running, it’ll take 5 to 15 MB at most, depending on how many threads I give it. -Vern Vern Jensen Software Engineer Artificial Intelligence in Medicine (AIM) Program jens...@cshs.orgmailto:jens...@cshs.org Office: 310-423-8148 :: Fax: 310-423-0173 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users IMPORTANT WARNING: This message is intended for the use of the person or entity to which it is addressed and may contain information that is privileged and confidential, the disclosure of which is governed by applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this information is STRICTLY PROHIBITED. If you have received this message in error, please notify us immediately by calling (310) 423-6428 and destroy the related message. Thank You for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.8.6 coming soon
hi Jose, The SQL request with a in() that is improved by 5x in the latest beta is of form : select * from a , b where a.field1 in (b.column1, b.column2, b.column3, b.column4, 'fixed value') ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong?
On 8 Aug 2014, at 7:35pm, Errol Emden eem...@hotmail.com wrote: I am to list the film title and the leading actor for all of the films 'Julie Andrews' played in. You know, one day we should actually solve someone's homework questions. Just to mess with their head. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong?
I am to list the film title and the leading actor for all of the films 'Julie Andrews' played in. The following is structure of the tables used: movie(id, title, yr, director, budget, gross); actor(id, name); casting(movieid, actorid, ord). The column name ord has a value of 1 if the actor is in starring role. Translate the English in parts: all the films Julie Andrews olayed in - select movie.id from movie, casting, actor where movie.id = casting.movieid and casting.actorid = actor.id and actor.name = 'Julie Andrews' then wrap the part list the film title and the leading actor around that list of films: select movie.name, actor.name from movie, casting, actor where movie.id = casting.movieid and casting.actorid = actor.id and casting.org = 1 and movie.id in (select movie.id from movie, casting, actor where movie.id = casting.movieid and casting.actorid = actor.id and actor.name = 'Julie Andrews'); Being able to translate problem statements from well formed human language into well formed computer language statement(s) The Solution is what separates programmers from coders. The problem statement does not require any ordering of the results, so they are in visitation order (which is not random, but may appear to be random to coders). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong?
On Friday, 8 August, 2014 13:46, Petite Abeille petite.abei...@gmail.com said:
On Aug 8, 2014, at 8:35 PM, Errol Emden eem...@hotmail.com wrote:
I am to list the film title and the leading actor for all of the films
'Julie Andrews' played in.
And another one, for diversity's sake...
Assuming a slightly different data model:
with
AndrewsMovie
as
(
select movie_cast.movie_id
frommovie_cast
joinperson
on person.id = movie_cast.person_id
where person.name = 'Andrews, Julie (I)'
)
selectmovie.year,
movie.title,
person.name,
movie_cast.attribute
from movie_cast
join movie
onmovie.id = movie_cast.movie_id
join person
onperson.id = movie_cast.person_id
where movie_cast.attribute like '%1'
and exists
(
select 1
fromAndrewsMovie
where AndrewsMovie.movie_id = movie_cast.movie_id
)
order by movie.year,
movie.title;
Sample for 2010:
2010|20 to 1 (2005) {Our All Time Favourite Films (#9.11)}|Newton,
Bert|[Himself - Host] 1
2010|Breakfast (2000) {(2010-05-24)}|Stayt, Charlie|[Himself -
Presenter] 1
2010|The Daily Show (1996) {Julie Andrews (#15.12)}|Stewart, Jon
(I)|[Himself - Host] 1
2010|The Late Late Show with Craig Ferguson (2005) {(#6.80)}|Ferguson,
Craig (I)|[Himself - Host] 1
2010|The Oprah Winfrey Show (1986) {(2010-10-28)}|Winfrey,
Oprah|[Herself - Host] 1
2010|Despicable Me (2010)|Carell, Steve|(voice) [Gru] 1
2010|Shrek Forever After (2010)|Myers, Mike (I)|(voice) [Shrek] 1
2010|Tooth Fairy (2010/I)|Johnson, Dwayne (I)|[Derek] 1
Or perhaps even more complicated from the TMS database:
bindings - :lineup=0008100:X; :duration=24
select date(schedule.time, 'unixepoch', 'localtime') as showdate,
time(schedule.time, 'unixepoch', 'localtime') as showtime,
map.channel as channel,
callsign,
coalesce(runtime / 60, duration / 60) as runtime,
case hdtv when 1 then 'HD' else ' ' end as ishd,
case new when 1 then 'NEW' else ' ' end as isnew,
showtype,
case when subtitle != '' then title || ': ' || subtitle else title end
as title,
description,
(select group_concat(data, '; ')
from (select rolename || ': ' || group_concat(givenname || ' ' ||
surname, ', ') as data
from crewlink natural join crew
natural join role
where crewlink.program = program.program
group by rolename)
) as cast,
(select group_concat(advisoryname, ', ')
from advlink, advisory
where advlink.program = program.program
and advlink.advisory = advisory.advisory
group by advlink.program) as advisories,
(select group_concat(class, ', ')
from genrlink, genre
where genrlink.program = program.program
and genrlink.genre = genre.genre
group by genrlink.program) as genres,
originalAirDate,
year,
starRating,
syndicatedEpisodeNumber
from schedule, map, mychannels, station, program
where map.lineup = :lineup
and mychannels.lineup = map.lineup
and mychannels.channel = map.channel
and station.station = map.station
and schedule.station = map.station
and schedule.time = strftime('%s', 'now') - 86400
and schedule.time = strftime('%s', 'now') + (3600 * :duration)
and schedule.endtime strftime('%s', 'now')
and program.program = schedule.program
and map.validfrom = schedule.time
and map.validto = schedule.endtime
and showtype != 'Paid Programming'
and ( showtype not in ('Series', 'Sports', 'Paid programming')
or series in mySeries
or (substr(schedule.program, 1, 2) = 'EP' and new = 1)
or series in (select distinct series
from schedule join program using (program)
where new
and series is not null
and program like 'EP%')
)
and map.channel between 200 and 314
order by 1,2,9,3;
to produce (after formatting):
--
15:45 280 MCHD 1:34 HD Movie Hyde Park on Hudson
(2012); **+ Historical drama; Adult Situations; In
June 1939, President
Franklin Delano Roosevelt (Bill Murray) receives
Britain's monarchs
(Samuel West) at his home in upstate New York for an
unforgettable
weekend. Actor: Laura Linney, Bill Murray, Elizabeth
Wilson, Martin
[sqlite] SQLite 3.8.6 beta
A bit thanks to E.Pasma who found a performance regression in 3.8.6, which has now been fixed (with added test cases to prevent a recurrence). Fresh snapshots are now on the download page (http://www.sqlite.org/download.html). Please continue to test the latest snapshots and report any issues you find. Probably we will begin final release testing next week. Once the checklist (http://www.sqlite.org/checklists/3080600/index) goes all-green we will cut the release. A summary of changes for 3.8.6 is here: http://www.sqlite.org/draft/releaselog/3_8_6.html -- 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] What am I doing wrong?
The SQL script you wrote actually provides the same information as mine - it
lists all movies that Julie Andrews is in but it does NOT provide who is the
leading actor in each movie, as all names selected is that of Julie Andrews.
Date: Fri, 8 Aug 2014 20:55:55 +0200
From: luu...@gmail.com
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What am I doing wrong?
On 8-8-2014 20:35, Errol Emden wrote:
I am to list the film title and the leading actor for all of the films
'Julie Andrews' played in. The following is structure of the tables used:
movie(id, title, yr, director, budget, gross);
actor(id, name);
casting(movieid, actorid, ord). The column name ord has a value of 1 if the
actor is in starring role.
My attempt at a solution, produces all the movie titles but the actors'
names are all for Julie Andrews:
titlename10Julie AndrewsDarling LiliJulie AndrewsDespicable MeJulie
AndrewsDuet for OneJulie AndrewsHawaiiJulie AndrewsLittle Miss MarkerJulie
AndrewsMary PoppinsJulie AndrewsRelative ValuesJulie AndrewsS.O.B.Julie
AndrewsShrek the ThirdJulie AndrewsStar!Julie AndrewsThe Americanization of
EmilyJulie AndrewsThe Pink Panther Strikes AgainJulie AndrewsThe Princess
DiariesJulie AndrewsThe Princess Diaries 2: Royal EngagementJulie
AndrewsThe Sound of MusicJulie AndrewsThe Tamarind SeedJulie
AndrewsThoroughly Modern MillieJulie AndrewsTooth FairyJulie AndrewsTorn
CurtainJulie AndrewsVictor VictoriaJulie Andrews
The SQL code for the above is given below:
SELECT distinct m1.title, a1.name FROM casting c1
JOIN movie m1 on m1.id=c1.movieid
JOIN actor a1 on a1.id=c1.actorid
WHERE (a1.name='Julie Andrews' and c1.ord=1) or ('Julie Andrews' IN (
SELECT a2.name FROM actor a2
WHERE c1.ord1 and a2.id=a1.id));
Why don't I get the name of the star Dudley Moore for 10, Steve Carrell for
Despicable Me, etc.? I should appreciate your help in finding the
appropriate solution. Thanks and be well.
Errol Emden
This should list all movies with 'Julie Andrews' in a role
(starring, or not):
SELECT distinct m1.title, a1.name FROM casting c1
JOIN movie m1 on m1.id=c1.movieid
JOIN actor a1 on a1.id=c1.actorid
WHERE a1.name='Julie Andrews'
Can you explain why you added the part that i erased from the query?
Because, basically adding
'she should be starring' (ord=1)
or
'she should have a role not starring'
(c1.ord1 and a2.id=a1.id)
will make things unnecessarily complicated.
___
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] What am I doing wrong?
Keith...thanks vmuch for your insightful and enlightening approach of translating 'in parts'. I am truly indebted to you for your mentoring approach...be well. Date: Fri, 8 Aug 2014 15:03:13 -0600 From: kmedc...@dessus.com To: sqlite-users@sqlite.org Subject: Re: [sqlite] What am I doing wrong? I am to list the film title and the leading actor for all of the films 'Julie Andrews' played in. The following is structure of the tables used: movie(id, title, yr, director, budget, gross); actor(id, name); casting(movieid, actorid, ord). The column name ord has a value of 1 if the actor is in starring role. Translate the English in parts: all the films Julie Andrews olayed in - select movie.id from movie, casting, actor where movie.id = casting.movieid and casting.actorid = actor.id and actor.name = 'Julie Andrews' then wrap the part list the film title and the leading actor around that list of films: select movie.name, actor.name from movie, casting, actor where movie.id = casting.movieid and casting.actorid = actor.id and casting.org = 1 and movie.id in (select movie.id from movie, casting, actor where movie.id = casting.movieid and casting.actorid = actor.id and actor.name = 'Julie Andrews'); Being able to translate problem statements from well formed human language into well formed computer language statement(s) The Solution is what separates programmers from coders. The problem statement does not require any ordering of the results, so they are in visitation order (which is not random, but may appear to be random to coders). ___ 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] SQLite version 3.8.6 coming soon
On Wed, 6 Aug 2014 20:56:24 -0400 Richard Hipp d...@sqlite.org wrote: http://www.perfectyourenglish.com/vocabulary/backward-backwards.htm Two countries divided by a common tongue. Except, I speak Southern English, not British English. And I can promise you that we southerners prefer to add the s to the end of backwards. I'm not sure PerfectYourEnglish necessarily has all their facts straight. Oh, I'm sure you're right on both counts. You know what form you prefer, and jrandomsite.com shouldn't be assumed to be an authority. (When it comes to English usage in particular, there's a lot more pretended and presumptive authority on the web than actual.) I was just pointing out that the with-or-without-s is question (at least) regional, and extends to words other than backwards. You may recognize the lines Kiss today goodbye And point me toward tomorrow If ever you happen to be practicing that in a chorus, don't be too surprised if it takes more than a few tries before everyone understands there's no s (as written) in second line. You might could spend the better part of an hour on it. Don't ask me how I know. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong?
Leaving out the extra fields the movie table, enforcing referential integrity,
and using same column names to hold the same data, and declaring the right
indexes, you can get a much more efficient solution, both in the expression and
the execution:
These optimizations are only necessary *after* you have solved the problem, and
then really only if performance is inadequate. Enforcement of referential
integrity and ensuring good indexes should always be done, of course.
CREATE TABLE movie
(
movieid integer primary key,
titletext not null unique collate nocase
);
CREATE TABLE actor
(
actorid integer primary key,
name text not null unique collate nocase
);
CREATE TABLE casting
(
movieid integer not null references movie,
ord integer not null,
actorid integer not null references actor,
primary key(movieid, ord, actorid),
unique (actorid, movieid, ord)
) WITHOUT ROWID;
select title, name
from movie natural join casting natural join actor
where ord = 1
and movieid in (select movieid
from casting natural join actor
where name = 'Julie Andrews');
** note that the subquery for the IN list does not need to contain the movie
table at all since the movieid is in the casting table **
sqlite .explain
sqlite explain query plan
... select title, name
... from movie natural join casting natural join actor
... where ord = 1
...and movieid in (select movieid
... from casting natural join actor
... where name = 'Julie Andrews');
SELECT item[0] = {0:1}
item[1] = {2:1}
FROM {0,*} = movie
{1,*} = casting
{2,*} = actor
WHERE AND(AND(AND(EQ({1:1},1),IN({0:-1},SELECT {3:0}
FROM {3,*} = casting
{4,*} = actor
WHERE AND(EQ({4:1},'Julie
Andrews'),EQ({3:2},{4:-1}))
END)),EQ({0:-1},{1:0})),EQ({1:2},{2:-1}))
END
sele order from deta
-
0 0 0 SEARCH TABLE movie USING INTEGER PRIMARY KEY
(rowid=?)
0 0 0 EXECUTE LIST SUBQUERY 1
1 0 1 SEARCH TABLE actor USING COVERING INDEX
sqlite_autoindex_actor_1 (name=?)
1 1 0 SEARCH TABLE casting USING COVERING INDEX
sqlite_autoindex_casting_2 (actorid=?)
0 1 1 SEARCH TABLE casting USING PRIMARY KEY (movieid=?
AND ord=?)
0 2 2 SEARCH TABLE actor USING INTEGER PRIMARY KEY
(rowid=?)
Even more efficiently you could flatten the query thus:
select m.title, a.name
from movie as m, casting as c, actor as a, casting as c2, actor as a2
where a2.name = 'Julie Andrews'
and c2.actorid = a2.actorid
and m.movieid = c2.movieid
and c.movieid = c2.movieid
and c.ord = 1
and a.actorid = c.actorid;
which finds the appropriate actor row, uses that to retrieve all the casting
rows which in turn leads to all the movie rows. This row set is then joined
back through (another instance of the same) casting table with a specific ord
given to get the lead actor name (another instance of the same actor table).
The actor and casting table are used twice, so they have to be referenced twice
with different aliases.
sqlite explain query plan
... select m.title, a.name
... from movie as m, casting as c, actor as a, casting as c2, actor as a2
... where a2.name = 'Julie Andrews'
...and c2.actorid = a2.actorid
...and m.movieid = c2.movieid
...and c.movieid = m.movieid
...and c.ord = 1
...and a.actorid = c.actorid;
SELECT item[0] = {0:1}
item[1] = {2:1}
FROM {0,*} = movie (AS m)
{1,*} = casting (AS c)
{2,*} = actor (AS a)
{3,*} = casting (AS c2)
{4,*} = actor (AS a2)
WHERE AND(AND(AND(AND(AND(EQ({4:1},'Julie
Andrews'),EQ({3:2},{4:-1})),EQ({0:-1},{3:0})),EQ({1:0},{3:0})),EQ({1:1},1)),EQ({2:-1},{1:2}))
END
sele order from deta
-
0 0 4 SEARCH TABLE actor AS a2 USING COVERING INDEX
sqlite_autoindex_actor_1 (name=?)
0 1 3 SEARCH TABLE casting AS c2 USING COVERING INDEX
sqlite_autoindex_casting_2 (actorid=?)
0 2 0 SEARCH TABLE movie AS m USING INTEGER PRIMARY KEY
(rowid=?)
0 3 1 SEARCH TABLE casting AS c USING PRIMARY KEY
(movieid=? AND ord=?)
0 4 2 SEARCH TABLE actor AS a USING INTEGER PRIMARY KEY
(rowid=?)
sqlite
Unless there are many millions of rows in the tables both query forms are
probably equally efficient. This last form of the query is navigationally
constrained and specifically takes advantage of the nested loops method that
SQLite uses to actually perform the query and is somewhat implementation
dependent. Some SQL optimizers may arrive at the same end
Re: [sqlite] SQLite version 3.8.6 coming soon
big stone wrote... hi Jose, The SQL request with a in() that is improved by 5x in the latest beta is of form : select * from a , b where a.field1 in (b.column1, b.column2, b.column3, b.column4, 'fixed value') Hi Big Stone, This is what I am running with an ATTACHed DB as client... BEGIN; INSERT OR REPLACE INTO LSOpenProjects SELECT * FROM client.LSOpenProjects as A WHERE id IN ( SELECT id from LSOpenProjects WHERE login != 'user1' AND Date != A.Date ); END; It has not shown any speed improvement, but maybe the slowdown is caused by the INSERT. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need help on SQLite In-Memory Mode
Try this: SQLiteConnection connection = new SQLiteConnection( FullUri=file::memory:?cache=shared;); -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong?
Keith...as a neophyte, I am totally blown away by your skill level in SQL and the ease with which you explain its underlying operations. I am extremly impressed by the 'flattening of the query' and its operation...it worked perfectly!!. I am so happy that this forum has persons of your caliber who totally understands SQL and can provide efficient solutions to problems forum members, like myself, may have. Again, thanks and be well. Subject: RE: [sqlite] What am I doing wrong? Date: Fri, 8 Aug 2014 17:40:28 -0600 From: kmedc...@dessus.com To: eem...@hotmail.com CC: sqlite-users@sqlite.org Leaving out the extra fields the movie table, enforcing referential integrity, and using same column names to hold the same data, and declaring the right indexes, you can get a much more efficient solution, both in the expression and the execution: These optimizations are only necessary *after* you have solved the problem, and then really only if performance is inadequate. Enforcement of referential integrity and ensuring good indexes should always be done, of course. _ 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
