Can the sort order on Primary Key be removed?

Discuss all feature requests you have for a new Servoy versions here. Make sure to be clear about what you want, provide an example and indicate how important the feature is for you

Can the sort order on Primary Key be removed?

Postby martinh » Wed Mar 04, 2009 3:22 pm

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:

Code: Select all
_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
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium

Re: Can the sort order on Primary Key be removed?

Postby ngervasi » Wed Mar 04, 2009 8:36 pm

In 4.x you can do the following:

Code: Select all
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.
Nicola Gervasi
sintpro.com
SAN Partner
ngervasi
 
Posts: 1485
Joined: Tue Dec 21, 2004 12:47 pm
Location: Arezzo, Italy

Re: Can the sort order on Primary Key be removed?

Postby martinh » Thu Mar 05, 2009 12:48 am

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
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium

Re: Can the sort order on Primary Key be removed?

Postby rgansevles » Fri Mar 06, 2009 6:04 pm

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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Can the sort order on Primary Key be removed?

Postby martinh » Fri Mar 06, 2009 6:34 pm

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
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium

Re: Can the sort order on Primary Key be removed?

Postby rgansevles » Mon Mar 09, 2009 12:51 pm

Martin,

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

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

Re: Can the sort order on Primary Key be removed?

Postby sjk » Mon Mar 09, 2009 7:51 pm

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.
sjk
 
Posts: 13
Joined: Tue Jul 22, 2008 5:28 pm

Re: Can the sort order on Primary Key be removed?

Postby martinh » Mon Mar 16, 2009 11:13 am

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()

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

_fs.loadRecords(_query)


OR

Code: Select all
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)
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium


Return to Discuss Feature Requests

Who is online

Users browsing this forum: No registered users and 12 guests