Re: [sqlite] Bug in CREATE INDEX

[James K. Lowden]

On Mon, 8 Aug 2016 10:48:58 -0700
"Kevin O'Gorman"  wrote:

> Very cool.  But 4?  I will be running this on machines with 8 and 16
> cores.  Does going beyond 4 not help much?

Four doesn't seem like a bad starting point.  

I don't have any information specific to SQLite, but in general adding
cores to a sort helps only until I/O bandwidth is saturated (or RAM is
exhausted).  

And there's always the possibility the system may have other things to
do besides sorting.  

--jkl

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

Re: [sqlite] C API - Parameterized Atomic Transactions

[James K. Lowden]

On Tue, 9 Aug 2016 17:09:39 -0300
Paulo Roberto  wrote:

> I would like something like this:
> 
> "BEGIN EXCLUSIVE TRANSACTION;"
>  "SELECT counter FROM mytable WHERE counterid = ?;"
>  "UPDATE mytable SET counter=? WHERE counterid = ?;"
>   "COMMIT TRANSACTION;"

begin transaction;

UPDATE mytable SET counter = (
select 1 + max(counter)
from mytable where counterid = ? )
WHERE counterid = ?;

select counter - 1 as counter 
from mytable where counterid = ?;

commit transaction;

Standard SQL.  Doesn't rely on BEGIN EXCLUSIVE.  Should be just as
fast.  

--jkl

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

Re: [sqlite] 64-bit SQLite3.exe

[Michael Falconer]

Yes Richard,

in my experience your quoted time benefits are very much on target. I have
rewritten Excel/Access (VBA) which I originally developed (and was quite
proud of). Re-writes using C/C++ and SQLite indeed delivered the
performance gains you experienced and relieved me of the pain associated
with VBA development. A sort of win-win, and if you throw in moving away
from Windoze to something sensible you can get even more benefit. But that
is another tale...and sadly not always under our control.


On 19 August 2016 at 00:12, Rousselot, Richard A <
richard.a.rousse...@centurylink.com> wrote:

> Yes, it is much faster.  The process was done in Excel/Access before and
> took ages.  I have had processes go from 8 hours before to 30 min now using
> SQLite.
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Michael Gratton
> Sent: Wednesday, August 17, 2016 8:05 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] 64-bit SQLite3.exe
>
>
> Richard,
>
> On Thu, Aug 18, 2016 at 2:23 AM, Rousselot, Richard A <
> richard.a.rousse...@centurylink.com> wrote:
> > I was only interested in doing these calculations quickly; that is my
> > real-world.  The SQL scripts I am using were built with multiple steps
> > with intermediate temp tables that are used to create a final result
> > table.
> >
> > I understand my needs are not generally how others use SQLite.  I am
> > using it as a platform for fast calculations that happens to store
> > results in a way that can be quarried.  Every time, my process begins
> > all prior results are deleted, only the input tables stay the same.
>
>
> Out of curiosity, are you using these in an end-user application, i.e.
> as an interactive response to user actions? I would be curious to know if
> building intermediate temporary tables is fast enough for returning results
> for such uses.
>
> Thanks,
> //Mike
>
> --
> ⊨ Michael Gratton, Percept Wrangler.
> ⚙ 
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> This communication is the property of CenturyLink and may contain
> confidential or privileged information. Unauthorized use of this
> communication is strictly prohibited and may be unlawful. If you have
> received this communication in error, please immediately notify the sender
> by reply e-mail and destroy all copies of the communication and any
> attachments.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] SpeedTest1 Comparison of 32 vs 64 bit on Windows 10 13483.15

[Michael Falconer]

Nice Keith,

and very topical as well as being informative. Note a couple of things that
got my curiosity chip activating:

*subquery in result set* test produces interesting outcome with the 64 bit
version bucking the trend. Any ideas there?
Also it would appear *select* clauses demonstrate an above average gain
from 64 bit. Lack of disk activity sprung to my mind but interested to hear
any thoughts there.

excellent information and thanks for sharing it.


On 18 August 2016 at 14:38, Keith Medcalf  wrote:

>
> Same code, same compile options, same compiler version
> options -s -O3 -pipe -march=native -mtune=native -falign-functions=16
> -falign-loops=16 -flto
>
> 32-bit GCC 4.9.3
>
> >speedtest1 --size 1000
> -- Speedtest1 for SQLite 3.15.0 2016-08-17 11:14:39
> a861713cc6a3868a1c89240e8340bc
>  100 - 50 INSERTs into table with no index.
> 0.781s
>  110 - 50 ordered INSERTS with one index/PK
> 1.266s
>  120 - 50 unordered INSERTS with one index/PK..
> 1.672s
>  130 - 25 SELECTS, numeric BETWEEN, unindexed..
> 1.281s
>  140 - 10 SELECTS, LIKE, unindexed.
> 3.031s
>  142 - 10 SELECTS w/ORDER BY, unindexed
> 3.032s
>  145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..
> 3.063s
>  150 - CREATE INDEX five times.
> 2.641s
>  160 - 10 SELECTS, numeric BETWEEN, indexed
> 8.814s
>  161 - 10 SELECTS, numeric BETWEEN, PK.
> 9.282s
>  170 - 10 SELECTS, text BETWEEN, indexed...
> 3.241s
>  180 - 50 INSERTS with three indexes...
> 2.781s
>  190 - DELETE and REFILL one table.
> 3.111s
>  200 - VACUUM..
> 2.282s
>  210 - ALTER TABLE ADD COLUMN, and query...
> 0.094s
>  230 - 10 UPDATES, numeric BETWEEN, indexed
> 8.969s
>  240 - 50 UPDATES of individual rows...
> 1.641s
>  250 - One big UPDATE of the whole 50-row table
> 0.453s
>  260 - Query added column after filling
> 0.078s
>  270 - 10 DELETEs, numeric BETWEEN, indexed
> 2.235s
>  280 - 50 DELETEs of individual rows...
> 2.078s
>  290 - Refill two 50-row tables using REPLACE..
> 5.110s
>  300 - Refill a 50-row table using (b&1)==(a&1)
> 2.798s
>  310 - 10 four-ways joins..
> 5.320s
>  320 - subquery in result set..
>  22.936s
>  980 - PRAGMA integrity_check..
> 4.969s
>  990 - ANALYZE.
> 1.657s
>TOTAL...
> 104.616s
>
> 64-bit GCC 4.9.3
>
> >speedtest1 --size 1000
> -- Speedtest1 for SQLite 3.15.0 2016-08-17 11:14:39
> a861713cc6a3868a1c89240e8340bc
>  100 - 50 INSERTs into table with no index.
> 0.797s
>  110 - 50 ordered INSERTS with one index/PK
> 1.250s
>  120 - 50 unordered INSERTS with one index/PK..
> 1.609s
>  130 - 25 SELECTS, numeric BETWEEN, unindexed..
> 0.969s
>  140 - 10 SELECTS, LIKE, unindexed.
> 2.859s
>  142 - 10 SELECTS w/ORDER BY, unindexed
> 2.860s
>  145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..
> 2.813s
>  150 - CREATE INDEX five times.
> 2.219s
>  160 - 10 SELECTS, numeric BETWEEN, indexed
> 6.751s
>  161 - 10 SELECTS, numeric BETWEEN, PK.
> 7.229s
>  170 - 10 SELECTS, text BETWEEN, indexed...
> 2.719s
>  180 - 50 INSERTS with three indexes...
> 2.266s
>  190 - DELETE and REFILL one table.
> 2.266s
>  200 - VACUUM..
> 1.735s
>  210 - ALTER TABLE ADD COLUMN, and query...
> 0.062s
>  230 - 10 UPDATES, numeric BETWEEN, indexed
> 7.329s
>  240 - 50 UPDATES of individual rows...
> 1.516s
>  250 - One big UPDATE of the whole 50-row table
> 0.437s
>  260 - Query added column after filling
> 0.047s
>  270 - 10 DELETEs, numeric BETWEEN, indexed
> 2.047s
>  280 - 50 DELETEs of individual rows...
> 1.938s
>  290 - Refill two 50-row tables using REPLACE..
> 4.438s
>  300 - Refill a 50-row table using (b&1)==(a&1)
> 2.360s
>  

Re: [sqlite] Error File is Encrypted or is not a database

[Simon Slavin]

On 18 Aug 2016, at 6:24pm, Matias Badin  wrote:

> Thanks  you very much! The information was very usefull.
> I can recover the database info now, overwritting the header array.

Once you have the header array 'correct' and SQLite no longer refuses to open 
the database, use the SQLite command-line tool to run

PRAGMA integrity_check

and note the results.  It may be that the header isn't the only part of the 
database which was corrupted.

> Have you any information about how the header is corrupted?

Usually a bug in the program which writes it, overwriting files or memory which 
are meant to be private to SQLite.  Sometimes it is instead a sign of a 
hardware fault, which will gradually get worse until it is corrupting more 
files on the storage medium.

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

Re: [sqlite] Error File is Encrypted or is not a database

[Adam Devita]

You are welcome.I was worried about the infinite loop I wrote. ;)

Hopefully you can track down the reason, I don't know anything about your
file but what you tell us. There are some things that you can do to help
track it down.

You didn't mention what version of sqlite you were using, programming
language, dev or target platform etc. This may be helpful to track it down,
or attract the interest of other list members more versed in an
implementation similar to  yours.

Also, if you have some sort of way of knowing the time the error was
written, if the file was exposed to users  etc. it may be helpful. If
you have a copy of the corrupt header and look at it from the point of view
of "what program would write such a thing to my file?"For example
"another program's" known file header, or (if the program was supposed to
append but didn't seek to the end before writing), some other stuff.

Have you ruled out your disk starting to fail?
Does corruption time co-relate to a power failure?  (Not all hard drives
tell the truth that the data is safely in the file.)
Is there a chance a user or other process opened the file, then closed it
saving its header over top of the sqlite one?

regards,
Adam





On Thu, Aug 18, 2016 at 1:24 PM, Matias Badin  wrote:

> Thanks  you very much! The information was very usefull.
> I can recover the database info now, overwritting the header array.
> Have you any information about how the header is corrupted? I would like to
> know the reason to resolve it.
> Thanks again!
>
> 2016-08-18 10:14 GMT-03:00 Adam Devita :
>
> > Good day,
> >
> > A few things that you can try
> > 1) One could download a hex editor and review the beginning of the file
> and
> > compare to https://www.sqlite.org/fileformat2.html . If some other
> program
> > has over-written the header,  you should be able to observe that,
> hopefully
> > identifying a program with a problem.
> >
> > 2) Back up you hard drive. Run hardware diagnostics.
> >
> > 3) Review https://www.sqlite.org/howtocorrupt.html
> >
> > 4) Do this list in reverse order. :)
> >
> >
> > regards,
> > Adam
> >
> >
> > On Thu, Aug 18, 2016 at 8:56 AM, Matias Badin 
> wrote:
> >
> > > Hi Everyone;
> > > I have a problem with a sqlite database that was working very good but
> > > suddenly started to give the message "File is encrypted or is not a
> > > database". Then I can´t access it and i have to replace it with a new
> > one.
> > >
> > > Can anyone help me with this problem?
> > > I don´t know how to re-open the database. I can´t access to the
> > information
> > > saved in it
> > > Thanks all;
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > --
> > VerifEye Technologies Inc.
> > 151 Whitehall Dr. Unit 2
> > Markham, ON
> > L3R 9T1
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error File is Encrypted or is not a database

[Matias Badin]

Thanks  you very much! The information was very usefull.
I can recover the database info now, overwritting the header array.
Have you any information about how the header is corrupted? I would like to
know the reason to resolve it.
Thanks again!

2016-08-18 10:14 GMT-03:00 Adam Devita :

> Good day,
>
> A few things that you can try
> 1) One could download a hex editor and review the beginning of the file and
> compare to https://www.sqlite.org/fileformat2.html . If some other program
> has over-written the header,  you should be able to observe that, hopefully
> identifying a program with a problem.
>
> 2) Back up you hard drive. Run hardware diagnostics.
>
> 3) Review https://www.sqlite.org/howtocorrupt.html
>
> 4) Do this list in reverse order. :)
>
>
> regards,
> Adam
>
>
> On Thu, Aug 18, 2016 at 8:56 AM, Matias Badin  wrote:
>
> > Hi Everyone;
> > I have a problem with a sqlite database that was working very good but
> > suddenly started to give the message "File is encrypted or is not a
> > database". Then I can´t access it and i have to replace it with a new
> one.
> >
> > Can anyone help me with this problem?
> > I don´t know how to re-open the database. I can´t access to the
> information
> > saved in it
> > Thanks all;
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> --
> VerifEye Technologies Inc.
> 151 Whitehall Dr. Unit 2
> Markham, ON
> L3R 9T1
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit SQLite3.exe

[Rousselot, Richard A]

Yes, it is much faster.  The process was done in Excel/Access before and took 
ages.  I have had processes go from 8 hours before to 30 min now using SQLite.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Michael Gratton
Sent: Wednesday, August 17, 2016 8:05 PM
To: SQLite mailing list
Subject: Re: [sqlite] 64-bit SQLite3.exe


Richard,

On Thu, Aug 18, 2016 at 2:23 AM, Rousselot, Richard A 
 wrote:
> I was only interested in doing these calculations quickly; that is my
> real-world.  The SQL scripts I am using were built with multiple steps
> with intermediate temp tables that are used to create a final result
> table.
>
> I understand my needs are not generally how others use SQLite.  I am
> using it as a platform for fast calculations that happens to store
> results in a way that can be quarried.  Every time, my process begins
> all prior results are deleted, only the input tables stay the same.


Out of curiosity, are you using these in an end-user application, i.e.
as an interactive response to user actions? I would be curious to know if 
building intermediate temporary tables is fast enough for returning results for 
such uses.

Thanks,
//Mike

--
⊨ Michael Gratton, Percept Wrangler.
⚙ 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
This communication is the property of CenturyLink and may contain confidential 
or privileged information. Unauthorized use of this communication is strictly 
prohibited and may be unlawful. If you have received this communication in 
error, please immediately notify the sender by reply e-mail and destroy all 
copies of the communication and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error File is Encrypted or is not a database

[David Raymond]

1) One could download a hex editor and review the beginning of the file and
compare to https://www.sqlite.org/fileformat2.html . If some other program
has over-written the header,  you should be able to observe that, hopefully
identifying a program with a problem.

2) Back up you hard drive. Run hardware diagnostics.

3) Review https://www.sqlite.org/howtocorrupt.html

4) Do this list in reverse order. :)





4) Do this list in reverse order. :)





1) Do this list in reverse order. :)

2) Review https://www.sqlite.org/howtocorrupt.html

3) Back up you hard drive. Run hardware diagnostics.

4) One could download a hex editor and review the beginning of the file and
compare to https://www.sqlite.org/fileformat2.html . If some other program
has over-written the header,  you should be able to observe that, hopefully
identifying a program with a problem.





1) Do this list in reverse order. :)





1) One could download a hex editor and review the beginning of the file and
compare to https://www.sqlite.org/fileformat2.html . If some other program
has over-written the header,  you should be able to observe that, hopefully
identifying a program with a problem.

2) ...

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

Re: [sqlite] Error File is Encrypted or is not a database

[Adam Devita]

Good day,

A few things that you can try
1) One could download a hex editor and review the beginning of the file and
compare to https://www.sqlite.org/fileformat2.html . If some other program
has over-written the header,  you should be able to observe that, hopefully
identifying a program with a problem.

2) Back up you hard drive. Run hardware diagnostics.

3) Review https://www.sqlite.org/howtocorrupt.html

4) Do this list in reverse order. :)


regards,
Adam


On Thu, Aug 18, 2016 at 8:56 AM, Matias Badin  wrote:

> Hi Everyone;
> I have a problem with a sqlite database that was working very good but
> suddenly started to give the message "File is encrypted or is not a
> database". Then I can´t access it and i have to replace it with a new one.
>
> Can anyone help me with this problem?
> I don´t know how to re-open the database. I can´t access to the information
> saved in it
> Thanks all;
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error File is Encrypted or is not a database

[Matias Badin]

Hi Everyone;
I have a problem with a sqlite database that was working very good but
suddenly started to give the message "File is encrypted or is not a
database". Then I can´t access it and i have to replace it with a new one.

Can anyone help me with this problem?
I don´t know how to re-open the database. I can´t access to the information
saved in it
Thanks all;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users