Performance Issues with Foreign Databases

Performance Issues with Foreign Databases

Hi guys,

I have a question.

One of our clients have a very heavy process and it integrates on two to three foreign databases. Currently, we are experiencing performance issues with the application we have developed for them.

Upon checking our General Logs, there are numerous SLOWSQL queries we have to check. Upon investigating the logs, it seems most of the queries were queries from the foreign databases (Oracle and SQL Server) and not with the OutSystems database.

Question is that, is it really expected that the foreign database, when executed with a query, has poor performance or it could be our query against the foreign database. Our queries' SELECT contains specified columns and having structures as Output Entities.

Please help. Thanks,


How are you referencing those external databases? Through Service Center's Database Connections? Linked Server (wich joins over local tables)?
It may be that those foreign tables need indexes, in which case your DB admin could help. Using the Cache in Minutes property in your query can also help in some scenarios.
Have you tried generating a Slow Sql report (Service Center > Analytics > Reports)? This can help you to find the top queries to improve.

Hi Paulo,

The foreign tables are from Linked Servers and they are not joined with local tables. These are integrated via Integration Studio.

We've asked their administrator to generate them also, and I have a suspicion that it may be the foreign database's network connection. But yet I can't prove it because of the SLOWSQLs. Execution times are high (like 97K ms)

I'll check that out. Seems the link may help me check even more.

While I am waiting for a reply, I was doing my own investigations too not to forget that the application code can be blamed too.

Thank you very much.