TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
rizwana ahmed
NA
56
1.8k
Stored procedure taking too much time when fetching records
Aug 6 2016 2:20 AM
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??
Reply
Answers (
2
)
Sql server complete material
Error in Function