Database Query and SLOWSQL

  

Hello,

I am seeing a large number of SLOWSQL warnings in Service Center on queries which are searching for Users. These warnings are across a number of different queries, some Aggregates and some Advanced SQL.

An example of one of our Advanced Queries which took a maximum of 11.6 seconds to run in Production yesterday:

SyntaxEditor Code Snippet

SELECT {User}.*
FROM {User}
JOIN {User_Role} on {User_Role}.[User_Id] = {User}.[Id]
WHERE {User_Role}.[Role_Id] = @RoleID
AND {User}.[Is_Active] = 1
AND {User}.[Name] <> 'Administrator'
ORDER BY {User}.[Username]

I have had a look at the User table which shows an index on only the Username attribute. Is this correct should there be and are there indexes on other attributes which as can't see? We don't have direct access to the Database so can't look for exactly what indexes are there.

We are in the Cloud using v9.1.501 on the Java / Oracle stack. I don't believe we can use the Cache in Minutes option to try and Cache the results of these queries, is there an alternative option for caching in Java?

Is it possible to create a materialized view through Outsystems? We only need to query about 1000 out of our 10000 users who have a specific role so it may be useful if we could create a view of only these users. Is this possible?


Thanks, Stuart


That request is already on the waiting list.

Native Support for Database Views


You can't create views in OutSystems yet, but you can use existing views from database.

Can OutSystems work with DB views?


Because User table isn't eSpace dependent, the name is always the same. It looks like a great candidate for having a view.


Still, the time isn't great. Is anything locking the table?


Hi Nuno,


It could be possible that the table is being locked. We don't do many additions as our internal users are already in the table but we do have some external users being created but not a large volume. There is the Last_Login field which looks like it would be updated every time someone logs on which could lock the table?


Is there a table hint or something similar which we could add to get around any locking issues?

The table has quite some users, but last login isn't that used. No way it can lock a table for 10 minutes.

In OutSystems you have the Slow Queries screen you already know. I'd say you can make some tests only with Users entity and Users+Roles, but the lock is easier to find by debugging SQL.