StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOptimizing query with nested where clauses
text
Body
copied!<p>Im working on a calls/servicedesk software with this kind of layout ( Firebird 2.1 ):</p> <ol> <li><p>Calls are opened by selecting an area and a type of call from that area (tables calls , areas and types)</p></li> <li><p>Users are assigned profile which says which areas and types they can view or edit calls in (table users and table profiles)</p></li> <li><p>Depending on the profile, a user may be able to view or edit ALL types of a certain area (table profile_areas), or just view or edit selected types of calls (table profile_types)</p></li> <li><p>Users may have special permission to, besides being able to view areas assigned on their profile, also be able to view any calls they opened (boolean column on table profiles)</p></li> </ol> <p>Im posting a stripped down, column renamed version of the main structure. I dont think I will be able to post the real tables, which spans over 300 fields and a lot more FK's, tables. </p> <p>This is the bare minimum structure concerning calls and user permissions.</p> <pre><code>CREATE TABLE CALLS ( CALLID INTEGER, /* PK */ AREAID INTEGER, /* FK ON TABLE AREAS */ TYPEID INTEGER, /* FK ON TABLE TYPES */ USERID_OPENED_BY, /* FK ON TABLE USERS */ STATUS CHAR(1) ); CREATE TABLE AREAS ( AREAID INTEGER, /* PK */ AREA_NAME VARCHAR(50), ); CREATE TABLE TYPES ( TYPEID INTEGER, /* PK */ AREAID INTEGER, /* FK ON TABLE AREAS */ TYPE_NAME VARCHAR(50), ); CREATE TABLE USERS ( USERID INTEGER, /* PK */ PROFILEID INTEGER, /* FK ON TABLE PROFILES */ USER_NAME VARCHAR(50), ); CREATE TABLE PROFILES ( PROFILEID INTEGER, /* PK */ PROFILE_NAME VARCHAR(50), VIEW_ALL_CALLS_OPENED CHAR(1) /* if true, user can always view any calls he opened, regardless of area or type */ ); CREATE TABLE PROFILES_AREAS ( PAREA_ID INTEGER, /* PK */ PROFILEID INTEGER, /* FK ON TABLE PROFILES */ AREAID INTEGER (FK), CAN_VIEW_AREA CHAR(1), /* can view any calls on this area, regardless of types */ CAN_EDIT_AREA CHAR(1) /* can edit any calls on this area, regardless of types */ ); CREATE TABLE PROFILES_TYPES ( PTYPE_ID INTEGER, /* PK */ PROFILEID INTEGER, /* FK ON TABLE PROFILES */ TYPEID INTEGER, /* FK ON TABLE TYPES */ CAN_VIEW_TYPE CHAR(1), /* can view any calls of this type */ CAN_EDIT_TYPE CHAR(1) /* can edit any calls of this type */ ); </code></pre> <p>We are starting to see our first clients reach the 10+ million calls mark, and any of the main simple queries are starting to become quite slow.</p> <p>When analizing the query plan, all seems properly indexed, but the number of indexed reads pretty much always shows 10 million or so, even if the query returns a total of 5 or so results.</p> <p>The problem seems to be that we are not being successfull in using joins to build the where clause, because of the many different variations a profile may have, which in turn creates a great deal of different OR clauses we have to deal with.</p> <p>The worst case scenario is, as such :</p> <p>1.User can view all calls he opened</p> <p>2.User can view some areas, but not all</p> <p>3.User can view some types, but not all</p> <p>Which is leaving us with something like this (lets say the user id is "1"):</p> <pre><code>SELECT CALLID FROM CALLS WHERE CALLS.USERID_OPENED_BY = 1 /* .User can view all calls he opened */ OR ( CALLS.AREAID IN (1,2,3) /* areas the user can view, in his profile. we tried using a subselect here and things just went from bad to much, much worse */ OR CALLS.TYPEID IN (1,2,3) /* types the user can view, in his profile. we tried using a subselect here and things just went from bad to much, much worse */ ) </code></pre> <p>And that kind of where clause is killing performance. </p> <p>Someone advised us to try to split the OR's into different queries and add up with union, but a number of other factors makes that very troublesome.</p> <p>Ideally we try to restrain our clients from using such a wide variation of profile permissions, but instead the trend seems to be newer and more obscure needs of types of profiling (which is the reason why "see all calls he opened" was implemented, for example).</p> <p>Any better strategy we should be following? </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