locked
Please help - query that execute for 1 sec in SQL 2000, takes 12 sec in 2005 RRS feed

  • Question

  • Please help me figure out why this query executes 10 times slower in SQL 2005 than in SQL 2000 server. I recently migrated to 2005 and have difficulties with some of the queries.

    Elena


    SELECT Friends.FriendID, BlockedUsers.BlockUserID, SQRT( POWER((z2.Lat69 - z1.Lat69) ,2) + POWER( (z2.Long69 - z1.Long69) * z1.coslat, 2)) as Distance, ContestData.EnterDate as ContestEnterDate,ContestData.ID AS ContestDataID, ContestData.UserID, contestData.Title, ContestData.Picture,ContestData.VideoExt, ContestData.ImageNum, Users.ID, Users.Mobile, Users.CamOnTimeStart, Users.Type, Users.Mv_AgeID, Users.Mv_Weight, Users.Mv_HeightFTID, Users.Mv_HeightINID, Users.Mv_SexualOrientationID, Users.Fv_AgeID, Users.Fv_Weight, Users.Fv_HeightFTID, Users.Fv_HeightINID, Users.Fv_SexualOrientationID, Users.Tv_AgeID, Users.Tv_Weight, Users.Tv_HeightFTID, Users.Tv_HeightINID, Users.Tv_SexualOrientationID, Users.UserName, Users.City, Users.State, Users.zip, Users.Country, Users.v_DescribeID, Users.ImageSmall,Users.ImageLarge, Users.TurnIM, Users.PartyPlannerID, SUM(cr1.Rate)/cast(COUNT(cr1.Rate)as float) as score,max(cr2.Rate) as rate FROM Users INNER JOIN ContestData ON Users.ID = ContestData.UserID AND ContestData.Approved = 1 AND ContestData.ContestID = 76 INNER JOIN ZIPCodes z1 ON z1.ZIPCode = '20009' AND z1.CityType = 'D' INNER JOIN ZIPCodes z2 ON z2.City = Users.City AND z2.StateCode = Users.state AND z2.ZIPCode = Users.ZIP LEFT JOIN Friends ON Users.ID = Friends.FriendID AND Friends.UserID = 231 LEFT JOIN BlockedUsers ON Users.ID = BlockedUsers.BlockUserID AND BlockedUsers.UserID = 231 LEFT JOIN Contest_Rate as cr1 ON ContestData.ID = cr1.ContestDataID AND ContestData.ContestID = 76 LEFT JOIN Contest_Rate as cr2 ON ContestData.ID = cr2.ContestDataID AND cr2.UserID = 231 AND ContestData.ContestID = 76 WHERE status = 1 AND ((Users.ID = 231) OR (Users.ID NOT IN (SELECT UserId FROM BlockedUsers WHERE BlockUserID = 231) AND Users.ID NOT IN (SELECT BlockUserID FROM BlockedUsers WHERE UserID = 231) AND 1 NOT IN (SELECT BlockedTypes.UserTypeID FROM BlockedTypes WHERE BlockedTypes.UserID = Users.ID) AND Users.type NOT IN (SELECT BlockedTypes.UserTypeID FROM BlockedTypes WHERE BlockedTypes.UserID = 231) )) GROUP BY Friends.FriendID, BlockedUsers.BlockUserID, SQRT(POWER(z2.LAT69 - z1.LAT69, 2) + POWER((z2.LONG69 - z1.LONG69) * z1.COSLAT, 2)), ContestData.EnterDate , ContestData.ID, ContestData.UserID, ContestData.Title, ContestData.Picture, ContestData.VideoExt, ContestData.ImageNum, Users.ID, Users.Mobile, Users.CamOnTimeStart, Users.Type, Users.Mv_AgeID, Users.Mv_Weight, Users.Mv_HeightFTID, Users.Mv_HeightINID, Users.Mv_SexualOrientationID,Users.Fv_AgeID, Users.Fv_Weight, Users.Fv_HeightFTID, Users.Fv_HeightINID, Users.Fv_SexualOrientationID, Users.Tv_AgeID, Users.Tv_Weight, Users.Tv_HeightFTID, Users.Tv_HeightINID, Users.Tv_SexualOrientationID, Users.UserName, Users.City, Users.State, Users.zip, Users.Country, Users.v_DescribeID, Users.ImageSmall, Users.ImageLarge, Users.TurnIM, Users.PartyPlannerID ORDER BY score DESC
    Sunday, December 12, 2010 4:41 PM

Answers