We have a form with a foundset based on a view which is based on multiple tables. When this form is loaded for the first time, this takes at least 5 seconds. This happens because Servoy does this query ‘select top 201 [key] from order by [key] asc’ by default. In our case the is a view with several millions of records and the [key] is a set of keys (2 fields) which are table primary keys and indexed.
This is the query:
select top 201 [Key], PriceListKey from axvwRealTimePricelistRow order by [Key] asc, PriceListKey asc
This query forces SQL Server to order the view in a way the indices can’t be used. We don’t want Servoy to do this query because we don’t use it, keeps the user waiting and eats a lot of our db-server processing time! When we do a search we use both keys so this is very fast.
The question here is: can we stop Servoy from doing this default query? And if not, does someone have a workaround for us?