On Sat, Mar 5, 2016 at 11:21 PM, Stephan Beal wrote:
> On Sat, Mar 5, 2016 at 10:43 PM, Domingo Alvarez Duarte <
> sqlite-mail at dev.dadbiz.es> wrote:
>
>> Hello !
>>
>> There is an user pointer that you pass and you can get it back using
>> https://www.sqlite.org/c3ref/user_data.html !
>>
>
>
On Sat, Mar 5, 2016 at 10:43 PM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:
> Hello !
>
> There is an user pointer that you pass and you can get it back using
> https://www.sqlite.org/c3ref/user_data.html !
>
i've got that, but that user data pointer is my callback function.
Hello !
There is an user pointer that you pass and you can get it back using?
https://www.sqlite.org/c3ref/user_data.html !
Cheers !
> Sat Mar 05 2016 9:22:23 pm CET CET from "Stephan Beal"
> Subject: [sqlite] How does your sqlite script
>binding handle aggregate UDFs?
>
> Hi, all,
>
>
Hi, all,
this question is aimed at any of you who have experience adding
script-defined UDF support to sqlite/script bindings (regardless of the
scripting language). Everyone can tap delete now :).
"i've got this friend" who has an sqlite3/script-language binding which (as
of an hour or so ago)
On 5 Mar 2016, at 7:10pm, Paul Sanderson
wrote:
> The savings as mentioned earlier are IO related due to matching the
> page size to the underlying hardwares block size and for larger
> payloads ensuring less IO due to no or lesss overflow pages.
An important point. Because of the way I
Records can span multiple pages and the record itself is unrelated to
the block size.
For smaller tables indexes an increase in page size can increase the
DB size - consider a DB with 1024 byte pages and one table that
occupies 6 pages - i.e. 6K. in a new 4096 page size DB it would take
up two
On 5 Mar 2016, at 4:21pm, Domingo Alvarez Duarte
wrote:
> Also could the commands that take time to complete have an option to show the
> completion stats ?
You can do this using the SQLite shell tool:
.timer ON
VACUUM;
.timer OFF
Simon.
Paul Sanderson wrote:
> I have users who need to execute queries that generate a large number
> of rows, I have other users that create queries that generate a large
> number of rows by accident (i.e. cross joins). I have no control over
> what they enter but I want to do something to warn them
>From: Domingo Alvarez Duarte
>I have a question, is it enough to vacuum a database to update to the new
>page size ?
Apparently all you need to is "pragma page_size=4096; vacuum;" using the
appropriate page size.
This makes very easy to convert any(all) database(s) with a single command
from
On 3/5/16, Stephan Beal wrote:
> On Sat, Mar 5, 2016 at 10:43 PM, Domingo Alvarez Duarte <
>
> The scenario i'm concerned about is that sqlite calls my aggregate N times,
> then an error is triggered elsewhere which keeps sqlite from making the
> final() aggregate call.
IIRC, SQLite always calls
Sorry I mean resurrect it ?
>Is it too hard to survive it ?
>
>
>>SQLite version 2 supported COPY That was dropped when we moved to SQLite
>> 3.
>>
>
>
>
Also could the commands that take time to complete have an option to show the
completion stats ?
Cheers !
> Just allow them to cancel the query; use sqlite3_progress_handler().
I am using a third party data access component so this is out of my control.
If it computes many things and doesn't return many rows then I don't
really care. I only want to know how many rows a query will return
before I
On 05 Mar 2016 at 16:12, Richard Hipp wrote:
> On 3/5/16, Paul Sanderson wrote:
>> Clemens,
>>
>> Tim has the same issue as me, while
>>
>> SELECT EXISTS (select status from mytable where status=1);
>>
>> works the portion in brackets is still executed in full and this is
>> what we are
On Saturday, 5 March, 2016 14:03, James K. Lowden
said:
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Can I implement a scrolling window using LIMIT and
> OFFSET ?
> On Fri, 04 Mar 2016 00:35:47 -0800
> Darren Duncan wrote:
> > > How exactly is the first way
Is it too hard to survive it ?
>
> SQLite version 2 supported COPY That was dropped when we moved to SQLite
>3.
>
>
>
>
Jim Callahan wrote:
> Is 4096 bytes a large enough page size?
>
> Apparently the disk drive industry has shifted from 512 byte sectors to
> 4096 byte sectors.
> http://tilt.lib.tsinghua.edu.cn/docs/tech/tp613_transition_to_4k_sectors.pdf
>
> Should SQLite maintain a 1:1 ratio between page size and
Hello !
I have a question, is it enough to vacuum a database to update to the new
page size ?
Or need a complete backup restore ?
Also I have a database not too big 6GB but only 12GB free space on that disk,
if I try a dump/restore with the actual sqlite3 maybe I'll run out of disk
space,
Thanks Richard
so suppose I have two tables table1 and table2 each with 1000 rows and
say 100 columns some containing large blobs.
My user choose a query "select * from table1, table2"
I can modify the query and do a "select count(*) from table1, table2"
to determine that 1M rows will be
On 3/5/2016 7:48 AM, Paul van Helden wrote:
> SELECT * FROM tleft, tright WHERE Txt=Flt; -- returns a match!
>
> Is it because of the following from https://www.sqlite.org/datatype3.html
No. It is because Flt has REAL affinity, but 1.0 has no affinity. You
can use CAST to explicitly give it one:
Tim Streater wrote:
> On 05 Mar 2016 at 13:05, Clemens Ladisch wrote:
>> But why do you want to determine the number of rows in the first place?
>
> In my case I want to know whether at least one row exists that has a certain
> column which has a given value. At the minute I do this:
>
>
On 3/5/2016 12:20 PM, Paul Sanderson wrote:
> If it computes many things and doesn't return many rows then I don't
> really care. I only want to know how many rows a query will return
> before I execute it in full.
That would require a crystal ball or a time machine. Absent those, it's
On Fri, 04 Mar 2016 00:35:47 -0800
Darren Duncan wrote:
> > How exactly is the first way "easiest"?
>
> If these are pages displayed to the user, they may want to scroll
> backwards at some point;
They might, and if you say it's easier to go back to the database than
to keep track of
Clemens,
Tim has the same issue as me, while
SELECT EXISTS (select status from mytable where status=1);
works the portion in brackets is still executed in full and this is
what we are trying to avoid.
I am not working directly with the sqlite library but rather though a
DAC so I am also
That doesn't work for me:
I am trying to avoid (select col1, col2, ... colx ) from ...
and want just a row count which is much less resource intensive.
I have users who need to execute queries that generate a large number
of rows, I have other users that create queries that generate a large
Hi again,
CREATE TABLE test (A TEXT);
INSERT INTO test VALUES ('1');
SELECT * FROM test WHERE A=1; -- returns 1
SELECT * FROM test WHERE A=1.0; -- returns nothing because the RHS is
converted to '1.0'
This would not have been a problem if CAST(1.0 AS TEXT) produced '1' (like
FloatToStr does in
On 3/5/16, Jim Callahan wrote:
>
> ?I am confused.
> The "page" is an in-memory structure; i?t is how large a chunk the program
> reads from the file (analogous to how large a scoop or shovel),
That is but one of many definitions of "page". That word "page" gets
used (and misused) for a lot of
Paul Sanderson wrote:
> I am trying to determine before a query is executed how many rows will
> be returned. the following query works as expected
>
> select count(*) from table
>
> but
>
> select count(*) from table limit 100
>
> still returns the number of rows in the table not the number of
??
> From: Domingo Alvarez Duarte
> I have a question, is it enough to vacuum a database to update to the new
> page size ?
>
Apparently all you need to is "pragma page_size=4096; vacuum;" using the
> appropriate page size.
> This makes very easy to convert any(all) database(s) with a single
>
On 05 Mar 2016 at 13:05, Clemens Ladisch wrote:
> Paul Sanderson wrote:
>> I am trying to determine before a query is executed how many rows will
>> be returned. the following query works as expected
>>
>> select count(*) from table
>>
>> but
>>
>> select count(*) from table limit 100
>>
>>
I am trying to determine before a query is executed how many rows will
be returned. the following query works as expected
select count(*) from table
but
select count(*) from table limit 100
still returns the number of rows in the table not the number of rows
that would be returned by the
>From: Richard Hipp
>Can you run sqlite3_analyzer on some of your databases and send me the
>output?
I will as soon as I can manage to build it under Windows. Although I
regularly build sqlite3, lemon, and sqldiff, with sqlite3_analyzer I get a
lot of errors like these:
...
On 3/5/16, Domingo Alvarez Duarte wrote:
> Is it too hard to survive it ?
>>
>> SQLite version 2 supported COPY That was dropped when we moved to SQLite
>>3.
>>
Yes.
--
D. Richard Hipp
drh at sqlite.org
On 3/5/16, Paul Sanderson wrote:
> Clemens,
>
> Tim has the same issue as me, while
>
> SELECT EXISTS (select status from mytable where status=1);
>
> works the portion in brackets is still executed in full and this is
> what we are trying to avoid.
The query planner in SQLite, while not
On 3/5/16, Domingo Alvarez Duarte wrote:
>
> I have a question, is it enough to vacuum a database to update to the new
> page size ?
The new page size only applies to newly created database files.
VACUUM-ing an existing database file will not change its page size.
However, if you say "PRAGMA
On 5 Mar 2016, at 10:07am, tonyp at acm.org wrote:
>> From: Richard Hipp
>> Can you run sqlite3_analyzer on some of your databases and send me the
>> output?
>
> I will as soon as I can manage to build it under Windows.
Can download pre-built binaries for Windows and other popular platforms.
>From: tonyp at acm.org
>To: SQLite mailing list
>Subject: Re: [sqlite] Changing the default page_size in 3.12.0
>I ran some tests and almost all of my databases (about 100 of them with
>different content mix and with the biggest one being around 500MB) inflated
>by a lot while only a couple or
On Sat, 05 Mar 2016 13:24 +, Tim Streater wrote:
>
> In my case I want to know whether at least one row exists that has a certain
> column which has a given value. At the minute I do this:
>
> select count(*) from mytable where status=1 limit 1;
>
SELECT 1 FROM mytable WHERE status=1 LIMIT
On 3/5/16, Jim Callahan wrote:
> Is 4096 bytes a large enough page size?
>
> Are there any benchmarks?
>
https://www.sqlite.org/tmp/small-v-large-cache.jpg
Results from running "rm -f x.db; time ./speedtest1 x.db --size 400
--release-memory --pagesize N" (after "make speedtest1") off of the
tip
Is 4096 bytes a large enough page size?
Apparently the disk drive industry has shifted from 512 byte sectors to
4096 byte sectors.
http://tilt.lib.tsinghua.edu.cn/docs/tech/tp613_transition_to_4k_sectors.pdf
Should SQLite maintain a 1:1 ratio between page size and sector size?
or should the page
On 3/5/16, tonyp at acm.org wrote:
>>From: Richard Hipp
>>Can you run sqlite3_analyzer on some of your databases and send me the
>>output?
>
> I will as soon as I can manage to build it under Windows. Although I
> regularly build sqlite3, lemon, and sqldiff, with sqlite3_analyzer I get a
> lot
AFAIK, 4, 8, 16 KB sized I/O based on flash memory shows better
performance. (1.x ~ 3.x)
As a result, Android uses 4KB page size.
It's good to embedded devices using flash memory.
2016? 3? 5? (?) 11:22, Donald Shepherd ?? ??:
> On Sat, 5 Mar 2016 at 09:19 Roger Binns wrote:
>
> > -BEGIN
On Sat, 5 Mar 2016 at 09:19 Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 04/03/16 07:48, Richard Hipp wrote:
> > The tip of trunk (3.12.0 alpha) changes the default page size for
> > new database file from 1024 to 4096 bytes. ... This seems like a
> > potentially
I ran some tests and almost all of my databases (about 100 of them with
different content mix and with the biggest one being around 500MB) inflated
by a lot while only a couple or so shrunk in size by not much. (It could be
that I'm just unlucky with my database contents and this change simply
On 03/04/2016 11:03 PM, Dominique Devienne wrote:
> On Fri, Mar 4, 2016 at 4:48 PM, Richard Hipp wrote:
>
>> https://www.sqlite.org/draft/releaselog/3_12_0.html
>
> - from 2000 to -2000
> + from 2000 to 500
>
> [OT] The PRAGMA defer_foreign_keys=ON statement now also disables RESTRICT
>
44 matches
Mail list logo
