56
Views
14
Comments
Query performing very slow
Question

Hey all!

I’m trying to run a simple select query with a where statement to get only rows with a specific Id (one row should return) my table does have around 17k rows (and three columns) but other tables with similar amounts of records perform fine  
one thing that I noticed is when I remove the parameter from the query and just plugmy Id (same used for the test) it works better

When running in sql server it take one second at most when running I outsystems environment it sometime works but slow or even timesout sometimes 

I was hoping someone with more knowledge about how queries run on outsystems might be able to help me


thanks!!

Rank: #200

Hi Joharah,

Can you share your advanced query please, for we understand better why not works.

Regards.

Hi,

Let me ask you if you use sql advance or Aggregates? With SQL advance you have more control but normally the aggregates have a nice performance because they are optimized. If you just want some rows, you can define the max of records that you want in both ways (aggregates and sql advance). Another point, you can create in entities an index with the attributes that you want use, the queries will be more fast in read but in write will be more slow.

Cheers 

Rank: #138

Hi Joharah,

First of all, you check how many records are there in your table, for this you put a count query and check it once.

After that you can check once for the top 10 or top 100 records by checking whether there is a problem of slowness or a problem of data.

Vijay M-
Rank: #138


Apart from this, you try to validate that your query is taking time or there is some other problem, for this you can check the log from the service center as per below screenshot


mvp_badge
MVP
Rank: #18

Hi joharah k,

Where are you using that query? Are you displaying it on the screen? If so, I strongly advise you to use Aggregates, like Miguel mentions, the platform optimizes the number of records fetched and the attributes fetched based on what is actually used. You can use the Executed SQL attribute if you want to know exactly what SQL is executed by OutSystems to fetch the preview data displayed in the Aggregate.

Restarting the OutSystems services should have no impact on your issue, as they are not used for database access.

Can you please:

  • Check the errors log for info on why the query was aborted? and
  • Extract a report on SLOW QUERIES from Service Center (like Vijay describes above) and check whether the query shows up there?
  • Since you have access to the external database, can you check the logs for any extra info on what might be happening with the OutSystems-initiated queries?

Please share that information here so we can try to help you with more knowledge.