Post reply

Function taking long time to execute

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply