ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Query Fetch taking a long time when ORDER BY is specified

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query Fetch taking a long time when ORDER BY is specified

    I'm posting here because the question is trying to focus on how DB2 works and is not supposed to be specific to my situation/query.

    My particular situation for some context:

    I have a query which is used in a search/filter/order type program with many potential WHERE clause parameters based on user input. I've noticed when I specify an order by clause it is drastically slower than without. While looking at the execution plan I noticed it takes milliseconds up until the fetch stage. After a bit of reading I've come to realize this is because it has to create a temporary table in the specified order before it can even begin to return the first record ('Materialized Cursor' is what I believe this is called). Now from what I've read, it only has to do this step if it can't use an existing index on that field in the specified order (desc/asc). I've been testing with 1 particular column as parts of the ORDER BY and there exists an index over that field in the correct order. Now this query is run against 2 views each on top of other tables and views. The top level views JOIN on hard coded conditions (IE: ...ON t1.col1 = 'Hard Coded Value') while the few underlying views contain their own WHERE clauses as well. I'm assuming because of the nature of views on top of views with conditional joins and where clauses is preventing the index from being used.

    My questions:
    A view which is just a replica of a table which only uses scalar functions such as TRIM or UPPER on the columns should be able to make use of indexes?
    While a view which involves multiple tables or even other views and WHERE clauses will likely not?

    Many Thanks in Advance,
    Dan

  • #2
    Have you ran it thru VE?

    Comment


    • #3
      Yes, that's what I meant by "While looking at the execution plan". Sorry, that's just how I refer to VE, I should have worded it differently. It's clear that 99% of the time spent on the query in VE is spent during the fetch phase (which is the top most node). When I remove the ORDER BY, the query takes the same amount of time minus the extra ~99% which comes out to milliseconds.

      However, I am more interested in the relationship between views and indexes in DB2. More specifically if the assumptions/questions I made in the original post are true or not.

      I just finished playing around with my query so it did not use these complex views within views just to test my assumptions. It appears they were correct. It now picks up the indexes which eliminated most of the table scans and is running in ~4 sec for VE as opposed to ~102 for VE. Outside of VE it now runs in tens of milliseconds.

      It seems I've been able to prove my assumptions, but if someone who knows beyond a doubt this is how DB2 works please still post confirming so. I'm just trying different things to see which performs better and it's possible that while the end result is the same, the logic behind it may be different than what I assume. And I'd just like to know the correct "Why?" rather than assume.

      Many Thanks

      Comment


      • #4
        OK, if you ran VE and checked it's Optimizer Messages, I'm afraid that's all I got.

        Good luck,

        Comment


        • #5
          Wait, what do you mean by "checked it's Optimizer Messages"? All I'm aware of is mousing over/clicking on the nodes to see detailed information about them in the right hand panel. Then there is the index adviser, but beyond that I'm not aware of any messages or anything. How do I get to that?

          Comment


          • #6
            I type my SQL and click "Run and Explain", and the Visual Explain window appears with the graphic at the top-left, attributes at the top-right and the statement text at the bottom. At the very bottom, there's a tab for "Optimizer Messages". That tab has messages from the optimizer. Double-clicking them will give you the details. For example, CPI432C will list all the access paths that were evaluated and why it made the decision it did.

            I'm using the iACS version of VE, but I don't know if that makes a difference.

            Comment


            • #7
              I've never noticed the "Optimizer Messages" tab down there, this is really neat. I'm definitely going to start using this, thank you.

              Comment

              Working...
              X