need help to optimize query

I'm curious Ronnie, does the value returned change during the day? Also, if the same function is called many times during the day does it really get parsed every time. I don't think it does. I think that as long as a function stays cached it will stay parsed. And besides by creating a function you will prevent yourself from coding this logic in multiple places. Which leads to the axiom code once use many. Why keep coding the same logic over and over again when you can leave it in one place? If it is related to other code then create a package otherwise leave it as a stand alone function. Too many times I have heard a programmer say we can't change that because it is hard coded in too many places. If business logic is encapsulated into a set of functions then you can easily change how this value is calculated without changing a lot of code. But don't listen to me. This is alos one of the things that I picked up from Steven Feuestein, you might want to listen to him.

Quote:

---

Originally posted by gandolf989
Also, if the same function is called many times during the day does it really get parsed every time. I don't think it does. I think that as long as a function stays cached it will stay parsed.

---

Test it. The SQL will absolutely be parsed and executed each time (at least in 8i). Of course, only the first time (if not already cached) will be a hard parse - the rest will be soft. But regardless, it is a whole lot more overhead than the in-line sub-select. Sorry.

Quote:

---

Originally posted by gandolf989
And besides by creating a function you will prevent yourself from coding this logic in multiple places. Which leads to the axiom code once use many. Why keep coding the same logic over and over again when you can leave it in one place? If it is related to other code then create a package otherwise leave it as a stand alone function. Too many times I have heard a programmer say we can't change that because it is hard coded in too many places. If business logic is encapsulated into a set of functions then you can easily change how this value is calculated without changing a lot of code.

---

I'm glad you brought that up.:) The proper way to handle this issue is to indeed create a function, but the function simply adds SQL to a string.

The process should be something like:

- Build static and unique parts of SQL string.
- Call procs to build re-usable parts of SQL
- Use code and procs to build variable parts of SQL
- Put it all together
- Execute it

Of course, I have a lot of supporting procs to make this all very easy, but this is the proper solution, IMHO.

Again, using functions *in your SQL* is innefficient, period. You don't want to do it if you don't have to. However, as you mention, you also want to consolidate code as much as possible - re-usability is critical. The solution is to code functions that generate the oft-used pieces of SQL to be added to the larger SQL statements.

In this way, you have re-usability at the same time that your final SQL statement is as efficient as possible.

- Chris

Another advantage of a function is that it will get parsed the same way every time. If a program has the same sql code in many places, along with the support problems, there might be subtle differences in the code causing it to get hard parsed more than once. The more duplicate code in an application the more likely Oracle is to hard parse. So which is faster?

Quote:

---

Originally posted by gandolf989
Another advantage of a function is that it will get parsed the same way every time. If a program has the same sql code in many places, along with the support problems, there might be subtle differences in the code causing it to get hard parsed more than once. The more duplicate code in an application the more likely Oracle is to hard parse. So which is faster?

---

You are quite persistent, aren't you? ;)

Again you bring up a good concern - something that one should always consider - but one that does not apply here.

Basically, these 'subtle' differences might exist in the 'calling' SQL. Say there are 2 different statements that use this functionality. In my case, the SELECT MAX() (or whatever) is part of that statement. In yours, it is a separate function. In either case, these two 'base' SQL statements are both hard-parsed the first time they are called and soft-parsed every time after that, *regardless* of whether they contain a function call or a sub-select. However, in your case, we also have the hard parse of the statement inside the function as well as a soft parse of that same statement for every single row in the outer SQL.

The other case might be that the 'calling' SQL is the same, but the sub-SELECT is different on two calls (for whatever reason). So, if this sub-SELECT is in the actual SQL, then we end up with 2 hard-parses. If it is in the function, then we end up with 3 hard parses and one soft. The 'base' SQL is the same in both calls, so one hard and one soft.The sub-SELECT contained in the function changes, so 2 hard parses.

In reading your reply again, it occurs to me that you may be ignoring my last response and again be assuming that this 'common' sub-select is actually hard-coded each time it is used. I already addressed that, but we can follow this path anyway.

So, you appear to have the following assumptions:
- This 'exact-same functionality' is required in, say, 2 SQL statements
- However, it is implemented differently in these 2 statements.
- This is the only difference between the 2 statements, so now we have 2 'different' statements instead of the 1 we would have had had they both called your function

Seems a little extreme, don't you think? First, if the statements are exactly the same, why are there 2 of them? Shouldn't they simply be calling the same statement in both cases, thereby eliminating the problem? Further, you are assuming they used the *exact* same spacing, case, CR/LFs, etc. for the *entire* SQL statement *except* for the sub-SELECT. Again, quite unlikely.

More likely is that either they would have one single statement that was called twice, or they have the same statement twice, but it is either exactly the same in both places (if they are detailed enough to do that and still not make one statement) or they differ in more than just the sub-SELECT. Again, in either of these cases, whether they hard-code the sub-SELECT or call your function has no bearing on the number of final 'base' statements in the SGA. However, the function-based solution will *always* add more statements, parses and executions, as I've already shown.

You can keep trying, but I guarantee you that my approach is faster.

- Chris

If talking about good coding practices does not apply here, then when does it become relevent? As the cliche goes programming is a discipline. So how can you pick and choose when you should write proper code?

Quote:

---

Originally posted by gandolf989
If talking about good coding practices does not apply here, then when does it become relevent? As the cliche goes programming is a discipline. So how can you pick and choose when you should write proper code?

---

Okay, now I'm torn. I was hoping you hadn't completely given up, because this has been somewhat entertaining. However, I am now thoroughly disappointed in that you seem to have completely missed my argument.

I *fully support* proper coding. I am actually incredibly anal about it. You should work with me sometime. I have yet to meet somebody more anal about coding practices than I am. :D

And, in this case, as always, I *am* fully advocating the use of proper coding practices. I am advocating the use of re-usable code. What you seem to be missing is that my re-usable code *builds the string* to perform the functionality while yours actually *performs* the functionality. Mine will be faster, but it is equally as maintainable. I have taken both considerations into account: Maintainability and efficiency. The string that my re-usable code builds is then added to the final SQL string so that an in-line sub-SELECT is the final product, which I have shown to be faster. But this re-usable string is still built in its own function which can be called by anybody who is building a string that needs that functionality.

I don't know how to make it any clearer,

- Chris

Quote:

---

Originally posted by chrisrlong

Quote:

---

Originally posted by gandolf989
Another advantage of a function is that it will get parsed the same way every time. If a program has the same sql code in many places, along with the support problems, there might be subtle differences in the code causing it to get hard parsed more than once. The more duplicate code in an application the more likely Oracle is to hard parse. So which is faster?

---

You are quite persistent, aren't you? ;)

Again you bring up a good concern - something that one should always consider - but one that does not apply here.

---

In re-reading, I see where your previous comment about coding practices 'not applying here' came from. You mis-read my reply. What I was saying was that 'yes, you should always be on the lookout for duplicate code, but *there is no duplicate code* here.' That is what I meant by your concern not applying - you were bringing up something that, rather obviously, I'm afraid, was never a problem with my solution. And I would have thought that the rest of my reply would have made that apparent.

- Chris

I'm sure I don't get what you are talking about like I'm sure that you don't get what I am talking about. I don't believe in hard coding sql statements, not do I beleive in creating redundant code. If this value is only needed once fine. But what are the odds that a value like this will only get used in one place? In my mind this is the kind of thing that pops up all over, hence a single function that computes the value. why not maintain business logic in one place and one place only. But since Ronnie has lost interest in the post, the whole conversation is rather meaningless. Except to say you were ready to put down my code without thinking about what I was trying to say. No big deal. I'm just expressing my opinion. Hopefully, I am being more clear. and yes your answers were amusing as well.

I give up. You are obviously not reading my answers. I AM RE-USING CODE!! I AM CONSOLIDATING THE LOGIC!!! Just like you are advocating. You are simply not listening. Read the thread over - many times. I have. I am extremely confident that I understand what you are talking about and I have directly addressed your issues in every one of my replies.

Please, please, please re-read the thread. Then at least let me know that you have a glimmer of understanding about what I am telling you. Otherwise I will simply have to assume that you are 'leading me on' and 'feigning ignorance'. And I honestly don't have the time for such things.

- Chris

I do know what you are talking about. Here is a quote from Steven Feuerstein's Oracle PL/SQL best practices, Chapter 6 Page 86.

One of the reasons developers like PL/SQL so much is that it's easy to write SQL inside a PL/SQL block of code.

One of the most dangerous aspects of PL/SQL is that it's so easy to write SQL inside a PL/SQL block of code.

If this was your system you would store the sql as text and execute it when needed, fine. If it was up to me I would write a stored procedure, I don't see the hard parse as being that big of an issue. I think that this is a philosophical difference.

And besides if this is a real time system do you think that they are entering detail records for trades with yesterday's date? I'm guessing, and I can only guess, that the query is being used to create reports of what happened yesterday. Hence a static function. My answer was based on that assumption as you pointed out already.

As it is I have already spent too much time on this issue. It was never my intention to have a go at you.