Stored procedure taking too much time while fetching data through sql

Question

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:

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

please post execution plan,To know how use this link :http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan — TheGameiswar, Aug 05 '16 at 11:17
Can you tell me is there anything changes need in my sp?? i don't know how to post schema or execution plan?? — Rizwana Ahmed, Aug 05 '16 at 11:21
Tag the dbms used. (That code doesn't look like ANSI SQL at all.) — jarlh, Aug 05 '16 at 11:29

Jofy Baby · Answer 1 · 2016-08-08T07:29:34.127

    ALTER 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)

  SELECT
    pa.*,
    ISNULL(t1.AdsImage, 'noimage.jpg') AS AdsImage,
    ct.Name AS CategoryName,
    ct.ShowReply AS ShowReply,
    ISNULL(mf.ID, 0) AS FeatureId,
    ISNULL(mf.Title, '') AS FeatureTitle,
    (SELECT
      ISNULL(dbo.ConcatDynamicFields(pa.Id), ''))
    AS DynamicFields,
    ISNULL(t2.IsLogin, 0) AS IsLoggedinUser,
    ISNULL((SELECT
      dbo.ConcatLocations1(pa.Id)), '') AS location, 
    cs.currency AS Currency,
    ct.IsPriceAvailable
  FROM PostedAds pa
  LEFT JOIN Category ct
    ON pa.CategoryID = ct.ID
  LEFT JOIN Country_State cs
    ON pa.CountryID = cs.ID
  LEFT JOIN (SELECT TOP 1 pai.AdsImage, pai.PostedAdsID
  FROM PostedAdsImages pai inner join PostedAds pa2  on
  pai.PostedAdsID = pa2.ID) t1
    ON t1.PostedAdsID = pa.ID
  LEFT JOIN MembershipSuscription ms
    ON pa.SubscriptionId = ms.ID
  LEFT JOIN dbo.MembershipPlan mp
    ON ms.MembershipPlanID = mp.ID
  LEFT JOIN MembershipFeature mf
    ON mp.FeatureID = mf.ID
  LEFT JOIN (SELECT TOP 1 ul.IsLogin, ul.UserId
  FROM UserLogin ul  inner join PostedAds pa2  on
  ul.UserId = pa2.UserId
  ORDER BY ul.ID DESC) t2
    ON t2.UserId = pa.UserId
  WHERE pa.Status = 1
  AND pa.CountryID = @countryId
  AND (pa.CityId = @cityId
  OR pa.VisiblityRestriction = 3)
  AND pa.AdType = @AdType
  AND
     CASE
       WHEN @ParentCatID > 0 AND
         pa.CategoryID = @CategoryId THEN 1
       WHEN @CategoryId > 0 AND
         ct.ParentID = @CategoryId THEN 1
       ELSE 0
     END = 1
  ORDER BY CASE
    WHEN ISNULL(mf.Title, '') = 'Premium Ads' THEN '1'
    WHEN ISNULL(mf.Title, '') = 'Urgent Ads' THEN '2'
    WHEN ISNULL(mf.Title, '') = '' THEN '3'
    ELSE ISNULL(mf.Title, '')
  END
  , pa.CreatedDate DESC

END

@jBaby....Gives Error....Msg 4104, Level 16, State 1, Procedure usp_PostedAds_GetAllByCountryCityCategoryIdAdType_new, Line 36 The multi-part identifier "pa.ID" could not be bound. Msg 4104, Level 16, State 1, Procedure usp_PostedAds_GetAllByCountryCityCategoryIdAdType_new, Line 48 The multi-part identifier "pa.UserId" could not be bound. — Rizwana Ahmed, Aug 05 '16 at 12:57
@RizwanaAhmed I have corrected the alter query, please check again — Jofy Baby, Aug 08 '16 at 07:30
To explain you what I have done: use joins instead of subqueries, joins will always run faster than subqueries. So basically I have removed all the subqueries that were created in the select and instead joined the respective tables. — Jofy Baby, Aug 08 '16 at 08:59

Stored procedure taking too much time while fetching data through sql

1 Answers1