locked
Why do the dynamically built subscription views use an inline function? RRS feed

  • Question

  • I am using Master Data Services for SQL Server 2012 SP2.  All the subscription views that are created use code similar to:

    ALTER VIEW [mdm].[MdsView]
    /*WITH ENCRYPTION*/
    AS SELECT 
    	T.ID AS ID
    	,T.MUID AS MUID 
    
    	/* More Columns */
    
    FROM mdm.[tbl_3_36_EN] AS T 
    INNER JOIN mdm.tblModelVersion AS V ON V.ID = T.Version_ID  
    	AND V.ID = [mdm].[udfModelVersionIDGetbyFlagID](3)
    LEFT JOIN mdm.tblModelVersionFlag AS DV ON DV.ID =  V.VersionFlag_ID
    WHERE T.Status_ID = 1
    	AND T.Version_ID = [mdm].[udfModelVersionIDGetbyFlagID](3);

    In that first inner join with mdm.tblModelVersion MDS subcription views are using a inline function mdm.udfModelVersionIDGetbyFlagID(3).  This seems to be redundant because the where clause filters the tblModelVersion table on the same call to mdm.udfModelVersionIDGetbyFlagID(3). 

    Regardless of the redundancy it would be great if this inline call was converted just another clause to filter the mdm.tblModelVersion on the version flag ID because when the query optimizer decides to join one of these views using a loop join it can take hours to execute for a view with thousands of rows as the inline function was being called twice for every row in the joined view.

    We ran into this problem when we had a stored procedure call two levels deep into a call stack join against multiple MDS views.

    We worked around the problem by specifying HASH or MERGE joins explicitly when joining MDS subscription views.


    Monday, March 2, 2015 7:41 PM

Answers

  • The fix will be included in SQL Server 2012 SP2 CU6, the release date is mid May.

    And then it will be ported in SQL Server 2014 CUs.

    Thursday, March 5, 2015 10:12 PM
  • Hi Michael,

    We completely agree with you and are working on a fix for it, in the SQL v.next. Once we have the fix tested, we will assess the risk and decide if we can port the fix for SQL2012 CUs.

    I will let you know when the decision is made.


    Monday, March 2, 2015 9:13 PM
  • Hi DeeBeeGee,

    This has made the way into sql 2012 Sp2 CU6 which was done by Vincent, just verified the history of this work item. may be there was no hotfix item created for this and hence you might not be seeing in the list .

    let me know if you have any questions

    thanks,

    santhosh

    Sunday, January 22, 2017 7:58 AM

All replies

  • Hi Michael,

    We completely agree with you and are working on a fix for it, in the SQL v.next. Once we have the fix tested, we will assess the risk and decide if we can port the fix for SQL2012 CUs.

    I will let you know when the decision is made.


    Monday, March 2, 2015 9:13 PM
  • The fix will be included in SQL Server 2012 SP2 CU6, the release date is mid May.

    And then it will be ported in SQL Server 2014 CUs.

    Thursday, March 5, 2015 10:12 PM
  • Did this fix make it in SQL 2012 SP2 CU6?

    I can't see it in any of the listed hotfixes

    https://support.microsoft.com/en-us/kb/3052468

    Monday, January 16, 2017 11:41 AM
  • Hi DeeBeeGee,

    This has made the way into sql 2012 Sp2 CU6 which was done by Vincent, just verified the history of this work item. may be there was no hotfix item created for this and hence you might not be seeing in the list .

    let me know if you have any questions

    thanks,

    santhosh

    Sunday, January 22, 2017 7:58 AM