Hello Zwe Mann,

From the information you provided, it seems that your SQL queries perform significantly slower in the real application compared to the cloned independent module. This discrepancy could be caused by a variety of factors. Here are some possible explanations and troubleshooting steps you can take:

1. Differences in Application Load:

   • In the real application, the database or app server might be under heavier load due to concurrent users, background processes, or insufficient resources.
   • Check whether there are database locks, resource contention, or extensive logging happening in the real application.

2. Differences in Application Context:

   • The cloned module operates in isolation, whereas the real application might have additional dependencies (e.g., other SQL queries, integrations, or business processes) which could slow down the overall performance.

3. Execution Plan Differences:

   • In some cases, SQL Server or another database may generate different execution plans for an identical query depending on parameters, schema changes, or database statistics.
   • Use the "Execution Plan" in your database management tool (e.g., SQL Server Management Studio) for both scenarios to pinpoint where the bottleneck lies. Refer to SQL query takes long time to complete for more details on analyzing SQL execution plans.

4. Data Volume:

   • The same query might be running against different amounts of data in the real application versus the cloned module. Check if filters, indexes, or the volume of data differ between environments.

5. Indexes and Database Optimization:

   • Ensure that the required indexes are correctly applied on the database tables used by the query. Missing indexes can significantly increase query execution time.
   • Indexes are critical for improving query performance, as highlighted in SQL query takes long time to complete.

6. Parameter Sniffing:

   • In some cases, specific parameter values can lead the database to generate inefficient execution plans. This is often referred to as a parameter-sniffing issue.

7. Network Latency or Environment Configuration:

   • Cloned modules may run in a different environment (e.g., personal or test environment) with less network latency or different configurations compared to the production environment.
   • Network-related overheads around external connections (e.g., databases or APIs) impact performance, as seen in Big difference between query took time and node's execution time.

Steps to Diagnose and Fix:

1. Compare Execution Plans:

   • Log and compare the execution plans for the query in both the real application and the cloned module to identify differences.

2. Profile the Query:

   • Use database profiling tools (e.g., SQL Server Profiler) to monitor detailed query performance.

3. Index Optimization:

   • Review and optimize indexes on tables involved in the slow queries. Add or refine indexes as required.

4. Test Query Variations:

   • Try running the affected query directly in the database using SQL tools (e.g., SQL Server Management Studio) on both environments to understand differences.

5. Parameter Optimization:

   • If the issue is tied to certain parameters or parameter sniffing, you can use query hints or rewrite your SQL to mitigate the problem.

6. Monitor Logs:

   • Monitor the "SLOWSQL" or other logging in OutSystems Service Center for detailed information on performance bottlenecks.

Applying these steps should help you pinpoint the source of the performance gap between the environments and improve your real application's query performance. Let us know if you discover anything specific!

Hi @Zwe Mann Oo Your SQL query might not be an issue but all the above factors do play an important role for slowing down, specifically the dependencies used in real time and the Index Optimization, to start with.