In response to Will Leshner, could you tell me how to access this
functionality from the sqlite3 shell? I'll then try it and
respond. Thanks, -P.
Hi,
Below please find a Perl DBI program, the output of the program,
and a stack trace following the SEGV. It appears to be dying
in the sqlite DSO as the result of an attempt to get the TYPE
of a field in a VIEW.
I have no such problem obtaining the TYPE information in a TABLE;
I only have the
On Tue, 19 Apr 2005 12:40:51 -0500, William Hachfeld <[EMAIL PROTECTED]> wrote:
> uint64_t t_stop = Now();
>
> assert(sqlite3_exec(handle, "COMMIT TRANSACTION;",
> NULL, NULL, NULL) == SQLITE_OK);
What happens to your insert times if you swap the above 2 lines? If
Just a thought. If transactions speed up the access, try this:
Begin exclusive
Select
Select ...
Select ...
Commit
Ie use a transaction around READS.
This may acquire the lock once, and give you the same performance as
inserts.
> -Original Message-
> From: William Hachfeld
Hello all,
I'm having a problem here that seemed to work for SQLite < v3.
When I do have a query like:
SELECT
UPPER(col1)||
''
'||
col2||
'
'||
CASE
WHEN col3 IS NULL THEN ''19XX''
ELSE col3
END AS 'col3'||
'
'
FROM mytable;
I get an error:
near "||": syntax error.
If I take out
I didn't notice the version everyone discussing this issue is using. I
am using 3.2.0. Might this be relevant?
-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 19, 2005 3:43 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Locking Methods
I had
I had the same trouble he did. Here's what I did that doesn't work:
select * from a into result;
foreach row in result
' this fails:
update b set col = a.value;
next
But based on what I read here it's supposed to do this.
On 4/19/05, Gerry Blanchette <[EMAIL PROTECTED]>
On Tue, Apr 19, 2005 at 11:58:11AM -0700, Ted Unangst wrote:
> If you don't need locking, and it sounds like you don't, just neuter the
> fcntl calls in os_unix.c. In fact, there's a #define and comment for DJGPP
> that does exactly what you want.
Hmmm. Thanks for the suggestion, Ted. I think
Could you please elaborate your scenario?
I tried a test myself but am afraid I may not have interpreted your test
case properly.
I have 2 tables, fred and bob, each with 1 rows. I select a column
from fred and bind the value obtained from sqlite3_column_int to an
update statement that
William Hachfeld wrote:
Nope. that isn't going to be an option for me. Preserving the "zero
configuration" and "single file" environment is of utmost importance in our
application.
If you don't need locking, and it sounds like you don't, just neuter the
fcntl calls in os_unix.c. In fact, there's
Any chance you can write a really simple shell script/C/Perl program to do
a bunch of lock/unlocks and benchmark it?
> Possibly. Although as I noted, our "real" application is showing a speed
> differential of about 50x. Much more than can be attributed to simple transfer
> rates. But it sounds
On Tue, Apr 19, 2005 at 02:23:44PM -0400, Griggs, Donald wrote:
> I'm not sure how your network is set up, but if you're using 100mbps
> ethernet, the full- table-scan queries are only 4-5 times slower over the
> net. Wouldn't that be the same order of magnitude as the ratio of link
> speed to
On Tue, Apr 19, 2005 at 01:20:35PM -0500, Kurt Welgehausen wrote:
> SQLite is not a client-server database.
Yup. I realize that.
> In the worst case, when you query a database file over NFS, all the tables in
> your query are sent over the network to be processed on your cpu; so if you
> want
hi
I noticed that function sqlite3_step() is very slow when i want to fetch
query with join(s).
For example :
create same table t (a int, b int, c int ... );
and insert into t large numbers of records, (about 1)
time of fetching select * form t limit 0,100 is this same what select
*
On 4/19/05, Kurt Welgehausen <[EMAIL PROTECTED]> wrote:
> SQLite is not a client-server database. In the worst case,
> when you query a database file over NFS, all the tables in
> your query are sent over the network to be processed on
> your cpu; so if you want to select one row from a table of
>
I'm not sure how your network is set up, but if you're using 100mbps
ethernet, the full- table-scan queries are only 4-5 times slower over the
net. Wouldn't that be the same order of magnitude as the ratio of link
speed to ATA local disk bus speed?
As for the inserts, if you're achieving over
SQLite is not a client-server database. In the worst case,
when you query a database file over NFS, all the tables in
your query are sent over the network to be processed on
your cpu; so if you want to select one row from a table of
a million rows, the million rows are retrieved over the
network
That sounds right. I might not understand what you're doing so it's
just a SWAG ;)
On 4/19/05, William Hachfeld <[EMAIL PROTECTED]> wrote:
> On Tue, Apr 19, 2005 at 01:07:03PM -0500, Jay Sprenkle wrote:
> > On a system we developed several years ago (non sqlite) we noted that
> > obtaining locks
On Tue, Apr 19, 2005 at 01:07:03PM -0500, Jay Sprenkle wrote:
> On a system we developed several years ago (non sqlite) we noted that
> obtaining locks on an NFS mounted file was VERY slow. I *think* it's trying
> to establish a lock then waiting at least the propagation delay of your
> network to
On a system we developed several years ago (non sqlite) we noted that obtaining
locks on an NFS mounted file was VERY slow. I *think* it's trying to
establish a lock
then waiting at least the propagation delay of your network to ensure
there was no
collision with another process.
Doing anything
Sorry for the ambiguity - yes, I believe that's the case. It's
certainly the case with every other database I've used, and given what I
know of SQLite I can't see any reason why it wouldn't (though I will say
that I yet haven't delved too far into how joins are processed, so I
could be
On Tue, 2005-04-19 at 10:27 -0700, Ted Unangst wrote:
> Two tables in a where can use two indices in sqlite?
>
Correct. SQLite (and every other SQL RDBMS that I know
of) uses as many as but no more than one index per table
in a join. If the same table is used more than once in
a join, then
Heh everyone!
Can anyone explain to me why SQLite shows such poor query performance when the
database is located on an NFS-mounted file system? When running with a single
process accessing the database, I'm finding that my insertion rates are similar
to local disk, but my query performance is
Thomas Briggs wrote:
I think the common misconception is that indexes on multiple
individual columns can be used in conjunction with one another, which
isn't the case (unless you're talking about bitmap indexes, but since
SQLite doesn't have those, we clearly aren't :P). Once you get
Kervin,
I would be happy to help all I can on such a venture. But I am not sure
what that may be :)
Regards,
Ben.
Kervin L. Pierre wrote:
I think that's an excellent idea, and I'd like
to help however possible if work starts on a
patch.
My wishlist
1. Finer grain locking ( Row/table )
2.
Thank you for spelling out that only one index can be
used per query - probably a basic principle for sqlite
experts, but one that had passed me by!
The following tuning guide mentions that "if there is
a choice of indexes, the query optimizer may make a
bad choice":
> I've got one application that writes to the database, and one that reads
> from it. When a table in the database has changed, the reading
> application needs to know that. Of course I can send a signal from the
You may be able to use sqlite_schema:
(From the FAQ)
(17) What is an
I think that's an excellent idea, and I'd like
to help however possible if work starts on a
patch.
My wishlist
1. Finer grain locking ( Row/table )
2. Memory resident resultsets
3. Reduction or elimination SQLITE_SCHEMA
I think memory resident resultsets would be an
excellent feature to start
> I was puzzled that removing the single-column index on
> Season actually enabled the original query to
> complete, having read somewhere in the sqlite docs
> that indexing columns used in WHERE conditions
> improves performance. Is this something to do with the
That's a true statement in
This is true - selecting * from a table of size exceeding the memory of
the machine would not work in any way. Any large table would be slow.
A solution used by MySQL is to have two cursors.
The standard cursor returns a memory resident table containing the
result of the query. Ideal for
Using a trigger is exactly what I have done. It works great and you get
to easily control the parameters of event.
Just register your user-defined function and create a trigger thusly.
create trigger log_report after
insert on logEvent
for each row
begin
select appLogCallback
You don't need a correlated subquery:
sqlite> select * from tbl;
c1 c2
-- --
1 a
1 a
1 b
1 a
2 b
2 c
2 a
3 c
3
Hi Frank,
F.W.A. van Leeuwen wrote:
I've asked this two weeks ago but no reply yet...
I've got one application that writes to the database, and one that reads
from it. When a table in the database has changed, the reading
application needs to know that. Of course I can send a signal from the
You could poll the database as Micah suggested. However you may get
into the locking problems that I have. When two processes attempt a
read and write at the same time, as statistically will happen using this
method, your have a LOCK failure.
A method I suggest is using a log file. When you
This probably isn't much better than coupling the reader/writer, but you could
have the reader periodically query a table that keeps track of the last update.
Every time the writer makes a change to the database have it write to another
one row by one column table. That table would just
Thank you very much for your reply, I'm using gcc but I think I have
nothing to do with a Mac, since the PowerPC I mention is not the PowerPC
of Macintosh. I'm working on a Motorola PowerPC.
Thomas Steffen wrote:
On 4/18/05, Cem Vedat ISIK <[EMAIL PROTECTED]> wrote:
Is there any information
I've asked this two weeks ago but no reply yet...
I've got one application that writes to the database, and one that reads
from it. When a table in the database has changed, the reading
application needs to know that. Of course I can send a signal from the
writer to the reader app, but if
There are two thinks I am working on. Firstly, just to loop around the
execution:
do {
execute(sql);
} while (SQLITE_BUSY || SQLITE_LOCKED)
This does the job, but is not very nice. Also impossible to tell where
a genuine lock through bad programming, and a lock through heavy load.
I am new to correlated subqueries, but have spent a couple hours scouring the
web for any information why this query doesn't work to no avail. Everything I
have read suggests that I am doing everything correctly, yet I receive an error
stating:
SQL error: no such column: t1.myColumn1
Does
39 matches
Mail list logo
