You can translate the question and the replies:

$Count parameter, slow perfomance

Hello Team, My service contains huge amount of data. Under the hood it uses base view plus few joins. It’s basically a lot of simple columns plus few computed. When I want to do API call with **$count=1** parameter this operation takes around 40-50s to finish. I checked **execution plan** and for each one computed type, there is a generated sql query ``` SQLSentence = SELECT * FROM ( SELECT t0.FieldID AS FieldID, t0.ProjectID AS ProjectID, t1.ProjectName AS ProjectName FROM dbo.dProject t0 JOIN dbo.fProject t1 ON ((t0.ProjectID = t1.ProjectID) ) WHERE t0.FieldID IS NOT NULL) ob ORDER BY FieldID ASC ``` As you can see here, there is no **TOP/LIMIT/OFFSET** keyword here, so basically all items are returned to memory and later filter there. My question is: How to pass this **$Count** parameter to database level and make such filtering there. Below example, what I want to achieve: ``` SQLSentence = SELECT TOP 1 * FROM ( SELECT t0.FieldID AS FieldID, t0.ProjectID AS ProjectID, t1.ProjectName AS ProjectName FROM dbo.dProject t0 JOIN dbo.fProject t1 ON ((t0.ProjectID = t1.ProjectID) ) WHERE t0.FieldID IS NOT NULL) ob ORDER BY FieldID ASC ```
user
10-10-2023 09:54:25 -0400
code

2 Answers

Hi, I'm assuming you're using $count=1 from Restful WS or a published Web service. It will be converted in VQL as `FETCH NEXT <count> ROWS ONLY`. To understand why Denodo is not adding the limit rows statement I would try to check if the query is being completely delegated to the data source. If yes, the limit statement is usually added. Instead, if you have different Data Sources and your joins are performed in Denodo's memory there's no way to know in advance how many rows are needed from the different data sources and the entire data set must be ridden. If the query is delegated and you are still not having the LIMIT clause, you may check the Data Source configuration from Virtual DataPort Administration Tool (not Design Studio). Under `Configuration > Source Configuration` there are some options to enable/disable delegation for FETCH and LIMIT statements. I would check these options and possibly try forcing the LIMIT clause delegation using the Delegate operators list. If you expect that your query is delegated to the Data Source but it's not being done I would check the execution trace looking for a "No Delegation Cause" field that explains the reasons. Hope this helps.
Denodo Team
11-10-2023 09:23:04 -0400
code
Thanks you! We can see that in case of join between different views the root cause is "nest" function that cannot be delegated. On the other hand we have scenarios when we join to "json data source" view in order to parse json strings stored in columns. In such a case limit is also not delegated to the data source and we cannot see in execution trace any "No Delegation Cause".
user
11-10-2023 09:47:08 -0400
You must sign in to add an answer. If you do not have an account, you can register here