Re: [sqlite] Suggestion to add locate as a broader version of instr
Hello, Thanks to Keith's help I succeeded to set up a comparison a native sqrt() versus a python mysqrt() function . The speed-up in a best case non-realistic scenario is only 40%. create_function() looks very performant. Regards, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion to add locate as a broader version of instr
Hello Keith, Thanks for the .timer on tip. I only succeed to get a Error: not such function : sqrt from default Sqlite.exe How did you get that native sqrt working ? regards, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion to add locate as a broader version of instr
I built-in an extension which defines all the standard math functions as SQL functions. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of big stone Sent: Monday, 10 March, 2014 13:22 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Suggestion to add locate as a broader version of instr Hello Keith, Thanks for the .timer on tip. I only succeed to get a Error: not such function : sqrt from default Sqlite.exe How did you get that native sqrt working ? regards, ___ 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] Suggestion to add locate as a broader version of instr
Hello Max, Your link is pretty interesting. It looks that : - method1 should be easily implemented with SQLite floating point representation, - and with a very very small code size. Here is the benchmarking of the two available methods : https://raw.github.com/stonebig/ztest_donotuse/master/square_rooting_benchmark.GIF So : - your method is only 3 times slower than the python sqrt(), - if SQLite team accepts to sacrifice a few bytes to implement sqrt(), we may benefit a 389% speed-up (300/27*.652/1.86) at least. (300/27*.652/1.86) sqrt() is very interesting for statistics on-the-go over sql datas. Regards, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion to add locate as a broader version of instr
sqlite create virtual table n using wholenumber; sqlite .timer on sqlite select sum(sqrt(value)) from n where value between 1 and 1000; 21097.4558874807 Run Time: real 0.001 user 0.00 sys 0.00 sqlite select sum(sqrt(value)) from n where value between 1 and 100; 67166.458841 Run Time: real 0.160 user 0.156250 sys 0.00 sqlite select sum(sqrt(value)) from n where value between 1 and 10; 21081851083598.4 Run Time: real 151.021 user 151.031250 sys 0.00 sqlite select sum(value) from n where value between 1 and 10; 55 Run Time: real 89.341 user 89.343750 sys 0.00 A native sqrt takes about 60 ns per operation. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of big stone Sent: Sunday, 9 March, 2014 03:35 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Suggestion to add locate as a broader version of instr Hello Max, Your link is pretty interesting. It looks that : - method1 should be easily implemented with SQLite floating point representation, - and with a very very small code size. Here is the benchmarking of the two available methods : https://raw.github.com/stonebig/ztest_donotuse/master/square_rooting_benc hmark.GIF So : - your method is only 3 times slower than the python sqrt(), - if SQLite team accepts to sacrifice a few bytes to implement sqrt(), we may benefit a 389% speed-up (300/27*.652/1.86) at least. (300/27*.652/1.86) sqrt() is very interesting for statistics on-the-go over sql datas. Regards, ___ 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] Suggestion to add locate as a broader version of instr
Apologies for the interruption and sort of off topic, but, is .timer part of the CLI only or is it part of the SQL language? Can I get the result of a timer from a call, or do I have to put a wrapper on my wrapper? On Sun, Mar 9, 2014 at 8:17 PM, Keith Medcalf kmedc...@dessus.com wrote: sqlite create virtual table n using wholenumber; sqlite .timer on sqlite select sum(sqrt(value)) from n where value between 1 and 1000; 21097.4558874807 Run Time: real 0.001 user 0.00 sys 0.00 sqlite select sum(sqrt(value)) from n where value between 1 and 100; 67166.458841 Run Time: real 0.160 user 0.156250 sys 0.00 sqlite select sum(sqrt(value)) from n where value between 1 and 10; 21081851083598.4 Run Time: real 151.021 user 151.031250 sys 0.00 sqlite select sum(value) from n where value between 1 and 10; 55 Run Time: real 89.341 user 89.343750 sys 0.00 A native sqrt takes about 60 ns per operation. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of big stone Sent: Sunday, 9 March, 2014 03:35 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Suggestion to add locate as a broader version of instr Hello Max, Your link is pretty interesting. It looks that : - method1 should be easily implemented with SQLite floating point representation, - and with a very very small code size. Here is the benchmarking of the two available methods : https://raw.github.com/stonebig/ztest_donotuse/master/square_rooting_benc hmark.GIF So : - your method is only 3 times slower than the python sqrt(), - if SQLite team accepts to sacrifice a few bytes to implement sqrt(), we may benefit a 389% speed-up (300/27*.652/1.86) at least. (300/27*.652/1.86) sqrt() is very interesting for statistics on-the-go over sql datas. Regards, ___ 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] Suggestion to add locate as a broader version of instr
On Sunday, 9 March, 2014 19:38, Stephen Chrzanowski pontia...@gmail.com inquired: Apologies for the interruption and sort of off topic, but, is .timer part of the CLI only or is it part of the SQL language? Can I get the result of a timer from a call, or do I have to put a wrapper on my wrapper? .timer is a shell command specific to the sqlite shell and not part of the SQL language. You would have to put your own wrapper to collect timing data. On Sun, Mar 9, 2014 at 8:17 PM, Keith Medcalf kmedc...@dessus.com wrote: sqlite create virtual table n using wholenumber; sqlite .timer on sqlite select sum(sqrt(value)) from n where value between 1 and 1000; 21097.4558874807 Run Time: real 0.001 user 0.00 sys 0.00 sqlite select sum(sqrt(value)) from n where value between 1 and 100; 67166.458841 Run Time: real 0.160 user 0.156250 sys 0.00 sqlite select sum(sqrt(value)) from n where value between 1 and 10; 21081851083598.4 Run Time: real 151.021 user 151.031250 sys 0.00 sqlite select sum(value) from n where value between 1 and 10; 55 Run Time: real 89.341 user 89.343750 sys 0.00 A native sqrt takes about 60 ns per operation. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of big stone Sent: Sunday, 9 March, 2014 03:35 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Suggestion to add locate as a broader version of instr Hello Max, Your link is pretty interesting. It looks that : - method1 should be easily implemented with SQLite floating point representation, - and with a very very small code size. Here is the benchmarking of the two available methods : https://raw.github.com/stonebig/ztest_donotuse/master/square_rooting_ben c hmark.GIF So : - your method is only 3 times slower than the python sqrt(), - if SQLite team accepts to sacrifice a few bytes to implement sqrt(), we may benefit a 389% speed-up (300/27*.652/1.86) at least. (300/27*.652/1.86) sqrt() is very interesting for statistics on-the-go over sql datas. Regards, ___ 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] Suggestion to add locate as a broader version of instr
On 10 Mar 2014, at 1:38am, Stephen Chrzanowski pontia...@gmail.com wrote: Apologies for the interruption and sort of off topic, but, is .timer part of the CLI only or is it part of the SQL language? Can I get the result of a timer from a call, or do I have to put a wrapper on my wrapper? Commands which start with a dot are part of the Shell Tool, not built into the SQLite API. Furthermore there are no rules about which order SQLite would execute something like SELECT timerBefore(), somethingComplicated(), timerAfter() FROM myTable in. Sorry. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion to add locate as a broader version of instr
On Sat, Mar 8, 2014 at 2:24 AM, big stone stonebi...@gmail.com wrote: Ooups ! Thanks to the awesome posts about RPAD/LPAD, I understood that I could already create a sqrt() function for SQLite3 in interpreted python. Yes, that discussion was inspiring :) Looking at your task I also played with cte version of sqrt. Based on the guessing approach from one of the answers from http://stackoverflow.com/questions/3581528/how-is-the-square-root-function-implemented the following query finally worked. /* :value=12345 */ with recursive sqrt(depth, val, guess) as ( select 1, :value, Cast(:value as Float)/2 UNION ALL select depth + 1, val as newval, ((guess + val/guess)/2) as newguess from sqrt where abs(newguess - guess) 1e-308 and depth 100 ) select guess from sqrt order by depth desc limit 1 but I could not overcome some pecularities of float numbers so depth 100 here is for cases when comparison fails to stop. Also for CTE queries in general I wonder whether there is another faster way to get the last row of the query (in natural executing order), so order by depth can be replaced by something else. I suspect ordering here triggers temporary storage. I tested this function as expression function implemented based on that thread and an average speed of this one is about 4000 sqrt operations / second on a mobile Intel i3. Not so fast, but if one desperately needs one, then it would be ok. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion to add locate as a broader version of instr
Ooups !
Thanks to the awesome posts about RPAD/LPAD, I understood that I could
already create a sqrt() function for SQLite3 in interpreted python.
(example) ***
import sqlite3
db_filename = ':memory:'
def mysqrt(s):
returns sqrt(s)
#must return a string, apparently
return (%s %sqrt(s))
with sqlite3.connect(db_filename) as conn:
conn.create_function('mysqrt', 1, mysqrt)
cursor = conn.cursor()
query = select 'hello, sqrt' , mysqrt(3), 'of ', mysqrt(3)*mysqrt(3)
cursor.execute(query)
for row in cursor.fetchall():
print (row)
cursor.close
cursor = None
conn.close
conn = None
(The link that showed nicely how to play with that) ***
http://pymotw.com/2/sqlite3/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion to add locate as a broader version of instr
+1 . A few more 'classic/simple' sql instructions would not be a bad thing : sqrt(), locate(substring, string, start), ... They are not in a sql official normalisation, but : - '%' is not either, - avg() looks a little bit incomplete without sqrt(). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion to add locate as a broader version of instr
On Fri, Feb 14, 2014 at 2:33 PM, Max Vlasov max.vla...@gmail.com wrote: Hi, Some time ago when there was no instr functions, I looked at Mysql help pages and implemented a user function locate as the one that allows searching starting a particular position in the string. With two parameters form it was just identical to instr only the order of parameters was reversed. As I see, the latest sqlite has only instr. It's not a big deal, but I noticed that locate with three parameters becomes convenient for CTE recursive queries since it allows search sequentially in the string. For example, a little bulky at last, but I managed to do comma-list to dataset query I suppose implementing locate and doing instr as a call to locate would cost the developers probably no more than a hundred of bytes for the final binary Parsing fields is also done with substring_index in MySQL and having both locate and substring_index would be useful for MySQL compatibility. Parsing fields created using group_concat is one particular example. One related item is that in MySQL group_concat can specify the order of rows to be concatenated as well as a number of other aspects not currently available in SQLite. -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Suggestion to add locate as a broader version of instr
Hi, Some time ago when there was no instr functions, I looked at Mysql help pages and implemented a user function locate as the one that allows searching starting a particular position in the string. With two parameters form it was just identical to instr only the order of parameters was reversed. As I see, the latest sqlite has only instr. It's not a big deal, but I noticed that locate with three parameters becomes convenient for CTE recursive queries since it allows search sequentially in the string. For example, a little bulky at last, but I managed to do comma-list to dataset query I suppose implementing locate and doing instr as a call to locate would cost the developers probably no more than a hundred of bytes for the final binary Thanks Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
