Slow SQL and Information Does Not Load
Question
Application Type
Reactive

Hi, I'm using a server action to fetch data so I can fill the screen with this data. Sometimes, the information does not load. Other times, the information does load. When I go into Service Center, I see the error "Slow SQL." I am not sure if this is what is stopping the information from loading to the screen. I was thinking maybe it could be a server-action timeout or something related. Any ideas for how I could resolve this? Thanks so much guys.

Hi Joeyy,

Can you please try to increase the server time out and see if it resolves your problem? And are you using aggregate or advance SQL to fetch the data? If you are using advance SQL and have wrote your own query, please also check the joins and any SQL functions used are correctly made to that query.

Thanks & Kind Regards,

Sachin

Hello @Joeyy,

Hopefully the above suggestion helps your situation. If not, please help us understand your situation better:

1. What is the total count of rows you are fetching?

2. 'Slow SQL' is not an error per se and you are most likely seeing this in the General Log. Can you share a screenshot so we can see how many ms or sec the query is taking?

3. If you are experiencing timeouts they would be logged for sure. Please check and share screenshots if possible.

4. Are you using an Aggregate or an Advanced SQL query to fetch data? Aggregates are typically optimized in OutSystems and hence the preferred choice.

5. Are you joining several tables (if so how many)?

Regards,

AJ

On the Slow SQL message what is the time that shows against it? If it is a few 100ms it should be fine however if it is in the range of 30 seconds or more then you are likely getting timeouts. 

The issue will be in the SQL query itself and probably related to the amount of data. Outsystems tries to optimise but there is only so much that it can do if you have bad SQL query practices. Try doing the following


1) Add indexes for items in the Filter or sort. Outsystems only creates indexes automatically for identity links. Proper indexes are what SQL needs to make efficient queries

2) Avoid the using like "%"+searchstring+"%" in queries filters that have a lot of records. SQL can't use an index on these fields and this can cause table scans which are bad.

3) review any join's to very large tables and see if you can add additional filters into the join query itself. Also look at indexes for the joins if they are on more than just ID fields. Note SQL can be a little unpredictable with this, sometimes it works great but in other cases I've had it slow right down.

Post a copy of the Aggregate or query and we can see if there are any further suggestions we can make

Hi Joeyy

i could be indeed has to do with a query, but it can be a loop over a long list as well. 

see the list on https://success.outsystems.com/Documentation/Best_Practices/Performance_and_Monitoring as well which contains lots of information about performance improvements.

regards Hans

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