Log in

entries friends calendar profile My Website Previous Previous Next Next
MySQL User Defined Aggregate Function, how fast are they? - Mark Atwood
MySQL User Defined Aggregate Function, how fast are they?
MySQL has User Defined Functions. These are loadable modules, written in C, that can be loaded into a running MySQL server, and used like a "native" built-in function.

A UDF can be an aggregate function. An aggregate function is one that takes in multiple rows, but emits only one result. For example, SUM() is a built-in aggregate function.

I just proved to myself via testing and benchmarking that user defined aggregate functions are about three times slower than the call interface to the built-in functions.


In about twelve hours I am supposed to be presenting a talk titled Using User-defined Functions and Aggregates to Speed Up Your Data Warehouse Processing.

Um. Yeah...


It turns out to be a MacOS thing. On Linux, the performance penalty is less than 10%. Further research will surely be done.

The talk itself went reasonably well.


6 comments or Leave a comment
docorion From: docorion Date: April 16th, 2008 12:11 pm (UTC) (Link)
Sounds like you need a new topic...
From: nancylebov Date: April 16th, 2008 12:21 pm (UTC) (Link)
Definitely interesting. I hope you post about how the speech works out.
From: gillgunson Date: April 16th, 2008 03:18 pm (UTC) (Link)
Wow. Is that across the board? I'm surprised nobody picked that out before.

I shall have to ask to see your slides/notes later, as this is the sort of stuff I need to be aware of, what with the data warehousing I deal with.
dossy From: dossy Date: April 16th, 2008 08:36 pm (UTC) (Link)
I'm not surprised that built-in call overhead is less than UDFs, but 3x more expensive? Ouch.

Of course, if you have a UDF that does complex work--i.e., more than 3 built-in aggregates worth--perhaps the extra cost in UDF call overhead is absorbed?
awfief From: awfief Date: April 17th, 2008 05:21 pm (UTC) (Link)
So you learned that you should do a bit more research next time, before proposing a topic?
fallenpegasus From: fallenpegasus Date: April 17th, 2008 07:04 pm (UTC) (Link)
I didn't actually propose the topic, I was asked to help present on it.
6 comments or Leave a comment