Re: [sqlite] Need Help SQL
#>There is a slight complication with the case where endDate < startDate, #>where you want to wrap around to next year. For that, I simply need to #>perform calculations modulo a large number - any number greater than the #>representation of 12/31 in my scheme. 1300 is one such number. #> #>Igor Tandetnik That slight complication is still there though. It works when the start < end, but it fails the other way around. Pretty clever with the numbering system though. And thanks for suggestion. :-) Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need Help SQL
Rick Ratchford wrote: > Okay, I give. > > What do the 100 and 1300 values signify? I am completely baffled at > how you > arrived at these values and what they do. I simply combine (month, day) pair into a single number - the same way you combine two digits of a decimal number by multiplying the first by 10 and adding the second. The multiplier doesn't have to be 100 - any number greater than 31 will do. Now that I have a flat numbering for calendar dates, I can do a check like this: date - startDate < endDate - startDate There is a slight complication with the case where endDate < startDate, where you want to wrap around to next year. For that, I simply need to perform calculations modulo a large number - any number greater than the representation of 12/31 in my scheme. 1300 is one such number. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need Help SQL
Igor, Okay, I give. What do the 100 and 1300 values signify? I am completely baffled at how you arrived at these values and what they do. Thanks. Rick #>-Original Message- #>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- #>boun...@sqlite.org] On Behalf Of Igor Tandetnik #>Sent: Friday, October 09, 2009 6:46 PM #>To: sqlite-users@sqlite.org #>Subject: Re: [sqlite] Need Help SQL #> #>Rick Ratchford #>> Data Fields: ID, Date, Month, Day, Year, Price #>> #>> Problem: When provided the starting Month/Day numbers, and ending #>> Month/Day numbers, what is the correct way to SQL the database so #>> that the recordset created returns as follows (assume 4 years of #>> data): #>> #>> In other words, all the 3/12's first, then 3/13's, then 3/14's, etc. #>> all the way down to the ending Month/Date. #>> #>> Where I really get stuck is when the Starting Month number is greater #>> than the Ending Month number. For example, say I want the starting #>> Month/Day as 10/22 and the ending Month/Day as 4/16. Simply stating #>> WHERE Month >= Start Month AND Month <= End Month doesn't seem #>> correct. Since I want to return all the prices between 10/22 and 4/16 #>> of each year of data I have in the table, no Month number could be #>> greater than/equal to 10 and also less than/equal to 4. #> #>Try something like this: #> #>select Month, Day, Price from mytable #>where ((Month - :StartMonth)*100 + (Day - :StartDay) + 1300) % 1300 < #> ((:EndMonth - :StartMonth)*100 + (:EndDay - :StartDay) + 1300) #>% 1300 #>order by ((Month - :StartMonth)*100 + (Day - :StartDay) + 1300) % 1300; #> #>Igor Tandetnik #> #>___ #>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
Re: [sqlite] sqlite in-memory database far too slow in my use case
Hello!
On Sunday 11 October 2009 22:52:29 Jay A. Kreibich wrote:
> A bit to my surprise, the difference is even more significant using
> prepared statements in a C program. For a half-million selects over a
> similar table in a :memory: database, there is a 20% speed-up by
> wrapping all the selects in a transaction (vs the 10% you're seeing).
> It's averaging about 4.3 seconds in auto-commit mode, and 3.4 seconds
> in an explicit transaction.
The tcl programm doing copy of the selected rows. May be your C programm
doesn't copy the selected data?
The modified tcl test script can show how transaction increase speed of data
extracting. In this case the test programm perform copy of data too but the
data doesn't extracting from database table when we use the "select NULL"
construction.
The performance increased of ~10% when we extract table data and of
~3% again.
$ ./test.tcl
insert transaction 50 rows
21233766 microseconds per iteration
select 50 rows
28164019 microseconds per iteration
select without extract 50 rows
26379441 microseconds per iteration
select transaction 50 rows
25749923 microseconds per iteration
select transaction without extract 50 rows
25644248 microseconds per iteration
$ cat ./test.tcl
#!/usr/bin/tclsh8.5
package require sqlite3
sqlite3 db :memory:
set limit 50
db eval {create table test(id int primary key, value text)}
puts "insert transaction $limit rows"
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set value "value $i"
db eval {insert into test (value) values ($value)}
}
}
}]
puts "\nselect $limit rows"
puts [time {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select value from test where id=$rowid}
}
}]
puts "\nselect without extract $limit rows"
puts [time {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select NULL from test where id=$rowid}
}
}]
puts "\nselect transaction $limit rows"
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select value from test where id=$rowid}
}
}
}]
puts "\nselect transaction without extract $limit rows"
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select NULL from test where id=$rowid}
}
}
}]
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Imprecise entry into rtree
Hey folks, Thanks for the quick responses. Should have had gmane sort an "rtree" query by date instead of relevance ... On Sun, Oct 11, 2009 at 2:10 PM, George Hartzell wrote: > Wow, there must be an outbreak of SQLite-genome-itis going around. Apparently so! Perhaps it'll pop up on google's *trends soon (a lá http://www.google.org/flutrends/). > I've successfully built an SQLite DB of UCSC's snp130 data using an > rtree_i32 virtual table. > > Try creating the rtree with something like this (from memory): > > create virtual table my_index using rtree_i32 ( >id, >etc > ); Seems to work perfectly, thanks.. > I'd still like to know if rtree_i32 is considered "supported" or > likely to disappear, or If it's going to stay around it'd be nice > to mention it on the rtree page. If there's some problem lurking > behind the corner it'd be nice to know that too. Agreed. Thanks once again, -steve -- Steve Lianoglou Graduate Student: Computational Systems Biology | Memorial Sloan-Kettering Cancer Center | Weill Medical College of Cornell University Contact Info: http://cbio.mskcc.org/~lianos/contact ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] flexible data import
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 P Kishor wrote: > Shame on you Roger Binns, for suggesting CouchDB, because now, it > seems that CouchDB might supplant SQLite as one of my most favorite > pieces of software. It is a great complement to SQLite, and is a better choice when your application is web based. The builtin types are pretty much the same as SQLite but views written in Javascript can then turn the data into anything. It has a larger footprint (10-20MB). What it excels at is "offline mode" and synchronization having been designed in from the start - this is the better solution for those who keep trying to bolt on that kind of thing to SQLite. It also scales up very well (across machines) and with builtin map/reduce. It is starting to be used as an alternative to SQLite in places - for example the next Ubuntu release will store contacts/bookmarks etc in CouchDB because of the ability to synchronize, replicate and work offline. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkrSK+AACgkQmOOfHg372QS0cACgv8FdlZ1UcpAL7FoW6Me0O/yd 7oYAoIT9zUU5gi52iXUrAUUXa5ZYxdJ4 =d1YE -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite in-memory database far too slow in my use case
On Sun, Oct 11, 2009 at 11:49:57AM +0400, Alexey Pechnikov scratched on the wall: > Hello! > > On Sunday 11 October 2009 00:54:04 Simon Slavin wrote: > > > Using transactions speeds up a long series of SELECTs because it > > > eliminates the need to re-acquire a read-only file-lock for each > > > individual SELECT. > > > > > > Since in-memory databases have no file locks, I'm not sure that is > > > relevant to this specific case. > > > > I wasn't sure about that. It could still be slower > > You can check it very easy. > > In transactions: > ve...@veter-laptop:/tmp$ ./test.tcl > 19968119 microseconds per iteration > 25649514 microseconds per iteration > > Without transactions: > ve...@veter-laptop:/tmp$ ./test.tcl > 35586024 microseconds per iteration > 28630785 microseconds per iteration A bit to my surprise, the difference is even more significant using prepared statements in a C program. For a half-million selects over a similar table in a :memory: database, there is a 20% speed-up by wrapping all the selects in a transaction (vs the 10% you're seeing). It's averaging about 4.3 seconds in auto-commit mode, and 3.4 seconds in an explicit transaction. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA journal_mode
Am Sun, 11 Oct 2009 10:45:57 -0500 schrieb Jay A. Kreibich: > The docs for "PRAGMA journal_mode" are pretty clear on this: it > returns the current mode (i.e. after the command is run) because it may > not be able to change to the requested mode. Thanks. That made me realize that my local copy of the docs is a little outdated (3.6.7) ... fixed now. :-) Wolfgang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Imprecise entry into rtree
Igor Tandetnik writes: > Steve Lianoglou wrote: > > So I'm building a db with some RTree indices to store genome > > information. Among other things, I have an RTree index to store the > > start/end positions of genes on chromosomes. The problem is that the > > numbers stored in the RTree aren't the ones I'm entering, sometimes > > they can be several base pairs (int values) off. > > This has been discussed just the other day: > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg47004.html > Wow, there must be an outbreak of SQLite-genome-itis going around. I've successfully built an SQLite DB of UCSC's snp130 data using an rtree_i32 virtual table. Try creating the rtree with something like this (from memory): create virtual table my_index using rtree_i32 ( id, etc ); I'd still like to know if rtree_i32 is considered "supported" or likely to disappear, or If it's going to stay around it'd be nice to mention it on the rtree page. If there's some problem lurking behind the corner it'd be nice to know that too. g. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ron Arts wrote: > Will the amalgamated version be faster > than linking the lib at runtime? The SQLite website quotes a 10% performance improvement for the amalgamation. The reason for the improvement is that the compiler gets to see all the SQLite code at once and so makes good decisions about inlining and other optimizations. (Note that in some cases the library was builtin from the amalgamation so there won't be that much difference.) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkrSEN0ACgkQmOOfHg372QRCNACbBC0ZXP1Crg/Qlx8xUlpBudBQ N4IAoMb01aGGvBYOAnMPwVCZsqHyAux0 =crfu -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)
Are there compile time switches which I can use to speed up
selects in memory databases? Will the amalgamated version be faster
than linking the lib at runtime?
Thanks,
Ron
Pavel Ivanov schreef:
>> I use the following queries:
>>
>> CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>
> I'm not sure how SQLite treats this table definition but probably
> because of your ASC it could decide that id shouldn't be a synonym for
> rowid which will make at least inserts slower.
>
>> But I'm still looking to speed up selects.
>
> Other conditions that can speed up selects:
> - Depending on relation between size of your database and size of
> database cache selects could work faster if all database is cached. So
> for example in just started application first set of selects will work
> slower than second. But if full database cannot fit into cache then
> different sets of selects will have the same performance on average.
> - If you don't care about changing your database concurrently from
> other processes you can place all your inserts in one transaction or
> in case of your real application just start transaction at the
> beginning and commit/rollback it at the end.
>
> Tell us if it still doesn't satisfy you.
>
>
> Pavel
>
> On Sun, Oct 11, 2009 at 4:04 AM, Ron Arts wrote:
>> Olaf Schmidt schreef:
>>> "Ron Arts" schrieb im
>>> Newsbeitrag news:4ad10a9e.3040...@arts-betel.org...
>>>
Here's my new benchmark output:
sqlite3 insert 50 records time: 17.19 secs
sqlite3 select 50 records time: 18.57 secs
sqlite3 prepared select 50 records time: 3.27 secs
glib2 hash tables insert 50 records time: 0.38 secs
glib2 hash tables lookup 50 records time: 0.24 secs
The prepared select indeed speeds up things tremendously,
a 5-fold increase.
>>> Now do the same thing (prepared commands) for
>>> the sqlite3 - inserts too ... wrapped in a transaction.
>>>
>>> Against an InMemory-Table I reach here about
>>> 12 Inserts per second (filling up and inserting
>>> "mixed values" against a 8-column-table).
>>> That's on a 1.8GHz Intel-(mobile)CPU.
>>>
>>> As long as your benchmarked-tabledef (your insert) has not
>>> much more columns than my above mentioned '8', then
>>> you should see something like "factor 5" there too.
>>>
>>> With a small two-column-table (a prepared two-column-insert-Cmd)
>>> I see about 36 inserts per second, somewhat depending
>>> on the used datatypes (Integers and Doubles work a bit faster of
>>> course than inserting the same "amount of Columns" as Text-Values).
>>>
>>> Another reason for your bad insert-performance could of
>>> course be, that you already defined an index on the table
>>> in question (or in case your ID-Field is defined as
>>> INTEGER PRIMARY KEY *and* you're filling up
>>> new IDs in non-consecutive order).
>>> In that case your current results seem a bit more reasonable.
>>>
>>> If you don't have an index created yet (on your "HashKey-
>>> ID-Column" ... or if you don't have mapped your ID-Field
>>> to SQLites RowID yet (per INTEGER PRIMARY KEY)
>>> then you maybe should try to create one - if possible, after
>>> your "main-amount" of fillups was done - that adds some
>>> additional time to your overall-data-preparation efforts of your
>>> "table-list" - but will be of benefit for your single-record-lookups,
>>> based on your "... Where ID = ? ".
>>>
>>>
>> Olaf,
>>
>> I tried it, and indeed, this speeds up inserts tremendously as well,
>> but in fact I'm not at all concernced about insert speed, but much more about
>> select speed. I use the following queries:
>>
>> CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>>
>> Then I insert 50 records like this:
>>
>> INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
>>
>> (with consecutive values for the id value.)
>>
>> do you expect the id column now to be mapped to the Row ID, so
>> this results in the fastest possible way of selecting by id?
>>
>> I now get this:
>>
>> sqlite3 prepared insert in trx 50 records time: 5.08 secs
>> sqlite3 select 50 records time: 19.28 secs
>> sqlite3 prepared select 50 records time: 3.47 secs
>> glib2 hash tables insert 50 records time: 0.37 secs
>> glib2 hash tables lookup 50 records time: 0.25 secs
>>
>> But I'm still looking to speed up selects.
>>
>> Thanks,
>> Ron
>>
>>> Olaf Schmidt
>>>
>>>
>>>
>>> ___
>>> 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
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-
Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)
Pavel Ivanov schreef:
>> I use the following queries:
>>
>> CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>
> I'm not sure how SQLite treats this table definition but probably
> because of your ASC it could decide that id shouldn't be a synonym for
> rowid which will make at least inserts slower.
>
>> But I'm still looking to speed up selects.
>
> Other conditions that can speed up selects:
> - Depending on relation between size of your database and size of
> database cache selects could work faster if all database is cached. So
> for example in just started application first set of selects will work
> slower than second. But if full database cannot fit into cache then
> different sets of selects will have the same performance on average.
> - If you don't care about changing your database concurrently from
> other processes you can place all your inserts in one transaction or
> in case of your real application just start transaction at the
> beginning and commit/rollback it at the end.
>
> Tell us if it still doesn't satisfy you.
>
Pavel,
does the cache work for memory datsbases too?
Thanks,
Ron
>
> Pavel
>
> On Sun, Oct 11, 2009 at 4:04 AM, Ron Arts wrote:
>> Olaf Schmidt schreef:
>>> "Ron Arts" schrieb im
>>> Newsbeitrag news:4ad10a9e.3040...@arts-betel.org...
>>>
Here's my new benchmark output:
sqlite3 insert 50 records time: 17.19 secs
sqlite3 select 50 records time: 18.57 secs
sqlite3 prepared select 50 records time: 3.27 secs
glib2 hash tables insert 50 records time: 0.38 secs
glib2 hash tables lookup 50 records time: 0.24 secs
The prepared select indeed speeds up things tremendously,
a 5-fold increase.
>>> Now do the same thing (prepared commands) for
>>> the sqlite3 - inserts too ... wrapped in a transaction.
>>>
>>> Against an InMemory-Table I reach here about
>>> 12 Inserts per second (filling up and inserting
>>> "mixed values" against a 8-column-table).
>>> That's on a 1.8GHz Intel-(mobile)CPU.
>>>
>>> As long as your benchmarked-tabledef (your insert) has not
>>> much more columns than my above mentioned '8', then
>>> you should see something like "factor 5" there too.
>>>
>>> With a small two-column-table (a prepared two-column-insert-Cmd)
>>> I see about 36 inserts per second, somewhat depending
>>> on the used datatypes (Integers and Doubles work a bit faster of
>>> course than inserting the same "amount of Columns" as Text-Values).
>>>
>>> Another reason for your bad insert-performance could of
>>> course be, that you already defined an index on the table
>>> in question (or in case your ID-Field is defined as
>>> INTEGER PRIMARY KEY *and* you're filling up
>>> new IDs in non-consecutive order).
>>> In that case your current results seem a bit more reasonable.
>>>
>>> If you don't have an index created yet (on your "HashKey-
>>> ID-Column" ... or if you don't have mapped your ID-Field
>>> to SQLites RowID yet (per INTEGER PRIMARY KEY)
>>> then you maybe should try to create one - if possible, after
>>> your "main-amount" of fillups was done - that adds some
>>> additional time to your overall-data-preparation efforts of your
>>> "table-list" - but will be of benefit for your single-record-lookups,
>>> based on your "... Where ID = ? ".
>>>
>>>
>> Olaf,
>>
>> I tried it, and indeed, this speeds up inserts tremendously as well,
>> but in fact I'm not at all concernced about insert speed, but much more about
>> select speed. I use the following queries:
>>
>> CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>>
>> Then I insert 50 records like this:
>>
>> INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
>>
>> (with consecutive values for the id value.)
>>
>> do you expect the id column now to be mapped to the Row ID, so
>> this results in the fastest possible way of selecting by id?
>>
>> I now get this:
>>
>> sqlite3 prepared insert in trx 50 records time: 5.08 secs
>> sqlite3 select 50 records time: 19.28 secs
>> sqlite3 prepared select 50 records time: 3.47 secs
>> glib2 hash tables insert 50 records time: 0.37 secs
>> glib2 hash tables lookup 50 records time: 0.25 secs
>>
>> But I'm still looking to speed up selects.
>>
>> Thanks,
>> Ron
>>
>>> Olaf Schmidt
>>>
>>>
>>>
>>> ___
>>> 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
>>
> ___
> 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
Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)
> I use the following queries:
>
> CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
I'm not sure how SQLite treats this table definition but probably
because of your ASC it could decide that id shouldn't be a synonym for
rowid which will make at least inserts slower.
> But I'm still looking to speed up selects.
Other conditions that can speed up selects:
- Depending on relation between size of your database and size of
database cache selects could work faster if all database is cached. So
for example in just started application first set of selects will work
slower than second. But if full database cannot fit into cache then
different sets of selects will have the same performance on average.
- If you don't care about changing your database concurrently from
other processes you can place all your inserts in one transaction or
in case of your real application just start transaction at the
beginning and commit/rollback it at the end.
Tell us if it still doesn't satisfy you.
Pavel
On Sun, Oct 11, 2009 at 4:04 AM, Ron Arts wrote:
> Olaf Schmidt schreef:
>> "Ron Arts" schrieb im
>> Newsbeitrag news:4ad10a9e.3040...@arts-betel.org...
>>
>>> Here's my new benchmark output:
>>>
>>> sqlite3 insert 50 records time: 17.19 secs
>>> sqlite3 select 50 records time: 18.57 secs
>>> sqlite3 prepared select 50 records time: 3.27 secs
>>> glib2 hash tables insert 50 records time: 0.38 secs
>>> glib2 hash tables lookup 50 records time: 0.24 secs
>>>
>>> The prepared select indeed speeds up things tremendously,
>>> a 5-fold increase.
>>
>> Now do the same thing (prepared commands) for
>> the sqlite3 - inserts too ... wrapped in a transaction.
>>
>> Against an InMemory-Table I reach here about
>> 12 Inserts per second (filling up and inserting
>> "mixed values" against a 8-column-table).
>> That's on a 1.8GHz Intel-(mobile)CPU.
>>
>> As long as your benchmarked-tabledef (your insert) has not
>> much more columns than my above mentioned '8', then
>> you should see something like "factor 5" there too.
>>
>> With a small two-column-table (a prepared two-column-insert-Cmd)
>> I see about 36 inserts per second, somewhat depending
>> on the used datatypes (Integers and Doubles work a bit faster of
>> course than inserting the same "amount of Columns" as Text-Values).
>>
>> Another reason for your bad insert-performance could of
>> course be, that you already defined an index on the table
>> in question (or in case your ID-Field is defined as
>> INTEGER PRIMARY KEY *and* you're filling up
>> new IDs in non-consecutive order).
>> In that case your current results seem a bit more reasonable.
>>
>> If you don't have an index created yet (on your "HashKey-
>> ID-Column" ... or if you don't have mapped your ID-Field
>> to SQLites RowID yet (per INTEGER PRIMARY KEY)
>> then you maybe should try to create one - if possible, after
>> your "main-amount" of fillups was done - that adds some
>> additional time to your overall-data-preparation efforts of your
>> "table-list" - but will be of benefit for your single-record-lookups,
>> based on your "... Where ID = ? ".
>>
>>
>
> Olaf,
>
> I tried it, and indeed, this speeds up inserts tremendously as well,
> but in fact I'm not at all concernced about insert speed, but much more about
> select speed. I use the following queries:
>
> CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>
> Then I insert 50 records like this:
>
> INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
>
> (with consecutive values for the id value.)
>
> do you expect the id column now to be mapped to the Row ID, so
> this results in the fastest possible way of selecting by id?
>
> I now get this:
>
> sqlite3 prepared insert in trx 50 records time: 5.08 secs
> sqlite3 select 50 records time: 19.28 secs
> sqlite3 prepared select 50 records time: 3.47 secs
> glib2 hash tables insert 50 records time: 0.37 secs
> glib2 hash tables lookup 50 records time: 0.25 secs
>
> But I'm still looking to speed up selects.
>
> Thanks,
> Ron
>
>> Olaf Schmidt
>>
>>
>>
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 create index ... on conflict
If you can re-create the table (or create some temporary one, transfer
all your data to it, drop old one and rename temporary the same way as
the original table) then you can do it like this:
CREATE TABLE AVP_VAC("avp_id", "vacation_start_date", "vacation_length",
"vacation_status", "vacation_plan_note", "temp_charge_id1",
"temp_charge_id2", "temp_charge_id3", "employee_id", "status",
"created_date", "author_id",
UNIQUE (avp_id, vacation_start_date) ON CONFLICT REPLACE);
It will have the same effect as your CREATE TABLE and CREATE INDEX combined.
Pavel
On Sat, Oct 10, 2009 at 11:35 PM, Hajime MATSUMOTO wrote:
> thank you for replying my question.
> i am wondering if i can use ALTER TABLE for two columns unique.
> i am showing my sql and also i tryed what you said.
>
> please take a look.
>
> CREATE TABLE AVP_VAC("avp_id", "vacation_start_date", "vacation_length",
> "vacation_status", "vacation_plan_note", "temp_charge_id1",
> "temp_charge_id2", "temp_charge_id3", "employee_id", "status",
> "created_date", "author_id");
> CREATE UNIQUE INDEX vac_index ON AVP_VAC(avp_id, vacation_start_date)
> ON CONFLICT REPLACE;
> SQL error near line 3: near "ON": syntax error
>
>
>
> ALTER TABLE ADD COLUMN UNIQUE(avap_id, vacation_start_date) ON CONFLICT
> REPLACE;
> SQL error near line 3: near "ADD": syntax error
>
> Hajime
>
>
>
> Simon Slavin
>> On 10 Oct 2009, at 9:27am, Hajime MATSUMOTO wrote:
>>
>>
>>> i used to use "CREATE INDEX" with "ON CONFLICT".
>>> i am sure it was able to do on sqlite 2.x.x.
>>> but i tryed on sqlite3 it dose not work.
>>>
>>
>> You can use 'ON CONFLICT' in either of
>>
>> CREATE TABLE
>> ALTER TABLE ADD COLUMN ... UNIQUE
>>
>> but not when defining an INDEX after you've defined the columns it
>> depends on.
>>
>> Simon.
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Imprecise entry into rtree
Steve Lianoglou wrote: > So I'm building a db with some RTree indices to store genome > information. Among other things, I have an RTree index to store the > start/end positions of genes on chromosomes. The problem is that the > numbers stored in the RTree aren't the ones I'm entering, sometimes > they can be several base pairs (int values) off. This has been discussed just the other day: http://www.mail-archive.com/sqlite-users@sqlite.org/msg47004.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Imprecise entry into rtree
On Sun, Oct 11, 2009 at 11:29:02AM -0400, Steve Lianoglou scratched on the wall: > This row should have start=94622317, why is it inserting 94622320? > > I know the rtree documentation says that the index "does not normally > provide the exact answer," but I guess I'd be a bit surprised if this > is what is supposed to be happening. Is this expected behavior? Yes. RTrees use 32-bit floating point numbers. They have 23+1 bits of precision, which translates to about 7.2 base-10 significant digits. You're numbers are bigger, so you're starting to see rounding and clipping issues. Also see this discussion from a few days ago: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg47004.html If you can use integers, there appears to be some undocumented methods to convert RTrees to integer indexes. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA journal_mode
On Sun, Oct 11, 2009 at 01:22:22PM +0200, Wolfgang Enzinger scratched on the wall: > Apologies if this topic has been discussed before ... > > I just came across a surprising behaviour when executing this command: > PRAGMA journal_mode=; > > I would have expected that sqlite3_step() will return SQLITE_DONE with this > command, as is the case with (AFAIK) any other PRAGMA command that assigns > a value, i.e. a "=" is contained. > > However, SQLITE_ROW ist returned instead because the command not only sets > the new value but also returns the current (?) one. The docs for "PRAGMA journal_mode" are pretty clear on this: it returns the current mode (i.e. after the command is run) because it may not be able to change to the requested mode. > Is this behaviour intended? It can bee watched in the CLI also. Yes. Any SQL value that is "returned" is returned as a result set, i.e. a table. The only way to get result sets back out of the database using sqlite3_step() is via SQLITE_ROW. The function sqlite3_step() returns a C value, but that's an API error/result code, not a return code from the SQL command. Similar things can be seen with sqlite3_exec() or sqlite3_get_table(). If you set "PRAGMA count_changes=yes", INSERT, UPDATE, and DELETE will also cause sqlite3_step()/sqlite3_exec()/sqlite3_get_table() to return a one-row/one-column result set. Also, if you call "PRAGMA table_info( )", you'll get a six-column result with one row for each column of the specified table. In all cases, these are just values returned by SQL commands. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Imprecise entry into rtree
Hi all, So I'm building a db with some RTree indices to store genome information. Among other things, I have an RTree index to store the start/end positions of genes on chromosomes. The problem is that the numbers stored in the RTree aren't the ones I'm entering, sometimes they can be several base pairs (int values) off. For instance, here's an RTree "table" I'm using to store the start/end boundaries of genes: CREATE VIRTUAL TABLE gene_tree USING rtree( id, -- primary & foreign key to gene.id start, -- as far 5' as the farthest transcript (+ strand) end,-- as far 3' as the farthest transcript (+ strand) chr1, -- bound on the chromosome (fkey to chromosome.id) chr2-- same ); Let's insert this info for a gene on chromosome 14: INSERT INTO gene_tree (id, start, end, chr1, chr2) VALUES (1, 94622317.0, 94693512.0, 14, 14); Now, selecting from gene_tree sqlite> select * from gene_tree; id start end chr1chr2 -- -- -- -- -- 1 94622320.0 94693512.0 14.014.0 This row should have start=94622317, why is it inserting 94622320? I know the rtree documentation says that the index "does not normally provide the exact answer," but I guess I'd be a bit surprised if this is what is supposed to be happening. Is this expected behavior? I'm using sqlite version 3.6.18 Thanks for any help, -steve -- Steve Lianoglou Graduate Student: Computational Systems Biology | Memorial Sloan-Kettering Cancer Center | Weill Medical College of Cornell University Contact Info: http://cbio.mskcc.org/~lianos/contact ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)
"Ron Arts" schrieb im
Newsbeitrag news:4ad19195.2060...@arts-betel.org...
> I tried it, and indeed, this speeds up inserts tremendously as well,
> but in fact I'm not at all concernced about insert speed, but much more
about
> select speed. I use the following queries:
>
>CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
Not sure, if an explicit "text-storage-class-hint" for your name
column will help to speed things up a bit more, but try:
CREATE TABLE company(id INTEGER PRIMARY KEY, name TEXT)
> Then I insert 50 records like this:
>
>INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
You should prepare the statement like this:
INSERT INTO company (id, name) VALUES (?, ?)
And then treat the (now RowID-mapped) Integer ID as an
Integer, not as a String.
Just use the correctly "typed" binding-calls.
And then wrap the Insert-Loop within a transaction.
> But I'm still looking to speed up selects.
With regards to Selects (searching for random "single IDs"), you
probably already reached the maximum (in case you've not done
any mistakes with the Type-Binding).
Olaf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] flexible data import
On Sun, Oct 11, 2009 at 3:16 AM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > .. > > If each "entry" will have a random set of arbitrary columns then you'll be > better off with a schema less database - CouchDB is my favourite and the > Javascript it includes has XML processing primitives. > .. Shame on you Roger Binns, for suggesting CouchDB, because now, it seems that CouchDB might supplant SQLite as one of my most favorite pieces of software. Have just started playing with it, but CouchDB really seems to be a great piece of work. Thanks. -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Schaumburg, IL, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)
On 11 Oct 2009, at 9:04am, Ron Arts wrote:
> CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>
> Then I insert 50 records like this:
>
> INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
>
> (with consecutive values for the id value.)
I think you can remove the single quotes from around your value for
the id column, because both the column definition and the values
you're supplying for it are integers rather than strings. This might
speed up your INSERT even more.
> more about
> select speed
When testing SELECT speeds to work out how best to use the library,
include code to take the values that are returned and put them into
some variables in whatever language you're using. In some languages,
using some compilers, and with some optimisations turned on, the
SELECT command itself executes quickly but extracting the values to
variables takes longer.
Also, note that if you don't use the values from the variables some
compilers (e.g. recent versions of gcc) will optimise out the routines
which are meant to set the values. So if you're running comparative
speed tests and getting weird results try using the variables, for
example printing them to /dev/null.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA journal_mode
Apologies if this topic has been discussed before ... I just came across a surprising behaviour when executing this command: PRAGMA journal_mode=; I would have expected that sqlite3_step() will return SQLITE_DONE with this command, as is the case with (AFAIK) any other PRAGMA command that assigns a value, i.e. a "=" is contained. However, SQLITE_ROW ist returned instead because the command not only sets the new value but also returns the current (?) one. Is this behaviour intended? It can bee watched in the CLI also. Wolfgang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)
Alexey Pechnikov schreef:
> Hello!
>
> On Sunday 11 October 2009 12:04:37 Ron Arts wrote:
>>CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>>
>> Then I insert 50 records like this:
>>
>>INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
>>
>> (with consecutive values for the id value.)
>>
>> do you expect the id column now to be mapped to the Row ID, so
>> this results in the fastest possible way of selecting by id?
>
> Yes, the id is alias for rowid in your table. Check it by
> sqlite3 > explain query plan select name from company where id=1;
>
>> I now get this:
>>
>> sqlite3 prepared insert in trx 50 records time: 5.08 secs
>> sqlite3 select 50 records time: 19.28 secs
>> sqlite3 prepared select 50 records time: 3.47 secs
>> glib2 hash tables insert 50 records time: 0.37 secs
>> glib2 hash tables lookup 50 records time: 0.25 secs
>>
>> But I'm still looking to speed up selects.
>
> Hm... I think you may not open more than 8 000 tcp/ip sockets per second
> in common case and so SQLite speed is good enough. Why you write about
> "to handle tens of thousands requests per second"?
>
Well, in my case there can be tens of thousands of connections open
at the same time, where each connection can last days.
Each connection can spit out multiple messages per second, and each
message need around 5 SQL queries.
Ron
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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
Re: [sqlite] flexible data import
Hello! On Sunday 11 October 2009 12:56:59 Alexander Poddey wrote: > my question again: does something like this already exist? There are a few discussion topics about the ETL solutions. [sqlite] Someone knows about an ETL tool in foreign languages? [sqlite] Software operation and ETL tools [sqlite] Low-Cost data migration and ETL tools etc. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] flexible data import
Hi, no, its neither about the database structure to be changed ( i know about ALTER TABLE ADD COLUMN) nor about xml (I have xerces in use). the question is about: given a fixed database and serveral different sources with partly encoded information e.g. in filenames and (relative) paths in the collection. this means that i regularly need to formulate 'rules' how to extract the needed parametervalues (to be stored in the database) from filenames and paths etc. background: my application code is in c/c++. I have xml parsing posibilities -> my approach would be to partly tokenize filenames and paths in c++. the rules for forming the tokens etc. could be provided to the importer via xml, as well as the missing information (in the example above the authors name). another example: suppose we would have an importer-layer understanding rules written in xml as follows: filename _ _ lets say this would extract the needed parameter 'month' from the filename. this however would allow me import the 'day' without changing the importer code - only the riles need to be adapted: filename _ _ with a set of simple basic rules, importing from different sources would be just a matter of writing the xml rules without the need to change the importer code itself. did this become clearer now? my question again: does something like this already exist? alex > Alexander Poddey wrote: > > does there exist some approaches to deal with situations like this? > > It isn't clear exactly what your question is. You can quite happily add > columns to an existing table at any time using ALTER TABLE ADD COLUMN. > > If each "entry" will have a random set of arbitrary columns then you'll be > better off with a schema less database - CouchDB is my favourite and the > Javascript it includes has XML processing primitives. > > If your question is generally about dealing with XML then I suggest fixing > a large amount of padding on the table in front of you right in the spot > where you will be repeatedly banging your head. > > Roger > ___ > 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
Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)
Hello!
On Sunday 11 October 2009 12:04:37 Ron Arts wrote:
>CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
>
> Then I insert 50 records like this:
>
>INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
>
> (with consecutive values for the id value.)
>
> do you expect the id column now to be mapped to the Row ID, so
> this results in the fastest possible way of selecting by id?
Yes, the id is alias for rowid in your table. Check it by
sqlite3 > explain query plan select name from company where id=1;
> I now get this:
>
> sqlite3 prepared insert in trx 50 records time: 5.08 secs
> sqlite3 select 50 records time: 19.28 secs
> sqlite3 prepared select 50 records time: 3.47 secs
> glib2 hash tables insert 50 records time: 0.37 secs
> glib2 hash tables lookup 50 records time: 0.25 secs
>
> But I'm still looking to speed up selects.
Hm... I think you may not open more than 8 000 tcp/ip sockets per second
in common case and so SQLite speed is good enough. Why you write about
"to handle tens of thousands requests per second"?
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] flexible data import
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alexander Poddey wrote: > does there exist some approaches to deal with situations like this? It isn't clear exactly what your question is. You can quite happily add columns to an existing table at any time using ALTER TABLE ADD COLUMN. If each "entry" will have a random set of arbitrary columns then you'll be better off with a schema less database - CouchDB is my favourite and the Javascript it includes has XML processing primitives. If your question is generally about dealing with XML then I suggest fixing a large amount of padding on the table in front of you right in the spot where you will be repeatedly banging your head. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkrRlHIACgkQmOOfHg372QRBuQCgoYiIaac0Zv0FpNAGnjjmLZaV Qg0AnRfw3nz/+qtbtpVuL0dqylvm9LAv =H8c+ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)
Olaf Schmidt schreef:
> "Ron Arts" schrieb im
> Newsbeitrag news:4ad10a9e.3040...@arts-betel.org...
>
>> Here's my new benchmark output:
>>
>> sqlite3 insert 50 records time: 17.19 secs
>> sqlite3 select 50 records time: 18.57 secs
>> sqlite3 prepared select 50 records time: 3.27 secs
>> glib2 hash tables insert 50 records time: 0.38 secs
>> glib2 hash tables lookup 50 records time: 0.24 secs
>>
>> The prepared select indeed speeds up things tremendously,
>> a 5-fold increase.
>
> Now do the same thing (prepared commands) for
> the sqlite3 - inserts too ... wrapped in a transaction.
>
> Against an InMemory-Table I reach here about
> 12 Inserts per second (filling up and inserting
> "mixed values" against a 8-column-table).
> That's on a 1.8GHz Intel-(mobile)CPU.
>
> As long as your benchmarked-tabledef (your insert) has not
> much more columns than my above mentioned '8', then
> you should see something like "factor 5" there too.
>
> With a small two-column-table (a prepared two-column-insert-Cmd)
> I see about 36 inserts per second, somewhat depending
> on the used datatypes (Integers and Doubles work a bit faster of
> course than inserting the same "amount of Columns" as Text-Values).
>
> Another reason for your bad insert-performance could of
> course be, that you already defined an index on the table
> in question (or in case your ID-Field is defined as
> INTEGER PRIMARY KEY *and* you're filling up
> new IDs in non-consecutive order).
> In that case your current results seem a bit more reasonable.
>
> If you don't have an index created yet (on your "HashKey-
> ID-Column" ... or if you don't have mapped your ID-Field
> to SQLites RowID yet (per INTEGER PRIMARY KEY)
> then you maybe should try to create one - if possible, after
> your "main-amount" of fillups was done - that adds some
> additional time to your overall-data-preparation efforts of your
> "table-list" - but will be of benefit for your single-record-lookups,
> based on your "... Where ID = ? ".
>
>
Olaf,
I tried it, and indeed, this speeds up inserts tremendously as well,
but in fact I'm not at all concernced about insert speed, but much more about
select speed. I use the following queries:
CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
Then I insert 50 records like this:
INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')
(with consecutive values for the id value.)
do you expect the id column now to be mapped to the Row ID, so
this results in the fastest possible way of selecting by id?
I now get this:
sqlite3 prepared insert in trx 50 records time: 5.08 secs
sqlite3 select 50 records time: 19.28 secs
sqlite3 prepared select 50 records time: 3.47 secs
glib2 hash tables insert 50 records time: 0.37 secs
glib2 hash tables lookup 50 records time: 0.25 secs
But I'm still looking to speed up selects.
Thanks,
Ron
> Olaf Schmidt
>
>
>
> ___
> 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
Re: [sqlite] sqlite in-memory database far too slow in my use case
Hello!
On Sunday 11 October 2009 00:54:04 Simon Slavin wrote:
> > Using transactions speeds up a long series of SELECTs because it
> > eliminates the need to re-acquire a read-only file-lock for each
> > individual SELECT.
> >
> > Since in-memory databases have no file locks, I'm not sure that is
> > relevant to this specific case.
>
> I wasn't sure about that. It could still be slower
You can check it very easy.
In transactions:
ve...@veter-laptop:/tmp$ ./test.tcl
19968119 microseconds per iteration
25649514 microseconds per iteration
Without transactions:
ve...@veter-laptop:/tmp$ ./test.tcl
35586024 microseconds per iteration
28630785 microseconds per iteration
$ cat ./test.tcl
#!/usr/bin/tclsh8.5
package require sqlite3
sqlite3 db :memory:
set limit 50
db eval {create table test(id int primary key, value text)}
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set value "value $i"
db eval {insert into test (value) values ($value)}
}
}
}]
puts [time {
db transaction {
for {set i 0} {$i<$limit} {incr i} {
set rowid [expr round(rand()*$limit)]
db onecolumn {select value from test where rowid=$rowid}
}
}
}]
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] flexible data import
Hi all, i plan to use a sqlite database in a situation, where data needs to be imported from different sources with changing 'structure' regularly. to get concrete: for a scientific project - a database for pattern recognition test pictures - i have lots of pictures which can be characterized by a large number of parameters (e.g. when / where taken by whom, bw or colour, etc...). i plan to store the parameters and the relative path to the file (i don't want to have the pictures themself in the database). i will regularly get collections of pictures from different people. the values for the parameters stored in the database may be coded in the path and/or in the filename within the collection or be missing. My approach would be to have some sort of script language which allows me to easily formulate rules in order to extract parameter values from filenames, and combine this with extra information. small example: given the following directory structure and files: collection/ bw/ 2009_10_11_pic1.png 2009_10_11_pic2.png 2009_10_11_pic3.png assume we would have to fill the follwing parameters for the database: author mode (bw/color) year month day I could use xml snippets to provide missing information, e.g. nameoftheauthor mode could be extracted via string tokenizer: between 1. and 2nd / year: between 2nd / and following _ and so on. does there exist some approaches to deal with situations like this? thanks alex ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
