Hi,
We have a weird issue when complex SQL query is run fast (less than a second) in SQL Server Studio and when using Test query button in Service Studio. But in web app runtime (with and without debug) same query with same inputs is taking over 20 seconds to execute... We've get the query code as described here https://www.outsystems.com/forums/discussion/15702/tip-performance-of-query-in-outsystems-platform-slower-than-running-in-database/
Is there any difference of how OutSystems make calls to DB from ServiceStudio when testing query and when it is excused in runtime? Any ideas of what can cause this "inconsistency" would be appreciated. Thank you.
Hi Mykola (Nick) Tkachenko ,
I believe, your query runs fast while testing it with the Test query button in Service Studio is because the no of records fetch during the test is limited. The default value is 100.
Please see the screenshot below for reference.
pranav pandey wrote:
I believe, your query runs fast while testing it with the Test query button in Service Studio is because the no of records fetch during the test is limited. The default value is 100. ....
Hi Pranav, it is same even if query return no rows or have several records in response.
Hi Nick,
First of all have checked in Analytics that what is causing 20sec and how to diagnose that.
Please check this URL: https://success.outsystems.com/Documentation/11/Managing_the_Applications_Lifecycle/Monitor_and_Troubleshoot/Troubleshoot_the_Performance_of_an_Application
Abdul quadir Saifee wrote:
....
Hi Abdul, in this case the SQL query in question that has server time execution over 15 sec .
Before starting the query, you print a log message and after being executed, then print the log message.
This will let you know how much the query is getting when it is executed.
Sometimes pages or web blocks or other functions take time which we are not able to know once you try it
Vijay M-
Vijay Malviya wrote:
Hi Vijay, we did exactly this and on runtime query takes over 15 seconds... same if we debug step to execute query is same slow...
It would be easy to blame the query but it is fast if run directly or with test button. Return result set is not big - around 0-10 results. Query has joins, unions and like search and around 20 input parameters.
Mykola (Nick) Tkachenko wrote:
Try to outsystem Executed SQL in SQL Server Studio .
Check how much time is it taking.
It is also fast in SQL server. We also got query as described in this post https://www.outsystems.com/forums/discussion/15702/tip-performance-of-query-in-outsystems-platform-slower-than-running-in-database/ and after variables assign it is fast...
To which database is OutSystems connected to? And do you have access to the database or a access to the execution plans of queries (to verify the different execution plans of those 2 queries)? e.g. on sql server
SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.* FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.dbid = DB_ID('msdb') ORDER BY deqs.last_execution_time DESC
Hi Hans, we are using Microsoft SQL Server. Thanks for the query we will analyse the results with our DB admin.