[sqlite] Performance comparison between SQLite and SQL Server?

[Simon Slavin]

On 15 Feb 2016, at 9:41pm, James K. Lowden  wrote:

> SQL Server has none of those restrictions, and probably keeps pace with
> SQLite even on its home turf.  But the administration of SQL Server is
> nontrivial.  For that reason alone, I would never use it in situations
> where SQLite would do. 

That's the fella.  Major advantage of SQLite: zero admin.  Not even a 
background task.

Second advantage: you know exactly where you data is.  Better still, it's 
simple: one database == one file, and the file has the same name as the 
database.  I remember trying to reconstruct a MySQL database from a dead 
server.  One folder with a confusing mass of files in.  Your database is part 
of some of those files, but the files may be huge even if the one database you 
care about is tiny.  That was not a fun time.

Simon.

[sqlite] Performance comparison between SQLite and SQL Server?

[Eric Sink]

Just for fun:

I know a friend who has a Ferrari.  It is faster than my Ford F-150.

Unless we are racing with both vehicles pulling a 7,000 pound trailer
uphill.  Then I would probably win.

Thousand-mile trip?  Take a sports car.

Moving a couch a thousand miles?  Use a pickup truck.

SQLite is kinda like a sports car.

SQL Server is kinda like a pickup truck.

And this car metaphor of mine is kinda like a motorcycle -- if you lean on
it too hard, it'll probably fall over.

--
E


On Mon, Feb 15, 2016 at 5:24 PM, Michael Falconer <
michael.j.falconer at gmail.com> wrote:

> Good thread,
>
> which absolutely nails the point 'dev decisions for app cases' make a
> developers world go round. I personally couldn't think of a greater waste
> of time than a benchmark comparison between client server rdbms's and
> sqlite. Do what benefits your case most. The above from Jim pretty much
> encapsulates my thoughts:
>
> "SQLite is not directly comparable to client/server SQL database engines
> > such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying
> to
> > solve a different problem.   Client/server SQL database engines strive to
> > implement a shared repository of enterprise data. ...SQLite strives to
> > provide local data storage for individual applications and devices."
> >
>
> I could bang on about my own preferences and decisions I've made but they'd
> only be reiterating the points made above. They were based on system
> requirement specs and where local storage was involved it was a blindingly
> obvious decision to go with sqlite. Rob above made another excellent point
> often overlooked (usually an afterthought for many dev's):
>
> 4. The support is top notch. I have brought and paid for govt scale
> > databases for governments and to be honest the support for SQLite is just
> > as good, and to be honest I would say better than Big Red or Big Blue
> (and
> > I used to work for Big Blue).
> >
>
> It is another unique property of a great product. Support is not just
> sqlite specific either (a cop out on many a tech forum) and particularly on
> this list the topics can be rather broad. There is plenty of good quality
> feedback and many a good general SQL solution which just adds to the sqlite
> package as a whole.
>
>
> On 16 February 2016 at 09:42, Jim Callahan  >
> wrote:
>
> > SQLite would be most comparable to *SQL Server Express LocalDB* edition
> > which is introduced in this July 2011 blog post
> >
> >
> https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/
> >
> > More uptodate information about *SQL Server Express LocalDB* edition
> > is in this 2016 Microsoft Developer's Network (MSDN) article
> > https://msdn.microsoft.com/en-us/library/hh510202.aspx
> >
> > This page "*Appropriate Uses for SQLite*" (whentouse.html) describes BOTH
> > "*Situations Where SQLite Works Well*"
> >
> > and
> >
> > "*Situations Where A Client/Server RDBMS May Work Better*"
> > http://sqlite.org/whentouse.html
> >
> >
> > Opening lines of whentouse.html:
> >
> > "SQLite is not directly comparable to client/server SQL database engines
> > such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying
> to
> > solve a different problem.   Client/server SQL database engines strive to
> > implement a shared repository of enterprise data. ...SQLite strives to
> > provide local data storage for individual applications and devices."
> >
> > Even Microsoft has adopted SQLite for some limited tasks (such as storing
> > state) within every shipping copy of Windows 10.
> > "SQLite is a unique case: it is an open source, externally developed
> > software that is used by core system components, and our flagship apps
> like
> > Cortana and Skype.  ...After shipping SQLite as a system component in
> July,
> > we wanted to include it in our SDK for November. With more than 20,000
> > Windows Apps and more than half of our top apps using SQLite, it made
> sense
> > to just make expose the system SQLite to app developers."
> > http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/
> >
> >
> > There is a historical and unfair (specially compiled version of SQLite
> > against default settings of PostgreSQL) benchmark
> > available on this page, but now that you understand the use cases, this
> > particular benchmark is not that useful in addition
> > to being out of date and unfair.
> > https://www.sqlite.org/speed.html
> >
> > Jim Callahan
> > Data Scientist
> > https://www.linkedin.com/in/jamesbcallahan
> > Orlando, FL
> >
> > On Mon, Feb 15, 2016 at 4:54 PM, Simon Slavin 
> > wrote:
> >
> > >
> > > On 15 Feb 2016, at 9:41pm, James K. Lowden 
> > > wrote:
> > >
> > > > SQL Server has none of those restrictions, and probably keeps pace
> with
> > > > SQLite even on its home turf.  But the administration of SQL Server
> is
> > > > nontrivial.  For that reason alone, I would never use it in
> situations
> > > > where SQLite would do.
> > >
> > > That's the 

[sqlite] s::[i] Why SQLite does not use a web-based forum?

[Drago, William @ CSG - NARDA-MITEQ]

Send your question to a mailing list and a large number of people see it right 
away. Discussion follows as quickly as email can be sent and rec'd. You get the 
answer to your question in the least possible time. Post your question on a 
web-based forum and it will take much longer for the same number of people to 
see it, and most of those people are there trying to solve their own problems 
not help you with yours.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of admin at shuling.net
> Sent: Sunday, February 14, 2016 8:19 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: s::[i] [sqlite] Why SQLite does not use a web-based forum?
> Importance: High
> Sensitivity: Confidential
>
> Hi,
>
> Why SQLite does not utilize a web-based forum for all users discuss
> problems? I think that will be more convenient and can help more people.
>
> Thanks
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Performance comparison between SQLite and SQL Server?

[Scott Robison]

On Mon, Feb 15, 2016 at 9:07 PM, Keith Medcalf  wrote:

>
> On the other hand if you drive either on a road with a speed limit of 30
> miles per hour (and go the speed limit), they both go the same distance in
> the same time.
>
> In other words, inquiring "which gets from one side of town to the other"
> the fastest, a Ferrari or an F-150, is not dependent on either the Ferrari
> or the F-150, but the infrastructure on which they are travelling.  A
> similar question would be "which weighs more, a ton of feathers or a ton of
> depleted uranium?".
>
> So the answer is that both SQL Server and SQLite will "travel" at the
> "speed limit" imposed by the hardware on which they are run.
>

On the third hand, how many system resources will SQL Server use to achieve
a given level of performance vs SQLite. You don't want a Ferrari or an
F-150. You want a Prius. :)

[sqlite] Correlated subquery throwing an error

[da...@andl.org]

OK, I get it now.

You need a whole query to calculate a value for val, and then another query
to find the lowest distance match on val. You can't do that with simple
correlated queries or subqueries, except by repeating a lot of the work.

The only reasonable prospect I could see for efficiency is to use a CTE to
calculate the intermediate table containing VAL and DIST<25, a main query
for the final result with a value subquery to compute min(VAL). Or you could
use two queries and an explicit temporary table.

The reason for my interest is how to write this query in Andl, and since on
Sqlite Andl generates SQL I'd like to check that the SQL actually works. My
solution for this problem requires storing the intermediate results, which
is trivial in Andl and much harder to do in SQL.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Gary
Briggs
Sent: Monday, 15 February 2016 11:47 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Correlated subquery throwing an error

On Mon, Feb 15, 2016 at 11:11:26AM +1100, david at andl.org wrote:
> Why not
> 
> SELECT foo.*,
>   (SELECT id, DIST(foo.x, foo.y, nearest.x, nearest.y) AS d
>FROM foo AS nearest
>WHERE d < 25
>ORDER BY val, d
>LIMIT 1) AS id2
> FROM foo

That specific SQL gives the error:
"only a single result allowed for a SELECT that is part of an expression"
But when I removed the "id" column from the inner select, it worked. It's
when I replace "d" with the full DISTANCE() in just the ORDER BY clause that
it stops working.

Works:
 SELECT foo.*,
   (SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d
FROM foo AS nearest
WHERE d < 25
ORDER BY val, d
LIMIT 1) AS id2
 FROM foo
LIMIT 5

Works:
 SELECT foo.*,
   (SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d
FROM foo AS nearest
WHERE DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0)  < 25
ORDER BY val, d
LIMIT 1) AS id2
 FROM foo
LIMIT 5

Doesn't work ["no such column: foo.x"]:
 SELECT foo.*,
   (SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d
FROM foo AS nearest
WHERE d  < 25
ORDER BY val, DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0)
LIMIT 1) AS id2
 FROM foo
LIMIT 5


Thanks,
Gary

> Regards
> David M Bennett FACS
> 
> Andl - A New Database Language - andl.org
> 
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
> Gary Briggs
> Sent: Monday, 15 February 2016 10:43 AM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Correlated subquery throwing an error
> 
> I posted a question on stackoverflow, here:
> http://stackoverflow.com/questions/35382897/implementing-a-sql-query-w
> ithout
> -window-functions
> 
> In short, I have a table that I'm trying to query:
> CREATE TABLE foo (
>id INTEGER PRIMARY KEY,
>x REAL NOT NULL,
>y REAL NOT NULL,
>val REAL NOT NULL,
>UNIQUE(x,y));
> 
> I have a helper function, DISTANCE(x1,y1,x2,y2).
> The results I'm looking for are:
> 
> "For every row in that table, I want the entire row in that same table 
> within a certain distance [eg 25], with the lowest "val". For rows 
> with the same "val", I want to use lowest distance as a tie breaker."
> 
> The answer I got on stackoverflow included this correlated subquery, 
> but it's not working:
> SELECT foo.*,
>   (SELECT id
>FROM foo AS nearest
>WHERE DIST(foo.x, foo.y, nearest.x, nearest.y) < 25
>ORDER BY val, DIST(foo.x, foo.y, nearest.x, nearest.y)
>LIMIT 1) AS id2
> FROM foo
> 
> I get the error "no such column: foo.x"
> 
> I tried rewriting it to have the subquery in a WHERE clause instead 
> [not quite the same query, but I think the problem I have is the same]
> 
> SELECT outerfoo.*
>  FROM foo outerfoo
>  WHERE outerfoo.id=(SELECT id
>   FROM foo AS nearest
>   WHERE DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y) <= 25
>   ORDER BY val, DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y)
>   LIMIT 1)
> 
> And now I get the error "no such column: outerfoo.x"
> 
> Can anyone advise on what I might do?
> 
> Thank-you very much,
> Gary
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Performance comparison between SQLite and SQL Server?

[Keith Medcalf]

On the other hand if you drive either on a road with a speed limit of 30 miles 
per hour (and go the speed limit), they both go the same distance in the same 
time.  

In other words, inquiring "which gets from one side of town to the other" the 
fastest, a Ferrari or an F-150, is not dependent on either the Ferrari or the 
F-150, but the infrastructure on which they are travelling.  A similar question 
would be "which weighs more, a ton of feathers or a ton of depleted uranium?".

So the answer is that both SQL Server and SQLite will "travel" at the "speed 
limit" imposed by the hardware on which they are run.

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Eric Sink
> Sent: Monday, 15 February, 2016 20:52
> To: SQLite mailing list
> Subject: Re: [sqlite] Performance comparison between SQLite and SQL
> Server?
> 
> Just for fun:
> 
> I know a friend who has a Ferrari.  It is faster than my Ford F-150.
> 
> Unless we are racing with both vehicles pulling a 7,000 pound trailer
> uphill.  Then I would probably win.
> 
> Thousand-mile trip?  Take a sports car.
> 
> Moving a couch a thousand miles?  Use a pickup truck.
> 
> SQLite is kinda like a sports car.
> 
> SQL Server is kinda like a pickup truck.
> 
> And this car metaphor of mine is kinda like a motorcycle -- if you lean on
> it too hard, it'll probably fall over.
> 
> --
> E
> 
> 
> On Mon, Feb 15, 2016 at 5:24 PM, Michael Falconer <
> michael.j.falconer at gmail.com> wrote:
> 
> > Good thread,
> >
> > which absolutely nails the point 'dev decisions for app cases' make a
> > developers world go round. I personally couldn't think of a greater
> waste
> > of time than a benchmark comparison between client server rdbms's and
> > sqlite. Do what benefits your case most. The above from Jim pretty much
> > encapsulates my thoughts:
> >
> > "SQLite is not directly comparable to client/server SQL database engines
> > > such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is
> trying
> > to
> > > solve a different problem.   Client/server SQL database engines strive
> to
> > > implement a shared repository of enterprise data. ...SQLite strives to
> > > provide local data storage for individual applications and devices."
> > >
> >
> > I could bang on about my own preferences and decisions I've made but
> they'd
> > only be reiterating the points made above. They were based on system
> > requirement specs and where local storage was involved it was a
> blindingly
> > obvious decision to go with sqlite. Rob above made another excellent
> point
> > often overlooked (usually an afterthought for many dev's):
> >
> > 4. The support is top notch. I have brought and paid for govt scale
> > > databases for governments and to be honest the support for SQLite is
> just
> > > as good, and to be honest I would say better than Big Red or Big Blue
> > (and
> > > I used to work for Big Blue).
> > >
> >
> > It is another unique property of a great product. Support is not just
> > sqlite specific either (a cop out on many a tech forum) and particularly
> on
> > this list the topics can be rather broad. There is plenty of good
> quality
> > feedback and many a good general SQL solution which just adds to the
> sqlite
> > package as a whole.
> >
> >
> > On 16 February 2016 at 09:42, Jim Callahan
>  > >
> > wrote:
> >
> > > SQLite would be most comparable to *SQL Server Express LocalDB*
> edition
> > > which is introduced in this July 2011 blog post
> > >
> > >
> > https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-
> localdb-an-improved-sql-express/
> > >
> > > More uptodate information about *SQL Server Express LocalDB* edition
> > > is in this 2016 Microsoft Developer's Network (MSDN) article
> > > https://msdn.microsoft.com/en-us/library/hh510202.aspx
> > >
> > > This page "*Appropriate Uses for SQLite*" (whentouse.html) describes
> BOTH
> > > "*Situations Where SQLite Works Well*"
> > >
> > > and
> > >
> > > "*Situations Where A Client/Server RDBMS May Work Better*"
> > > http://sqlite.org/whentouse.html
> > >
> > >
> > > Opening lines of whentouse.html:
> > >
> > > "SQLite is not directly comparable to client/server SQL database
> engines
> > > such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is
> trying
> > to
> > > solve a different problem.   Client/server SQL database engines strive
> to
> > > implement a shared repository of enterprise data. ...SQLite strives to
> > > provide local data storage for individual applications and devices."
> > >
> > > Even Microsoft has adopted SQLite for some limited tasks (such as
> storing
> > > state) within every shipping copy of Windows 10.
> > > "SQLite is a unique case: it is an open source, externally developed
> > > software that is used by core system components, and our flagship apps
> > like
> > > Cortana and Skype.  ...After shipping SQLite as a system component in
> > July,
> > 

[sqlite] Correlated subquery throwing an error

[Simon Slavin]

On 15 Feb 2016, at 8:58pm, James K. Lowden  wrote:

> Clemens Ladisch  wrote:
> 
>> I don't know why correlated subqueries cannot use values from the
>> outer query in the ORDER BY or LIMIT clauses; 
> 
> ORDER BY is not part of SELECT!  It's not a relational operator.
> Per the SQL standard -- ORDER BY cannot appear in a subquery. It can
> appear in only one place: as a kind of post-processor that determines
> the order in which the rows are delivered to the client. 

And to build on that, if you cannot specify ORDER BY then you cannot expect 
LIMIT to do anything useful.  Since you don't know which ones will be top in 
the list there's no point in picking just the top ones.

It took me a long time to convince myself that SQL really was a language based 
on sets.  And the elements of sets don't have any order.

Simon.

[sqlite] empty page corrupt

[sanhua.zh]

Yeah, but I don?t know how to debug it. I follow the howtocorrupt.html 
suggestion but nothing helpful.
I write in Objective-C. all sqlite relative operation isencapsulated by 
Objective-C and thread mutex.



???:Simon Slavinslavins at bigfraud.org
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
:2016?2?15?(??)?20:03
??:Re: [sqlite] empty page corrupt


On 15 Feb 2016, at 11:59am, sanhua.zh sanhua.zh at foxmail.com wrote:  I did it 
on iOS.  I already checked the howtocorrupt.html, but I can?t find anything 
helpful. This is almost always a result of hardware failure or programming 
error. SQLite does not corrupt databases that way. What language are you 
programming in ? Simon. ___ 
sqlite-users mailing list sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] empty page corrupt

[sanhua.zh]

I did it on iOS.
I already checked the howtocorrupt.html, but I can?t find anything helpful.



???:Clemens Ladischclemens at ladisch.de
???:sqlite-userssqlite-users at mailinglists.sqlite.org
:2016?2?15?(??)?18:42
??:Re: [sqlite] empty page corrupt


sanhua.zh wrote:  I find some db corrupted and try to find out the reason. Are 
you using a network file system or virtual machine? What type of disk? 
http://www.sqlite.org/howtocorrupt.html Regards, Clemens 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] empty page corrupt

[sanhua.zh]

I find some db corrupted and try to find out the reason.
After a simple analysis, I found that many of the db pages are empty, which 
means all bits in this page is zero. This is the reason causing sqlite report 
db corrupt.
I don?t know why this happen, does someone have the same problem ever?


Here is some info,
1. this db is in WAL mode
2. 4% cause corrupt ( from the online data)
3. this db is used for fts
4. I think all db relative operation is locked well

[sqlite] Performance comparison between SQLite and SQL Server?

[Jim Callahan]

SQLite would be most comparable to *SQL Server Express LocalDB* edition
which is introduced in this July 2011 blog post
https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/

More uptodate information about *SQL Server Express LocalDB* edition
is in this 2016 Microsoft Developer's Network (MSDN) article
https://msdn.microsoft.com/en-us/library/hh510202.aspx

This page "*Appropriate Uses for SQLite*" (whentouse.html) describes BOTH
"*Situations Where SQLite Works Well*"

and

"*Situations Where A Client/Server RDBMS May Work Better*"
http://sqlite.org/whentouse.html


Opening lines of whentouse.html:

"SQLite is not directly comparable to client/server SQL database engines
such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to
solve a different problem.   Client/server SQL database engines strive to
implement a shared repository of enterprise data. ...SQLite strives to
provide local data storage for individual applications and devices."

Even Microsoft has adopted SQLite for some limited tasks (such as storing
state) within every shipping copy of Windows 10.
"SQLite is a unique case: it is an open source, externally developed
software that is used by core system components, and our flagship apps like
Cortana and Skype.  ...After shipping SQLite as a system component in July,
we wanted to include it in our SDK for November. With more than 20,000
Windows Apps and more than half of our top apps using SQLite, it made sense
to just make expose the system SQLite to app developers."
http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/


There is a historical and unfair (specially compiled version of SQLite
against default settings of PostgreSQL) benchmark
available on this page, but now that you understand the use cases, this
particular benchmark is not that useful in addition
to being out of date and unfair.
https://www.sqlite.org/speed.html

Jim Callahan
Data Scientist
https://www.linkedin.com/in/jamesbcallahan
Orlando, FL

On Mon, Feb 15, 2016 at 4:54 PM, Simon Slavin  wrote:

>
> On 15 Feb 2016, at 9:41pm, James K. Lowden 
> wrote:
>
> > SQL Server has none of those restrictions, and probably keeps pace with
> > SQLite even on its home turf.  But the administration of SQL Server is
> > nontrivial.  For that reason alone, I would never use it in situations
> > where SQLite would do.
>
> That's the fella.  Major advantage of SQLite: zero admin.  Not even a
> background task.
>
> Second advantage: you know exactly where you data is.  Better still, it's
> simple: one database == one file, and the file has the same name as the
> database.  I remember trying to reconstruct a MySQL database from a dead
> server.  One folder with a confusing mass of files in.  Your database is
> part of some of those files, but the files may be huge even if the one
> database you care about is tiny.  That was not a fun time.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Performance comparison between SQLite and SQL Server?

[James K. Lowden]

On Mon, 15 Feb 2016 11:21:06 +0800
 wrote:

> I am just curious whether there is a performance comparison between
> SQLite and SQL Server? 

Odds are you will never see a such a comparison published.  If you read
your SQL Server EULA, you'll see it specifically prohibits publishing
benchmark results.  

The restriction is not purely evil.  It could be argued that the flaws
embedded in any testing regimen render the conclusion meaningless.  

Performance of any kind is notoriously difficult to measure.  That's
especially true of DBMSs because of their complexity. Optimal DBMS
performance is a function of hardware, OS, and DBMS configuration.
That's a lot of expertise to bring to bear on making *one* DBMS perform
at its best; doing it for N is at least N times harder, especially if
you're being scrupulous.  

Back when SQL was young, people still read magazines about new
technology, and benchmarks were a popular feature.  I remember poring
over them to learn whether Oracle was faster than Sybase, etc.  But the
more I learned, the better I understood the futility of the question.
There were too many moving parts, and the journalists had nowhere near
the resources needed to do the job properly.  Any decision they
rendered was flawed, and in any case became obsolete as the technology
evolved.  

I think it was in answer to such testing that the vendors began
including restrictions on published performance measurements in their
EULAs.  

In answer to your question, I would say SQLite performs very well
provided you live with its restrictions.  In particular:

1.  Very little write contention.  
2.  All processes running on the same machine, using a local
filesystem.  
3.  Queries of limited complexity, or data of limited size.  

That is, you're lijmited to one writer at a time, you can't use a
network filesystem, and query optimization will hurt with large tables
if, for example, subqueries can't be flattened.  

SQL Server has none of those restrictions, and probably keeps pace with
SQLite even on its home turf.  But the administration of SQL Server is
nontrivial.  For that reason alone, I would never use it in situations
where SQLite would do.  

--jkl

[sqlite] Correlated subquery throwing an error

[James K. Lowden]

On Mon, 15 Feb 2016 08:56:35 +0100
Clemens Ladisch  wrote:

> I don't know why correlated subqueries cannot use values from the
> outer query in the ORDER BY or LIMIT clauses; 

ORDER BY is not part of SELECT!  It's not a relational operator.
Per the SQL standard -- ORDER BY cannot appear in a subquery. It can
appear in only one place: as a kind of post-processor that determines
the order in which the rows are delivered to the client.  

I suspect that's why Postgres ORDER BY doesn't recognize column names
in the enclosing scope.  If the day ever comes when SQLite can remove
language features, internal ORDER BY (and LIMIT) would be at the top of
my list, along with double-quotes for strings. 

--jkl

[sqlite] Correlated subquery throwing an error

[James K. Lowden]

On Mon, 15 Feb 2016 10:39:31 +0100
Clemens Ladisch  wrote:

> > you need to explicitly limit a subquery that is a field and must
> > only ever return 1 result if the where clause is ambiguous about it
> 
> Not in SQLite.  (It ignores superfluous rows, and returns NULL if
> there are no rows.)

Yes, but the right way to do is to use logic instead of brute force.
Instead of 

select * from (select t from T order by t limit 1) as T;

use

select * from (select min(t) as t from T group by t) as T;

The latter has the benefit that it can be easily modified to add 
COUNT(*) to the subquery, and check for perhaps erroneous cases where
COUNT(*) > 1.  

--jkl

[sqlite] Correlated subquery throwing an error

[Dan Kennedy]

On 02/15/2016 02:56 PM, Clemens Ladisch wrote:
> Gary Briggs wrote:
>>> SELECT
>>>a.id AS a_id,
>>>(SELECT b.id
>>> FROM foo AS b
>>> WHERE b.id!=a.id
>>>   AND distance(a.x,a.y,b.x,b.y)<=25
>>> ORDER BY b.val, distance(a.x,a.y,b.x,b.y))
>>> FROM foo AS a
>> This is the bit that doesn't seem to work; having
>> distance(a.x,a.y,b.x,b.y) in the ORDER BY clause on the inner query is
>> what appears to cause the error that it can't find a.x, from the outer query.
> I don't know why correlated subqueries cannot use values from the outer
> query in the ORDER BY or LIMIT clauses; this does not look as if it were
> by design.

ORDER BY and GROUP BY cannot refer to columns from the outer select by 
design:

   http://www.sqlite.org/src/artifact/9f7ce3a3c087a?ln=1252-1253

I think this is an SQL standard thing. It's what Postgres does too. 
Which is probably why SQLite is this way.

LIMIT and OFFSET expressions cannot refer to any columns, correlated or 
otherwise.

Dan.

[sqlite] Correlated subquery throwing an error

[Keith Medcalf]

Pretty sure you meant:

select * from (select min(t) as t from T) as T;

adding a group by will return the minimum value of T for each group of T which 
is equivalent to
select distinct T from T

min(t) group by t, max(t) group by t, avg(t) group by t, distinct t

all return the same results.


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of James K. Lowden
> Sent: Monday, 15 February, 2016 13:58
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Correlated subquery throwing an error
> 
> On Mon, 15 Feb 2016 10:39:31 +0100
> Clemens Ladisch  wrote:
> 
> > > you need to explicitly limit a subquery that is a field and must
> > > only ever return 1 result if the where clause is ambiguous about it
> >
> > Not in SQLite.  (It ignores superfluous rows, and returns NULL if
> > there are no rows.)
> 
> Yes, but the right way to do is to use logic instead of brute force.
> Instead of
> 
>   select * from (select t from T order by t limit 1) as T;
> 
> use
> 
>   select * from (select min(t) as t from T group by t) as T;
> 
> The latter has the benefit that it can be easily modified to add
> COUNT(*) to the subquery, and check for perhaps erroneous cases where
> COUNT(*) > 1.
> 
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Correlated subquery throwing an error

[Scott Robison]

On Mon, Feb 15, 2016 at 2:05 PM, Simon Slavin  wrote:

>
> On 15 Feb 2016, at 8:58pm, James K. Lowden 
> wrote:
>
> > Clemens Ladisch  wrote:
> >
> >> I don't know why correlated subqueries cannot use values from the
> >> outer query in the ORDER BY or LIMIT clauses;
> >
> > ORDER BY is not part of SELECT!  It's not a relational operator.
> > Per the SQL standard -- ORDER BY cannot appear in a subquery. It can
> > appear in only one place: as a kind of post-processor that determines
> > the order in which the rows are delivered to the client.
>
> And to build on that, if you cannot specify ORDER BY then you cannot
> expect LIMIT to do anything useful.  Since you don't know which ones will
> be top in the list there's no point in picking just the top ones.
>

I think there is still a potential use for limit (though maybe there's
another way I haven't considered). You have a table representing a job
queue. Each job will take some amount of time to process. The order doesn't
matter as long as all jobs are eventually processed and you have a single
process running the jobs. Limit 1 is a reasonable way to grab a single job.

-- 
Scott Robison

[sqlite] empty page corrupt

[Simon Slavin]

On 15 Feb 2016, at 11:59am, sanhua.zh  wrote:

> I did it on iOS.
> I already checked the howtocorrupt.html, but I can?t find anything helpful.

This is almost always a result of hardware failure or programming error.  
SQLite does not corrupt databases that way.  What language are you programming 
in ?

Simon.

[sqlite] empty page corrupt

[Clemens Ladisch]

sanhua.zh wrote:
> I find some db corrupted and try to find out the reason.

Are you using a network file system or virtual machine?
What type of disk?

http://www.sqlite.org/howtocorrupt.html


Regards,
Clemens

[sqlite] Correlated subquery throwing an error

[Gary Briggs]

On Mon, Feb 15, 2016 at 08:56:35AM +0100, Clemens Ladisch wrote:
> Gary Briggs wrote:
> >> SELECT
> >>   a.id AS a_id,
> >>   (SELECT b.id
> >>FROM foo AS b
> >>WHERE b.id!=a.id
> >>  AND distance(a.x,a.y,b.x,b.y)<=25
> >>ORDER BY b.val, distance(a.x,a.y,b.x,b.y))
> >> FROM foo AS a
> >
> > This is the bit that doesn't seem to work; having
> > distance(a.x,a.y,b.x,b.y) in the ORDER BY clause on the inner query is
> > what appears to cause the error that it can't find a.x, from the outer 
> > query.
> 
> I don't know why correlated subqueries cannot use values from the outer
> query in the ORDER BY or LIMIT clauses; this does not look as if it were
> by design.
> 
> Anyway, I got it to work with another indirection:
> 
> SELECT foo.*,
>(SELECT id
> FROM (SELECT id,
>  x,
>  y,
>  foo.x AS foo_x,
>  foo.y AS foo_y,
>  val
>   FROM foo)
> WHERE DIST(foo_x, foo_y, x, y) < 25
> ORDER BY val, DIST(foo_x, foo_y, x, y)
> LIMIT 1
>) AS id2
> FROM foo

Fantastic, thank-you. Does exactly what it says on the box!

Gary

[sqlite] applyng schema changes to several databases

[R Smith]

On 2016/02/15 11:01 AM, Luca Ferrari wrote:
> Hi all,
> this could sound trivial but I've got a few hundreds SQLite 3 database
> files, all with the same schema, that I need to alter adding a few
> columns here and there.
> While I'm pretty sure a simple sheel script that will execute, file
> per file, the alter table (within a transaction) will do the job I'm
> wondering if there's a better approach or a more automated one.
>
> Any suggestion is welcome.

If all the files are in the same folder and use the same extension, then 
a simple bash/batch script iterating the files feeding it into sqlite3 
cli along with the column-altering script - is by far the easiest.

If the file distribution is more complex, or table names differ, perhaps 
a custom little C program or something that allows you to pick files in 
bulk and then iterates the list pushing the SQL alterations to each one. 
I have in fact done this recently, but only on Windows in a small gui. 
If you are doing this on Windows, you are welcome to have the tool.

As an aside, may I ask why the hundreds of similar db files?
if they are in the same place, and the design doesn't absolutely require 
this fragmentation, it would be far more efficient to have one DB, in 
the table add an identifying column (perhaps the old DB name, but more 
efficient would be an Integer) to show where the row belongs to. This 
amalgamation would be easy to do too, for bulk files.

Cheers,
Ryan

[sqlite] query Benchmark

[Eduardo Morras]

On Mon, 15 Feb 2016 10:57:29 +0100
Michele Pradella  wrote:

> Sorry you are right, the test I did was with % not with a *just a 
> cut and paste error
> Anyway it's tricky because I have to tell sqlite which index to use
> in LIKE to fast search but I do not have to tell the index if start
> with % or _ because otherwise the query is not executed.. I'll handle
> it...

I have read the thread and still don't know what's your LIKE expression to 
search. If you know something about the string, you can use this tricks:

a) If you search for '%552', any string that ends with '552', you can create an 
index over reverse plate string and use that index,

b) If you search for '_552_' where the number of '_' before and after '552' are 
always the same (so, '552' begins at the same position on every plate), create 
a similar index to a) case, but rotating the letters on the string instead 
reversing it.

You know your data structure and the queries, perhaps other data transforms may 
create better indexs

> Selea s.r.l.
> 
> 
> Michele Pradella R

---   ---
Eduardo Morras 

[sqlite] Performance comparison between SQLite and SQL Server?

[ad...@shuling.net]

Hi,

I am just curious whether there is a performance comparison between SQLite
and SQL Server? Surely SQL Server will perform better on huge database with
thousands of tables(more than 10GB size). But whether SQLite will perform
better on smaller database such as one database with one table that is less
than 1GB?

Thanks

[sqlite] Correlated subquery throwing an error

[da...@andl.org]

Why not

SELECT foo.*,
  (SELECT id, DIST(foo.x, foo.y, nearest.x, nearest.y) AS d
   FROM foo AS nearest
   WHERE d < 25
   ORDER BY val, d
   LIMIT 1) AS id2
FROM foo

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Gary
Briggs
Sent: Monday, 15 February 2016 10:43 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: [sqlite] Correlated subquery throwing an error

I posted a question on stackoverflow, here:
http://stackoverflow.com/questions/35382897/implementing-a-sql-query-without
-window-functions

In short, I have a table that I'm trying to query:
CREATE TABLE foo (
   id INTEGER PRIMARY KEY,
   x REAL NOT NULL,
   y REAL NOT NULL,
   val REAL NOT NULL,
   UNIQUE(x,y));

I have a helper function, DISTANCE(x1,y1,x2,y2).
The results I'm looking for are:

"For every row in that table, I want the entire row in that same table
within a certain distance [eg 25], with the lowest "val". For rows with the
same "val", I want to use lowest distance as a tie breaker."

The answer I got on stackoverflow included this correlated subquery, but
it's not working:
SELECT foo.*,
  (SELECT id
   FROM foo AS nearest
   WHERE DIST(foo.x, foo.y, nearest.x, nearest.y) < 25
   ORDER BY val, DIST(foo.x, foo.y, nearest.x, nearest.y)
   LIMIT 1) AS id2
FROM foo

I get the error "no such column: foo.x"

I tried rewriting it to have the subquery in a WHERE clause instead [not
quite the same query, but I think the problem I have is the same]

SELECT outerfoo.*
 FROM foo outerfoo
 WHERE outerfoo.id=(SELECT id
  FROM foo AS nearest
  WHERE DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y) <= 25
  ORDER BY val, DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y)
  LIMIT 1)

And now I get the error "no such column: outerfoo.x"

Can anyone advise on what I might do?

Thank-you very much,
Gary

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

[sqlite] query Benchmark

[Michele Pradella]

Anyway thank you Simon the point of view it's clear now

Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.pradella at selea.com* 
*http://www.selea.com*
Il 15/02/2016 10.51, Simon Slavin ha scritto:
> On 15 Feb 2016, at 9:42am, Michele Pradella  
> wrote:
>
>> CREATE TABLE car_plates (Id INTEGER PRIMARY KEY AUTOINCREMENT,FileName 
>> VARCHAR(255),Plate VARCHAR(255));
>> CREATE INDEX car_plates_plate on car_plates(Plate);
>> PRAGMA case_sensitive_like=ON;
>>
>> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '*552*'); //OK
>> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '__552*'); //Error: no query solution
>> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '_*552*'); //Error: no query solution
>> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '*_552*'); /OK
>>
>> I think is a strange behavior, but I'm going to read documentation.
> An underline character _ in the LIKE field means "any character".  There is 
> no way for SQLite to use an index to find a string that starts with any 
> character.  The index provides no help at all.  The query will work perfectly 
> if you don't insist on using that index.
>
> By the way, I'm guessing from the above that you think the asterisk * means 
> "any string of characters".  It doesn't.  You should use the percent sign % 
> for that.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query Benchmark

[Michele Pradella]

Sorry you are right, the test I did was with % not with a *just a 
cut and paste error
Anyway it's tricky because I have to tell sqlite which index to use in 
LIKE to fast search but I do not have to tell the index if start with % 
or _ because otherwise the query is not executed.. I'll handle it...

Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.pradella at selea.com* 
*http://www.selea.com*
Il 15/02/2016 10.51, Simon Slavin ha scritto:
> On 15 Feb 2016, at 9:42am, Michele Pradella  
> wrote:
>
>> CREATE TABLE car_plates (Id INTEGER PRIMARY KEY AUTOINCREMENT,FileName 
>> VARCHAR(255),Plate VARCHAR(255));
>> CREATE INDEX car_plates_plate on car_plates(Plate);
>> PRAGMA case_sensitive_like=ON;
>>
>> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '*552*'); //OK
>> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '__552*'); //Error: no query solution
>> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '_*552*'); //Error: no query solution
>> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '*_552*'); /OK
>>
>> I think is a strange behavior, but I'm going to read documentation.
> An underline character _ in the LIKE field means "any character".  There is 
> no way for SQLite to use an index to find a string that starts with any 
> character.  The index provides no help at all.  The query will work perfectly 
> if you don't insist on using that index.
>
> By the way, I'm guessing from the above that you think the asterisk * means 
> "any string of characters".  It doesn't.  You should use the percent sign % 
> for that.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query Benchmark

[Michele Pradella]

Probably is documented so I'm going to read carefully, anyway if you do this

CREATE TABLE car_plates (Id INTEGER PRIMARY KEY AUTOINCREMENT,FileName 
VARCHAR(255),Plate VARCHAR(255));
CREATE INDEX car_plates_plate on car_plates(Plate);
PRAGMA case_sensitive_like=ON;

explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate  
WHERE (Plate LIKE '*552*'); //OK
explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate  
WHERE (Plate LIKE '__552*'); //Error: no query solution
explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate  
WHERE (Plate LIKE '_*552*'); //Error: no query solution
explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate  
WHERE (Plate LIKE '*_552*'); /OK

I think is a strange behavior, but I'm going to read documentation.

PS.I think another strange behavior is that if you use default COLLATION 
for the column and the default car_sensitive_like you will get always 
the slowest result in LIKE queries

Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.pradella at selea.com* 
*http://www.selea.com*
Il 13/02/2016 11.43, R Smith ha scritto:
>
>
> On 2016/02/12 6:38 PM, Michele Pradella wrote:
>> Already solved with UNION of SELECT
>>
>
> It isn't solved, it is circumvented by trial and error without 
> understanding why.
>
> I'm sure that works ok for you in this case, but the point is if you 
> do study those documents a bit more, you may grasp the reason too, and 
> perhaps make much better/faster queries in future without spending 
> hours on trial and error. Save yourself a lot of time, we think.
>
>
>
>> Il 12/02/2016 17.03, Richard Hipp ha scritto:
>>> ...//so that it is easier to understand. Perhaps a table that shows the
>>> various combinations of COLLATE and PRAGMA case_sensitive_like work
>>> together.  I dunno.  I'll take the action item to revisit the
>>> documentation and try to improve it.
>
> @Dr.Hipp: Would it be possible / difficult to perhaps improve the like 
> algorithm and pragma case-insensitive_like to take 3 values, such that 
> 0 = OFF, 1 = ON and 2 = AS PER COLUMN DEF?
>
> I realize the like function maybe doesn't know which column, nor is 
> the like operation guaranteed to happen on a column. Maybe there is 
> another way? Perhaps only from inside the query planner when making 
> plan decisions? (Currently it compares the ci-ness during planning to 
> decide on best index to use, if I'm not mistaken).
>
> What I have done is simply added a custom LIKE function that takes a 
> second parameter for case sensitivity to avoid pragma-juggling - but 
> by this, the query planner is none the wiser
>
> I find myself sometimes needing the above, though not often enough to 
> warrant requesting the feature, but it seems now maybe others have the 
> need too.
>
> Thanks,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Correlated subquery throwing an error

[Clemens Ladisch]

R Smith wrote:
> you need to alias a subquery always when it is used in a join

Not in SQLite.

> you need to explicitly limit a subquery that is a field and must only
> ever return 1 result if the where clause is ambiguous about it

Not in SQLite.  (It ignores superfluous rows, and returns NULL if there
are no rows.)


Regards,
Clemens

[sqlite] Correlated subquery throwing an error

[R Smith]

On 2016/02/15 3:01 AM, Gary Briggs wrote:
>
> This is the bit that doesn't seem to work; having
> distance(a.x,a.y,b.x,b.y) in the ORDER BY clause on the inner query is
> what appears to cause the error that it can't find a.x, from the outer query.
>
> It's not tied to it being a custom function; I can replace it with
> something else silly and get the same problem:
>
> SELECT
> a.id AS a_id,
> (SELECT b.id
>  FROM foo AS b
>  WHERE b.id!=a.id
>AND distance(a.x,a.y,b.x,b.y)<=25
>  ORDER BY b.val, (b.x-a.x)
>   FROM foo AS a
>
> no such column: foo.x

You are missing a closing bracket there, plus you need to alias a 
subquery always when it is used in a join (which isn't the case here, 
but in others you've shown it was omitted) plus you need to explicitly 
limit a subquery that is a field and must only ever return 1 result if 
the where clause is ambiguous about it, plus you cannot refer an 
outer-select in a subquery in the aggregate/ordering clauses because 
there is not a gauranteed 1-to-1 relation (I'm not sure if this is the 
standard saying so or not, but it doesn't work in any DB system I've 
used). You can get around it with CTE's and the like.

[sqlite] Performance comparison between SQLite and SQL Server?

[R Smith]

On 2016/02/15 5:21 AM, admin at shuling.net wrote:
> Hi,
>
> I am just curious whether there is a performance comparison between SQLite
> and SQL Server? Surely SQL Server will perform better on huge database with
> thousands of tables(more than 10GB size). But whether SQLite will perform
> better on smaller database such as one database with one table that is less
> than 1GB?
>
> Thanks

Some other replies have hinted at this already, but to expand: The one 
isn't faster than the other in all cases. There are ways in which SQLite 
is much faster even on a 100GB database - the "Lite" in SQLite doesn't 
mean database size - it pertains more to the available function-set. It 
can handle the very large DB's just as well as any other.

Where SQLite lacks (due to the "Lite"-ness) is in not having user-access 
control, not having programmability (stored procedures and functions, 
but then you can add custom functions to SQLite in C even, which you 
can't easily do with the others, especially not with MSSQL). The largest 
difference however, is that an SQLite connection operates on (talks-to) 
a file, and the others usually talk to a server.

The main advantage of SQLite is that it can be used for an application 
file format to your program, complete as if it was a database in itself 
(which, actually, it is), and moreover, you can embed the entire DB in 
your application and on your hardware etc - like the billions of 
handheld devices, phones, tablets, etc. that uses SQLite daily.

For more information, see:
http://www.sqlite.org/whentouse.html

To answer your speed question - it depends on data shape, size, IO 
access speeds, Memory on the generation machine etc. Import big datasets 
in both MSSQL and SQlite, run some queries, chances are some queries be 
slightly faster in SQLite, and some are slightly faster in MSSQL.  
Single query performance is not really the driver of that decision.

Cheers,
Ryan

[sqlite] Why SQLite does not use a web-based forum?

[Tim Streater]

On 15 Feb 2016 at 01:19, admin at shuling.net wrote:

> Why SQLite does not utilize a web-based forum for all users discuss
> problems? I think that will be more convenient and can help more people.

Anyone using SQLite should be able to cope with a mailing list. A list such as 
this is much quicker and more convenient than any web-based forum.

--
Cheers  --  Tim

[sqlite] query Benchmark

[Swithun Crowe]

Hello

MP> Anyway it's tricky because I have to tell sqlite which index to use in
MP> LIKE to fast search but I do not have to tell the index if start with
MP> % or _ because otherwise the query is not executed.. I'll handle it...

I'm not sure if other people have suggested it already, but you can create
another field in your table to store the text you want to search reversed,
plus another index for this reversed field.

Then you can match rows using LIKE with the wildcard at the end of your
query while still using an index.

Swithun.

[sqlite] applyng schema changes to several databases

[Luca Ferrari]

Hi all,
this could sound trivial but I've got a few hundreds SQLite 3 database
files, all with the same schema, that I need to alter adding a few
columns here and there.
While I'm pretty sure a simple sheel script that will execute, file
per file, the alter table (within a transaction) will do the job I'm
wondering if there's a better approach or a more automated one.

Any suggestion is welcome.

Thanks,
Luca

[sqlite] query Benchmark

[Simon Slavin]

On 15 Feb 2016, at 9:42am, Michele Pradella  
wrote:

> CREATE TABLE car_plates (Id INTEGER PRIMARY KEY AUTOINCREMENT,FileName 
> VARCHAR(255),Plate VARCHAR(255));
> CREATE INDEX car_plates_plate on car_plates(Plate);
> PRAGMA case_sensitive_like=ON;
> 
> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate WHERE 
> (Plate LIKE '*552*'); //OK
> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate WHERE 
> (Plate LIKE '__552*'); //Error: no query solution
> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate WHERE 
> (Plate LIKE '_*552*'); //Error: no query solution
> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate WHERE 
> (Plate LIKE '*_552*'); /OK
> 
> I think is a strange behavior, but I'm going to read documentation.

An underline character _ in the LIKE field means "any character".  There is no 
way for SQLite to use an index to find a string that starts with any character. 
 The index provides no help at all.  The query will work perfectly if you don't 
insist on using that index.

By the way, I'm guessing from the above that you think the asterisk * means 
"any string of characters".  It doesn't.  You should use the percent sign % for 
that.

Simon.

[sqlite] Why SQLite does not use a web-based forum?

[ad...@shuling.net]

Hi,

Why SQLite does not utilize a web-based forum for all users discuss
problems? I think that will be more convenient and can help more people.

Thanks

[sqlite] Performance comparison between SQLite and SQL Server?

[Clemens Ladisch]

admin at shuling.net wrote:
> I am just curious whether there is a performance comparison between SQLite
> and SQL Server?

That depends on the data, and the software, and the hardware.

In other way: you have to measure yourself.

> Surely SQL Server will perform better on huge database with thousands
> of tables(more than 10GB size).

If you write a query that needs to read all the data, both databases are
limited by the I/O speed.

Otherwise, the two databases will have similar performance, unless
you're using a query that can use some optimization that is implemented
only in one of them.

SQLite uses a much simpler locking scheme that can be faster if there
aren't concurrent accesses.  SQL Server has higher concurrency.

This is not necessarily dependent on the size of the database.


Regards,
Clemens

[sqlite] Correlated subquery throwing an error

[Clemens Ladisch]

Gary Briggs wrote:
>> SELECT
>>   a.id AS a_id,
>>   (SELECT b.id
>>FROM foo AS b
>>WHERE b.id!=a.id
>>  AND distance(a.x,a.y,b.x,b.y)<=25
>>ORDER BY b.val, distance(a.x,a.y,b.x,b.y))
>> FROM foo AS a
>
> This is the bit that doesn't seem to work; having
> distance(a.x,a.y,b.x,b.y) in the ORDER BY clause on the inner query is
> what appears to cause the error that it can't find a.x, from the outer query.

I don't know why correlated subqueries cannot use values from the outer
query in the ORDER BY or LIMIT clauses; this does not look as if it were
by design.

Anyway, I got it to work with another indirection:

SELECT foo.*,
   (SELECT id
FROM (SELECT id,
 x,
 y,
 foo.x AS foo_x,
 foo.y AS foo_y,
 val
  FROM foo)
WHERE DIST(foo_x, foo_y, x, y) < 25
ORDER BY val, DIST(foo_x, foo_y, x, y)
LIMIT 1
   ) AS id2
FROM foo


Regards,
Clemens

[sqlite] Performance comparison between SQLite and SQL Server?

[Rob Willett]

To add to the responses, we use SQLite for our main database which is 
currently around 60GB in size, so size isn?t an issue for SQLite. I am 
aware of other people with far, far larger SQLite databases. We did a 
quick test and ran up to a couple of hundred DB?s of data in SQLIte 
and it seemed fine.

We chose SQLite over other databases as

1. We didn?t need concurrency. All our data is written and read by a 
single process.
2. Its very lightweight. We can move the database around by simply 
copying the file which is fantastic for our use.
3. The speed is very, very fast. We haven?t found any significant 
speed differences FOR OUR USE CASE between Sqlite and other ?bigger? 
databases. YMMV.
4. The support is top notch. I have brought and paid for govt scale 
databases for governments and to be honest the support for SQLite is 
just as good, and to be honest I would say better than Big Red or Big 
Blue (and I used to work for Big Blue).

Thats not to say that SQLite is perfect, its not, however no database is 
perfect as everyones usage is different. I certainly wouldn?t state 
that SQLite is the answer to everyones problems and I can state with 
absolute certainty that DB2 is also not the answer for every use.

We are moving into a different phase of our development and we are 
investigating PostGIS for some of our work now, as that *MAY* be a 
better tool for some of our more exotic spatial queries and analysis. No 
reflection on SQLite but its a case of finding the right tool for the 
right job. After saying that we *may* end up using SQLite for this area 
as well.

Rob

On 15 Feb 2016, at 8:20, R Smith wrote:

> On 2016/02/15 5:21 AM, admin at shuling.net wrote:
>> Hi,
>>
>> I am just curious whether there is a performance comparison between 
>> SQLite
>> and SQL Server? Surely SQL Server will perform better on huge 
>> database with
>> thousands of tables(more than 10GB size). But whether SQLite will 
>> perform
>> better on smaller database such as one database with one table that 
>> is less
>> than 1GB?
>>
>> Thanks
>
> Some other replies have hinted at this already, but to expand: The one 
> isn't faster than the other in all cases. There are ways in which 
> SQLite is much faster even on a 100GB database - the "Lite" in SQLite 
> doesn't mean database size - it pertains more to the available 
> function-set. It can handle the very large DB's just as well as any 
> other.
>
> Where SQLite lacks (due to the "Lite"-ness) is in not having 
> user-access control, not having programmability (stored procedures and 
> functions, but then you can add custom functions to SQLite in C even, 
> which you can't easily do with the others, especially not with MSSQL). 
> The largest difference however, is that an SQLite connection operates 
> on (talks-to) a file, and the others usually talk to a server.
>
> The main advantage of SQLite is that it can be used for an application 
> file format to your program, complete as if it was a database in 
> itself (which, actually, it is), and moreover, you can embed the 
> entire DB in your application and on your hardware etc - like the 
> billions of handheld devices, phones, tablets, etc. that uses SQLite 
> daily.
>
> For more information, see:
> http://www.sqlite.org/whentouse.html
>
> To answer your speed question - it depends on data shape, size, IO 
> access speeds, Memory on the generation machine etc. Import big 
> datasets in both MSSQL and SQlite, run some queries, chances are some 
> queries be slightly faster in SQLite, and some are slightly faster in 
> MSSQL.  Single query performance is not really the driver of that 
> decision.
>
> Cheers,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance comparison between SQLite and SQL Server?

[Donald Shepherd]

They're intended for fundamentally different uses.  It's like asking what's
more energy efficient for cooking dinner - a wok or an oven.

On Mon, 15 Feb 2016 at 14:21  wrote:

> Hi,
>
> I am just curious whether there is a performance comparison between SQLite
> and SQL Server? Surely SQL Server will perform better on huge database with
> thousands of tables(more than 10GB size). But whether SQLite will perform
> better on smaller database such as one database with one table that is less
> than 1GB?
>
> Thanks
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Performance comparison between SQLite and SQL Server?

[Warren Young]

On Feb 15, 2016, at 1:02 AM, Clemens Ladisch  wrote:
> 
> SQLite uses a much simpler locking scheme that can be faster if there
> aren't concurrent accesses.  SQL Server has higher concurrency.

SQLite also doesn?t have the IPC overhead of a client/server DBMS, so if you 
don?t need concurrency or remote access, SQLite can be faster, since all data 
is moved around inside a single process.

[sqlite] Why SQLite does not use a web-based forum?

[Simon Slavin]

On 15 Feb 2016, at 1:19am, admin at shuling.net wrote:

> Hi,
> 
> Why SQLite does not utilize a web-based forum for all users discuss
> problems? I think that will be more convenient and can help more people.

You can access this mailing list using Nabble:



And you can find gmane.comp.db.sqlite.general in the expected place.

Simon.