On Oct 19, 2010, at 9:01 AM, Doug wrote:
> I'm not going to pretend to understand the SQLite source, but it seems
> like having a mutex per PCache1 (ie the param passed in to
> pcache1Fetch
> and other cache functions) would be a good approach instead of the
> global
> mutex. But that approa
I'm not going to pretend to understand the SQLite source, but it seems
like having a mutex per PCache1 (ie the param passed in to pcache1Fetch
and other cache functions) would be a good approach instead of the global
mutex. But that approach wasn't taken, and I've found everything to be
very wel
On Tue, 19 Oct 2010 10:54:13 +1100
BareFeetWare wrote:
> -- alternatively you could do this, which will update the existing
> row, if exists, or insert a new one if it doesn't:
>
> update users set "name" = 'Joe C', "type" = 4, where "id" = 1;
> insert or ignore into users ("id", "type", "name")
On 19/10/2010, at 8:10 AM, NSRT Mail account. wrote:
> I would use the update if I knew the entry already existed. In my application
> however, it doesn't know if the entry already exists. I was looking for
> something to replace MySQL's ON DUPLICATE KEY UPDATE.
>
> I modified my application to
I would use the update if I knew the entry already existed. In my application
however, it doesn't know if the entry already exists. I was looking for
something to replace MySQL's ON DUPLICATE KEY UPDATE.
I modified my application to use two SQL statements instead.
if (!db.execute("INSERT IN
NSRT Mail account. wrote:
> The entry in meetings is now gone. Should ON DELETE CASCADE be picking up an
> UPDATE as a DELETE via INSERT INTO from ON CONFLICT
> REPLACE?
REPLACE involves deleting conflicting rows, followed by INSERT, as explained by
the documentation at http://sqlite.org/lang_
I'm not sure if this is an error on my end or on SQLite's. I'm using 3.7.2.
I'm creating two tables as follows:
PRAGMA foreign_keys=ON;
CREATE TABLE 'users' (
'id' INTEGER NOT NULL,
'type' INTEGER NOT NULL,
'name' VARCHAR(64) NOT NULL,
PRIMARY KEY('id', 'type') ON CONFLICT REPLACE
);
CRE
On Oct 18, 2010, at 6:58 PM, Igor Tandetnik wrote:
> In general, I found that the idiom
>
> TableA left join TableB on (TableA.idInTableB = TableB.someId) where
> TableB.someId is null
>
> almost always performs worse than the equivalent NOT EXISTS or NOT IN query.
Hmmm... in practice it shou
Jeff,
I can agree that on Windows mutex performance can be awful especially
in such frequently called place as pcache1Fetch. So you have only two
options to solve the problem:
1) Split threads into different processes - make it one thread per process.
2) Make your own implementation of pcache. You
Take a look at the custom tokenizer API. I think tokens returned don't
necessarily have to be substrings of the text. So, maybe the text you
"tokenize" could be the file path, but the tokens could be things you
pull from the contents of the file.
Just a thought,
Cheers,
Sam
___
On 18 Oct 2010, at 6:25pm, Powell, Jeff wrote:
> Yes, each thread does its own sqlite3_open(). In fact, I get the same
> behavior when each thread is using completely separate files (for example,
> making multiple copies of the database, with each thread using a different
> copy).
That secon
Yes, each thread does its own sqlite3_open(). In fact, I get the same behavior
when each thread is using completely separate files (for example, making
multiple copies of the database, with each thread using a different copy).
-Jeff
-Original Message-
From: sqlite-users-boun...@sqlite.
I did some profiling of our current application through the Intel Parallel
Studio tools, and it identified the mutex in pcache1Fetch as the primary source
of waits. Each thread acts on its own, sharing nothing with the other threads,
so I would expect that there is zero waiting.
-Jeff
-Or
Nice Explanation Igor.
As it turns out, virtually all of the rows will match up as you correctly
suspected.
Thanks again.
Jack
The information contained in this communication may be CONFIDENTIAL and is
intended only for the use of the recipient(s) named above. If you are not the
intended
> In pcache1Fetch, sqlite mutexes around the cache handling, which appears to
> be causing significant waits/scalability issues in my application. If I
> disable this mutex, the application crashes.
Why do you think that this mutex causes significant waits?
Anyway ...
> Is it possible to cache
Tilghman, Jack wrote:
> Thanks Igor, works great!
>
> Btw, was there something incorrect about the way I had the query setup?
Not incorrect, just wasteful. I suspect the query was spending a lot of time
working through rows where link and node do match up, only to throw them away
in the end. I
Thanks Igor, works great!
Btw, was there something incorrect about the way I had the query setup?
Thanks,
Jack
The information contained in this communication may be CONFIDENTIAL and is
intended only for the use of the recipient(s) named above. If you are not the
intended recipient, you ar
On 18 Oct 2010, at 5:25pm, Powell, Jeff wrote:
> I have an application which utilizes multiple threads, each of which never
> writes to the database, and none of which shares its cache.
>
> In pcache1Fetch, sqlite mutexes around the cache handling, which appears to
> be causing significant wai
On Mon, 18 Oct 2010 15:07:35 +0200, Hilmar Berger
wrote:
> Hi,
>
>thanks to everybody that answered. I tried your suggestions but there
>was no measurable improvement. Possibly this is the best what I can get
>out of Sqlite.
>However, I tried a similar query on a larger table using both SQlite
On Sun, Oct 17, 2010 at 11:13 PM, Dami Laurent (PJ)
wrote:
>>Is it possible to use FTS3 for search without storing the actual file
>>contents/search terms/keywords in a row. In other words, create a FTS3
>>tables with rows that only contains an ID and populate the B-Tree with
>>keywords for search
Tilghman, Jack wrote:
> sqlite trace: prepare statement: SELECT COUNT(*) FROM link LEFT OUTER JOIN
> node ON node.pvid = link.ref_node_pvid WHERE
>link.ref_node_pvid != -1 AND link.ref_node_pvid != -2 AND node.pvid IS
> NULL;
Try this instead:
SELECT COUNT(*) FROM link
where ref_node_pvid
I'm seeing some scaling issues (which I'm hoping someone else has encountered
before). I have an application which utilizes multiple threads, each of which
never writes to the database, and none of which shares its cache.
In pcache1Fetch, sqlite mutexes around the cache handling, which appears
There appears to be a problem in the FTS3 module relating to tokenizers.
In my case if I register a custom tokenizer on a database connection
that does not have a table using the custom tokenizer, then when I run
my program with Valgrind I get a lot of "Use of uninitialised value of
size 4" message
On Mon, Oct 18, 2010 at 8:16 AM, Ian Hardingham wrote:
> Hey guys.
>
> If I wish to log how often a user does action x, I'm assuming I would be
> best off doing something like:
>
> /SELECT whatever FROM actionPerfomedTable WHERE user = y, action = x
> LIMIT 1/
>
> If a record is returned, perform
Thanks Simon.
I have a further question on this topic.
I would like to find out where my user ranks amongst all scores - so I want to
SELECT name, score FROM scoreTable WHERE id=x
And then I wish to know how many rows occur before the one where user=myuser.
I can loop through them in code, b
Please reply to the list, so that others may take part in the
conversation, and so that others with similar future questions can
search out the answers in the mailing list archive.
On Mon, Oct 18, 2010 at 02:55:44PM +0100, Ian Hardingham scratched on the wall:
> Thanks Jay.
>
> A slightly
On 18 Oct 2010, at 4:09pm, Ian Hardingham wrote:
> I also want to add selecting the highest score, and adding it to the
> results assuming it isn't already in there (ie unless it happens your or
> a friends' score is the highest). Could anyone advise me on how best to
> do that?
Can you not
Hey guys. I'm kind of revisiting something I asked about before. I
have a high scores table, and a table of friends, and I wish to select
for user x:
The score of x
The scores of all of x's friends
Ordered by score descending.
I am using this:
SELECT * FROM (SELECT * FROM cupPlayTable WHE
Query hung. Any help. (sqlite & dbi)
SQLite version 3.7.2 built on
This is perl, v5.8.8 built for x86_64-linux-thread-multi
DBI 1.611
DBD::SQLite 1.29
Centos Linux(2.6.18-164.11.1.el5xen) 64bit.
Below are output snippets from two dbi trace files. In the first snippet, the
sqlite.db is "smallish"
On Mon, Oct 18, 2010 at 02:16:55PM +0100, Ian Hardingham scratched on the wall:
> If I wish to log how often a user does action x, I'm assuming I would be
> best off doing something like:
> But it strikes me that an alternative is to have one row for each time
> the user performs action x, and
I have a query which takes 17 minutes to run with 3.7.3 against 800ms
with 3.7.2
The query is:
SELECT x.sheep_no, x.registering_flock, x.date_of_registration
FROM sheep x LEFT JOIN
(SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
s.date_of_registration, prev.owner_change_date
Hey guys.
If I wish to log how often a user does action x, I'm assuming I would be
best off doing something like:
/SELECT whatever FROM actionPerfomedTable WHERE user = y, action = x
LIMIT 1/
If a record is returned, perform
/UPDATE actionPerfomedTable SET number = incremented number WHERE
Hi,
thanks to everybody that answered. I tried your suggestions but there
was no measurable improvement. Possibly this is the best what I can get
out of Sqlite.
However, I tried a similar query on a larger table using both SQlite and
Postgresql (same machine, same table structure + indices, s
I made an error in my SQL when I did not include one of my non-aggregate
columns in my group. I was surprised that Sqlite did not catch this, and even
more surprised when the docs spelled out this behavior.
Is everyone ok with this?
Do any other SQL engines allow this?
(DB2 does not)
Sent from
Interesting. I get the same results as you when I use sqlite3.exe, but, in
a database manager, the result comes back as I reported. I'll contact the
developer of the utility and see if he can come up with something.
On Mon, Oct 18, 2010 at 5:31 AM, Simon Davies
wrote:
> On 18 October 2010 09:28
On 18 October 2010 09:28, Stephen Chrzanowski wrote:
> I seem to be having an odd behavioral problem with calculating time stamps.
>
.
.
.
> For instance:
>
> select strftime('%s','now') RealUTC,strftime('%s','now','localtime')
> LocalTime,
> strftime('%s','now') - strftime('%s','now',
I seem to be having an odd behavioral problem with calculating time stamps.
The software I'm using drops data into a field as a local time stamp (IE:
'2010-10-18 04:08:04.000') which is fine. However, when trying to pull that
data back out and convert the Sqlite NOW time to local, I'm getting
ext
37 matches
Mail list logo