Lazy Spool - What is causing it ?

• isuckatsql

  SSChampion

  Points: 10381

  More actions

  September 27, 2013 at 11:07 am

  #283157

  I removed the TOP, tried commenting out the WHERE, OR clauses, but cant seem to lose the Lazy Spool !

  Thanks for any help!

  USE [JobPortal9_10_13]

  GO

  /****** Object: StoredProcedure [dbo].[GetAllJobsSearchedDynamicQueryNew_Vikas_5_21_13] Script Date: 9/27/2013 11:56:57 AM ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  -- =============================================

  --

  -- Create date: <Create Date,5-7-13,>

  -- Description: <Description,[Get All Jobs Searched Structured SQL],>

  --

  -- =============================================

  ALTER Procedure [dbo].[GetAllJobsSearchedDynamicQueryNew_Vikas_5_21_13]

  -- Add the parameters for the stored procedure here

  @Title varchar(250),

  @CompanyID INT = NULL,

  @Industry int,

  @Industry2 int,

  @Industry3 int,

  @Date int,

  @JobTitle int,

  @JobType int,

  @Experience int,

  @education int,

  @State int,

  @City int,

  @Salary int,

  @MaxSalary int,

  @fromRec int,

  @toRec int,

  @SortType VARCHAR(50),

  @SortOrder VARCHAR(10)

  AS

  IF @CompanyID < 1

  SET @CompanyID = NULL

  DECLARE @ActualDate DateTime = cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20));

  DECLARE @C TABLE

  (

  ID int,

  CityID INT,

  StateID INT,

  Location VARCHAR(50)

  )

  DECLARE @HasLocation BIT = 0

  IF (@CITY IS NOT NULL AND @CITY > 0) OR (@State IS NOT NULL AND @State > 0)

  BEGIN

  SET @HasLocation = 1

  IF @City < 1 SELECT @City = NULL

  IF @State < 1 SELECT @State = NULL

  INSERT INTO @C

  SELECT ID, CityId, RegionId,

  ((SELECT TOP 1 NAME FROM Cities C WHERE C.ID = CityId) + ', ' +

  (SELECT TOP 1 NAME FROM Regions R WHERE R.ID = RegionID)) LOCATION

  FROM Companys

  WHERE (@City IS NULL OR CITYID = @City)

  AND (@State IS NULL OR REGIONID = @State);

  END;

  WITH CTE1 (RowID,id,CompanyID,title,contactperson,lastmodified,description,

  workexperience,jobtypeid,AcademicExperienceTypeId,workexperiencetypeid,

  industryid,industryid2,industryid3,salaryminid,salarymaxid,

  jobTitle,

  --city,state,

  --PostalCode,

  --name,

  positions,deadline)

  AS

  (SELECT

  ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowID,

  id, CompanyID, title, ContactPerson, LastModified, description,

  isnull((select we.[Name] from workexperiencetypes we where we.id=workexperiencetypeid),'') as workexperience,

  jobtypeid,AcademicExperienceTypeId,workexperiencetypeid,

  industryid,industryid2,industryid3,salaryminid,salarymaxid,

  isnull((select jt.[Name] from jobTitles jt where jt.id=jobtypeid),'') as jobTitle,

  positions,deadline

  FROM EmploymentOpportunities

  WHERE

  (@Title IS NULL or title = @Title)

  and (@Industry = 0 OR industryid = @Industry)

  and (@Industry2 = 0 OR Industryid2 = @Industry2)

  and (@Industry3 = 0 OR Industryid3 = @Industry3)

  and (@Date = 0 OR lastmodified >= @Date)

  and lastmodified is not null and lastmodified > @ActualDate

  AND (@CompanyID IS NULL OR COMPANYID = @CompanyID)

  and (@JobTitle = 0 OR title = @JobTitle)

  and (@JobType = 0 OR jobtypeid = @JobType)

  and (@Experience = 0 OR workexperiencetypeid = @Experience)

  and (@Education = 0 OR academicexperiencetypeid = @education)

  --and (@State = 0 OR c.RegionId = @State)

  --and (@City = 0 OR c.CityId = @City)

  and (@Salary = 0 OR SalaryMinID >= @Salary)

  and (@MaxSalary = 0 OR SalaryMaxID <= @MaxSalary)

  AND (@HasLocation = 0 OR COMPANYID IN (SELECT ID FROM @C))

  )

  SELECT *, (SELECT MAX(RowID) FROM CTE1) TotalCount,

  isnull((select we.[Name] from workexperiencetypes we where we.id=workexperiencetypeid),'') as workexperience,

  isnull((select jot.[Name] from jobtypes jot where jot.id=jobtypeid),'') as jobtype,

  isnull((select edu.[Name] from Degree edu where edu.Id=AcademicExperienceTypeId),'') as education,

  isnull((select ind.[Name] from industries ind where ind.id=industryid),'') as industryname,

  isnull((select ind.[Name] from industries ind where ind.id=industryid2),'') as industryname2,

  isnull((select ind.[Name] from industries ind where ind.id=industryid3),'') as industryname3,

  isnull((select jt.[Name] from jobTitles jt where jt.id=jobtypeid),'') as jobTitle,

  City + ', ' + [State] + ', ' + PostalCode AS Location

  FROM (SELECT

  ROW_NUMBER() OVER (ORDER BY --id) RowNumber,

  CASE WHEN @SortOrder = 'ASC' THEN

  CASE WHEN @SortType = 'LastModified' THEN CAST(LastModified AS VARCHAR(50))

  WHEN @SortType = 'Location' THEN City

  WHEN @SortType = 'Title' THEN Title

  END

  END ASC,

  CASE WHEN @SortOrder = 'DESC' THEN

  CASE WHEN @SortType = 'LastModified' THEN cast(LastModified AS VARCHAR(50))

  WHEN @SortType = 'Location' THEN City

  WHEN @SortType = 'Title' THEN Title

  END

  END DESC, id) RowNumber,

  *

  FROM (SELECT *,(SELECT TOP 1 NAME FROM Cities CT WHERE ID = (SELECT TOP 1 CO.CityId FROM COMPANYS CO WHERE CO.ID = CTE1.CompanyID)) City,

  (SELECT TOP 1 AbbreviatedName FROM Regions CT WHERE ID = (SELECT TOP 1 CO.RegionId FROM COMPANYS CO WHERE CO.ID = CTE1.CompanyID)) [State],

  (select top 1 PostalCode from companys co where co.id = cte1.CompanyID) PostalCode

  FROM CTE1) as p0)

  AS P1

  where RowNumber BETWEEN @fromRec AND @toRec OPTION(Maxdop 8)

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  September 27, 2013 at 5:11 pm

  #1654231

  Love your forum name 🙂

  That is one hell of a mess to be blunt!

  I would start by getting rid of all those subqueries, and replace them with joins or even outer applys where appropriate...

  You have two references to the CTE - which means it is "executing" more than once..

  I will spend a few minutes trying to rewrite the query another way, but without sample data and a desired result it will be tricky!

  After looking again, I would say Divide 'n' Conquer.

  You are trying to do too much in one big select - break it down into manageable sets and use temp tables instead of table variables, and add indexes to your temp tables to support the queries.

  It will be a lot easier to maintain that way as well.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]