Can the sort order on Primary Key be removed?
Posted: Wed Mar 04, 2009 3:22 pm
Hi,
When I see the performance statistics in administrator, I see the following trace
This query is result of the following script:
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
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