Parameterize Web GUI pagination total row count query

Related products: Windows GUI

The web GUI creates pages for the grid result. The query for the row count uses hardcoded values e.g.

select count(1) as tsf_rowcounter
from [table] as t1
where (t1.[col1] = 50 and t1.[col2] = 145 and t1.[col3] = 17340)

We have an issue where this specific query is sometimes slow with an execution time of 50 minutes or more and the paged query is fast (within seconds).

Please change the row count query to use parameters instead of hardcoded values. With this change we are able to attach a fixed query plan to have an consistent query performance. Also the number of compiled queries are reduces with this change

Hi efitskie,

We’ll probably won’t change this for the Web GUI anymore because it has quite an impact, but Indicium already uses prepared statements for all queries. 

Do you have ad hoc workload optimization enabled on your server?


Updated idea status NewNeeds feedback

No we do not have OPTIMIZE_FOR_AD_HOC_WORKLOADS  enabled on this server.


@efitskie Does it help if you enable it?


Hi @efitskie,

We would still like to hear your input on the question Jasper asked a few weeks ago. Thanks in advance!​​​​​​


Unfortunately, we have not received a response to our request for feedback. We therefore assume that the idea has already resolved itself or is no longer relevant.

Updated idea status Needs feedbackClosed