On 30 Oct 2015, at 4:22pm, Mark Hamburg wrote:
> I knew it would dirty the whole page. I was thinking about the memory work
> and ignoring that the disk I/O swamps that.
As someone who is not part of the development team I can write this.
I can't answer your question, but your pattern of
On Oct 30, 2015, at 5:56 AM, Richard Hipp wrote:
>> Will SQLite rewrite the whole row if you just change field2 from one float
>> to another?
>
> Yes. Not just the whole row but the whole page on which that row
> resides. And even if SQLite did just try to write the 8 bytes that
> changes,
On 10/30/15, Mark Hamburg wrote:
>
>> On Oct 29, 2015, at 12:24 PM, Richard Hipp wrote:
>>
>> If you do have large BLOBs or strings, SQLite handles this best if the
>> large blob/string is stored in a table by itself and then linked into
>> other tables using an integer primary key. For
> On Oct 29, 2015, at 12:24 PM, Richard Hipp wrote:
>
> If you do have large BLOBs or strings, SQLite handles this best if the
> large blob/string is stored in a table by itself and then linked into
> other tables using an integer primary key. For example:
>
> CREATE TABLE BigBlobTab (
>
> On 10/29/15, Jason H wrote:
> >
> > I'm open to ideas, but I was going to use this as an excuse to invent
> > something of a general tool.
>
> Post your schema and query. Also run ANALYZE and post the content of
> the sqlite_stat1 table.
I really appreciate the offer, but unfortunately, I
>
> On 10/29/15, Jason H wrote:
> >>
> > If I were to try to work around...
>
> Before we go any further, have you actually measured a performance
> problem? Or are you optimizing without prior knowledge of where your
> application is spending time?
Currently, I have a SQLite database of
On 29 Oct 2015, at 7:49pm, Jason H wrote:
> If I were to try to work around all this excess reading, how good or bad
> would it be to take the following approach:
> Define a set of smaller tables (column count and size) with a common key,
> then create a view for the specific query, then
On 29 Oct 2015, at 6:22pm, Jason H wrote:
> Ah, so this is what I seem to have missed. The pages... This is unfortunate
> as the read-heavy application won't likely benefit from SQLite. Only a small
> fraction of the row data is used each query, and it seems like It'll have to
> read the
On 29 Oct 2015, at 8:59pm, Jason H wrote:
> Currently, I have a SQLite database of around 10gig that takes 25 minutes to
> run a single query against no other activity (it's never queried in
> read/write, just read). I've created indexes the best I can.
You can do better. I have a 43
> On 10/29/15, Jason H wrote:
> >
> > Ah, so this is what I seem to have missed. The pages... This is unfortunate
> > as the read-heavy application won't likely benefit from SQLite.
>
> Your filesystem and your disk hardware work the same way. Your
> application might only ask for 10 bytes, but
On Thu, Oct 29, 2015 at 8:24 PM, Richard Hipp wrote:
> On 10/29/15, Dominique Devienne wrote:
> >
> > I've discussed blobs a few times already :). Especially the fact you
> can't
> > have transactional and incremental updates of blobs (i.e. the transaction
> > "copies" only the blob pages
On Thu, Oct 29, 2015 at 7:32 PM, Wade, William wrote:
> From https://www.sqlite.org/fileformat2.html, if a row does not fit into
> a single block, its overflow is stored in a linked list. The implication
> (to me) is that if a particular row requires twenty blocks of storage, and
> I want to
On Thu, Oct 29, 2015 at 7:22 PM, Jason H wrote:
>
> Or is that just crazy talk?
>
Mostly, yes. You seem to think that reading from disk at arbitrary offsets
and in arbitrarily small increments will be efficient. It won't be.
And as you pointed out, that ignores transactionality, WAL mode, etc...
On Thu, Oct 29, 2015 at 7:09 PM, Jason H wrote:
> This seems to be at odds with my understanding of "2.1 Record Format" of
> the document. Given that it reads the row varint, which contains the length
> of itself and the serial types of the columns, in order, it should be
> completely able to
> Sent: Thursday, October 29, 2015 at 2:04 PM
> From: "Paul Sanderson"
> To: "SQLite mailing list"
> Subject: Re: [sqlite] How would sqlite read this from disk?
>
> It reads a complete page at a time so there is no seeking other than
> to the start
> Sent: Thursday, October 29, 2015 at 1:53 PM
> From: "Simon Slavin"
> To: "SQLite mailing list"
> Subject: Re: [sqlite] How would sqlite read this from disk?
>
>
> On 29 Oct 2015, at 5:20pm, Jason H wrote:
>
> > Thanks, this is
> Sent: Thursday, October 29, 2015 at 1:34 PM
> From: "Scott Hess"
> To: "SQLite mailing list"
> Subject: Re: [sqlite] How would sqlite read this from disk?
>
> On Thu, Oct 29, 2015 at 10:20 AM, Jason H wrote:
> >
> > If I could ask a follo
> Sent: Thursday, October 29, 2015 at 12:10 PM
> From: "Simon Slavin"
> To: "SQLite mailing list"
> Subject: Re: [sqlite] How would sqlite read this from disk?
>
>
> On 29 Oct 2015, at 2:29pm, Jason H wrote:
>
> > In college databases, we c
On 29 Oct 2015, at 6:09pm, Jason H wrote:
> This seems to be at odds with my understanding of "2.1 Record Format" of the
> document. Given that it reads the row varint, which contains the length of
> itself and the serial types of the columns, in order, it should be completely
> able to skip
for SQLite
email from a work address for a fully functional demo licence
On 29 October 2015 at 17:59, Jason H wrote:
>
>
>> Sent: Thursday, October 29, 2015 at 1:34 PM
>> From: "Scott Hess"
>> To: "SQLite mailing list"
>> Subject: Re: [sqlite]
On 29 Oct 2015, at 5:20pm, Jason H wrote:
> Thanks, this is some really great information!
You're welcome.
> If I could ask a followup question. You made the statement "SQLite reads that
> row of the table from storage, from the first column to the last column
> needed by the SELECT, but
On 10/29/15, Jason H wrote:
>
> I'm open to ideas, but I was going to use this as an excuse to invent
> something of a general tool.
Post your schema and query. Also run ANALYZE and post the content of
the sqlite_stat1 table.
--
D. Richard Hipp
drh at sqlite.org
On 29 Oct 2015, at 2:29pm, Jason H wrote:
> In college databases, we calculated the estimated number of blocks (512-byte
> device blocks) read given schema and statistics. Now, I'm asking how would
> SQLite actually do that?
SQLite uses a block structure in its database files, but one file
On 10/29/15, Jason H wrote:
>>
> If I were to try to work around...
Before we go any further, have you actually measured a performance
problem? Or are you optimizing without prior knowledge of where your
application is spending time?
--
D. Richard Hipp
drh at sqlite.org
On Thu, Oct 29, 2015 at 3:29 PM, Jason H wrote:
> I'm trying to figure out how SQLite3 would read the schema and data off
> the disk below. I read https://www.sqlite.org/fileformat2.html but didn't
> find what I was looking for.
> In college databases, we calculated the estimated number of
I'm trying to figure out how SQLite3 would read the schema and data off the
disk below. I read https://www.sqlite.org/fileformat2.html but didn't find what
I was looking for.
In college databases, we calculated the estimated number of blocks (512-byte
device blocks) read given schema and
On 10/29/15, Dominique Devienne wrote:
>
> I've discussed blobs a few times already :). Especially the fact you can't
> have transactional and incremental updates of blobs (i.e. the transaction
> "copies" only the blob pages actually modified).
Large blobs are transactional when they are stored
On 10/29/15, Wade, William wrote:
> From https://www.sqlite.org/fileformat2.html, if a row does not fit into a
> single block, its overflow is stored in a linked list. The implication (to
> me) is that if a particular row requires twenty blocks of storage, and I
> want to read a one-byte field
On 10/29/15, Jason H wrote:
>
> Ah, so this is what I seem to have missed. The pages... This is unfortunate
> as the read-heavy application won't likely benefit from SQLite.
Your filesystem and your disk hardware work the same way. Your
application might only ask for 10 bytes, but the
On 10/29/15, Jason H wrote:
>
> This seems to be at odds with my understanding of "2.1 Record Format" of the
> document. Given that it reads the row varint, which contains the length of
> itself and the serial types of the columns, in order, it should be
> completely able to skip the reading of
On Thu, Oct 29, 2015 at 10:59 AM, Jason H wrote:
>
> The documentation does not go into the detail of the engine is able to
> skip the reading of unneeded interior rows. In theory, it can because the
> length is contained in the header. So instead of read() on every column in
> the row, it can
On Thu, Oct 29, 2015 at 10:20 AM, Jason H wrote:
>
> If I could ask a followup question. You made the statement "SQLite reads
> that row of the table from storage, from the first column to the last
> column needed by the SELECT, but perhaps not all the way to the end of the
> columns in the
33 matches
Mail list logo
