Hi,

I have a table which has 2 lakh records which taking more than a minute to load the page,even for search and sort it is taking more time is there any dependency that can be used instead of List_Navigation to increase the performance.


Thanks,

Ronan T.

Hi Ronan,

You should never display 20,000 records on a page, naturally that page is going to be slow. What makes the page slow? Is it the display, the Preperation to fetch the data, what? Please investigate such things before asking questions on the forum, thanks.

Kilian Hekhuis wrote:

Hi Ronan,

You should never display 20,000 records on a page, naturally that page is going to be slow. What makes the page slow? Is it the display, the Preperation to fetch the data, what? Please investigate such things before asking questions on the forum, thanks.

Hi Kilian Hekhuis,

 sorry, there are 2 lakh records in the databases but i am displaying 10 record per page,

even if i am displaying 10 records per page it is taking too long time to load.


Thanks,

Ronan T.


Hello Ronan, 

If you are using the aggregate that feeds the table records, like in screen actions (e.g. iterating it), the platform will not be able to optimize the aggregate and will fetch all data, even if you are showing only 10 records. 

Cheers 

Hi Ronan,

Have you tried my setting max record property value on SQL element.

Regards,

Koushik

Eduardo Jauch wrote:

Hello Ronan, 

If you are using the aggregate that feeds the table records, like in screen actions (e.g. iterating it), the platform will not be able to optimize the aggregate and will fetch all data, even if you are showing only 10 records. 

Cheers 

Hi Eduardo Jauch,

Is there any solution for this so that it need to fetch only 10 records at a time,

Thanks,

Ronan T


Yes. 

Don't use the preparation aggregate in screen actions (if is this that you are doing). If you need to use, do a data refresh in the aggregate before using it. 

By the way, why do you need to use the aggregate from preparation, used as source of the table records, in a screen action? 

Cheers

Eduardo Jauch wrote:

Yes. 

Don't use the preparation aggregate in screen actions (if is this that you are doing). If you need to use, do a data refresh in the aggregate before using it. 

By the way, why do you need to use the aggregate from preparation, used as source of the table records, in a screen action? 

Cheers

Hi Eduardo Jauch,

I have used preparation aggregate as a source record for the table any how the aggregate need to be refreshed onclick of pagination button so i am refreshing the aggregate,even after that the performance is slow.

Is there any serverside fetching of data  dependency in outsytems onclick of pagination button.

Thanks,

Ronan T


Ronan, 

There are LOTS of things that can slow down a page. 

- slow connection

- busy server

- bad queries

- fetching too much data

- bad logic

- etc. 

We will not be able to help you if you don't show us what are you doing (and maybe not even if you show). 

Sorry. 

Cheers

Hi Ronan,

Of course, it can also be that your query is very slow because of a lack of proper indexes (necessitating a full table scan).

Hi Eduardo Jauch,

Everytime the server cannot be slow know and in the logic we have removed all the unwanted code.

this is the query we have used

SELECT {TABLE1}.[ID],{TABLE1}.[COMPONENT],{TABLE1}.[TIME],case when {TABLE1}.[STATUS]=1 then "Processing" when {TABLE1}.[STATUS]=2 then "Success" when {TABLE1}.[STATUS]=3 then "Error" else null end as status, case when ID=FK then COUNT(NULLIF(2, {TABLE2}.[STATUS])) else null end AS Success, case when ID=FK then COUNT(NULLIF(1, {TABLE2}.[STATUS])) else null end AS Error FROM {TABLE1} LEFT OUTER JOIN {TABLE2} ON {TABLE1}.[ID] = {TABLE2}.[FK] WHERE ({TABLE1}.[COMPONENT]=@interface or @interface='')  and {TABLE1}.[ID] like CONCAT('%',Trim(@BatchID),'%')
 and {TABLE1}.[COMPONENT] in (@ListOfInterface )
 group by {TABLE1}.[ID]

Thanks,

Ronan T.

Hi Ronan,

You're doing a LIKE on TABLE1, so you're probably up against a full table scan. Since this is a SQL query, not an Aggregate, this is really not an OutSystems-related question. Please contact your DBA if you have one to see how you can optimize this.

Kilian Hekhuis wrote:

Hi Ronan,

You're doing a LIKE on TABLE1, so you're probably up against a full table scan. Since this is a SQL query, not an Aggregate, this is really not an OutSystems-related question. Please contact your DBA if you have one to see how you can optimize this.

Hi Kilian Hekhuis,

The performance was same even when we used aggregate because data is getting load from the client side, is there any serverside data fetching dependency in the outsystems?

Thanks,

Ronan T


Kilian Hekhuis wrote:

Hi Ronan,

You're doing a LIKE on TABLE1, so you're probably up against a full table scan. Since this is a SQL query, not an Aggregate, this is really not an OutSystems-related question. Please contact your DBA if you have one to see how you can optimize this.

Hi Kilian Hekhuis,

The performance was same even when we used aggregate because data is getting load from the client side, is there any serverside data fetching dependency in the outsystems?

Thanks,

Ronan T


Hi Ronan,

Of course, if you use a LIKE in an Aggregate, the performance will be slow as well. You just have created a very slow query, that performs a full table scan because of the LIKE. So your problem is that query, and that's not directly related to anything OutSystems, so this is not the place to ask for help.

Hi Ronan,

Your query enters in the - Bad query item of the list for bad performance.

The complexity of the filter, based on a LIKE (as @Kilian told), but not only (an IN, etc.), will most likely cause the database to do a full table scan to know which lines it must use before grouping the data.

Only someone familiar with the data model, the data and the database will be able to help you get a more 'performatic' query.

Cheers.