Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[big stone]

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"

[Keith Medcalf]

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"

[big stone]

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"

[Simon Slavin]

On 10 Mar 2014, at 1:38am, Stephen Chrzanowski  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"

[Keith Medcalf]

On Sunday, 9 March, 2014 19:38, Stephen Chrzanowski  
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 
>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"

[Stephen Chrzanowski]

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  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"

[Keith Medcalf]

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"

[big stone]

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"

[Max Vlasov]

On Sat, Mar 8, 2014 at 2:24 AM, big stone  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"

[big stone]

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"

[Gabor Grothendieck]

On Fri, Feb 14, 2014 at 2:33 PM, Max Vlasov  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

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[big stone]

+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

[sqlite] Suggestion to add "locate" as a broader version of "instr"

[Max Vlasov]

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