AcacioPN

Staff

Tip: performance of query in OutSystems Platform slower than running in database

Discussion

OS Certified

Performance

SQL

Database

How-to

Oracle

Symptom

Your application has a slow query built with OutSystems (be it an Aggregate, a Simple Query or an Advanced Query / SQL). When you are troubleshooting the problem, and connect directly to the database, the query is much faster.

You are using the OutSystems Platform with either SQL Server or Oracle backend.

Troubleshooting

When going after this problem, the most important thing to remember is: compare equal things. Just copying the text from the Advanced Query to SQL Developer or SQL Server Management Studio does not guarantee you are performing a correct test for comparison: other variables come at play that will influence your tests.

In this post I explain the common pitfalls in each of the database backends, and a possible approach to the problem. This approach requires added levels of access to the database (permission to query system views, etc). Make sure to request them from your DBA before moving forward.

Oracle

In Oracle the 4 most common pitfalls are:

1. Use the same sorting criteria as the platform (NLS_COMP and NLS_SORT)

2. Add your bind variables to the query rather than just replacing the text

3. Fetch the correct number of lines.

4. Run the query from the same location.

1. Use the same sorting criteria as the platform (NLS_COMP and NLS_SORT)

This is the most common source for performance mismatches. If you are running a query directly in SQL Developer or TOAD, chances are that you are using the incorrect NLS_COMP and NLS_SORT. This means you will get drastic performance differences in any query that might perform a text lookup.

The OutSystems Platform uses Case Insensitive and Accent Insensitive behavior by default. This means we set the NLS_COMP and NLS_SORT variables as:

- NLS_COMP: LINGUISTIC

- NLS_SORT: BINARY_AI

OutSystems Platform also creates function indexes on text columns for this to work. It does not create the standard indexes on text columns.

When you jump to TOAD or SQL Developer defaults are completely different. In most cases, you get:

- NLS_COMP: BINARY

- NLS_SORT: BINARY

This means that, if you are querying something, like

SELECT * from OSSYS_ESPACE where name = 'ServiceCenter'

Oracle will run a full table scan with the defaults in TOAD or SQL Developer. If you want to reproduce the platform behavior, you must perform some ALTER SESSIONS beforehand:

ALTER SESSION SET NLS_COMP = LINGUISTIC

ALTER SESSION SET NLS_SORT = BINARY_AI

Special note for integration with external databases

If you are integrating with external databases using a Database Connection, the OutSystems Platform will use different sorting criteria: this time, we use “the defaults" - whatever the database sets as default.

So how do you confirm that you're using the correct NLS_* parameters? You can use the attached eSpace - just publish it to your server and access it. To confirm the NLS_* parameters for a database connection, you need to change the eSpace to force connection to a specific entity (refer to section “Changing the Default connection" here).

2. Add your bind variables to the query rather than just replacing the text

After you fix the sorting criteria, you need to get the actual query running in your system. A good way to do it is getting it directly from Oracle’s execution plans.

Your favorite DBA may give you better options; me I’m a sucker for getting them straight out of Oracle. I typically use variations of the below query:

SELECT /* do not remove this comment */

substr(s.sql_text, 0, 75) || '...' begin_sql_text, sp.sql_id, s.cpu_time, s.elapsed_time, s.fetches, s.executions,

to_char(sp.timestamp, 'YYYY-MM-DD hh:mi:ss') parsing_time, s.parsing_schema_name, s.module,

sp.id, sp.depth, sp.operation, sp.options, sp.object_name, sp.object_alias, sp.object_type,

sp.cpu_cost, sp.io_cost, sp.time, s.sql_text, sp.projection

FROM v$sql s, v$sql_plan sp

WHERE s.address = sp.address AND s.hash_value = sp.hash_value

AND s.plan_hash_value = sp.plan_hash_value

and s.sql_text not like '%do not remove this comment%'

/* add your search condition below */

and s.sql_text like ‘%SEARCH%'

/* add your search condition above */

ORDER BY s.sql_text, s.sql_id, sp.id;

If you notice you have a place to add a search. Search for things that may be unique in your query: an attribute name, the name of an entity. You may want to add a dummy condition to your query to make it easier (e.g. where 123456=123456) and then look for that condition. Just pick your poison.

After you get the query, make sure to run it as-is. If it has bind variables, keep them - SQL Developer or TOAD will ask you for the actual values.

3. Fetch the correct number of lines.

This is something that still bites me a lot of times. By default SQL Developer only reads a few dozen lines. So if your query is taking a long time in retrieving data, you won’t see it. I don’t know if TOAD does the same - don’t use it that much.

To address this you may want to change the defaults in SQL Developer / TOAD. I usually just CTRL-END in SQL Developer to force fetching everything.

4. Run the query from the same location.

If the problem is the sheer volume of data, running the query in the same place is important because you want to test the performance of network transfers in the exact same conditions.

If it’s a Windows box just deploy SQL Developer there; if it’s a linux box, you may need other approaches (like running from a Windows server in the same network segment).

Just make sure you are not testing “locally” (in the database machine and/or in a laptop in the same network).

SQL Server

SQL Server has a different set of problems. Though they are similar, they manifest differently:

1. Use the query as run by the platform (using SP_EXECUTESQL)

2. Fetch the correct number of lines.

3. Run the query from the same location.

1. Use the query as run by the platform (using SP_EXECUTESQL)

You may be tempted to get the query directly from Service Studio (either using the SQL generated by the Aggregate / Simple Query or copy-paste of the Advanced Query). However, the Platform does not run queries like that - it runs them using sp_executesql.

So if you want to test the correct query, you need to get it from SQL Server directly.

Again, I’m a sucker for getting the query straight from SQL Server. This is how I usually do it:

SELECT [cp].[refcounts] /* do not remove this comment */

, [cp].[usecounts]

, [cp].[objtype]

, [st].[dbid]

, [st].[objectid]

, [st].[text]

, [qp].[query_plan]

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st

CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp

where st.text not like '%do not remove this comment%'

/* add your search condition below */

and st.text like ‘%SEARCH%'

/* add your search condition above */

order by usecounts desc;

If you run this query in SQL Server, in addition to getting the actual statement you can use to run and reproduce the problem, you get the execution plan of the query - just click the link to open it.

To run the query, after you got it, just convert the statement you obtained to SP_EXECUTESQL syntax. This is relatively simple: for example, if the query you get looks like this:

(@qpstP varchar(50))SELECT TOP (1) ENCOUNTRY.[CODE] o1, NULL o2 FROM [OUTSYSTEMS].DBO.[OSUSR_Q5I_COUNTRY] ENCOUNTRY WHERE (ENCOUNTRY.[NAME] = @qpstP)

and you want to test with country “United Kingdom", converting it to SP_EXECUTESQL syntax becomes something like:

EXEC SP_EXECUTESQL N'
SELECT TOP (1) ENCOUNTRY.[CODE] o1, NULL o2 FROM [OUTSYSTEMS].DBO.[OSUSR_Q5I_COUNTRY] ENCOUNTRY WHERE (ENCOUNTRY.[NAME] = @qpstP)
',
N'@qpstP varchar(50)',
@qpstP = 'United Kingdom'

2. Fetch the correct number of lines.

By default SQL Server Management Studio retrieves all the lines, so no stress here - unless you mistakenly left a TOP X in the query. In the example above it's the actual query that brings the TOP (1) - so don't remove it.

3. Run the query from the same location.

If the problem is the sheer volume of data, running the query in the same place is important because you want to test the performance of network transfers in the exact same conditions.

In this case you can just deploy SQL Server Management Studio in the front-end.

Just make sure you are not testing “locally” (directly in the database machine and/or in a laptop in the same network).

Additional reading

You may also find my other two forum posts interesting:

- www.outsystems.com/forums/discussion/8639/tip-queries-with-like-are-too-slow-when-using-agile-platform-and-oracle-10g/

- www.outsystems.com/forums/discussion/8248/advanced-query-pitfalls-using-alias-and-wildcards/

Regards,
Acácio

ListNLSParameters.oml

17 Aug 2015

Francisco Menezes

Staff

There is another situation to be considered that exhibits performance differences between runing a query in OutSystems and running it directly in the database.

If you copy the SQL generated in an advanced query with inline parameters, and repeateadly test it directly in the database (twice is enough), the query will get an optimised execution plan, while running the query in the platform may constantly generate different queries that never get optimised (if the inline parameters are too dynamic).

In this scenario you are actually comparing two different things - the only time measure comparable, is the first direct execution in the database after pasting the query for the first time...

18 Aug 2015

Rafael Valente

Hello there,

I'm having some difficulties understanding this post and, therefore, how to run some queries that i have with the sp_executesql. These queries are throwing SLOW SQL in the log of Service Center. For instance, i have this query:

SELECT SUM ({SPS_LINFACT2}.[Qtd]) as Soma
FROM {SPS_LINFACT2} JOIN {SPS_FACTURAS} ON {SPS_LINFACT2}.[IdCodFact] = {SPS_FACTURAS}.[IdCodFact]
WHERE {SPS_LINFACT2}.[NumUniBen] = @InNUB
AND {SPS_LINFACT2}.[CodCSaude] = @InCodCS
AND {SPS_LINFACT2}.[DtaCSaude] = @DtaCsaude
AND {SPS_LINFACT2}.[IndSituac] <> 'R'
AND {SPS_LINFACT2}.[IndSituac] <> 'E'
AND {SPS_LINFACT2}.[IdLinFact] <> @InCodLinha
AND {SPS_FACTURAS}.[IdEstadoDoc] <> @EstadoFicheiroCarregado
AND {SPS_FACTURAS}.[IdEstadoDoc] <> @EstadoDevolvida
AND {SPS_FACTURAS}.[IdEstadoDoc] <> @EstadoEmValidacao
AND {SPS_FACTURAS}.[IdCodFact] <> @IdCodFact

What do i have to modify to run this query in SQL Server Management Studio like is ran on the Outsystems Platform?

Thank you in advance and sorry for commenting this year old post.

Best regards,

Rafael Valente

30 Nov 2016

Neil Munro

This will convert the original query into (almost) the equivalent execute, you then just need to edit the params at the end and you're good to go :-)

SELECT CONCAT('EXEC SP_EXECUTESQL
N''',
REPLACE(SUBSTRING(t, CHARINDEX('SELECT',t),LEN(t)),'''',''''''),
''',
N''',
SUBSTRING(t, 2, CHARINDEX('SELECT',t)-3),
''',
',
REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(t, 2, CHARINDEX('SELECT',t)-3),' int','=0'),' nvarchar','='''''),' datetime','=''1900-01-01 00:00:00'''),',',',
')
) FROM
(SELECT [st].[text] AS t
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp
where st.text not like '%do not remove this comment%'

/* add your search condition below */
and st.text like '%SEARCH%'
/* add your search condition above */
) p

09 Oct 2018

Telmo Oliveira

Hello Acácio,

Thank you for the tip! It really helped me handling a performance issue executing a series of advanced queries.

Although, I have one question regarding the first topic on Oracle DB’s (Use the same sorting criteria as the platform (NLS_COMP and NLS_SORT)):

If I want to avoid the non-performant times on the advanced queries execution, wouldn’t be better to use the same sorting criteria as the SQL Developer in the platform and not the other way around?

Thank you,

Best regards,

Telmo Oliveira

26 Mar 2020

1 reply

26 Mar 2020

Show thread

Hide thread

AcacioPN

Staff

Telmo Oliveira wrote:

Hello Acácio,

Thank you for the tip! It really helped me handling a performance issue executing a series of advanced queries.

Although, I have one question regarding the first topic on Oracle DB’s (Use the same sorting criteria as the platform (NLS_COMP and NLS_SORT)):

If I want to avoid the non-performant times on the advanced queries execution, wouldn’t be better to use the same sorting criteria as the SQL Developer in the platform and not the other way around?

Thank you,

Best regards,

Telmo Oliveira

Hi Telmo

Thank you for the feedback! I'm glad you found it helpful!

Regarding your question, you will usually want to optimize your queries to the reality of your database. The indexes created by the platform are NLS_COMP=LINGUISTIC. So, even if your query today might become more performant by using the NLS_SORT of SQL Developer (NLS_COMP=BINARY), as your system evolves you will start getting worst performance. The fact your query performs better under "the wrong" NLS_COMP is an accident - by chance, Oracle is getting a plan that is less worse than the one it had. It is still not a good one.

Let me share a story from "back in the days" when I was doing Support (I believe it's from 2009 or 2010). A colleague from OutSystems was struggling with this exact problem: his query would run in the platform and take 2 minute-something (close to 3), while it would take 30 seconds in SQL Developer. It was still a huge amount of time, but he was insisting he needed to force the NLS_COMP to BINARY on the platform.

I worked with him on that query (an Advanced SQL with subqueries) and we understood that Oracle was making a very bad decision for the subquery. That subquery returned a very small result-set (a few hundred records) and my colleague told me that, by business restrictions, it would never return a huge result-set. Still I played safe and decided to add a "WHERE ROWNUM < 500000" (yes, 5-hundred-thousand) to that subquery.

The result? The query went down to a few dozen milliseconds (less than 100ms), both with NLS_COMP=BINARY and NLS_COMP=LINGUISTIC.

Where am I going with this? If your query is misperforming, and misperforms less in SQL Developer than in the platform, chances are you can optimize performance and slash it to 10% or less of the time in SQL Developer by keeping NLS_COMP=LINGUISTIC and analyzing the query text.

If you find an experienced DBA, you'll see the story above is not a fantasy nor rare... Oracle does a great job at optimizing queries, but sometimes... it fails.

Regards,
Acácio

26 Mar 2020

Muhammad Mahmudul Hasan

Hi @AcacioPN

We tried to reproduce platform behavior from TOAD. Even after setting "ALTER SESSIONS ", it's doing Full table scan means indexing not working on text column.

Index:

Query we executed:

Explain plan:

Kindly advise. Thank you

Regards,

Muhammad Mahmudul Hasan

08 Dec 2024

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines