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

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

  

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 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.
 
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:
Regards,
Acácio

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...

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