Hi,

Thank You for all the details. I will take your points and do the fixing as suggested.

[qry_GetUsers] is a view.

The query in the view is nothing but the below query...

How do I get rid of

EXEC sys.sp_addextendedproperty @name=N'GUID' from the table CREATE SCRIPT of the table

Here is the Data Definition for the 3 tables

--------------===============================================================

USE [jis_www_dev]

GO

/****** Object: Table [dbo].[aaaa] Script Date: 06/04/2012 15:31:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE...

Please check the attached -- Actual execution plan from Sql Server 2005.

Hi

Thank you

Here is the DDL of the functions

-------=========================================================

ALTER FUNCTION [dbo].[fn_getUserCategoryList](@userID int)

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @list nvarchar(64)

SELECT @list = category FROM t_UserPreferences WHERE userID = @userid

DECLARE @listStr VARCHAR(MAX)

SELECT@listStr =...

Thank You - I am able to test the stored proc using cursor, for some reason I am not able to execute to first approch but second approach helped me.

Can...

Hi,

Thank You for the suggestion of using AND instead of WHERE in the LEFT JOIN, I updated my sp with those changes and the stored proc execution time reduced from...

Can any one help me to optimize the above proc

Please check the attached - execution plans

Procedure

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[usp_xxxxxx]

(

@user_emailaddress nvarchar(50)

)

AS

BEGIN

DECLARE @count_UserPreference int, @count_Teaser int

DECLARE @categoryID nvarchar(50), @regionID nvarchar(50)

SELECT @count_UserPreference = Count(*)

FROM t_UserPreferences AS UP...

There are 4 steps in the Execution plan and I am adding them as attachment to this case

and there 5 tables used in this procedure,

Do you want me...

I am able to fig-out and fix it

not sure why but the issue is

The query is not executing if I write SET @sql =

but its working...