Run timer under OSADMIN account

Run timer under OSADMIN account

  

I am creating a timer which runs 'When Published' in which I want to create some indexes and tables using an Advanced Query on the OutSystems database, so not on an external database. The problem i am running in now is that the user OSRUNTIME doesn't have enough privileges to make such a query.

Therefore my question is, is it possible to have an espace / timer / query run using the OSADMIN account instead of the OSRUNTIME account?

Hi, 

Why couldn't you create the tables and the indexes in advance? 

You can always delete the tables contents in the "when Published" timer. 

Regards

Graça

Solution

Hi Joris,

The OSADMIN user is used at compile-time only. OutSystems applications will always use OSRUNTIME on their queries, so by default you cannot change the structure of the database/tables in any way.

It may be possible to create an external database connection to your OutSystems database, configuring it with the OSADMIN user, create an extension that makes use of it to expose the relevant entities. You should be able to use those "external" entities from a SQL tool... but this would be dangerous and should only be used when absolutely necessary, you are providing OutSystems database "admin" access to your applications, it can go very wrong.

Solution

Hi Joris,
Is there a reason you can't use the tools provided by Service Studio to create the indexes?

The reason we are doing this is because the indexes created through OutSystems aren't always effective. This because we use large multi-tenant databases and there is no way to place the tenant_id as first atttribute of the index. OutSystems will always place it last and in some cases this leads to full table scans even though we have other indexes. So some features are just slow and we know they can be much faster with the proper index.

What we try to achieve now is to create the proper indexes ourselves and doing so using a timer seemed the nicest solution because it can then be part of the regular deployment and we don't need to do any post-scripting ourselves.

Hi Joris,

There are 2 Api's that maybe could help you https://success.outsystems.com/Documentation/10/Reference/OutSystems_APIs/PlatformRuntime_API and https://success.outsystems.com/Documentation/10/Reference/OutSystems_APIs/RuntimePublic.Db_API

The first let you change the connection string in runtime.

Regards,

Marcelo

Joris van Geffen wrote:

The reason we are doing this is because the indexes created through OutSystems aren't always effective. This because we use large multi-tenant databases and there is no way to place the tenant_id as first atttribute of the index. OutSystems will always place it last and in some cases this leads to full table scans even though we have other indexes. So some features are just slow and we know they can be much faster with the proper index.

What we try to achieve now is to create the proper indexes ourselves and doing so using a timer seemed the nicest solution because it can then be part of the regular deployment and we don't need to do any post-scripting ourselves.

Well, that sucks...

I would have though that the TenantID column would be the first one in the index, since the likelihood of it being the most selective is pretty high, and also because most, if not all, queries on that table will include it in the WHERE clause.

An idea that might work is to use an extension to create the indexes, since inside the extension you can do pretty much anything you want. You could easily use .NET code to create the indexes, since you'd have full control of the database connection.