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??