Cost of logic in a query

Question

I have a query that looks something like this:

select xmlelement("rootNode",
    (case
       when XH.ID is not null then
     xmlelement("xhID", XH.ID)
       else
     xmlelement("xhID", xmlattributes('true' AS "xsi:nil"), XH.ID)
    end),
    (case
       when XH.SER_NUM is not null then
     xmlelement("serialNumber", XH.SER_NUM)
       else
     xmlelement("serialNumber", xmlattributes('true' AS "xsi:nil"), XH.SER_NUM)
    end),
/*repeat this pattern for many more columns from the same table...*/
FROM XH
WHERE XH.ID = 'SOMETHINGOROTHER'

It's ugly and I don't like it, and it is also the slowest executing query (there are others of similar form, but much smaller and they aren't causing any major problems - yet). Maintenance is relatively easy as this is mostly a generated query, but my concern now is for performance. I am wondering how much of an overhead there is for all of these case expressions.

To see if there was any difference, I wrote another version of this query as:

select xmlelement("rootNode",
                   xmlforest(XH.ID, XH.SER_NUM,...

(I know that this query does not produce exactly the same, thing, my plan was to move the logic for handling the renaming and xsi:nil attribute to XSL or maybe to PL/SQL)

I tried to get execution plans for both versions, but they are the same. I'm guessing that the logic does not get factored into the execution plan. My gut tells me the second version should execute faster, but I'd like some way to prove that (other than writing a PL/SQL test function with timing statements before and after the query and running that code over and over again to get a test sample).

Is it possible to get a good idea of how much the case-when will cost?

Also, I could write the case-when using the decode function instead. Would that perform better (than case-statements)?

Answer 1

Just about anything in your SELECT list, unless it is a user-defined function which reads a table or view, or a nested subselect, can usually be neglected for the purpose of analyzing your query's performance.

Open your connection properties and set the value SET STATISTICS IO on. Check out how many reads are happening. View the query plan. Are your indexes being used properly? Do you know how to analyze the plan to see?

Answer 2

You can use good old tkprof to analyze statistics. One of the many forms of ALTER SESSION that turn on stats gathering. The DBMS_PROFILER package also gathers statistics if your cursor is in a PL/SQL code block.

Answer 3

For the purposes of performance tuning you are dealing with this statement:

SELECT *
FROM XH
WHERE XH.ID = 'SOMETHINGOROTHER' 

How does that query perform? If it returns in markedly less time than the XML version then you need to consider the performance of the functions, but I would astonished if that were the case (oh ho!).

Does this return one row or several? If one row then you have only two things to work with:

• is XH.ID indexed and, if so, is the index being used?
• does the "many more columns from the same table" indicate a problem with chained rows?

If the query returns several rows then ... Well, actually you have the same two things to work with. It's just the emphasis is different with regards to indexes. If the index has a very poor clustering factor then it could be faster to avoid using the index in favour of a full table scan.

Beyond that you would need to look at physical problems - I/O bottlenecks, poor interconnects, a dodgy disk. The reason why your scope for tuning the query is so restricted is because - as presented - it is a single table, single column read. Most tuning is about efficient joining. Now if XH transpires to be a view over a complex query then it is a different matter.