Stored procedure taking too much time when fetching records

I want to fast my stored procedure.

While fetching the records the stored procedure taking too much time.

i.e. it will taking too much time (more then 1 min)..

So how to fast Stored procedure???

Here is my Stored procedure:

 

Create procedure [dbo].[usp_PostedAds_GetAllByCountryCityCategoryIdAdType](@countryId int,@cityId int, @CategoryId int,@AdType int) As

begin

DECLARE @ParentCatID int

set @ParentCatID =isnull( (SELECT ParentID FROM Category WHERE ID = @CategoryId),0 )

if(@ParentCatID>0)

begin

select PostedAds.*,isnull((select top 1 PostedAdsImages.AdsImage from PostedAdsImages where PostedAdsImages.PostedAdsID=PostedAds.ID),'noimage.jpg') as AdsImage

,(select Name from dbo.Category where id=PostedAds.CategoryID ) as CategoryName,(select ShowReply from dbo.Category where id=PostedAds.CategoryID ) as ShowReply,

isnull((select mf.ID from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),0) as FeatureId,

isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'') as FeatureTitle

,(select isnull(dbo.ConcatDynamicFields(PostedAds.Id),'')) as DynamicFields,

isnull((select top 1 ul.IsLogin from UserLogin ul where ul.UserId=PostedAds.UserId order by ul.ID desc),0) as IsLoggedinUser

,isnull((select dbo.ConcatLocations1(postedAds.Id)),'') as location,

(select Country_State.currency from Country_State where Country_State.ID=PostedAds.CountryID) as Currency

,c.IsPriceAvailable

from PostedAds join Category c on c.ID=PostedAds.CategoryID

where PostedAds.Status=1 and PostedAds.CountryID=@countryId and

(PostedAds.CityId=@cityId or PostedAds.VisiblityRestriction=3)and PostedAds.CategoryID=@CategoryId and PostedAds.AdType=@AdType

ORDER BY CASE

WHEN isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'') = 'Premium Ads' THEN '1'

WHEN isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'') = 'Urgent Ads' THEN '2'

WHEN isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'') = '' THEN '3'

ELSE isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'')

END

,PostedAds.CreatedDate desc

end

else

begin

if(@CategoryId>0)

begin

select PostedAds.*,isnull((select top 1 PostedAdsImages.AdsImage from PostedAdsImages where PostedAdsImages.PostedAdsID=PostedAds.ID),'noimage.jpg') as AdsImage

,(select Name from dbo.Category where id=PostedAds.CategoryID ) as CategoryName,(select ShowReply from dbo.Category where id=PostedAds.CategoryID ) as ShowReply,

isnull((select mf.ID from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),0) as FeatureId,

isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'') as FeatureTitle

,(select isnull(dbo.ConcatDynamicFields(PostedAds.Id),'')) as DynamicFields,

isnull((select top 1 ul.IsLogin from UserLogin ul where ul.UserId=PostedAds.UserId order by ul.ID desc),0) as IsLoggedinUser

,isnull((select dbo.ConcatLocations1(postedAds.Id)),'') as location,

(select Country_State.currency from Country_State where Country_State.ID=PostedAds.CountryID) as Currency

,c.IsPriceAvailable

from PostedAds join Category c on c.ID=PostedAds.CategoryID

where PostedAds.Status=1 and PostedAds.CountryID=@countryId and

(PostedAds.CityId=@cityId or PostedAds.VisiblityRestriction=3)

and c.ParentID=@CategoryId

and PostedAds.AdType=@AdType

ORDER BY CASE

WHEN isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'') = 'Premium Ads' THEN '1'

WHEN isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'') = 'Urgent Ads' THEN '2'

WHEN isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'') = '' THEN '3'

ELSE isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'')

END

,PostedAds.CreatedDate desc

End

else

Begin

select PostedAds.*,isnull((select top 1 PostedAdsImages.AdsImage from PostedAdsImages where PostedAdsImages.PostedAdsID=PostedAds.ID),'noimage.jpg') as AdsImage

,(select Name from dbo.Category where id=PostedAds.CategoryID ) as CategoryName,(select ShowReply from dbo.Category where id=PostedAds.CategoryID ) as ShowReply,

isnull((select mf.ID from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),0) as FeatureId,

isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'') as FeatureTitle

,(select isnull(dbo.ConcatDynamicFields(PostedAds.Id),'')) as DynamicFields,

isnull((select top 1 ul.IsLogin from UserLogin ul where ul.UserId=PostedAds.UserId order by ul.ID desc),0) as IsLoggedinUser

,isnull((select dbo.ConcatLocations1(postedAds.Id)),'') as location,

(select Country_State.currency from Country_State where Country_State.ID=PostedAds.CountryID) as Currency

,c.IsPriceAvailable

from PostedAds join Category c on c.ID=PostedAds.CategoryID

where PostedAds.Status=1 and PostedAds.CountryID=@countryId and

(PostedAds.CityId=@cityId or PostedAds.VisiblityRestriction=3)

--and c.ID=@CategoryId

and PostedAds.AdType=@AdType

ORDER BY CASE

WHEN isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'') = 'Premium Ads' THEN '1'

WHEN isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'') = 'Urgent Ads' THEN '2'

WHEN isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'') = '' THEN '3'

ELSE isnull((select mf.Title from MembershipSuscription ms left join dbo.MembershipPlan mp on mp.ID =ms.MembershipPlanID join MembershipFeature mf on mf.ID =mp.FeatureID where ms.ID=PostedAds.SubscriptionId),'')

END

,PostedAds.CreatedDate desc

End

end

end

 

This is my Table Schema:

 

CREATE TABLE [dbo].[Category](

[ID] [int] IDENTITY(1,1) NOT NULL,

[ParentID] [int] NOT NULL,

[Name] [varchar](max) NULL,

[CategoryImage] [varchar](max) NULL,

[Active] [bit] NOT NULL CONSTRAINT [DF_Category_Active] DEFAULT ((1)),

[Offer_Label] [varchar](100) NULL,

[Wanted_Label] [varchar](100) NULL,

[seo_keywords] [varchar](255) NULL,

[seo_description] [varchar](255) NULL,

[IsProduct] [bit] NULL,

[Order] [int] NULL DEFAULT ((0)),

[IsPriceAvailable] [bit] NOT NULL DEFAULT ((1)),

[ShowReply] [bit] NOT NULL DEFAULT ((1)),

[AllowImages] [bit] NOT NULL DEFAULT ((1)),

CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Table [dbo].[Country_State] Script Date: 8/5/2016 5:14:36 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Country_State](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](200) NULL,

[ParentID] [int] NULL,

[HasState] [bit] NULL,

[Currency] [varchar](50) NULL,

[IsActive] [bit] NOT NULL CONSTRAINT [DF_Country_State_IsActive] DEFAULT ((1)),

[IsTopCountry] [bit] NULL CONSTRAINT [DF_Country_State_IsTopCountry] DEFAULT ((0)),

CONSTRAINT [PK_Country_State] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Table [dbo].[MembershipFeature] Script Date: 8/5/2016 5:14:36 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[MembershipFeature](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Title] [nvarchar](255) NULL,

[Type] [nvarchar](255) NULL,

[Status] [bit] NULL,

CONSTRAINT [PK_MembershipFeature] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[MembershipPlan] Script Date: 8/5/2016 5:14:36 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[MembershipPlan](

[Id] [int] IDENTITY(1,1) NOT NULL,

[FeatureID] [int] NULL,

[NumberOfAds] [int] NULL,

[AdValidity] [int] NULL,

[PlanValidity] [int] NULL CONSTRAINT [DF_MembershipPlan_PlanValidity] DEFAULT ((0)),

[Price] [decimal](18, 2) NULL,

[Discount] [decimal](18, 2) NOT NULL,

CONSTRAINT [PK_Plan_1] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[MembershipSuscription] Script Date: 8/5/2016 5:14:36 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[MembershipSuscription](

[ID] [int] IDENTITY(1,1) NOT NULL,

[UserID] [bigint] NOT NULL,

[MembershipPlanID] [int] NOT NULL,

[StartDate] [datetime] NULL CONSTRAINT [DF_MembershipSuscription_StartDate] DEFAULT (getdate()),

[EndDate] [datetime] NULL CONSTRAINT [DF_MembershipSuscription_EndDate] DEFAULT (getdate()),

[AmountPaid] [decimal](18, 2) NULL,

[ServiceTax] [decimal](18, 2) NULL,

[Status] [bit] NULL,

[CreateDate] [datetime] NULL CONSTRAINT [DF_MembershipSuscription_CreateDate] DEFAULT (getdate()),

[AdRemaning] [int] NULL,

CONSTRAINT [PK_MerchantMembershipSuscription] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[PostedAds] Script Date: 8/5/2016 5:14:36 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[PostedAds](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[UserId] [bigint] NOT NULL,

[CategoryID] [int] NOT NULL,

[Price] [decimal](18, 2) NULL,

[Title] [nvarchar](max) NULL,

[Description] [nvarchar](max) NULL,

[MAPAddress] [nvarchar](max) NULL,

[VisiblityRestriction] [int] NULL,

[CountryID] [int] NOT NULL,

[CityId] [int] NOT NULL,

[Locality] [nvarchar](max) NULL,

[Status] [int] NOT NULL CONSTRAINT [DF_PostedAds_Status] DEFAULT ((0)),

[Seo_keyword] [nvarchar](555) NULL,

[Seo_description] [nvarchar](555) NULL,

[AdType] [int] NULL,

[PromotionType] [int] NOT NULL CONSTRAINT [DF_PostedAds_PromotionType] DEFAULT ((0)),

[SubscriptionId] [int] NULL CONSTRAINT [DF_PostedAds_SubscriptionId] DEFAULT ((0)),

[CreatedDate] [datetime] NULL CONSTRAINT [DF_PostedAds_CreatedDate] DEFAULT (getdate()),

CONSTRAINT [PK_PostedAds] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/****** Object: Table [dbo].[PostedAdsImages] Script Date: 8/5/2016 5:14:36 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[PostedAdsImages](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[PostedAdsID] [bigint] NOT NULL,

[AdsImage] [nvarchar](max) NULL,

[ImageTitle] [nvarchar](500) NULL,

[CreatedDate] [datetime] NULL CONSTRAINT [DF_PostedAdsImages_CreatedDate] DEFAULT (getdate()),

CONSTRAINT [PK_PostedAdsImages] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/****** Object: Table [dbo].[UserLogin] Script Date: 8/5/2016 5:14:36 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[UserLogin](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[UserId] [bigint] NULL,

[LoginDate] [date] NULL CONSTRAINT [DF_UserLogin_LoginDate] DEFAULT (getdate()),

[LoginTime] [varchar](50) NULL,

[LogOutTime] [varchar](50) NULL,

[IpAddress] [varchar](50) NULL,

[IsLogin] [bit] NULL,

CONSTRAINT [PK_UserLogin] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

How to fast this sp??

Is there any solutions??