On Tue, 28 Jul 2015 at 20:55, rotaiv wrote:
> I upgraded to the latest version and it decreased to 16 seconds.
> With indexes, 5 seconds. :-D
Is index creation time included in those 5 seconds?
If your database gets created from scratch and only used once every
time you do the syncing (as it
On 2015-07-28 09:25 PM, rotaiv wrote:
> Not quite. The older version brought it down to 8 seconds (as compared to
> 5 seconds) but still a whole lot better than 40+ minutes.
>
> It is very interesting to see an index can make such a remarkable
> difference in that particular scenario. This is
On 28 Jul 2015, at 8:25pm, rotaiv wrote:
> From what I read, it was recommended to create indexes for any field used
> in the SELECT statement. Is that correct? Give my original query:
>
> SELECT home.fpath
> FROM home
> LEFT JOIN work ON work.fpath = home.fpath
> WHERE work.fpath IS NULL
>
On 28 Jul 2015, at 7:55pm, rotaiv wrote:
> That is the machine. Originally. it was taking 40+ minutes. I upgraded to
> the latest version and it decreased to 16 seconds. With indexes, 5
> seconds. :-D
Ah, okay I get it. In that case the index would probably have given you a time
of 5
On 28 Jul 2015, at 7:43pm, rotaiv wrote:
> I create indexes and my original query time dropped from 16 seconds to this:
>
> real 0m5.928s
> user 0m5.361s
> sys 0m0.565s
Is it worth checking on the machine you originally reported 40+ minutes for ?
That one should be considerably faster.
Not quite. The older version brought it down to 8 seconds (as compared to
5 seconds) but still a whole lot better than 40+ minutes.
It is very interesting to see an index can make such a remarkable
difference in that particular scenario. This is my first time creating
indexes but I will
That is the machine. Originally. it was taking 40+ minutes. I upgraded to
the latest version and it decreased to 16 seconds. With indexes, 5
seconds. :-D
On Tue, Jul 28, 2015 at 2:53 PM, Simon Slavin wrote:
>
> On 28 Jul 2015, at 7:43pm, rotaiv wrote:
>
> > I create indexes and my original
I create indexes and my original query time dropped from 16 seconds to this:
real 0m5.928s
user 0m5.361s
sys 0m0.565s
However, when I use the query you suggested, it was a little slower:
real 0m9.827s
user 0m8.952s
sys 0m0.873s
On Mon, Jul 27, 2015 at 5:38 PM, Keith Medcalf wrote:
>
On 27 Jul 2015, at 7:32pm, rotaiv wrote:
> Originally, I copied the database file from my PC to my Linux server. I
> thought it might be a carriage return or line feed issue so I recreated the
> database from scratch on my Linux server. Didn't change anything.
SQLite is completely platform
On 2015-07-27 05:48 PM, Marc L. Allen wrote:
> When would that specific LEFT JOIN ever do anything except return NULLs for
> the right table? It only accepts rows from work where fpath is null, and
> only joins those rows where fpath = home.fpath. Since fpath must be null,
> home.fpath
On 2015-07-27 04:29 PM, rotaiv wrote:
>> ?WOW! That is an amazing difference. Makes me glad that I'm OCD about
>> staying relatively current. And please ignore my previous post. I somehow
>> managed to not register that you would doing a LEFT join, an equi-join.?
>>
> I am also OCD about
On Mon, 27 Jul 2015 at 17:48, Marc L. Allen wrote:
> When would that specific LEFT JOIN ever do anything except return
> NULLs for the right table? It only accepts rows from work where
> fpath is null, and only joins those rows where fpath = home.fpath.
> Since fpath must be null, home.fpath
On Mon, 27 Jul 2015 at 17:34, Marc L. Allen wrote:
> Sorry.. confused does sqlite allow comparison between NULLs?
No.
> LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL
>
> Should never match anything... it checks to see if work.fpath IS
> NULL and if the same
Or even better:
select fpath
from home
where not exists (select 1 from work where work.fpath=home.fpath and
work.ftype=home.ftype)
and ftype = 'f'
with a unique index on home (ftype, fpath) and a unique index on work (ftype,
fpath) of course.
> -Original Message-
> From:
On Mon, 27 Jul 2015 at 16:24, rotaiv wrote:
> I am more than open to a more logical and/or more efficient query if
> anyone has suggestions.
One alternative was posted by Igor, but it might end up as the same
query plan after the optimizer has done its thing (EXPLAIN will tell
you).
Adding
On 27 Jul 2015, at 3:04pm, Igor Tandetnik wrote:
> That's a common technique with LEFT JOIN
Thanks, Igor.
On 27 Jul 2015, at 1:44pm, rotaiv wrote:
> SELECT home.fpath
> FROM home
> LEFT JOIN work ON work.fpath = home.fpath
> WHERE work.fpath IS NULL
> AND home.ftype = 'f?;
Can you verify
On 27 Jul 2015 at 13:44, rotaiv wrote:
> Where is the resource bottleneck that causes a simple query to never
> complete after 40 minutes
>
> I am using sqlite to synchronize files on my home PC and my work PC. I
> created a file listing of each computer and imported into separate tables.
And
> > That's a common technique with LEFT JOIN
>
> Thanks, Igor.
>
A bit MySQL specific, but shows some options
http://explainextended.com/2010/05/27/left-join-is-null-vs-not-in-vs-not-exists-nullable-columns/
Regards
Andy Ling
On 27 Jul 2015, at 1:44pm, rotaiv wrote:
> SELECT home.fpath
> FROM home
> LEFT JOIN work ON work.fpath = home.fpath
> WHERE work.fpath IS NULL
> AND home.ftype = 'f?;
Are you sure you got this right ? It uses work.fpath twice:
> LEFT JOIN work ON work.fpath = home.fpath
> WHERE work.fpath
> It's not the older SQLite taking so much longer, it's simply that the file
> created with a newer SQLite probably contains a SCHEMA or objects not
> readily understood or able to be optimized by the older engine. if the file
> was made with the old engine it would perform perfectly well in both
On Mon, Jul 27, 2015 at 1:00 PM, Simon Slavin wrote:
>
> On 27 Jul 2015, at 7:32pm, rotaiv wrote:
>
> > Originally, I copied the database file from my PC to my Linux server. I
> > thought it might be a carriage return or line feed issue so I recreated
> the
> > database from scratch on my
Oh, jeeze. Wow.
And I know all that... I cleverly managed to ignore the "WHERE" and think it
was an AND because it was one the same line.
Duh.
Thanks.
-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
When would that specific LEFT JOIN ever do anything except return NULLs for the
right table? It only accepts rows from work where fpath is null, and only
joins those rows where fpath = home.fpath. Since fpath must be null,
home.fpath should never be equal.
-Original Message-
From:
Sorry.. confused does sqlite allow comparison between NULLs?
I mean...
LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL
Should never match anything... it checks to see if work.fpath IS NULL and if
the same work.fpath is equal to something.
I get the weird feeling that
> ?WOW! That is an amazing difference. Makes me glad that I'm OCD about
> staying relatively current. And please ignore my previous post. I somehow
> managed to not register that you would doing a LEFT join, an equi-join.?
>
I am also OCD about updates. I installed Sqlite using yum right before
> Also, I agree with Simon that the query itself, and it's use of NULL, looks
> weird. And I don't see where it could find any matches because the "ON
> work.fpath = home.fpath" should never match if either fpath is NULL.
>
>
I confess, I understand basic SQL but it is not one of my strengths at
Problem solved. I updated the version on the server and it completes in 16
seconds.
To answer your question, I am using the find command to created a delimited
text file. I copy that file to my home PC then importing that file into a
sqlite database table. I use the same process to create a
Thank for reminding me to check the obvious. The server was using version
3.6.20. I manually upgraded to version 3.8.10.2 and the query completed in
16 seconds. Still not as fast as my home PC but given the age of the
hardware, this is understandable. I will take 16 seconds as opposed to 40+
That is correct - I am looking for records that are empty. I tried this
version of the SQL query as well and during my testing, the first example
seemed to be more efficient.
Also, I have version 3.8.10.2 on my home PC and version 3.6.20 on my server
at work. I am going to update the server
On 7/27/2015 9:58 AM, Simon Slavin wrote:
> So you're checking both to see that [work.fpath = home.fpath ] and to see
> that [work.fpath IS NULL]. This looks weird to me.
That's a common technique with LEFT JOIN - it's selecting home records
that lack a corresponding work record. In other
On 7/27/15, rotaiv wrote:
>
> My question is ... What is about my machine at home
> that allows Sqlite to query 2 million files so quickly?
>
Probably a newer version of SQLite. You didn't tell us the version
numbers for SQLite on the two machines.
--
D. Richard Hipp
drh at sqlite.org
On Mon, Jul 27, 2015 at 9:21 AM, rotaiv wrote:
> Problem solved. I updated the version on the server and it completes in 16
> seconds.
>
?WOW! That is an amazing difference. Makes me glad that I'm OCD about
staying relatively current. And please ignore my previous post. I somehow
managed to
On Mon, Jul 27, 2015 at 8:42 AM, Richard Hipp wrote:
> On 7/27/15, rotaiv wrote:
> >
> > My question is ... What is about my machine at home
> > that allows Sqlite to query 2 million files so quickly?
> >
>
?I missed the initial post, somehow.?
?Could there be an index on something on the
Where is the resource bottleneck that causes a simple query to never
complete after 40 minutes
I am using sqlite to synchronize files on my home PC and my work PC. I
created a file listing of each computer and imported into separate tables.
Then I used the following query to locate missing
34 matches
Mail list logo
