Re: Inline function much faster than UDF in C?

Hi Alastair, the documentation at http://www.monetdb.org/Documentation/Manuals/SQLreference/Trace is indeed misleading --- sorry! Simply prefixing your SQL statement with TRACE in mclient will create both the normal result set of the query and a second result set containing the performance trace. Simply redirecting both result sets, i.e., the output of mclient into a file will do the job. Stefan ----- Original Message -----

Hi Stefan,

Thanks for your reply.

Just to clarify things, should I prepend the statement with TRACE and then "select * from tracelog()"; ?

Should I then use mclient --log to capture the output?

Best regards,

Alastair ________________________________________ From: users-list on behalf of Stefan Manegold Sent: 09 December 2013 13:05 To: Communication channel for MonetDB users Subject: Re: Inline function much faster than UDF in C?

Hi Alastair,

please profile your queries as described here http://www.monetdb.org/Documentation/Manuals/SQLreference/Trace http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming

to see which query spends how much time where, and share the results, so that we can interpret them.

Thanks! Stefan

----- Original Message -----

...

Hi everyone,

I have only been using MonetDB for a few weeks now and I have come across a very unexpected result when using User Defined Functions in C.

I have a query which does essentially this:

TABLE small_table ( int col1; float p; );

TABLE big_table ( int col1; int col2; float p; );

SELECT col1,1+SUM(MY_FUNCTION(big.p,small.p)) FROM small_table small inner join big_table big on big.col2 == small.col1 group by big.col1;

My function is f(x,y) = 0.7213*(x*ln(x/x+y) + y*ln(y/x+y))

I wrote an optimised version of this function using sse intrinsics in C and compiled it into MonetDB.

To my surprise, I discovered that writing this function inline in the query like so:

1+SUM(0.7213475*(big.p*LOG(big.p/(big.p+small.p))+small.p*LOG(small.p/(big.p+small.p))))

is a lot faster!

The query takes approx 2 seconds with the C version and less than 0.5 seconds with the inline version in SQL!

Can anyone explain this strange result?

Best regards,

Alastair

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |