October 3, 2016 at 9:48 am
What are other option instead of using maxrecursion in sql server 2008 ?
October 3, 2016 at 9:56 am
Other option for what?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2016 at 9:59 am
For max recursion.
I am getting error
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
My DBA don't allow me to Use this MaxRecursion. I am looking different option.
October 3, 2016 at 10:09 am
If you need to change the default recursion for CTEs, you need to use maxrecursion. Either work with your DBA so that he sees why you need it, or re-design the query so that it doesn't use recursive CTEs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2016 at 10:21 am
OK.. Thanks.
Let me check.
October 3, 2016 at 10:31 am
If you post your code with sample data, you might get an alternative to the code.
The reason to prevent the use of maxrecursion is that it might not be necessary and there's some bad coding in place.
October 3, 2016 at 11:14 am
If I use maxrecursion in query, is this change server level the thesold or database level ?
Can I implement in production ? What is the advantage and disadvange ?
Thanks
Bhavesh
October 3, 2016 at 11:19 am
patla4u (10/3/2016)
If I use maxrecursion in query, is this change server level the thesold or database level ?Can I implement in production ? What is the advantage and disadvange ?
Thanks
Bhavesh
Disadvantages - lack of scalability, potential for infinite loop (remember, you ran into the default level of recursion at 100), wrong solution for the problem.
Advantages - Not sure, haven't seen the code, DDL for the tables, or sample data and expected results.
October 3, 2016 at 11:19 am
CREATE VIEW [dbo].[test]
AS
WITH CTE_SUBJECT_VISIT_SCHEDULE AS
(
SELECT s.Subject_ID
,ce.ClinicalEvent_ID
,ce.Event_CD
,ce.Event_NM
,ce.Visit_NO
--,ce.TimeOrigin_IND
,CASE WHEN TimeOrigin_IND = 'L' THEN CAST((ce.ClinicalEvent_ID -1) AS Varchar(5))ELSE ce.TimeOrigin_IND END AS TimeOrigin_IND
,ce.TimeUnits_IND
,ce.TimeUnits_CNT
,CASE WHEN ce.Event_CD = 'SRN' THEN 'S'
WHEN ce.Event_CD = 'RND' THEN 'R'
WHEN ce.Event_CD = 'FD' THEN 'D'
WHEN ce.Event_CD = 'ENR' THEN 'N'
WHEN ce.Event_CD = 'RUNIN' THEN 'I'
WHEN TimeOrigin_IND IN ('R','L') Then CAST((ce.ClinicalEvent_ID) AS Varchar(5))
--ELSE ce.ClinicalEvent_ID
END AS Event_IND
,se.Event_DT
,s.phase_id
,s.Last_Visit_NO
--,Max(se.Event_DT) OVER (PARTITION BY S.SUBJECT_ID) AS Max_Visit_Date
FROM TEST_1 s
INNER JOIN TEST_2 si on s.Site_ID = si.Site_ID
CROSS JOIN T_ClinicalEvent ce
LEFT OUTER JOIN T_SubjectEvent se
ON ce.ClinicalEvent_ID = se.ClinicalEvent_ID
AND se.Subject_ID = s.Subject_ID
WHERE ce.Visit_NO IS NOT NULL AND ce.phase_id = s.phase_id
)
,CTE_RECURSIVE_SUBJECT_VISIT_SCHEDULE AS (
SELECT csr.Subject_ID
,CSR.ClinicalEvent_ID
,CSR.Event_CD
,CSR.Event_NM
,CSR.Visit_NO
,CSR.TimeOrigin_IND
,CSR.TimeUnits_IND
,CSR.TimeUnits_CNT
,CSR.Event_IND
,CSR.Event_DT
,CAST(NULL AS DATETIME) AS ScheduledEvent_DT
,csr.Last_Visit_NO
FROM CTE_SUBJECT_VISIT_SCHEDULE csr
WHERE csr.ClinicalEvent_ID IN (22,23,197)
UNION ALL
SELECT csr1.Subject_ID
,csr1.ClinicalEvent_ID
,CSR1.Event_CD
,CSR1.Event_NM
,CSR1.Visit_NO
,CSR1.TimeOrigin_IND
,CSR1.TimeUnits_IND
,CSR1.TimeUnits_CNT
,CSR1.Event_IND
,csr1.Event_DT
,CASE WHEN csr1.TimeUnits_IND= 'D' THEN DATEADD(day, csr1.TimeUnits_CNT, ISNULL(rv.Event_DT,rv.ScheduledEvent_DT))
WHEN csr1.TimeUnits_IND= 'W' THEN DATEADD(week, csr1.TimeUnits_CNT, ISNULL(rv.Event_DT,rv.ScheduledEvent_DT))
WHEN csr1.TimeUnits_IND= 'M' THEN DATEADD(MONTH, csr1.TimeUnits_CNT, ISNULL(rv.Event_DT,rv.ScheduledEvent_DT))
END as ScheduledEvent_DT
,csr1.Last_Visit_NO
FROM CTE_RECURSIVE_SUBJECT_VISIT_SCHEDULE rv
INNER JOIN CTE_SUBJECT_VISIT_SCHEDULE csr1
ON rv.Event_IND= csr1.TimeOrigin_IND and csr1.Subject_ID = rv.Subject_ID
WHERE DATEADD(day, csr1.TimeUnits_CNT, ISNULL(rv.Event_DT,rv.ScheduledEvent_DT)) < DATEADD(day, 250, GETDATE())
)
SELECT Depot_ID
,Site_ID
,ItemType_ID
--,ProjectedWindow
--,SUM(Quantity) as Quantity
,SUM(Projectedneed30Days) AS ProjectedNeed_30_Days
,SUM(Projectedneed60Days) AS Projectedneed_60_Days
,SUM(Projectedneed90Days) AS Projectedneed_90_Days
,SUM(Projectedneed180Days) AS Projectedneed_180_Days
,SUM(Projectedneed240Days) AS Projectedneed_240_Days
FROM
(
SELECT d.Depot_ID
,si.Site_ID
,cev.Subject_ID
,s.TreatmentArm_ID
,cet.CliniCalEvent_ID
,CET.ClinicalEventTreatment_ID
,CEIT.ItemType_ID
,i.Item_Quantity
,cev.Event_CD
,cev.Event_NM
,cev.Visit_No
,cev.Event_Dt
,cev.ScheduledEvent_DT
,DATEDIFF(dd, CAST(GetDate() As DATE),cev.ScheduledEvent_DT )As DaysLeftforVisit
,CASE WHEN DATEDIFF(dd, CAST(GetDate() As DATE),cev.ScheduledEvent_DT) between 0 and 30 Then i.Item_Quantity ELSE 0 END AS Projectedneed30Days
,CASE WHEN DATEDIFF(dd, CAST(GetDate() As DATE),cev.ScheduledEvent_DT) between 31 and 60 Then i.Item_Quantity ELSE 0 END AS Projectedneed60Days
FROM CTE_RECURSIVE_SUBJECT_VISIT_SCHEDULE cev
INNER JOIN (select distinct subject_id, last_visit_no from CTE_SUBJECT_VISIT_SCHEDULE) cte on cte.Subject_ID = cev.Subject_ID
INNER JOIN T_Subject s ON cev.Subject_ID = s.Subject_ID
INNER JOIN T_Site si on si.Site_ID = s.Site_ID
INNER JOIN T_Depot d on si.Depot_ID =d.Depot_ID
INNER JOIN T_ClinicalEventTreatment CET ON CET.ClinicalEvent_ID = cev.ClinicalEvent_ID AND cet.treatmentarm_id = s.treatmentarm_id
INNER JOIN T_ClinicalEventItemType CEIT ON CET.ClinicalEventTreatment_ID = CEIT.ClinicalEventTreatment_ID
LEFT OUTER JOIN ( SELECT i.Subject_ID,i.ItemType_ID,COUNT(1) AS Item_Quantity
FROM T_Item i
INNER JOIN
(SELECT se.Subject_ID,MAX(i.Subjectevent_ID)AS Subjectevent_ID
FROM T_SubjectEvent se
INNER JOIN T_ClinicalEvent ce on se.ClinicalEvent_ID = ce.ClinicalEvent_ID
INNER JOIN T_Subject s on s.Subject_ID = se.Subject_ID
INNER JOIN T_Item i on i.Subject_ID = s.Subject_ID and i.SubjectEvent_ID = se.SubjectEvent_ID
WHERE ce.Visit_NO is Not Null
GROUP BY se.Subject_ID
)lv on i.Subject_ID = lv.Subject_ID and i.Subjectevent_ID = lv.Subjectevent_ID
GROUP BY i.Subject_ID,i.ItemType_ID
)i on s.Subject_ID = i.Subject_ID
AND CEIT.itemtype_id = i.itemtype_id
WHERE cev.Visit_No > cte.Last_Visit_NO and cev.ScheduledEvent_DT >= Cast(getdate() As DATE)
) Projected
Group By Depot_ID,Site_ID,ItemType_ID
GO
October 3, 2016 at 11:21 am
patla4u (10/3/2016)
If I use maxrecursion in query, is this change server level the thesold or database level ?Can I implement in production ? What is the advantage and disadvange ?
Thanks
Bhavesh
Maxrecursion is a query hint. It only affects the query that is using it.
The advantage is that you can allow more or less recursion in a recursive cte.
The disadvantage is that you might get caught in an infinite loop or at least a bad performing query. I've never met a case that needed more than 20 recursion levels. Most won't require more than 7.
October 3, 2016 at 11:21 am
It's very hard to bring data for all those tables.
October 3, 2016 at 11:23 am
I am having 120 recursion. and based on code, I don't know where to put.
October 3, 2016 at 2:52 pm
Hi,,
I don't think, we are able to create view with Option(MaxRecursion 1000) Option.
Now this is really challenging for me, because due to this, my SSIS package is failing .
Thanks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply