Function taking long time to execute

• Hammad khan

  Mr or Mrs. 500

  Points: 552

  More actions

  May 11, 2007 at 11:54 am

  #124048

  Hi i have the function which is when executed take for ever to execute.I did some debugging and find out thie is part of the sql is taking for ever.

   

  INSERT #DuplicateListings

   SELECT

    Listing_RID

   FROM dbo.rap_fn_GetDuplicateListings(42206)

   DECLARE @Metalisting_Start_Date DATETIME,@Listing_RID int

          set @Listing_RID=42206

   SELECT

    @Metalisting_Start_Date = MAX(Start_Date)

   FROM (SELECT

     Start_Date

     FROM View_Property_Listing_Ranges D1

     WHERE Start_Date >= coalesce((

      SELECT

       MAX(dateadd(dd, M.CDOM_Days + 1, End_Date))

      FROM dbo.View_Property_Listing_Ranges D2

       INNER JOIN dbo.MLS_Master_2 M ON

        D2.MLS = M.MLS

      WHERE D2.Listing_RID IN (SELECT Listing_RID FROM #DuplicateListings) AND

       D2.Status IN ('I','E') AND

       D2.End_Date <= D1.Start_Date AND

       D2.Listing_RID <> D1.Listing_RID), '1800-01-01') AND

     Listing_RID IN (SELECT Listing_RID FROM #DuplicateListings)) AS SD

   WHERE Start_Date <= (SELECT Start_Date FROM dbo.View_Property_Listing_Ranges WHERE Listing_RID = @Listing_RID.

  any suggestions how to rewrote the above query.

  Thanks in advance.

   

  Hammad khan

• Jan-155192

  SSCrazy

  Points: 2613

  More actions

  May 15, 2007 at 6:10 am

  #706449

  Hammad,

  You should look at the data model, the query plan and the indexes and data distribution, and dbo.rap_fn_GetDuplicateListings. Just like this, this is a difficult problem for us to understand.

  Jan