Brannon King wrote:
So I dropped the "not null" and it gave a ~2% increase in overall speed.
It's not much, but may count for something. It's a little strange because
that is just the opposite of MySQL. In MySQL, it shrinks the database size
and speeds up the access by declaring a column not null.
Brandon,
I didn't say it would save a lot of time. ;-)
I am using the prepare/bind/step combination. The bind calls are negligible
time-wise. It's the step function that I think could run faster. As I
understand it, the step function is not actually doing an insert. That
doesn't happen until the "end transaction" statement. Therefore, the not
null thing should not effect the speed of the step function. Is that not
true?
The step function executes the VDBE opcodes until it does a Halt (for an
insert). The majority of the time will be taken to execute the Insert
opcode. This writes the new record into the database. The underlying
pager layer will cache these writes in memory until the cache is full or
the transaction is committed. At that point the cached pages are written
to disk. The Insert opcode still performs all the logic of locating the
next free record, allocating additional pages as necessary, updating the
b-tree, etc.
It seems that the step function should only occasionally allocate memory; it
should allocate enough for a number of expected queries per transaction.
That doesn't make sense, though, if you're not in the middle of a
transaction. Hence, I wonder if we really need two different functions. The
step function is overkill for just doing inserts that never return any data
and that are in the middle of a transaction. What we need in that situation
is a quick memcpy and nothing else.
I'm not sure what you are getting at here. Copy memory from where to
where? The database is much more than an array of record structures that
you can copy data into.
Dennis Cote
