Re: Outer Join vs. Function call

tarek__at_hotmail.com (tnabil) wrote in message news:<f6ce088b.0408040640.83c128_at_posting.google.com>...
> Thanks ed. I wasn't really expecting this answer, but I come from the
> development world, so maybe databases are different. I always thought
> that doing the join once and for all, would be faster than executing a
> query for every single row. On second thoughts, maybe such a judgement
> needs more knowledge of the internals of the database engine, so like
> you said, it's database specific. I think I'll take your advise and do
> some testing and try to figure out which is faster.
>
> I have a small question, though. How do I know how long it takes to
> execute a single query? I use toad, which displays a number in
> milliseconds whenever you execute some query, but this number
> decreases if you execute the query more than once. Is this due to some
> optimizations in the engine? Which number should I consider, the
> worst?
>
> Thanks again for your help.

FYI, I dropped the comp.databases for my reply, especially since this is now clearly an ORACLE specific reply.

If you run that SAME query, ORACLE's SQL parser is smart enough to recognize that and reuse information it may have from the previous run.

Which result time to use? One brute force way is to compare like you would almost any algorithm: worst case and best case times between the two styles.

If just performance is the concern (I sense it is NOT the only concern you have), then better test in an environment that closely matches production and get an average. Is the SAME query run repeatedly in production by the same SQL*Plus session? The likely answer is no. So take that into acount, IOW the numbers you are getting, even for the slower initial run are "best case" estimates.

Here's a different question you have to consider: is the time it takes to test the two designs worth the performance improvement to the users? If you cannot clearly show there is a benefit, then my (Software Engineering) suggestion is:

When maintaining code, follow the style of the code you are maintaining.

 When I've done maintenance, I either rip out whole packages/functions/whatever and replace with my code (never undertaken lightly), or I change things (fix bugs) following the style of the code. In either case, when I'm done, I'm happy if the code looks like it had only one author.

If you are putting on an addition, keep the style similar. (Note I did not say identical.)

HTH,
  Ed Received on Wed Aug 04 2004 - 13:25:35 CDT