Log in

No account? Create an account
entries friends calendar profile My Website Previous Previous Next Next
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