Re: Query Performance Decrease
Posted by: Rick James
Date: March 19, 2016 11:03PM

(No need for duplicating the responses to Peter and me.)

    Select ...
        FROM  tableaprerollup as ip
        INNER JOIN  tableb as p on ip.ProductId = p.Id
        LEFT JOIN  tl as l on ip.Id = l.InstalledProductId
        where  ip.ObligationType IN ('L')
        GROUP BY  ip.BusinessUnitId, ip.CustomerId, p.ShortNameId,
            LeaseContractNumber, l.Lease_Cust_Id ;

You are GROUPing BY 5 things, but have many more non-aggregates in the SELECT; this could lead to unexpected results.

What table is LeaseContractNumber in? Please prefix all column names with their table aliases.

Why "LEFT"? It seems like don't really care if tl is needed.

Please provide the EXPLAIN SELECT ... I'll guess that it it works something like:

1. Collect all the rows WHERE ip.ObligationType IN ('L'). If there is not an index starting with ObligationType (and maybe if there is), that will be a "table scan".
2. For each row there, reach into tableb for one or more rows. This may involve reading the entire table, but in a random order.
3. For each row, reach into tl for zero or more rows. This may involve reading the entire table, but in a random order.
4. Write to tmp table.
5. Sort (for GROUP BY)
6. Deliver results.

Since I/O is likely to be the slowest part of the process, we need to look at when I/O happens.

1. Reading all of `ip` is relatively simple and efficient.
2. Reading all of tableb randomly is not too bad if there is room to cache all of it. But it it is too big to be cache, then the cache with thrash -- bumping out blocks to read (or reread) other blocks.
3. Ditto.
4,5,6. etc.

That is, steps 2 and 3 are likely to be the worst, and are likely to slow down dramatically after the table gets bigger than cache. Before it is too big, each block is read once. After it is really big, it will grow worse until, at the extreme, it will do a disk read for each _record_, not each _block_. As a rule of thumb, a block can hold 100 records.

So, not really "exponential", but potentially 100x slower.

This size of the table versus the cache size probably also plays an "exponential" (or other) role in slowdown.

Suggest you time each statement to see if I focused on the slow one.

Options: ReplyQuote


Subject
Written By
Posted
March 15, 2016 04:14PM
Re: Query Performance Decrease
March 19, 2016 11:03PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.