Re: [sqlite] Why are two select statements 2000 times faster than one?
On 04/14/2012 03:14 AM, Steinar Midtskogen wrote: Puneet Kishor writes: If you want the results in separate columns, you can do something like SELECT Min(a) minimum, 'none' maximum FROM t UNION ALL SELECT 'none' minimum, Max(a) minimum FROM t; Then it does a full scan again. But Igor's suggestion "SELECT (SELECT min(unix_time) FROM table), (SELECT max(unix_time) FROM table)" works fine, and means less code. This: http://www.sqlite.org/optoverview.html#minmax Both the subqueries qualify for the optimization, so the overall query is fast. With the UNION ALL version, the second column in the result set disqualifies both sides from using the optimization. So it is slow. I think if you were to change the UNION ALL version to the following it would be just as fast as the sub-selects. SELECT Min(a) minimum FROM t UNION ALL SELECT Max(a) minimum FROM t; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.7.11
On Fri, Apr 13, 2012 at 4:54 PM, Pete wrote: > A couple of things in the Release Notes for 3.7.11 caught my eye: > > - ability to insert muyltiple rows in one INSERT command > - improvements to the handling of csv inputs in sqlite3 > > Is there more detailed information available about these changes. For > example,the INSERT syntax diagram/description doesn;t seem to cover the new > feature. > Yeah it does. (Do you need to press "Reload" on your browser?) It used to have VALUES -> ( goes -> expr in a loop -> ). Now it has another loop from the ) back to the (. > > Thanks, > > -- > Pete > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- 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] Version 3.7.11
On 13 Apr 2012, at 9:54pm, Pete wrote:
> A couple of things in the Release Notes for 3.7.11 caught my eye:
>
> - ability to insert muyltiple rows in one INSERT command
> - improvements to the handling of csv inputs in sqlite3
>
> Is there more detailed information available about these changes. For
> example,the INSERT syntax diagram/description doesn;t seem to cover the new
> feature.
I'd bet on the standard SQL format for multiple inserting:
INSERT INTO phone_book VALUES ('John Doe', '555-1212'), ('Peter Doe',
'555-2323')
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Version 3.7.11
A couple of things in the Release Notes for 3.7.11 caught my eye: - ability to insert muyltiple rows in one INSERT command - improvements to the handling of csv inputs in sqlite3 Is there more detailed information available about these changes. For example,the INSERT syntax diagram/description doesn;t seem to cover the new feature. Thanks, -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why are two select statements 2000 times faster than one?
On Apr 13, 2012, at 3:14 PM, Steinar Midtskogen wrote: > Puneet Kishor writes: > >> If you want the results in separate columns, you can do something like >> >> SELECT Min(a) minimum, 'none' maximum FROM t UNION ALL SELECT 'none' >> minimum, Max(a) minimum FROM t; > > Then it does a full scan again. > > But Igor's suggestion "SELECT (SELECT min(unix_time) FROM table), > (SELECT max(unix_time) FROM table)" works fine, and means less code. > Yes, Igor's suggestion is definitely better, but where is the full table scan? sqlite> EXPLAIN QUERY PLAN SELECT Min(a) FROM t UNION ALL SELECT Max(a) FROM t; selectid|order|from|detail 1|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (~1 rows) 2|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (~1 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL) Am I missing something? -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why are two select statements 2000 times faster than one?
Puneet Kishor writes: > If you want the results in separate columns, you can do something like > > SELECT Min(a) minimum, 'none' maximum FROM t UNION ALL SELECT 'none' minimum, > Max(a) minimum FROM t; Then it does a full scan again. But Igor's suggestion "SELECT (SELECT min(unix_time) FROM table), (SELECT max(unix_time) FROM table)" works fine, and means less code. Thanks! -- Steinar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why are two select statements 2000 times faster than one?
Try the following sqlite> EXPLAIN QUERY PLAN SELECT Min(a) FROM t UNION ALL SELECT Max(a) FROM t; selectid|order|from|detail 1|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (~1 rows) 2|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (~1 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL) Should be a lot faster than a single query without UNION. If you want the results in separate columns, you can do something like SELECT Min(a) minimum, 'none' maximum FROM t UNION ALL SELECT 'none' minimum, Max(a) minimum FROM t; On Apr 13, 2012, at 2:44 PM, Steinar Midtskogen wrote: > Alessandro Marzocchi writes: > >> What does EXPLAIN QUERY PLAN says? > > sqlite> EXPLAIN QUERY PLAN SELECT min(unix_time) FROM table; > 0|0|0|SEARCH TABLE table USING INTEGER PRIMARY KEY (~1 rows) > > sqlite> EXPLAIN QUERY PLAN SELECT max(unix_time) FROM table; > 0|0|0|SEARCH TABLE table USING INTEGER PRIMARY KEY (~1 rows) > > sqlite> EXPLAIN QUERY PLAN SELECT min(unix_time), max(unix_time) FROM table; > 0|0|0|SCAN TABLE table (~100 rows) > > I suppose a query for a single min/max gets optimised, while a query > involving multiple columns doesn't. > > I have a much bigger table as well, and on that one the speedup is in > the millions to run two SELECTs. It's hard to guess that there will > be such a difference, but I suppose I should be happy that there is at > least an optimised way to get min and max for the integer primary key. > > -- > Steinar > ___ > 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] Why are two select statements 2000 times faster than one?
On 4/13/2012 2:59 PM, Steinar Midtskogen wrote: I have a table with "unix_time" as primary key and I want to get the minimum and maximum values of "unix_time". When I do: SELECT min(unix_time), max(unix_time) from table; it is very slow. It takes about 250ms, nearly everything in the step() call. However, if I do: SELECT min(unix_time) FROM table; SELECT max(unix_time) FROM table; to get the same values, it takes a fraction of the time. The speedup is more than 2000x. If you want to do it with a single query (say, to minimize disturbance to existing code), you could make it select (SELECT min(unix_time) FROM table), (SELECT max(unix_time) FROM table); I'm pretty sure this will get executed the fast way. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why are two select statements 2000 times faster than one?
Alessandro Marzocchi writes: > What does EXPLAIN QUERY PLAN says? sqlite> EXPLAIN QUERY PLAN SELECT min(unix_time) FROM table; 0|0|0|SEARCH TABLE table USING INTEGER PRIMARY KEY (~1 rows) sqlite> EXPLAIN QUERY PLAN SELECT max(unix_time) FROM table; 0|0|0|SEARCH TABLE table USING INTEGER PRIMARY KEY (~1 rows) sqlite> EXPLAIN QUERY PLAN SELECT min(unix_time), max(unix_time) FROM table; 0|0|0|SCAN TABLE table (~100 rows) I suppose a query for a single min/max gets optimised, while a query involving multiple columns doesn't. I have a much bigger table as well, and on that one the speedup is in the millions to run two SELECTs. It's hard to guess that there will be such a difference, but I suppose I should be happy that there is at least an optimised way to get min and max for the integer primary key. -- Steinar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why are two select statements 2000 times faster than one?
What does EXPLAIN QUERY PLAN says? Il giorno 13 aprile 2012 21:04, Marc L. Allen ha scritto: > Maybe the query analyzer isn't smart enough to do two seeks in this case, > so it does a scan? > > > -Original Message- > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > boun...@sqlite.org] On Behalf Of Steinar Midtskogen > > Sent: Friday, April 13, 2012 3:00 PM > > To: sqlite-users@sqlite.org > > Subject: [sqlite] Why are two select statements 2000 times faster than > > one? > > > > Hello, > > > > I have a table with "unix_time" as primary key and I want to get the > > minimum and maximum values of "unix_time". When I do: > > > > SELECT min(unix_time), max(unix_time) from table; > > > > it is very slow. It takes about 250ms, nearly everything in the > > step() call. > > > > However, if I do: > > > > SELECT min(unix_time) FROM table; SELECT max(unix_time) FROM table; > > > > to get the same values, it takes a fraction of the time. The speedup > > is more than 2000x. > > > > Why? > > > > -- > > Steinar > > ___ > > 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] Why are two select statements 2000 times faster than one?
Maybe the query analyzer isn't smart enough to do two seeks in this case, so it does a scan? > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Steinar Midtskogen > Sent: Friday, April 13, 2012 3:00 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Why are two select statements 2000 times faster than > one? > > Hello, > > I have a table with "unix_time" as primary key and I want to get the > minimum and maximum values of "unix_time". When I do: > > SELECT min(unix_time), max(unix_time) from table; > > it is very slow. It takes about 250ms, nearly everything in the > step() call. > > However, if I do: > > SELECT min(unix_time) FROM table; SELECT max(unix_time) FROM table; > > to get the same values, it takes a fraction of the time. The speedup > is more than 2000x. > > Why? > > -- > Steinar > ___ > 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] Why are two select statements 2000 times faster than one?
Hello, I have a table with "unix_time" as primary key and I want to get the minimum and maximum values of "unix_time". When I do: SELECT min(unix_time), max(unix_time) from table; it is very slow. It takes about 250ms, nearly everything in the step() call. However, if I do: SELECT min(unix_time) FROM table; SELECT max(unix_time) FROM table; to get the same values, it takes a fraction of the time. The speedup is more than 2000x. Why? -- Steinar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] MIGRATING BLOB FIELD FIREBIRD TO SQLITE
Dear users. I am a beginner in using sqlite. I am having difficulty importing firebird blob fields for sqlite blob fields. Some help? My best regards -- [ ]´s Cezar Moniz "O emitente desta mensagem é responsável por seu conteúdo e endereçamento. Cabe ao destinatário cuidar quanto ao tratamento adequado. Sem a devida autorização, a divulgação, a reprodução, a distribuição ou qualquer outra ação em desconformidade com as normas internas do Sistema Petrobras são proibidas e passíveis de sanção disciplinar, cível e criminal." "The sender of this message is responsible for its content and addressing. The receiver shall take proper care of it. Without due authorization, the publication, reproduction, distribution or the performance of any other action not conforming to Petrobras System internal policies and procedures is forbidden and liable to disciplinary, civil or criminal sanctions." "El emisor de este mensaje es responsable por su contenido y direccionamiento. Cabe al destinatario darle el tratamiento adecuado. Sin la debida autorización, su divulgación, reproducción, distribución o cualquier otra acción no conforme a las normas internas del Sistema Petrobras están prohibidas y serán pasibles de sanción disciplinaria, civil y penal." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using \"attach database\" to work around DB locking
On Fri, Apr 13, 2012 at 9:56 AM, Simon Slavin wrote: > Actually I'd like to apologise for posting the above. I forgot that every > process has its own ':memory:'. If you use a different process to look at > ':memory:' it won't see the database. Sorry about that. You could use a > different thread of the same process, but of course "Threads are evil.". > In my experience every open() of :memory: is a difference instance? My hand is injured, making typing difficult, so i won't try it out right now :/. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select time('now')
YAN HONG YE wrote:
> the current time is 15:15
> when I use this following command:
> sqlite> Select time('now');
> return 07:15:42
> not current time,why?
Make it
select time('now', 'localtime');
7:15 is the time in UTC.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using \"attach database\" to work around DB locking
On 13 Apr 2012, at 8:41am, "Gabriel Corneanu" wrote: >> Or you can do your immediate writing to a database in memory, and have >> anotherprocess dump memory to disk in the background. Depending on how >> recent youneed reading you can read the one in memory or the one on disk. Actually I'd like to apologise for posting the above. I forgot that every process has its own ':memory:'. If you use a different process to look at ':memory:' it won't see the database. Sorry about that. You could use a different thread of the same process, but of course "Threads are evil.". Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to joe mistachkin - Ticket 4bbf851fa5
on http://www.sqlite.org/src/wiki?name=Bug+Reports
I read
"The idea is that bug reports can be filtered on the mailing list and those
that are actual new bugs can be transferred into our bug tracking system by
registered developers.
* Anonymous users can continue to append comments to existing bugs, just
not create new bugs.* "
Am 12. April 2012 23:53 schrieb Joe Mistachkin :
>
> Simon Hucksley wrote:
> >
> > since weeks I'm not able to edit the ticket as anonymous.
> > the edit button is missing on the menu.
> >
>
> Unfortunately, the ability to edit tickets anonymously had to be disabled.
>
> >
> > to ticket 4bbf851fa5
> >
> > It's the same old story:
> > after updating some hundred records
> > the DBConcurrencyException occurs.
> >
> > After updating the DateTime column
> > update t set dt = 2455926.6
> >
> > life goes on for some hundred updates.
> >
>
> As I said in the ticket, I've done quite a bit of careful analysis and
> troubleshooting on several issues that fall into this general area.
>
> The conclusion I came to was that the .NET Framework makes certain
> assumptions
> about ADO.NET providers (and their supported data types in particular)
> that
> do
> not always map correctly to how System.Data.SQLite handles its data types.
>
> Unfortunately, without breaking backward compatibility with previous
> versions,
> it is almost impossible to "fix" this issue from the System.Data.SQLite
> side.
>
> The best that can currently be done is to figure out exactly which of SQL
> queries generated by the .NET Framework are problematic and why (i.e. which
> use
> of the equality operator is causing the issue, given the parameter data
> types).
> As of release 1.0.80.0, This can be done by using the "Flags" connection
> string
> property, set to a value of "LogAll" (the important flags are "LogPrepare",
> "LogPreBind", and "LogBind"); however, "LogAll" is typically easier to
> specify.
> Here is an example:
>
> //
> // In order to actually see the output generated by these diagnostic
> flags,
> // you'll need to add a TraceListener of some kind to the TRACE listeners
> // collection (and the TRACE define constant must be present in your
> project
> // build configuration as well). For non-console applications, you'll
> want
> // to use the DefaultTraceListener instead (it will write output to the VS
> // output window when the application is running inside the debugger -OR-
> // you can use Sysinternals DebugView tool to see it when the application
> is
> // not running inside the debugger).
> //
> System.Diagnostics.Trace.Listeners.Add(new ConsoleTraceListener());
>
> using (SQLiteConnection connection = new SQLiteConnection(
> "Data Source=somefile.db;Flags=LogAll;"))
> {
>connection.Open();
>
>// code to execute query here...
> }
>
> --
> Joe Mistachkin
>
> ___
> 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] Select time('now')
YAN HONG YE writes:
> the current time is 15:15
> when I use this following command:
> sqlite> Select time('now');
> return 07:15:42
> not current time,why?
Read http://www.sqlite.org/lang_datefunc.html
"Format 11, the string 'now', is converted into the current date and
time as obtained from the xCurrentTime method of the sqlite3_vfs
object in use. Universal Coordinated Time (UTC) is used."
--
Steinar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using \"attach database\" to work around DB locking
Or you can do your immediate writing to a database in memory, and have
anotherprocess dump memory to disk in the background. Depending on how
recent youneed reading you can read the one in memory or the one on disk.
It seems I have reached the CPU boundary (>90% one 1 core), not waiting
for the disk anymore...
Plus that I'm not using fsync ("pragma synchronous"), so the disk cache is
in effect anyway.
If I need more through-output, I might go multi-threaded write (if
possible).
However it would be interesting to know what's really doing; I have an
"append" only usage.
I think most of the time is spent in updating/maintaining the primary key
btree, which is a simple "INTEGER PRIMARY KEY" with null on inserts - so
the values are auto-generated.
I am using a virtual table with a block of values (all fields except the
rowid) and a "insert into select * from ".
Is there a possibility to optimize this simple case (because the number of
records is known, so all new rowids are virtually known)?
Gabriel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Select time('now')
the current time is 15:15
when I use this following command:
sqlite> Select time('now');
return 07:15:42
not current time,why?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
