Re: [sqlite] Using COUNT(*) in unusual circumstances

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/10/11 18:52, Simon Slavin wrote:
> I'll just use LIMIT 51 on my query and if I get 51 results back I'll
> know there are too many for the query to be useful.

Incidentally you don't need to add a limit on the query.  In the code that
calls sqlite3_step just track if the 51st consecutive call returns
SQLITE_ROW or SQLITE_DONE.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6Py9QACgkQmOOfHg372QROzgCglOXXEa6lkXWZx99VCsZpg52m
4XIAoIcfYhVKlus34jq3zpw+QS9SNkV3
=Nf1K
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Simon Slavin]

On 8 Oct 2011, at 2:27am, Roger Binns wrote:

> Although various people keep mentioning it I haven't seen you acknowledge
> using EXPLAIN QUERY PLAN.  It would be nice to hear if it turns out to be
> helpful especially as it doesn't require mucking with the query itself.

Yes it was a good suggestion to my original problem, but now I know LIMIT works 
properly I don't think I need it.  I'll just use LIMIT 51 on my query and if I 
get 51 results back I'll know there are too many for the query to be useful.

My original query was posted because I thought SQLite always processed all rows 
even if you used LIMIT.

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

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/10/11 15:52, Simon Slavin wrote:
> But I was sure I had read that in SQLite the full result set was
> retrieved even if LIMIT was used.

For the outer query all LIMIT does is cause sqlite3_step to return done
after limit number of calls.  ie you could omit LIMIT and just call
finalize after you've had as many results as you want and it wouldn't make
any difference.  Similarly the first offset number of times when a row
would be returned are skipped.

Although various people keep mentioning it I haven't seen you acknowledge
using EXPLAIN QUERY PLAN.  It would be nice to hear if it turns out to be
helpful especially as it doesn't require mucking with the query itself.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6PpwoACgkQmOOfHg372QS/7gCdEtt7iuh1fOkZDP5lw/WMKEm4
OPEAoNOH6+A88h05+YC9XAFYJeVO0X1d
=AJuc
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Nico Williams]

On Fri, Oct 7, 2011 at 5:52 PM, Simon Slavin  wrote:
> Okay, I understand why defining an ORDER BY requires the entire result set to 
> be retrieved.  I had intended to remove ORDER BY when I used COUNT(*), though 
> I didn't mention that.

If the ORDER BY can be satisfied entirely by an index, then that's not true.

You can see whether this is the case from the query plan.

So you could reject queries for which you can't use OFFSET & LIMIT efficiently.

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

Re: [sqlite] System.Data.SQLite Exception with wrong password

[Joe Mistachkin]

Marco Cosentino wrote:
> 
> The ErrorCode is set to "NotADatabase".
> Wouldn't it more correct if this code is set to something like
> SQLiteErrorCode.Auth or the Exception is more specialized?
> 

The exceptions thrown by System.Data.SQLite reflect the underlying
error code returned from the native core SQLite library.  In the
case you mention, the exception is technically correct even though
it may seem counterintuitive at first glance.

--
Joe Mistachkin

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

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Kees Nuyt]

On Fri, 7 Oct 2011 19:57:09 +0100, Simon Slavin
 wrote:

>On 7 Oct 2011, at 7:17pm, Roger Binns wrote:
>
>> On 07/10/11 09:52, Simon Slavin wrote:
>>> "Do you really want to see all 50,000 entries that that search would
>>> return ?".  If this kind of search returns more than 100 records,
>>> there's no point in doing it at all.
>> 
>> You can solve this at the user interface layer and there is no need to
>> calculate how many matching records there are.
>> 
>> Lets say you can show 50 results on the screen.  Run the query but leave
>> it open until hitting the 51st result.  Show the 50 results on the screen
>> with a More button.
>
> It's not quite the problem I have.  My problem is that there is
> a huge amount of data involved in finding the 50,000 matches
> and merely processing the SELECT is very slow.  And SQLite
> has to find all 50,000 before it can show the first screen
> of matches.
>
> Imagine you're watching _CSI_ on TV and they have a test that
> identifies possible matches to something ... car colours and
> tyres perhaps.  If there are only 50 possible matches,
> it's worth getting printouts and eliminating them one by one.
> If there are 50,000 possible matches there's no point.
>
> My problem really seems to be with the way SQLite implements
> LIMIT n.  If I understand previous posts on this list correctly,
> instead of finding only the first n records, it does the entire
> search first, then returns the first n of the results it found.

What the first n rows are is determined by the ORDER BY clause.

> With a standard implementation I would simply do a search with
> a LIMIT 51 clause.  If 51 results are returned then I know the
> search is useless.  But even with a LIMIT clause SQLite will
> still do all the fetching involved in all 50,000 records.

Read about some optimization options on: 
http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite WPF application (designer view) toolbox greyed out

[Joe Mistachkin]

Christoph P.U. Kukulies wrote:
>
> Any clues why this doesn't work for WPF apps?
>

As far as I know, there is no specific designer support for WPF in the
System.Data.SQLite project (yet).  However, you can of course still use
all the System.Data.SQLite ADO.NET classes from inside a WPF project in
C# or VB.NET code.

--
Joe Mistachkin

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

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Simon Slavin]

On 7 Oct 2011, at 10:27pm, Roger Binns wrote:

> On 07/10/11 11:57, Simon Slavin wrote:
>> My problem really seems to be with the way SQLite implements LIMIT n.
>> If I understand previous posts on this list correctly, instead of
>> finding only the first n records, it does the entire search first, then
>> returns the first n of the results it found.
> 
> That is completely incorrect.  Each time you call sqlite3_step it finds
> the next row that matches the query and no more.  This is why SQLite uses
> that virtual machine (VDBE) stuff.  It needs to execute some code to find
> a matching row, return it, and then allow another call to get the next
> row.  This requires some way of encapsulating the processing state of a
> query so it can be resumed from where it was left off in order to get the
> next row.
> 
> The one fly in the ointment is if the results are sorted.  For example if
> there are 10,000 result rows then there is no way to return the first 100
> without getting all result rows since doing a sort requires you have all
> the data first to sort.

Okay, I understand why defining an ORDER BY requires the entire result set to 
be retrieved.  I had intended to remove ORDER BY when I used COUNT(*), though I 
didn't mention that.

But I was sure I had read that in SQLite the full result set was retrieved even 
if LIMIT was used.  Obviously I misremembered this.  If so, very good.  In that 
case, I can see if I have too many rows returned for everything that doesn't 
use a sub-SELECT or LIMIT by ...

Replace everything up to the first "FROM" by "SELECT COUNT(*) ".
Looking for ORDER BY and removing it and anything after it.
Add my own LIMIT clause.

Fortunately, the user-interface I'm using doesn't allow the use of sub-selects 
or LIMIT.  Or UNION/INTERSECT/EXCEPT.  Or DISTINCT.  Probably some others I 
didn't think of.

Thank you to everyone who has contributed to answering my question.

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

Re: [sqlite] how do I cd on Mac OX 10?

[TR Shaw]

On Oct 7, 2011, at 5:54 PM, TR Shaw wrote:

> 
> On Oct 7, 2011, at 5:46 PM, Mary Andes wrote:
> 
>> Last login: Fri Oct  7 17:26:34 on console
>> Mary-Andess-MacBook-Air:~ mjandes$ sqlite3
>> SQLite version 3.7.5
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> cd /Users/mjandes/SQLite3 and Hedis
>>  ...> 
>> I have a Mac Book Air. Lion operating system. I can get onto the terminal 
>> and get onto sqlite3. 
>> But, then I get stuck in trying to tell it to point to a particular 
>> directory. I've tried the above command and it doesn't work. 
>> Can anyone help me? 
> 
> 
> Looks like you have spaces in your path. So either quote the whole path of \ 
> the spaces
> 
>> cd "/Users/mjandes/SQLite3 and Hedis"
>> cd /Users/mjandes/SQLite3\ and\ Hedis
> 
> 
> 
> Standard bash.

My mistake you were trying within sqlite. You need to do the cd in bask (eg 
OSX's terminal shell) And you need to escape the path as I stated.

Tom

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

Re: [sqlite] how do I cd on Mac OX 10?

[Petite Abeille]

On Oct 7, 2011, at 11:46 PM, Mary Andes wrote:

> Can anyone help me? 

You cannot 'cd' from inside SQLite shell. 

If you wish to open a specific database, simply point sqlite3 to it before hand:

% sqlite3 /path/to/my/db


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

Re: [sqlite] how do I cd on Mac OX 10?

[TR Shaw]

On Oct 7, 2011, at 5:46 PM, Mary Andes wrote:

> Last login: Fri Oct  7 17:26:34 on console
> Mary-Andess-MacBook-Air:~ mjandes$ sqlite3
> SQLite version 3.7.5
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> cd /Users/mjandes/SQLite3 and Hedis
>   ...> 
> I have a Mac Book Air. Lion operating system. I can get onto the terminal and 
> get onto sqlite3. 
> But, then I get stuck in trying to tell it to point to a particular 
> directory. I've tried the above command and it doesn't work. 
> Can anyone help me? 


Looks like you have spaces in your path. So either quote the whole path of \ 
the spaces

>  cd "/Users/mjandes/SQLite3 and Hedis"
>  cd /Users/mjandes/SQLite3\ and\ Hedis



Standard bash.

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

[sqlite] how do I cd on Mac OX 10?

[Mary Andes]

Last login: Fri Oct  7 17:26:34 on console
Mary-Andess-MacBook-Air:~ mjandes$ sqlite3
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> cd /Users/mjandes/SQLite3 and Hedis
   ...> 
I have a Mac Book Air. Lion operating system. I can get onto the terminal and 
get onto sqlite3. 
But, then I get stuck in trying to tell it to point to a particular directory. 
I've tried the above command and it doesn't work. 
Can anyone help me? 

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

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/10/11 11:57, Simon Slavin wrote:
> My problem really seems to be with the way SQLite implements LIMIT n.
> If I understand previous posts on this list correctly, instead of
> finding only the first n records, it does the entire search first, then
> returns the first n of the results it found.

That is completely incorrect.  Each time you call sqlite3_step it finds
the next row that matches the query and no more.  This is why SQLite uses
that virtual machine (VDBE) stuff.  It needs to execute some code to find
a matching row, return it, and then allow another call to get the next
row.  This requires some way of encapsulating the processing state of a
query so it can be resumed from where it was left off in order to get the
next row.

The one fly in the ointment is if the results are sorted.  For example if
there are 10,000 result rows then there is no way to return the first 100
without getting all result rows since doing a sort requires you have all
the data first to sort.

But as I mentioned you can add together the numbers from EXPLAIN QUERY
PLAN which will at least give you an idea of probable order of magnitude
of the results (eg if there are tens, thousands or millions).

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6PbtIACgkQmOOfHg372QSczQCfdEFFqE798O53wHsdPLYOkkua
9rIAnj/4E2BZLqdD1W+X1k0WfIFxq+zL
=NNCg
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Nico Williams]

On Fri, Oct 7, 2011 at 1:17 PM, Roger Binns  wrote:
> On 07/10/11 09:52, Simon Slavin wrote:
>> "Do you really want to see all 50,000 entries that that search would
>> return ?".  If this kind of search returns more than 100 records,
>> there's no point in doing it at all.
>
> You can solve this at the user interface layer and there is no need to
> calculate how many matching records there are.
>
> Lets say you can show 50 results on the screen.  Run the query but leave
> it open until hitting the 51st result.  Show the 50 results on the screen
> with a More button.  Most of the time the user will realise their query is
> too broad and change it, or see the values they were looking for.  If they
> hit next you can get the next 50 results which will allow you to know if
> you should show another More button.  Heck you can even show page numbers
> to 10 like Google does and if they get clicked on keep advancing the query
> through the results.  If the user clicks on page 10 but there are only 7
> pages of results then update the user interface to show page 7 and get rid
> of 8, 9 and 10.

The problem is you need to reap state in the case of the user
abandoning this "session" without telling the application.  This
happens all the time in web apps.

If there was a way to serialize the state of a SQLite3 VM...  then the
server could encrypt it and send it to the client as a cookie, say.
But that's not possible, and rather ugly.  Better not mention that
again :/

You could add OFFSET/LIMIT to the query and add those to the "next"
URLs.  But for many queries this doesn't really help.  Another
possibility is to combine this with what you propose next so the user
doesn't feel the pain, but the server still would:

> A different approach is infinite scroll.  As the user hits the bottom of
> the currently shown results you append more results in the display.

Definitely doable with JavaScript.

For web UIs you could also limit the queries to canned ones whose
results can be cached or better estimated.

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

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Simon Slavin]

On 7 Oct 2011, at 7:17pm, Roger Binns wrote:

> On 07/10/11 09:52, Simon Slavin wrote:
>> "Do you really want to see all 50,000 entries that that search would
>> return ?".  If this kind of search returns more than 100 records,
>> there's no point in doing it at all.
> 
> You can solve this at the user interface layer and there is no need to
> calculate how many matching records there are.
> 
> Lets say you can show 50 results on the screen.  Run the query but leave
> it open until hitting the 51st result.  Show the 50 results on the screen
> with a More button.

It's not quite the problem I have.  My problem is that there is a huge amount 
of data involved in finding the 50,000 matches and merely processing the SELECT 
is very slow.  And SQLite has to find all 50,000 before it can show the first 
screen of matches.

Imagine you're watching _CSI_ on TV and they have a test that identifies 
possible matches to something ... car colours and tyres perhaps.  If there are 
only 50 possible matches, it's worth getting printouts and eliminating them one 
by one.  If there are 50,000 possible matches there's no point.

My problem really seems to be with the way SQLite implements LIMIT n.  If I 
understand previous posts on this list correctly, instead of finding only the 
first n records, it does the entire search first, then returns the first n of 
the results it found.  With a standard implementation I would simply do a 
search with a LIMIT 51 clause.  If 51 results are returned then I know the 
search is useless.  But even with a LIMIT clause SQLite will still do all the 
fetching involved in all 50,000 records.

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

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/10/11 09:52, Simon Slavin wrote:
> "Do you really want to see all 50,000 entries that that search would
> return ?".  If this kind of search returns more than 100 records,
> there's no point in doing it at all.

You can solve this at the user interface layer and there is no need to
calculate how many matching records there are.

Lets say you can show 50 results on the screen.  Run the query but leave
it open until hitting the 51st result.  Show the 50 results on the screen
with a More button.  Most of the time the user will realise their query is
too broad and change it, or see the values they were looking for.  If they
hit next you can get the next 50 results which will allow you to know if
you should show another More button.  Heck you can even show page numbers
to 10 like Google does and if they get clicked on keep advancing the query
through the results.  If the user clicks on page 10 but there are only 7
pages of results then update the user interface to show page 7 and get rid
of 8, 9 and 10.

A different approach is infinite scroll.  As the user hits the bottom of
the currently shown results you append more results in the display.

There is a "cheating" way of giving some idea of how many results there
may be.  Use EXPLAIN QUERY PLAN and just add together the estimated number
of rows from each line returned.  Sure it won't be accurate but it will be
more accurate than a wild guess and the number will be somewhat
proportional to the amount of work necessary to process the query anyway.

Another approach is to run the query in a different thread feeding updates
to the UI.  You can call sqlite3_interrupt from the UI if you no longer
want any more results.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6PQkgACgkQmOOfHg372QRniwCgoRmQM818lLm4m6k7BUCfjed+
tR4An1Nm8WevXNEBxJ7SulzIUYiRyEGX
=K2O/
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using COUNT(*) in unusual circumstances

[David Garfield]

Puneet Kishor writes:
> Especially, note Pavel's recent, wonderful explanation of how SQLite steps 
> through the result set handing you the data row by row. It *has* to go 
> through the set to know how big the set is... there is no way around it.
> 

Actually, sometimes there are.  Consider:

SELECT COUNT(*) FROM t1, t2;

SELECT (SELECT COUNT(*) FROM t1) * (SELECT COUNT(*) FROM t2);

Of course, those are special cases.  Application programmers should
probably watch for this kind of thing, but it probably wasn't what the
programmer wanted to count in the first place.

--David Garfield

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

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Puneet Kishor]

On Oct 7, 2011, at 11:52 AM, Simon Slavin wrote:

> 
> On 7 Oct 2011, at 5:31pm, Puneet Kishor wrote:
> 
>> assuming you have some other application level language you are using to get 
>> the data, you could stuff the result set into an array and then report the 
>> highest index of the array which might be faster than doing a second query 
>> for just the count.
> 
> I'm trying to save the resources necessary for having SQLite fetch the values 
> which the SELECT would return.  Any solution mentioned so far has not done 
> that.
> 
> To see what I'm after, imagine a use interface which asked the user "Do you 
> really want to see all 50,000 entries that that search would return ?".  If 
> this kind of search returns more than 100 records, there's no point in doing 
> it at all.  I want to know the "50,000" number while placing the minimum load 
> on the system.
> 

As a person way more knowledgable than I once stated (I believe it was Igor), 
the database can't magically know there are 50,000 entries in the table. It has 
to sometime, somewhere, somehow count it. It can't just divine it.

You could track the count of each table in a counts_table that you 
increment/decrement with TRIGGERs, and then query just that table. But that 
still wouldn't solve your specific problem of "how many queries are returned by 
a specific SELECT." Somewhere, somehow you will have to count it.

Especially, note Pavel's recent, wonderful explanation of how SQLite steps 
through the result set handing you the data row by row. It *has* to go through 
the set to know how big the set is... there is no way around it.

Unless you are making an app in a device with limited resources, who cares what 
load you put on SQLite... it won't complain.

--
Puneet Kishor.

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

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Simon Slavin]

On 7 Oct 2011, at 5:31pm, Puneet Kishor wrote:

> assuming you have some other application level language you are using to get 
> the data, you could stuff the result set into an array and then report the 
> highest index of the array which might be faster than doing a second query 
> for just the count.

I'm trying to save the resources necessary for having SQLite fetch the values 
which the SELECT would return.  Any solution mentioned so far has not done that.

To see what I'm after, imagine a use interface which asked the user "Do you 
really want to see all 50,000 entries that that search would return ?".  If 
this kind of search returns more than 100 records, there's no point in doing it 
at all.  I want to know the "50,000" number while placing the minimum load on 
the system.

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

Re: [sqlite] Can pre-sorted data help?

[Simon Slavin]

On 7 Oct 2011, at 5:30pm, Mohit Sindhwani wrote:

> I have been trying to see how we can make one of our databases more space 
> efficient.  I am now looking at seeing if we have the right indexes and if 
> there is a way to save space by removing indexes.
> 
> Is there some way to make use of the fact that the data can be sorted by a 
> specific column in the desired order before it is imported into the table, or 
> do we need to create an index on that column anyway?

You need an index on that column and that column only if something is going to 
use it.

> Eliminating the index could save us space.  One of the things in our case is 
> that the data is in a read only database (and further compressed/ encrypted 
> by CEROD).

The most important thing to note is that having an index on each column is 
completely different to having indexes which speed up your SELECT and UPDATE 
commands.  Many people starting out with SQL think that indexing each column is 
a useful thing to do.  Instead you should be looking at each SELECT and UPDATE 
command and trying to work out indexes which would be useful for those commands.

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

Re: [sqlite] Can pre-sorted data help?

[Richard Hipp]

On Fri, Oct 7, 2011 at 12:30 PM, Mohit Sindhwani  wrote:

> Hi All,
>
> I have been trying to see how we can make one of our databases more space
> efficient.  I am now looking at seeing if we have the right indexes and if
> there is a way to save space by removing indexes.
>

Have you run the sqlite3_analyzer.exe utility (available from
http://www.sqlite.org/download.html) on your database to see how efficiently
the data is being stored and which tables are taking up the most space?  Can
you share the output of sqlite3_analyzer.exe with us?


>
> Is there some way to make use of the fact that the data can be sorted by a
> specific column in the desired order before it is imported into the table,
> or do we need to create an index on that column anyway?   Eliminating the
> index could save us space.  One of the things in our case is that the data
> is in a read only database (and further compressed/ encrypted by CEROD).
>
> Any ideas?
>
> Best Regards,
> Mohit.
> 8/10/2011 | 12:29 AM.
> __**_
> 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] ensuring uniqueness of tuples spanning across multipletables?

[Jim Morris]

On 10/6/2011 10:43 PM, Ivan Shmakov wrote:

Jim Morris writes:

  >  The recent thread may relate: "[sqlite] Is there an efficient way to
  >  insert unique rows (UNIQUE(a, b, c)) into an fts3 virtual table?"

  >  INSERT INTO fts3_table (a,b,c)
  >  SELECT 'an A','a B','a C'
  >  WHERE NOT EXISTS
  >  (SELECT DISTINCT a,b,c
  >  FROM fts3_table
  >  WHERE a='an A' AND b='a B' AND c='a C');

  >  The above SQL could be adapted to your schema.  As mentioned, the
  >  performance will be slower than a straight insert.

Thanks.  It's a solution not quite for the problem I'm having,
but I'll probably stick to it (and to the denormalized schema it
imples.)

However, I wonder, would the following (slightly more concise)
query imply any performance loss in comparison to the one above?

INSERT INTO fts3_table (a, b, c)
 SELECT 'an A', 'a B', 'a C'
 EXCEPT SELECT DISTINCT a, b, c
FROM fts3_table;

Also, I'm curious if DISTINCT may cause any performance loss in
the case that the columns in question are constrained by an
UNIQUE index?  Like:

CREATE UNIQUE INDEX "foo-unique"
 ON "foo" (a, b, c);
I don't know for sure.  You would need to do some testing to determine 
performance issues.
I wouldn't use distinct if the values are already guaranteed to be 
unique or in a not exits clause since it may have additional overhead.

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

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Puneet Kishor]

On Oct 7, 2011, at 9:46 AM, Simon Slavin wrote:

> 
> On 7 Oct 2011, at 2:19pm, Igor Tandetnik wrote:
> 
>> SQLite has a non-standard extension whereby aliases assigned to expressions 
>> in the SELECT clause may be used in the WHERE and other clauses:
>> 
>> select 1+2 as alias from mytable where alias > 0;
> 
> Arg !
> 
> Okay, so I guess the form
> 
>> select count(*) from (
>> select ...
>> );
> 
> is necessary.  But that defeats the point of me doing it in the first place: 
> I was hoping that COUNT(*) would be less resource-hungry than the real query,



assuming you have some other application level language you are using to get 
the data, you could stuff the result set into an array and then report the 
highest index of the array which might be faster than doing a second query for 
just the count.


> and I suppose this form doesn't realise it doesn't need the values.
> 
> Bah.  Okay, I'll abandon that idea, then.
> 
> Thanks, Igor.  You saved me some hugely annoying support calls long after the 
> program was in the field.
> 
> 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

[sqlite] Can pre-sorted data help?

[Mohit Sindhwani]

Hi All,

I have been trying to see how we can make one of our databases more 
space efficient.  I am now looking at seeing if we have the right 
indexes and if there is a way to save space by removing indexes.


Is there some way to make use of the fact that the data can be sorted by 
a specific column in the desired order before it is imported into the 
table, or do we need to create an index on that column anyway?   
Eliminating the index could save us space.  One of the things in our 
case is that the data is in a read only database (and further 
compressed/ encrypted by CEROD).


Any ideas?

Best Regards,
Mohit.
8/10/2011 | 12:29 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Simon Slavin]

On 7 Oct 2011, at 2:19pm, Igor Tandetnik wrote:

> SQLite has a non-standard extension whereby aliases assigned to expressions 
> in the SELECT clause may be used in the WHERE and other clauses:
> 
> select 1+2 as alias from mytable where alias > 0;

Arg !

Okay, so I guess the form

> select count(*) from (
>  select ...
> );

is necessary.  But that defeats the point of me doing it in the first place: I 
was hoping that COUNT(*) would be less resource-hungry than the real query, and 
I suppose this form doesn't realise it doesn't need the values.

Bah.  Okay, I'll abandon that idea, then.

Thanks, Igor.  You saved me some hugely annoying support calls long after the 
program was in the field.

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

Re: [sqlite] SQLITE LIMIT clause

[Gabríel A. Pétursson]

Be aware that if you do not specify an ORDER BY clause, the order of the 
returned rows are undefined. You might not even end up with rows with a 
primary key even near 100.


What you probably want is:
  SELECT * FROM test WHERE PK1 > 100 LIMIT 100 ORDER BY PK1 ASC;

Other than that, those two queries should be near identical in your 
situation. Mind the gaps.


On 10/07/2011 03:24 PM, cricketfan wrote:

I have a table called test and it has about 50 columns ( about 200 bytes of
data, all columns combined). I need to browse the entire table periodically.
I have a primary key PK1 which basically is a increasing
sequence number.

SELECT * from test WHERE PK1>100 AND PK1<200;

SELECT * from test WHERE PK1>100 LIMIT 100;

Will the above queries have the same effect? Or will LIMIT behave
differently, i.e. get the entire result set and then return the first 100
from it?

I delete records in my table (it is like a queue implementation) so I might
have gaps in between which is why I want to use the LIMIT clause.

Any advice would be greatly appreciated.


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

Re: [sqlite] SQLITE LIMIT clause

[Pavel Ivanov]

> SELECT * from test WHERE PK1>100 AND PK1<200;>> SELECT * from test WHERE 
> PK1>100 LIMIT 100;>> Will the above queries have the same effect? Or will 
> LIMIT behave> differently, i.e. get the entire result set and then return the 
> first 100> from it?
If your PK1 has no gaps then those two queries will behave almost
identically (except that first one selects 99 rows and second one -
100). If PK1 has gaps then as you've figured out these queries behave
differently.
Also SQLite never does such thing as "select entire result set and
then return it to you row-by-row". In fact SQLite process your select
row-by-row as you call sqlite3_step(). So you can execute query with
LIMIT 100 but after 3rd row call sqlite3_finalize() and performance of
your program will be the same as if you queried LIMIT 3 from the very
beginning.


Pavel


On Fri, Oct 7, 2011 at 11:24 AM, cricketfan  wrote:
>
> I have a table called test and it has about 50 columns ( about 200 bytes of
> data, all columns combined). I need to browse the entire table periodically.
> I have a primary key PK1 which basically is a increasing
> sequence number.
>
> SELECT * from test WHERE PK1>100 AND PK1<200;
>
> SELECT * from test WHERE PK1>100 LIMIT 100;
>
> Will the above queries have the same effect? Or will LIMIT behave
> differently, i.e. get the entire result set and then return the first 100
> from it?
>
> I delete records in my table (it is like a queue implementation) so I might
> have gaps in between which is why I want to use the LIMIT clause.
>
> Any advice would be greatly appreciated.
> --
> View this message in context: 
> http://old.nabble.com/SQLITE-LIMIT-clause-tp32607006p32607006.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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] SQLITE LIMIT clause

[cricketfan]

I have a table called test and it has about 50 columns ( about 200 bytes of
data, all columns combined). I need to browse the entire table periodically.
I have a primary key PK1 which basically is a increasing 
sequence number. 

SELECT * from test WHERE PK1>100 AND PK1<200;

SELECT * from test WHERE PK1>100 LIMIT 100;

Will the above queries have the same effect? Or will LIMIT behave
differently, i.e. get the entire result set and then return the first 100
from it? 

I delete records in my table (it is like a queue implementation) so I might
have gaps in between which is why I want to use the LIMIT clause.

Any advice would be greatly appreciated.
-- 
View this message in context: 
http://old.nabble.com/SQLITE-LIMIT-clause-tp32607006p32607006.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Using COUNT(*) in unusual circumstances

[David Garfield]

WHERE or HAVING clauses that refer back to named results could be a
problem with the simple replacement.

SELECT a,b,a+b AS ab FROM t WHERE ab>10

Igor's suggestion work there too.   

--David Garfield

Simon Slavin writes:
> I'm trying to write some code which has to be useful under many different 
> circumstances.  Something I want to be able to do is to take an arbitrary 
> SELECT statement and replace the columns which would normally be returned 
> with COUNT(*) to find out how many rows would be returned.  To do this I 
> replace the text between "SELECT" and "FROM".
> 
> I suspect this won't work well with unusual SELECTs which include 
> sub-selects.  That's okay.
> 
> I'm trying to get my head around whether JOINs could be a problem.
> 
> Also, does anyone know whether some combination of NULs might make COUNT(*) 
> give the wrong result ?
> 
> And anything else relevant anyone wants to mention is fine too.
> 
> 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

[sqlite] SQLite

[Arbol One]

Hey kids.

Looking at the choices given at http://www.sqlite.org/download.html, I would
like to know what would be the best way to add SQLite to my app. 

I am using Windows 7-64bit as the OS and Code::Blocks with MinGW/GNU C++. 

 

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

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Simon Slavin]

On 7 Oct 2011, at 2:19pm, Igor Tandetnik wrote:

[useful stuff]

Thanks Igor.

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

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Igor Tandetnik]

Simon Slavin  wrote:
> I'm trying to write some code which has to be useful under many different 
> circumstances.  Something I want to be able to do is to
> take an arbitrary SELECT statement and replace the columns which would 
> normally be returned with COUNT(*) to find out how many
> rows would be returned.  To do this I replace the text between "SELECT" and 
> "FROM".

An alternative might be to wrap the counting SELECT around the original 
statement:

select count(*) from (
  select ...
);

> I suspect this won't work well with unusual SELECTs which include sub-selects.

I don't think subselects between SELECT and FROM should be any problem. Of 
course you'd need to count parentheses and quotes and square brackets carefully 
to find the right FROM.

SQLite has a non-standard extension whereby aliases assigned to expressions in 
the SELECT clause may be used in the WHERE and other clauses:

select 1+2 as alias from mytable where alias > 0;

> I'm trying to get my head around whether JOINs could be a problem.

I don't see how.

> Also, does anyone know whether some combination of NULs might make COUNT(*) 
> give the wrong result ?

No. COUNT(*) counts the number of rows, regardless of what those rows contain.
-- 
Igor Tandetnik

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

[sqlite] Using COUNT(*) in unusual circumstances

[Simon Slavin]

I'm trying to write some code which has to be useful under many different 
circumstances.  Something I want to be able to do is to take an arbitrary 
SELECT statement and replace the columns which would normally be returned with 
COUNT(*) to find out how many rows would be returned.  To do this I replace the 
text between "SELECT" and "FROM".

I suspect this won't work well with unusual SELECTs which include sub-selects.  
That's okay.

I'm trying to get my head around whether JOINs could be a problem.

Also, does anyone know whether some combination of NULs might make COUNT(*) 
give the wrong result ?

And anything else relevant anyone wants to mention is fine too.

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

Re: [sqlite] Big FLOAT bug

[Wiktor Adamski]

> Changing the 2 "15g" entries in sqlite3.c to "16g" corrects this problem. 15 
> digits is all that is guaranteed but the vast majority of 16-digit values are 
> representable.
>
> Is this a valid solution?  Or are there other side effects?

It should be ok. However there's another bug that will appear after
the change:

C:\Users\vic\Desktop\sqlite-amalgamation-3070800>cat test.c
#include "sqlite3.h"
#include 

int main() {
  double d = 8901.0;
  char * c = sqlite3_mprintf("%.15g\n%.16g\n", d, d);
  printf("%s%.16g\n", c, d);
}

C:\Users\vic\Desktop\sqlite-amalgamation-3070800>cl /nologo test.c
sqlite3.c
test.c
sqlite3.c
Generating Code...

C:\Users\vic\Desktop\sqlite-amalgamation-3070800>test.exe
8901
8901.0001
8901

C:\Users\vic\Desktop\sqlite-amalgamation-3070800>gcc test.c sqlite3.c

C:\Users\vic\Desktop\sqlite-amalgamation-3070800>a.exe
8901
8901.0001
8901

So with .15g result is correct but with .16g is not. The problem with .
16g doesn't appear in gcc's and visual's printf.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Meaning of sqlite3_wal_checkpoint_v2 parameters

[Dan Kennedy]

On 10/06/2011 02:29 PM, David Barrett wrote:

Hi!  Can you help me understand more exactly what the output parameters
are from sqlite3_wal_checkpoint_v2()? Specifically:

1) What is the relationship between pnLog and pnCkpt: is pnLog>=pnCkpt
always true?


Yes. Always true.


2) Under what circumstances would pnLog>pnCkpt be true?


When an SQLITE_CHECKPOINT_PASSIVE checkpoint is run, SQLite attempts to
copy the entire WAL file into the database. If it is successful,
(*pnLog==*pnCkpt).

However, if there are existing readers, then it may not be possible
to copy all WAL data into the database file. The reason being that
this might overwrite a database page that is part of the 'snapshot'
that the reader is accessing. In this case it is possible that
(*pnLog>*pnCkpt).

Using SQLITE_CHECKPOINT_FULL or SQLITE_CHECKPOINT_RESTART instead forces
the checkpoint to wait for such readers to finish. If you use one of
these, it is guaranteed that (*pnLog==*pnCkpt) (assuming your
busy-handler is prepared to wait long enough for readers to finish -
if it gives up before all readers have finished we will be back to
*pnLog>*pnCkpt).


Also, can you confirm I understand the following correctly:

3) When using SQLITE_CHECKPOINT_PASSIVE, the WAL file will grow as
needed, indefinitely, without ever shrinking.


It is possible. But we hope it's the exception, not the rule.

If a checkpoint runs and copies all WAL data into the database file,
the next writer starts writing into the start of the WAL file again.
The WAL file is not usually truncated (see PRAGMA journal_size_limit
if you want it to be) here. The reason being that it is faster to
overwrite an existing file than it is to truncate one and then start
appending to it.

So, if all goes well, SQLite should start over at the start of the
WAL file after each checkpoint. Preventing the WAL file from growing
indefinitely. There are two things that can go wrong:

  * A reader might prevent a checkpointer from copying all data from
the WAL into the database file, or

  * While the checkpoint is underway, some other process may be
writing to the database (appending to the WAL file).

If either of the above occur, then the next writer will append to the
WAL file, instead of writing into the start of it. If this happens
every checkpoint, then the WAL file will grow without bound.

Use CHECKPOINT_FULL or CHECKPOINT_RESTART (respectively) to prevent
the two conditions enumerated above.


4) When using a separate checkpointing thread, pnLog only goes down if
the read/write thread has no active queries when the checkpoint runs.
(Eg, if the checkpoint thread runs while the read/write thread has an
active query, it'll still checkpoint, but it just won't reset the pnLog
to 0.)


That's pretty much the gist of it. Some more details above.


Finally, and I'm not sure if this is related, but I've read in several
threads that sometimes you can get the SQLITE_CORRUPT error even if the
database isn't corrupt, and the proper response is to just retry.

5) Is this considered to be a bug, or is it the result of some temporary
hardware issue?

6) Is this aggravated by the use of WAL, or totally unrelated?


Sounds like questionable information to me. We know of no such bugs
at present.

Dan.

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