addFoundSetFilterParam slows the server extremely down

Questions and answers regarding general SQL and backend databases

addFoundSetFilterParam slows the server extremely down

Postby ttmgybta » Mon Jun 20, 2011 12:10 pm

addFoundSetFilterParam slows the server extremely down, when I add for example 300 to 400 filter.
Code: Select all
var _CostCenterArray = new Array(<CostCenter_oid>,<CostCenter_oid>, ...)

databaseManager.addTableFilterParam(‘myDb’, null, 'oidCostCenter', 'in', _CostCenterArray, 'CostCenter_filter')

So when now the function is called in the client, it tacks a long time to be applied. In the trace of the server I see that he is firing 100 of Querys which I guess slows down everything.

Any ides
Regards
ttmgybta
ttmgybta

Servoy Version: 6.0.5 - build 1230
ttmgybta
 
Posts: 163
Joined: Thu Nov 11, 2010 10:39 am
Location: Da-vos schön isch

Re: addFoundSetFilterParam slows the server extremely down

Postby rgansevles » Mon Jun 27, 2011 10:14 am

ttmgybta,

When you add a table filter, all active foundsets on that table are reloaded to be checked against the filter.
Maybe some indexes may make the query faster.

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: addFoundSetFilterParam slows the server extremely down

Postby ttmgybta » Mon Jun 27, 2011 2:36 pm

Hello Rob

We figured out, that it is not the addTableFilterParam which sets the filter on all tables whit the field ‘oidCostCenter’, which slows everything down.
It is the addTableFilterParam on a single table, which slows everything down.
Code: Select all
databaseManager.addTableFilterParam(globals.nav_server_user, 'CostCenter’, 'oid', 'in', _ _CostCenterArray, 'CostCenter_filter')


Could it be that the oid on the CostCenter table is the primary key?

Regards
ttmgybta

Servoy Version: 6.0.5 - build 1230
ttmgybta
 
Posts: 163
Joined: Thu Nov 11, 2010 10:39 am
Location: Da-vos schön isch

Re: addFoundSetFilterParam slows the server extremely down

Postby rgansevles » Tue Jun 28, 2011 11:29 am

ttmgybta,

A table filter on a very large set of pks works but is probably not very good for performance.
When a filter contains more than 200 values, Servoy will create a temp table for each query on that table, put the values in the temp table, join on it and drop the temp table.

Filters work best when they are simple like 'cost_center_code = <value>'.

If you have very specific logic to filter on cost center you can also do something like:
Code: Select all
databaseManager.addTableFilterParam(globals.nav_server_user, 'CostCenter’, 'oid', 'in','select oid from tab where ...', 'CostCenter_filter')
, but a straight filter value is best.

How do you determine the valid cost center values for that client?

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 7 guests

cron