Toad World® Forums

Complex Evaluations

I tried searching but couldn't find anything, please forgive me if I overlooked something.

I have a query that returns roughly 30 columns, 5 of which are currently derived from in-line functions. They are generally simple functions 3 of them return simple single values for input, 2 of them return a concatenated string from children tables.

I want to set up an optimization run that looks at the query being ran and can tell me if it is most optimal to leave the inline function calls as is, bring them in as inline selects or even as joins.

So far I have tried setting up batch runs as well as singles, and I can't seem to get that sort of evaluation so I am hoping I am just missing something putting in the request. I have at least 3 years ahead of me doing this very sort of analysis and updating on a database that has been migrated and shuffled around since 7.3.4. So I want to maximize the value of the tool as much as possible.

Would you please give a sample SQL to explain your question? Do you want SQL Optimizer to generate inline select or join syntax for you? SQL Optimizer will generate inline select or join syntax, but if this SQL has the same plan with other alternatives, SQL Optimizer will skip it.

You can also add inline select or join syntax as “User-defined SQL” and batch run it with other SQLs generate by SQL Optimizer.

I can't put in the specific query but can give you a likeness that covers the functions in this one query, but do keep in mind I will be doing this across thousands of queries over the next few years.
These are all simple functions meaning:

  • they check if a record exists in the child, return a yes/no
  • they pull all of the children records and concatenate them into a string for display
  • they pull earliest date or latest date
  • they match an ID to a name for display
  • they check another table to see if this item is approved for next step
    Example is: (assume join is correct):

string_function(varA, varB) concat_str,
min_date(varA, varB) min_prnt_dt,
max_date(varA, varB) max_prnt_dt,
lookup_name(varC) lookup_name,
chld_exist(varA,varB) count_chld_rec,
approval_flg(varA, varB) approv_flg,
FROM tabA, tabB, tabC, tabD

This query may return 10,000 rows or even 100,000 rows What I would like to ensure SQL Optimizer is doing (or figure out if the tool is capable of doing it) is:

  1. Evaluate the performance of the function call as part of the inline knowing that it can return 10,000 rows meaning it will call it X number of times.
  2. Take the logic of the function and attempt it as an inline sql statement, compare that to the performance of the function.
  3. See if the function in that volume would be better suited to make it a join on an in-line view and compare that to the previous results.

For example, on my approval flag value is it better for me to interrogate the additional table one at a time for the value even it if is primary key or join it as a 3 column inline view on the varA, varB

SQL Optimizer could not translate a function logic to a SQL statement. The function can have complex logic control like “if … then ... else, loop…” and it could not represent in a SQL statement. You need to rewrite the SQL logic and SQL Optimizer can find the best performance syntax for you.