www.digitalmars.com         C & C++   DMDScript  

digitalmars.D - mysql user definde functions (UDF) in D

reply Martin Tschierschke <mt smartdolphin.de> writes:
I am wondering has anybody tried to do it with D?

http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html

By looking on the processing chain for rendering a web page, 
based on mysql data,
I thought it would be very efficient, to let mysql deliver the 
datasets already rendered as html using a template engine like 
vibe.d diet templates.

At the end of this integration process even the web server could 
be integrated in the db engine. I know this has been done as 
prove of concept for an other (db) system to deliver json data 
via http.

Any suggestion?

Regards mt.
Jan 12 2017
parent reply crimaniak <crimaniak gmail.com> writes:
On Thursday, 12 January 2017 at 09:49:08 UTC, Martin Tschierschke 
wrote:

 I am wondering has anybody tried to do it with D?

 http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html

 By looking on the processing chain for rendering a web page, 
 based on mysql data,
 I thought it would be very efficient, to let mysql deliver the 
 datasets already rendered as html using a template engine like 
 vibe.d diet templates.

 At the end of this integration process even the web server 
 could be integrated in the db engine. I know this has been done 
 as prove of concept for an other (db) system to deliver json 
 data via http.

 Any suggestion?


1. I don't see, why moving HTML rendering task from vibe.d thread 
to MySQL thread will give CPU benefit.
2. HTML usually bigger then data used to construct it (sometimes 
much more bigger) so it will be more traffic in pipe/socket 
between MySQL and vibe.d
3. MySQL database more hard to scale than WEB frontend so moving 
more work to MySQL makes application less scalable.
4. This will add complexity to maintain changes

So my resume: this is bad case for UDF. Really good case is then 
we have a lot of initial data to consider but relatively small 
result. For example, ForceAtlas2 UDF, I think, is more 
interesting idea.
Jan 13 2017
parent reply Martin Tschierschke <mt smartdolphin.de> writes:
On Friday, 13 January 2017 at 09:31:38 UTC, crimaniak wrote:

 On Thursday, 12 January 2017 at 09:49:08 UTC, Martin 
 Tschierschke wrote:

 I am wondering has anybody tried to do it with D?

 http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html

 By looking on the processing chain for rendering a web page, 
 based on mysql data,
 I thought it would be very efficient, to let mysql deliver the 
 datasets already rendered as html using a template engine like 
 vibe.d diet templates.




[...]

 Any suggestion?


 1. I don't see, why moving HTML rendering task from vibe.d 
 thread to MySQL thread will give CPU benefit.
 2. HTML usually bigger then data used to construct it 
 (sometimes much more bigger) so it will be more traffic in 
 pipe/socket between MySQL and vibe.d
 3. MySQL database more hard to scale than WEB frontend so 
 moving more work to MySQL makes application less scalable.
 4. This will add complexity to maintain changes

 So my resume: this is bad case for UDF. Really good case is 
 then we have a lot of initial data to consider but relatively 
 small result. For example, ForceAtlas2 UDF, I think, is more 
 interesting idea.


Thank you for your thoughts!

My thought was that the main benefit of using the DB engine to do 
the layout rendering, would be that you do not need to convert db 
values to serialized data, and than convert them to values before 
you convert them to stings again.
My first idea was to make a not compiled stored procedure and 
just concat() the output together with some html, but I was told, 
that concat usually is quite ineffective, I tested it and the 
result was: It is really slow.

The second thought is: When you get the power of vibe.d inside 
mysql, than everyone who is using mysql could use vibe to do the 
dataset rendering and by this you may speedup every system using 
mysql without having to change the complete front end to vibe 
allowing a smooth migration. (?)

Now I will try to use vibe.d as a service layer between rails and 
mysql,
to do some time consuming rendering of partials.

Regards mt.
Jan 13 2017
parent crimaniak <crimaniak gmail.com> writes:
On Friday, 13 January 2017 at 12:30:51 UTC, Martin Tschierschke 
wrote:

 My thought was that the main benefit of using the DB engine to 
 do the layout rendering, would be that you do not need to 
 convert db values to serialized data, and than convert them to 
 values before you convert them to stings again.


I think, MySQL binary protocol is very efficient. In any case, 
first it need to check how much it costs, else it will be classic 
premature optimization.


 Now I will try to use vibe.d as a service layer between rails 
 and mysql,
 to do some time consuming rendering of partials.


  The most correct approach, I think. 👍 In the style of 
microservices. Testable, interchangeable, scalable.
Jan 14 2017