Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-9860

Excessive db queries generated from Site Info / user service

    XMLWordPrintable

    Details

      Description

      We have a large site with approx 22,380 members (entire student body).

      Site Info takes a very long time to load (159s on an unloaded test server). Analysis of db activity shows:

      • 1 query for every user (i.e. 22K+ queries) to get user details:

      select SAKAI_USER.USER_ID,SAKAI_USER.EMAIL,SAKAI_USER.EMAIL_LC,SAKAI_USER.FIRST_NAME,SAKAI_USER.
      LAST_NAME,SAKAI_USER.TYPE,SAKAI_USER.PW,SAKAI_USER.CREATEDBY,SAKAI_USER.MODIFIEDBY,SAKAI_USER.CREATEDON,SAKAI_USER.MODIFIEDON from SAKAI_USER where ( USER_ID = x'6162646D6173303031' )

      • 4 queries for every user to SAKAI_USER_ID_MAP, e.g. for this user (where ID happens to equal EID):

      select USER_ID from SAKAI_USER_ID_MAP where EID=x'6172746D6963303033'
      select EID from SAKAI_USER_ID_MAP where USER_ID=x'6172746D6963303033'
      select EID from SAKAI_USER_ID_MAP where USER_ID=x'6172746D6963303033'
      select USER_ID from SAKAI_USER_ID_MAP where EID=x'6172746D6963303033'

      So total queries to render one tool page, approx. 112,000, which needless to say is very bad for database and application performance.

      If you navigate away to another tool and back to Site Info, it does the 112K queries all over again, so nothing is getting cached by the application.

      Solutions:

      • The EID/USER_ID mappings and possibly user info should get cached by the app servers
      • The EID/USER_ID mappings should possibly get preloaded (e.g. a single 'select * from SAKAI_USER_ID_MAP' on startup).
      • Where multiple users are being fetched, they should get read in with a single SQL statement, e.g. (SELECT ... from XYZ where USER_ID IN 'user1','user2','user3',...) batched into groups (<1000 to keep Oracle happy), e.g. as is done by the Profile service. This obviously requires co-operation between the tool and the service.

        Gliffy Diagrams

          Zeplin

            Attachments

              Issue Links

                Activity

                  People

                  Assignee:
                  ianeboston Ian Boston
                  Reporter:
                  smarquard Stephen Marquard
                  Votes:
                  3 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                    Dates

                    Created:
                    Updated:
                    Resolved:

                      Git Integration