[sqlite] Final preparations for the release of System.Data.SQLite v1.0.85.0 have begun...

[Joe Mistachkin]

If you have any issues with the current code, please report them via this
mailing
list (and/or by creating a ticket on "https://system.data.sqlite.org/;)
prior to
this Friday, April 12th.

Thanks.

--
Joe Mistachkin

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

Re: [sqlite] SQL Logic error or missing database

[Pavel Ivanov]

Do you know that VS2012 has known optimizer bugs?

Pavel
On Apr 6, 2013 5:01 AM, "ibrahim"  wrote:

> On 05.04.2013 17:01, Dan Kennedy wrote:
>
>> On 04/05/2013 09:08 PM, Rob Collie wrote:
>>
>>> Yeap, I'm on Visual Studio 2012. I've created a console app:
>>>
>>>
>>>   sqlite3 *oDatabase;
>>>   int returnValue;
>>>   returnValue = sqlite3_open_v2("file://C:/**Newfolder/testing.db",
>>> , SQLITE_OPEN_CREATE, NULL);
>>>   if (returnValue != SQLITE_OK )
>>>   {
>>>//sqlite3_close(oDatabase);
>>>return returnValue ;
>>>   }
>>>   int anyKey;
>>>   return 0;
>>>
>>> It returns 21. Checking the other project, the open actually does return
>>> 21
>>> too.
>>>
>>
>> This one is returning SQLITE_MISUSE because the SQLITE_OPEN_READWRITE
>> flag is not being passed. It seems quite odd that the other code
>> would do the same though.
>>
>>
>>
>>
>>
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>>
>
> Thanks Dan I just copied and paste his code.
>
> correction :
>
> returnValue = sqlite3_open_v2 ("C:\\Newfolder\\testing.db", ,
> SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL) ;
>
> and to make the test just more simple :
>
> returnValue = sqlite3_open ("C:\\Newfolder\\testing.xyz", ) ;
>
> try the different file extension could be a problem on some systems.
> __**_
> 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] Partial sorting

[Baruch Burstein]

I was thinking more of general (not just LIMIT 1) optimization:
http://en.wikipedia.org/wiki/Partial_sorting

On Sun, Apr 7, 2013 at 6:16 PM, Simon Slavin  wrote:

>
> On 7 Apr 2013, at 3:51pm, Stephen Chrzanowski  wrote:
>
> > I don't know if it'd be an interesting optimization.  Who's to say what
> the
> > order ends up as prior to the sort?  Take for example if I have a list of
> > dollars and cents being returned from a query.  I want the 5 top highest
> > cost items out of 6 possibilities, if I keep the top 5 unsorted, item 6
> > could have been the very top of the list.  Thereby making the desired
> > result wrong.
>
> The optimization I was thinking of was simpler than that.  An example
> would have been ORDER BY score DESC LIMIT 1.  Theoretically the engine
> could notice that all it needed was the top-scoring result, and just do a
> quick scan for the highest score, which would be less effort than sorting
> all the rows into order.  I can imagine one of the big heavy-duty SQL
> engines doing this but perhaps not SQLite because it would require a lot of
> extra code and introduce a lot of test cases.
>
> A server/client SQL system can do a kind of progressive indexing because
> it runs on a server and can do a lot of processing without tying up the
> client computer.  So with the above type of SELECT the engine will return
> the first result as the client asks for it.  Then it will immediately try
> to find the second result, then the third, etc. aiming to have each result
> ready before the client asks for it.  I believe MySQL does this under
> certain conditions.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

[Max Vlasov]

On Sun, Apr 7, 2013 at 3:55 PM, Chris Smith  wrote:

> Possibly related:
> http://en.wikipedia.org/wiki/Thrashing_(computer_science)
>
>
That's an interesting direction. Surprisingly if one query
... site:microsoft.com "Thrashing" "memory-mapped"...
on google, he or she would find a forum topic "Memory mapped file
performance" where the first contributor to answers is Igor Tandetnik and
we knows that Igor gave very valuable answers also on this list, so he
(having good knowledge about both win32 and sqlite) could also give his
expert opinion on this topic .

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

Re: [sqlite] Partial sorting

[Simon Slavin]

On 7 Apr 2013, at 6:56pm, Richard Hipp  wrote:

> It uses a btree as a priority queue.  If you have LIMIT N, then it
> initially starts putting results into the btree until the btree contains N
> entries.  Then for each additional row, it first adds the value to the
> btree, then removes the largest entry in the btree.  Hence the btree always
> holds the smallest N entries seen so far. Once all is finished, it walks
> the btree to output the results.
> 
> So with limit N, it never stores more than N results at one time.

That's neat.

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

Re: [sqlite] 64bit compatibility warnings

[Simon Slavin]

On 7 Apr 2013, at 6:26pm, f...@cetussoft.com wrote:

>> I think that in general it might be a good idea to update the code to
>> not produce any 64 bit portability warnings, so that we know for sure,
>> that compiling 64 bit does not introduce any 64 bit side effects or possible 
>> bugs.
> 
> ...as long as doing so does not break 32-bit code...

One of the problems with compiler warnings is that different compilers generate 
warnings about different things.  So it's not "Get rid of 64-bit warnings" it's 
"Try to get rid of 64--bit warnings in GCC without creating more of them in LCC 
and Visual C+, and try to get rid of them with all the different directives 
most people use most of the time.".  And it turns out that this is very 
difficult:



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

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

[Chris Smith]

Possibly related:
http://en.wikipedia.org/wiki/Thrashing_(computer_science)


On Sun, Apr 7, 2013 at 6:25 AM, Jean-Christophe Deschamps
wrote:

> Hi Max,
>
> The link is dead. I'd like to know more about this so can you provide us
> with a live link?
>
> Thanks.
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
>


-- 
History tells all of us that nobody gets a pass.  Your [country's]
perpetual existence is not guaranteed.  If you do not believe in yourself,
and believe that you're better than the alternative, and have the
educational skills to come to that empirical judgment, then there is no
reason for you to continue, and often you won't. --Victor Davis Hanson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Partial sorting

[Richard Hipp]

On Sun, Apr 7, 2013 at 9:08 AM, Baruch Burstein wrote:

> If I issue a select statement with a ORDER BY clause and a LIMIT clause,
> does SQLite do a full sort (assuming no index) and then return the first X
> rows, or just a partial sort to get the first X sorted results?
>

It uses a btree as a priority queue.  If you have LIMIT N, then it
initially starts putting results into the btree until the btree contains N
entries.  Then for each additional row, it first adds the value to the
btree, then removes the largest entry in the btree.  Hence the btree always
holds the smallest N entries seen so far. Once all is finished, it walks
the btree to output the results.

So with limit N, it never stores more than N results at one time.

-- 
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] 64bit compatibility warnings

[fred]

> I think that in general it might be a good idea to update the code to
> not produce any 64 bit portability warnings, so that we know for sure,
> that compiling 64 bit does not introduce any 64 bit side effects or possible 
> bugs.

...as long as doing so does not break 32-bit code...

Fred

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

Re: [sqlite] Partial sorting

[Clemens Ladisch]

Stephen Chrzanowski wrote:
> I don't know if it'd be an interesting optimization.  Who's to say what the
> order ends up as prior to the sort?

When doing a merge sort, it would actually be possible to abort the very
last merge step when the first LIMIT entries have been merged.

I might be wrong, but it looks as if the implementation of OP_SorterNext
(sqlite3VdbeSorterNext) does this, by doing the last merge step only for
the next record that is currently being needed.

> Indexes also aren't used for sorting, only for finding relevant data.

  sqlite> create table t(x,y);
  sqlite> explain query plan select * from t order by x;
  0|0|0|SCAN TABLE t (~100 rows)
  0|0|0|USE TEMP B-TREE FOR ORDER BY
  sqlite> create index tx on t(x);
  sqlite> explain query plan select * from t order by x;
  0|0|0|SCAN TABLE t USING INDEX tx (~100 rows)

(BTW, that "TEMP B-TREE" is an in-memory index, and gets written out to
disk for a merge sort only when it becomes too big.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 64bit compatibility warnings

[Alexandr Němec]

Dear all,
 
when compiling the latest 3.7.16.1 version of SQLite, the VS compiler complains 
about some 64 bit portability issues, see below. May these warning be safely 
ignored when compiling 64 bit? All warnings refer to the sqlite3.c amalgamation 
file. Thanks in advance.
 
Line 6766   u.bc.r.flags = (u16)(UNPACKED_INCRKEY * (1 & (u.bc.oc - 
OP_SeekLt)));  WARNING: conversion from 'u16' to 'u8', possible loss of 
data
Line 71133 iBuf = p->iReadOff % p->nBuffer; WARNING: 
conversion from 'i64' to 'int', possible loss of data
Line 71209 iBuf = p->iReadOff % p->nBuffer;     WARNING: 
conversion from 'i64' to 'int', possible loss of data
Line 71286 iBuf = iStart % nBuf;   
WARNING: conversion from 'i64' to 'int', possible loss of data
Line 71574 p->iBufEnd = p->iBufStart = (iStart % nBuf); WARNING: 
conversion from 'i64' to 'int', possible loss of data
 
I think that in general it might be a good idea to update the code to not 
produce any 64 bit portability warnings, so that we know for sure, that 
compiling 64 bit does not introduce any 64 bit side effects or possible bugs. 
Thanks.
 
Continue your great SQLite work.
 
Alex
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Partial sorting

[Simon Slavin]

On 7 Apr 2013, at 3:51pm, Stephen Chrzanowski  wrote:

> I don't know if it'd be an interesting optimization.  Who's to say what the
> order ends up as prior to the sort?  Take for example if I have a list of
> dollars and cents being returned from a query.  I want the 5 top highest
> cost items out of 6 possibilities, if I keep the top 5 unsorted, item 6
> could have been the very top of the list.  Thereby making the desired
> result wrong.

The optimization I was thinking of was simpler than that.  An example would 
have been ORDER BY score DESC LIMIT 1.  Theoretically the engine could notice 
that all it needed was the top-scoring result, and just do a quick scan for the 
highest score, which would be less effort than sorting all the rows into order. 
 I can imagine one of the big heavy-duty SQL engines doing this but perhaps not 
SQLite because it would require a lot of extra code and introduce a lot of test 
cases.

A server/client SQL system can do a kind of progressive indexing because it 
runs on a server and can do a lot of processing without tying up the client 
computer.  So with the above type of SELECT the engine will return the first 
result as the client asks for it.  Then it will immediately try to find the 
second result, then the third, etc. aiming to have each result ready before the 
client asks for it.  I believe MySQL does this under certain conditions.

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

Re: [sqlite] Partial sorting

[Stephen Chrzanowski]

I don't know if it'd be an interesting optimization.  Who's to say what the
order ends up as prior to the sort?  Take for example if I have a list of
dollars and cents being returned from a query.  I want the 5 top highest
cost items out of 6 possibilities, if I keep the top 5 unsorted, item 6
could have been the very top of the list.  Thereby making the desired
result wrong.

AFAIK, the Limit command is, without question, the final decision on what
is being sent to the caller... or parent-query... or.. output... or...
whatever is selected... etc, etc.  Formatting of the data output via the
CLI is done within the CLI, not the result set. (IE: Pipe, tab, or space
delimiters)

Indexes also aren't used for sorting, only for finding relevant data.  True
that if you include an index against a field and return results on that
without an ORDER BY, you MIGHT get back a sorted list, but that is due to
the fact that the index could be sorted, but you can't guarantee that.
ORDER BY would probably end up being near the end of the processing steps.
(I'm still going through the 4meg worth of C code to see what its about)

The direct answer to the question is: SQLite does the reordering, then only
returns the first X number of rows.

On Sun, Apr 7, 2013 at 9:17 AM, Simon Slavin  wrote:

>
> On 7 Apr 2013, at 2:08pm, Baruch Burstein  wrote:
>
> > If I issue a select statement with a ORDER BY clause and a LIMIT clause,
> > does SQLite do a full sort (assuming no index) and then return the first
> X
> > rows, or just a partial sort to get the first X sorted results?
>
> The ORDER BY clause does not know about the LIMIT clause.  So even with a
> LIMIT 1 it doesn't know that all it needs to do is pick the lowest value
> and not bother sorting the rest.
>
> A limit clause just makes _step() return "I'm run out of result rows"
> sooner than normal.  It's like doing
>
> _prepare()
> _step()
> _step()
> _finalize()
>
> stopping _step() after a few times, before you run out of results.  So the
> LIMIT clause has no influence on any ORDER BY clause.
>
> Actually what you suggest is an interesting optimization.  But I think it
> would introduce too much extra code to be appropriate for SQLite.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

[fred]

> The link is dead. I'd like to know more about this so can you provide us
> with a live link?

The problem is the closing parenthesis has become part of the link in Max's
first email - remove it in your browser URL window and the link works fine.

Or -

http://social.msdn.microsoft.com/Forums/en-US/windowsgeneraldevelopmentissues/thread/81dd029f-2f55-49f2-bd02-1a8ceb0373eb

Fred

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

Re: [sqlite] Partial sorting

[Simon Slavin]

On 7 Apr 2013, at 2:08pm, Baruch Burstein  wrote:

> If I issue a select statement with a ORDER BY clause and a LIMIT clause,
> does SQLite do a full sort (assuming no index) and then return the first X
> rows, or just a partial sort to get the first X sorted results?

The ORDER BY clause does not know about the LIMIT clause.  So even with a LIMIT 
1 it doesn't know that all it needs to do is pick the lowest value and not 
bother sorting the rest.

A limit clause just makes _step() return "I'm run out of result rows" sooner 
than normal.  It's like doing

_prepare()
_step()
_step()
_finalize()

stopping _step() after a few times, before you run out of results.  So the 
LIMIT clause has no influence on any ORDER BY clause.

Actually what you suggest is an interesting optimization.  But I think it would 
introduce too much extra code to be appropriate for SQLite.

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

[sqlite] Partial sorting

[Baruch Burstein]

If I issue a select statement with a ORDER BY clause and a LIMIT clause,
does SQLite do a full sort (assuming no index) and then return the first X
rows, or just a partial sort to get the first X sorted results?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

[Max Vlasov]

don't know what's wrong with the link, I'm clicking the one from gmail
thread and it works. Other way is to google [Unresponsive system under some
file-mapping related conditions] and the first result is the thread link

Max


On Sun, Apr 7, 2013 at 2:25 PM, Jean-Christophe Deschamps
wrote:

> Hi Max,
>
> The link is dead. I'd like to know more about this so can you provide us
> with a live link?
>
> Thanks.
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

[Jean-Christophe Deschamps]

Hi Max,

The link is dead. I'd like to know more about this so can you provide 
us with a live link?


Thanks.

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

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

[Max Vlasov]

On Thu, Apr 4, 2013 at 4:02 PM, Richard Hipp  wrote:

> By making use of memory-mapped I/O, the current trunk of SQLite (which will
> eventually become version 3.7.17 after much more refinement and testing)
> can be as much as twice as fast, on some platforms and under some
> workloads.  We would like to encourage people to try out the new code and
> report both success and failure.
>


Not particulary about this draft version, but about my experience with
memory mapped files on Windows If you don't mind .

When I worked with memory-mapped files on Windows two years ago, I
implemented a library for accessing files virtually unlimited in size with
sliding-view approach. There was an interesting effect affecting the system
as a whole. It's when  I write sequentially and starting some point the
system became unresponsive as a whole. This is an important point, not the
application that wrote to the file, the whole system, so no Alt-Tab, no
blinking caret in another application and sometimes even no mouse moving. I
tried to report and MS forums (
http://social.msdn.microsoft.com/Forums/en-US/windowsgeneraldevelopmentissues/thread/81dd029f-2f55-49f2-bd02-1a8ceb0373eb),
but seems like this wasn't noticed. I added a small procedure to show the
effect at the forum topic in pascal (it's sill there) that can easily be
ported to any other language supporting windows api directly.

Right now I tried to reproduce this while writing this message. The machine
is windows 64 bit 4 Gb memory. I started the program writing the the file
until 10Gb. And no surprise, at about 5-6 Gb, the notepad (another
application), stopped responding on my key presses, the caret stopped
blinking and Alt-tab and taskbar didn't work for about a minute. So I could
not do anything (!) on my computer for about minute or so while other
application did something using official documented API.

I don't know whether such scenario is possible with sqlite. Only that on
Windows memory-mapped files are still implemented as a very special entity,
sometimes having exclusively more permissions than other entities
regardless of permissions of the application that uses it. Probably I
should do some particular sqlite-specific tests to find out whether this
affects sqlite but before this I wanted to share this information.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users