Can the sort order on Primary Key be removed?

Classic Servoy
1

Hi,

When I see the performance statistics in administrator, I see the following trace

select top 201 article_id from article where (article_code = ? and sys_company_id = ?) and sys_company_id = ? order by article_id asc

This query is result of the following script:

_fs_article.find();
_fs_article.article_code = _article_code;
_fs_article.sys_company_id = globals.interface_company_id;
_fs_article.search();

So nothing special about this coding (please ignore the double sys_company_id in WHERE clause, because there is also a tablefilter active).

What you see is that Servoy by default adds the order by article_id asc to the query.
I would like to see that Servoy runs the query without this ORDER BY.
My database is optimized with clustered indexes and what happens now is that SQL Server retrieves the data in order of the clustered index, and then sort the foundset on the article_id because it was added to the query
And next I have to do a manual sort again on the foundset, to sort my foundset.

So I would to see that this ORDER BY that is added by default is removed or some setting on database server properties or servoy admin pages to enabled/disable this ORDER BY
Or perhaps some databaseManager.JSTABLE property where you can set ORDER BY enabled or disable.
Or a new method databaseManager.sortByPrimaryKey(servername, tablename, true | false)

Perhaps the last solution is the most dynamic one and is in line with for example the addTabeFilterParam() method

Please consider the above. I would have a positive effect on the performance in database that have been optimized already.

Martin

2

In 4.x you can do the following:

controller.sort('my field asc',true)  // sort will be deferred
controller.find()
...
...
controller.search() // the previous sorting will be used and only 1 single query will be sent to the DB

I always use it when loading data using loadRecords(sql) to save one query but it should work nicely with controller.search() as well.

3

OK that is new for me.
I’m still using 3.5.8
And it also means that before each find/search you have to add this statement.

I would like to see a more general solution like I described above or something like this:

databaseManager.setDefaultSort(servername, tablename, sortstring)

Martin

4

Martin,

Servoy needs to use an order by clause in each query because we may requery primary keys if the user progresses in the foundset.
We initially select 1 batch of PKs (1-200) and query again if more records (201-400) have to be loaded.
If there is no order by clause the ordering is not determined and may be inconsistent over different queries.

You can set the initialSort property on the form to match the optimal for your db.

Rob

5

Hi Rob,

I understand this; but I have lot of situations where I have a second unique index on my table.

For example:

I have:

article_id (internal id) integer used as primary key in Servoy
article_code char(30) used as unique index also

My database has a clustered index on article_code which means that the records are ordered in this order, because I would like to show my articles always in order of the article_code
I’m not using the initialSort on form level because I want to do that in script, and as far as I know this initialSort I can’t use in script.

In my case when Servoy wants to get the next batch of 200 I would like to see it based on the alternate primary key (article_code); I guess it would avoid this additional sort
Specially when there are 6400 articles in my database .

Making article_code primary key is not an option, because my article_code is unique within a company, but company-id is a table filter. So in fact my logical PK is (company_id, article_code) where company_id is a tablefilter.
And because my PK is always 1 field (article_id, integer with sequence or UUID), I afraid the current solution had bad influance on the performance.

So that is why I was looking for a solution to optimize the performance within the possibilities of Servoy.

Maybe the following:

databaseManager.setAlternatePrimaryKey(servername, tablename, fields) for example databaseManager.setAlternatePrimaryKey(‘myServer’, ‘article’, ‘company_id, article_code’)

Martin

6

Martin,

We will not create this new feature in 3.5 because 4.1 already contains the controller.sort() option Nicola mentioned.

Rob

7

How would you defer the sort order if you are not coding for the find() ?. We use a global find method for all the forms and enter the query criteria in the form fields. We use database views(read only) but Servoy requires primary key row_ident columns. The Pk columns in the order by are causing poor query performance. We also would like the sort order to be removed.

8

I can agree with that. I also use global methods for find.

As soon as I have migrated to version 4.1 I’m going to test this controller.sort() and see if I can fit this in my framework, so that Servoy will use the order that I prefer.
The only problem left is when using foundsets that are not on a form create with databaseManager.getFoundSet()

var _fs = databaseManager.getFoundSet('myServer', 'article')
var _query = 'SELECT article_id FROM article ORDER BY article_code DESC'

_fs.loadRecords(_query)

OR

var _fs = databaseManager.getFoundSet('myServer', 'article')
_fs.find()
_fs.sort('article_code DESC')
_fs.search()

I hope the above will fill the foundset directly in the required sort order without having to sort the foundset again
I’ll test this also as soon as I’ll be working on 4.1 (which will be within a month)