title: Stored procedure taking too much time while fetching data through sql
body: <p>I want to fast my stored procedure.</p>
  
  <p>While fetching the records the stored procedure taking too much time.</p>
  
  <p>i.e. it will taking too much time (more then 1 min)..
  So how to fast Stored procedure???</p>
  
  <p>Here is my Stored procedure:</p>
  
  <pre><code>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&gt;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&gt;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
  </code></pre>
  
  <p>This is my Table Schema:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>How to fast this sp??</p>
  
  <p>Is there any solutions??</p>