Performance question on large external database

Performance question on large external database

  

We are using external entities in our OS applications and some of the tables are really big, like in 10 million rows. We are now experiencing timeouts when querying these tables, even though we use whereclauses to limit the number of rows. 

I cannot find any documentation on how queries are performed under the hood, a colleague here is saying that OS first gets all the data from the external database and applies the whereclause afterwards, this would explain the poor performance. 

Does anyone have a suggestion to improve performance here? Is there a best practice?

Hi Wendy Tromp,

Any application that uses external entities needs to have the proper setup, especially in those scenarios of million of records.

There is no need most of the times to fetch/search in all the records. Just do a simple service to ask the database to return only the needed records.

Regards,

Solution

I have been looking at explain plans and even though there was a function based index on the column we were querying for, the cost of the query went up significantly if we used lower(column) in the where clause. 

So we ended up omitting the lower() in the where clause, knowing that the column we were search for is always stored in lowercase anyway.

Solution

Hi Wendy Tromp,

Can you detail more the solution you got, are those "explain plans" something we would need in our applications? 

And is that function "lower()", the built-in function from OutSystems "ToLower()"?

The analysis is something I do on external tables, in this case in an Oracle database. I use TOAD to generate explain plans, it doesn't have much to do with Outsystems. 

The function based index I was talking about is in this external database, it is using the oracle function lower() around the values.  In the aggregate I was using ToLower(entity.column), assuming we would use the function based index as described before.  But for some inexplicable reason the query turned out to be a lot more expensive when we used this so I decided to omit it. 

Wendy Tromp,

Good you found solution to your parameterization issue, when using external databases services.

In fact, most of performance degradation when using external databases connections, is not OutSystems related, and most of the times is related to how external services are consumed (or functions) provided by external connectors.

Thanks for sharing this information.