Add MAXRECURSION to a view definition in the SF

Related products: Software Factory

I've ran into a problem with a view created within a project. The view shows all months (periods) for the total duration of a contract… It turns out we have active contracts that run more than 200 months, so while creating the view using one CTE (periods, see code below), opening the view in the GUI it crashes. This is due to a limitation within SQLServer; a maximum of 100 rows are allowed when using a CTE. 

SQLServer provides a workaround, but the client (the GUI in this case) must add something the select instruction when querying the view: MAXRECURSION

Example:
SELECT * FROM service_contract_premium_view OPTION (MAXRECURSION 200)

That would work, but Thinwise has to make an addition to the view definition within the SF. 
 

This would help:

 

Now, I'm forced to create a piece of crappy code like below. That'll keep working until we have even longer lasting contracts…..:
 

Code example:


with periodes
(
subsidiary_id
, service_contract_id
, ending_date
, starting_date
, contract_duration
, periode
)
as
(
select
subsidiary_id
, service_contract_id
--, isnull( DateAdd(day , 1 , EOMonth(ending_date) ) , DateAdd( month , contract_duration , isnull( starting_date, convert( date , getdate() ) ) ) )
, isnull( DateAdd(day , 1 , EOMonth( isnull( ending_date , case when scheduled_ending_date is null then scheduled_ending_date when scheduled_ending_date > convert(date , getdate()) then scheduled_ending_date else convert(date , getdate()) end ) ) ) , DateAdd( month , contract_duration , isnull( starting_date, convert( date , getdate() ) ) ) )
, isnull( starting_date , convert( date , getdate() ) )
, contract_duration
, 1
from service_contract

union all

select
subsidiary_id
, service_contract_id
, ending_date
, starting_date
, contract_duration
, periode + 3
from periodes
where periode < DateDiff( month, starting_date , ending_date )
+ case when day(starting_date) = 1 then 0 else 1 end --Extra periode indien niet beginnend op 1e vd maand
)

, periodes2
(
subsidiary_id
, service_contract_id
, ending_date
, starting_date
, contract_duration
, periode
)
as
(
select
subsidiary_id
, service_contract_id
--, isnull( DateAdd(day , 1 , EOMonth(ending_date) ) , DateAdd( month , contract_duration , isnull( starting_date, convert( date , getdate() ) ) ) )
, isnull( DateAdd(day , 1 , EOMonth( isnull( ending_date , case when scheduled_ending_date is null then scheduled_ending_date when scheduled_ending_date > convert(date , getdate()) then scheduled_ending_date else convert(date , getdate()) end ) ) ) , DateAdd( month , contract_duration , isnull( starting_date, convert( date , getdate() ) ) ) )
, isnull( starting_date , convert( date , getdate() ) )
, contract_duration
, 2
from service_contract

union all

select
subsidiary_id
, service_contract_id
, ending_date
, starting_date
, contract_duration
, periode + 3
from periodes2
where periode < DateDiff( month, starting_date , ending_date )
+ case when day(starting_date) = 1 then 0 else 1 end --Extra periode indien niet beginnend op 1e vd maand
)


, periodes3
(
subsidiary_id
, service_contract_id
, ending_date
, starting_date
, contract_duration
, periode
)
as
(
select
subsidiary_id


etc etc etc

Hi Henri,

A temporary work-around would be to place the CTE in a (multi-statement) table-valued function. Here, the option (maxrecursion x) can be set. The view can then query from the table-valued function.

You can do this by adding a function-typed subroutine with return type table and provide it with a result table name and return table column definition. This will cause the function to have a table variable available for the result.

Do note: If the table name for the table-valued function in subroutine is not set, it will be generated as an in-line table valued function and suffer the same problem with maxrecursion.


Thanks @Anne Buit ,

I got your workaround figured out and working. Initially I wanted maxrecursion as a parameter in the subroutine, but it’s not allowed to have a parameter in the maxrecursion option. That also can be worked around, but I'll leave it at this. 

The tablename in the subroutine can be anything (doesn't need to exist in the project, just needs to be there for the subroutine to return a named table). For now I just hardcode the number for maxrecursion at a number high enough for the next couple of years… :grin:

Your workaround results in more or less the same as my workaround (3 unions to get to a maximum of 300 rows) but it sure is easier to figure out what is happening in the code…. Performance wise I don't see any noticeable difference.

As you mentions, it's another workaround to the stricktness of sqlserver and I hope a setting will be added in the SF as it comes to views and to have the GUI add the maxrecursion information when doing a query on the view in question..

 

 

 


@Anne Buit 

i've been busy with other stuff, but just to let you know. I've changed things back to my earlier workaround. The view and table_valued solution both were more or less similar performance wise. But the view is also used in other views that performed drasticly poorer with the table_valued solution! Change from one record to another, where the view in question is a detail tab went from 1 second to 10 or more seconds. So no way this is what'll be accepted by the enduser.

So a possibility to configure a setting in the SF will be much appreciated!