1
Vote

Improve Perf of GetMedia

description

please use the following version for better performance:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetMedia]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
    DROP PROCEDURE {databaseOwner}[{objectQualifier}GetMedia];
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetMedia] 
    @ModuleID INT 
AS 
BEGIN 
    SET NOCOUNT ON;
    SELECT  m.[ModuleID],
            m.[Alt],
            m.[Width],
            m.[Height], 
            {databaseOwner}[{objectQualifier}FilePath](m.[Src])         AS Src,
            {databaseOwner}[{objectQualifier}FilePath](m.[NavigateUrl]) AS NavigateUrl,
            t.[NewWindow],
            t.[TrackClicks],
            m.[MediaAlignment],
            m.[AutoStart],
            m.[MediaLoop],
            m.[NewWindow],
            m.[TrackClicks],
            m.[MediaType], 
            m.[MediaMessage], 
            m.[LastUpdatedBy], 
            m.[LastUpdatedDate] 
     FROM      {databaseOwner}[{objectQualifier}Media]       AS m
     LEFT JOIN {databaseOwner}[{objectQualifier}UrlTracking] AS t ON m.[NavigateUrl] = t.[Url] AND t.[ModuleId] = @ModuleID 
     WHERE  m.[ModuleId] = @ModuleId;
     SET NOCOUNT OFF;
END
GO

comments