John,
looking at your example I have only some of the
standard remarks that might help to speed up:
1. add an index on tbl_data(cell) since you query/update
for cell = ?", each time.
2. Try to increase the cache for example by using:
db.execDML("PRAGMA page_size = 4096;");
db.execDML("PRAGMA default_cache_size = 10000;");
do this right after you open the DB for the very first time.
3. If you do your AddData in a loop without having a
"BEGIN TRANSACTION" before, there is no doubt that it will
be slow. Try it... If you don't want to have a transaction
open all the time you may try at least couple your AddData
in a transaction for, say, 1000 updates/inserts.
4. Consider to do this on a in-memory data as mentioned allready here?
I think your sparkling, 64Bit, super-size, laptop should be
able to hold some memory... ;)
Hope this helps
Marcus
>
>
>
> Marcus Grimm wrote:
>>
>> you should in any case try to encapsulate your
>> inserting loop with a transaction. you should still
>> be able to do select statements inside, in particular
>> if all is done within one DB connection.
>> transactions do not speed up considerably, they do it dramatically... ;)
>>
>> also you may give a code example how you do this, since
>> there are enough sql gurus around here that may help to
>> improve further.
>>
>> hth
>> Marcus
>>
>>
>
> I have attached a .cpp and .h file that show what I am doing. A couple of
> notes: I am using a C++ wrapper to the sqlite API. The bottleneck is
> obviously in AddData. For testing I am just looping through 10000 randoms
> entries. My last run took about 23 minutes for those 10000 and I have a
> brand spanking new kick-ass Dell 2.93 Ghz laptop running Vista 64-bit.
> The
> helper functions in the AddData take no time at all, they were in use when
> I
> was doing everything in memory. Let me know what you think about SQLite
> end
> of it, I am trying to learn and hoping it can be a useful solution. I
> have
> already incorporated it for other file formats. Ones that don't need
> speed
> intensive, consistent use, of course. I do appreciate the comments (I was
> trying not to say that because the posting rules mention staying away from
> the little talk :-))
>
> John
> --
> View this message in context:
> http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22381160.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
