Databases
StackOverflow2013
Postsdbo
POA query that performs slowly as a single query but in fractions of a second when done in two steps
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POA query that performs slowly as a single query but in fractions of a second when done in two steps
    text
    copied!<p>I have two views in an ORACLE DB:</p> <pre><code>The view USERS with columns: UserId | GroupId | Details </code></pre> <p>where there's 1 record for each User, total 40.000, and there c.a. 1-30 users in each Group.</p> <p>and</p> <pre><code>The view SUMMARY with columns: UserId | Summary </code></pre> <p>where there's 1 record for each User</p> <p>The SUMMARY view is very complex but is still quite fast when i query it based on user. That is:</p> <pre><code>select * from SUMMARY where UserId='some_user_id' </code></pre> <p>performs in 0,1 sec</p> <p>The USERS view is pretty simple (it's a union all of USERS_TYPE1 and USERS_TYPE2 tables) and a query of the type:</p> <pre><code>select * from USERS where GroupId='some_group_id' </code></pre> <p>performs in 0,02 sec</p> <p>Now here's the catch, when I do:</p> <pre><code>select * from USERS JOIN SUMMARY ON USERS.UserId = SUMMARY.UserId WHERE USERS.GroupId = 'some_group_id' </code></pre> <p>I get AWFUL performance of 90seconds - even though there are only 3 users in the group.</p> <p>This should take only a fraction of a second if the user ID's are found first and then the SUMMARY table is queried with those user ID's. Is there a way I can hint the DB to do that?</p> <p>I have indexes based on UserId, GroupId and (GroupId,UserId) on all underlying tables.</p> <p>((This is pretty hard to reproduce In a simple manner since the complex view has a lot of underlying tables. I have a couple of versions of the SUMMARY view. In some cases (depending on how SUMMARY is built) it stops being awful when i materialize the Users view, but in other cases it does not.))</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload