Re: UDR Performance Question

"Sean Baker" <SBaker@moneymailer.com> wrote in message news:<c1vnbf$uts$1@terabinaries.xmission.com>... > This is a multi-part message in MIME format. Aaaaargh! > We are also going to start using Java UDR's as well, and possibly some > UDR's written in C. > > My question is, regarding straight performance of getting data out of > the IDS database and returning the results, which UDR platform has the > best performance (SPL, Java, C)? I've posted on this before. In a nutshell: there are three costs to consider: a) Cost of whatever SQL queries the UDR contains. b) Computational cost of the UDR (without queries). c) Calling overhead. 1. If the UDR contains SQL, all options are equally bad. In general, try to avoid 'black-boxing' SQL within a UDR. The optimizer can't "see" what's going on in there. UDFs are really useful for encapsulating some kind of repeated logic. If your UDR contains SQL, just use SPL. It's simple. 2. 'C' is better than Java is better than SPL. 'C' compiles down to the metal, Java has a better run-time interpreter than SPL. 3. Calling overhead is the amount of work the server has to do to set up to call a UDF implemented in one of these three languages. For example, before it calls a 'C' UDF, the server allocates memory slabs, copies data, checks to see that the UDF's shared library is loaded, etc. C and SPL incur about the same per-call costs. Calling a Java function is *very* expensive. Before you call a Java method you've got to whack the JVM hard, repeatedly, and then take what it says and translate it back into the engine's runtime. So: If it's got SQL in it, use SPL, and try to avoid using said routine in SQL queries, if it's at all possible (sometimes, it isn't). If the UDF logic is small and relatively simple, use SPL. (Developing 'C' is a pain not only in the you-know-what, but a pain spread liberally throughout your entire body). If it's beastly nasty complex ugly needs to be really fast and you use it a lot, use 'C'. Why Java at all? Because there are things you can do with Java that you can't do (easily) with C or SPL, like call things outside the engine using the java facilities. Hope this yelps! KR Pb