quote:

---

it's hard to compare when table scans show up in the execution plan

---

quote:

---

(sad to see since the table is FULLY indexed

---

FeedBack2ID INT (IDENTITY(0,1) PRIMARY KEY

DELETE A
FROM FeedBack2 A
INNER JOIN FeedBack2 B ON A.CourseID=B.CourseID AND A.UserID=B.UserID AND A.FormID=B.FormID AND A.CreateDate < B.CreateDate

quote:

---

This thought process leads to a formulation for the recordset to SAVE (not DELETE)

---

delete 	FeedBack2 
where Feedback2ID not in
(
	select Feedback2ID from FeedBack2 c 
	join 		(
				select 	
					distinct a.courseid,
					a.userid,
					a.formid,
					(select max(createdate) from FeedBack2 b where b.courseid=a.courseid and b.userid = a.userid and b.formid=a.formid) createdate
				from FeedBack2 a
			) d
	on c.courseid=d.courseid and c.userid = d.userid and c.formid=d.formid and c.createdate = d.createdate
) 

quote:

---

One option is to force an index...
You can add a primary key, such as:

FeedBack2ID INT (IDENTITY(0,1) PRIMARY KEY


spooling costs but scales better than the table scans

DELETE A
FROM FeedBack2 A
INNER JOIN FeedBack2 B ON A.CourseID=B.CourseID AND A.UserID=B.UserID AND A.FormID=B.FormID AND A.CreateDate < B.CreateDate

---

SELECT	CASE WHEN ChoiceID > 0 THEN Total ELSE NULL END AS Total ,
	CAST(CASE WHEN ChoiceID > 0 AND GrandTotal > 0 THEN CAST(100.0 * Total / GrandTotal AS NUMERIC(3,0)) ELSE NULL END AS VARCHAR) + '%' AS [Percent]  ,
	Question 
	FROM	(
		SELECT	FormID, 0 as ChoiceID,
			'<b>' + MAX(Question) + '</b>' AS Question, 
			COUNT(*) as Total , -- List of all the questions
			1 as GrandTotal
			FROM	dbo.Feedback2
			WHERE	CourseID = @CourseID
			AND	ChoiceID > 0 -- Select just the "Choice" Responses
			AND	CreateDate BETWEEN @StartDate AND @FinishDate
			GROUP BY FormID 
		UNION ALL
		SELECT	F.FormID, ChoiceID, ChoiceText, COUNT(*) AS Total , MAX(GrandTotal) AS GrandTotal -- FormID, Choice and Total for each choosable item
			FROM	dbo.Feedback2 F
			LEFT OUTER JOIN (SELECT FormID, COUNT(*) AS GrandTotal
						FROM dbo.Feedback2
						WHERE	CourseID=@CourseID
						AND	ChoiceID > 0
						AND	CreateDate BETWEEN @StartDate AND @FinishDate
						GROUP BY FormID) GT ON GT.FormID = F.FormID
			WHERE	CourseID = @CourseID
			AND	ChoiceID > 0 -- Select just the "Choice" Responses
			AND	CreateDate BETWEEN @StartDate AND @FinishDate
			GROUP BY F.FormID, ChoiceID, ChoiceText
	) X
	ORDER BY FormID ASC, ChoiceID ASC

CREATE TABLE Feedback2 (
	  UserID 	int 		NOT NULL
	, CourseID 	int 		NOT NULL
	, CreateDate 	datetime 	NOT NULL DEFAULT Getdate()
	, FormID 	int 		NOT NULL
	, Question 	varchar (1000)
	, ChoiceID 	int
	, ChoiceText 	varchar (2000)
)
GO

 CREATE  INDEX IDX_Feedback1 ON dbo.Feedback2(UserID)
 CREATE  INDEX IDX_Feedback2 ON dbo.Feedback2(CourseID)
 CREATE  INDEX IDX_Feedback3 ON dbo.Feedback2(FormID)
 CREATE  INDEX IDX_Feedback4 ON dbo.Feedback2(ChoiceID)
 CREATE  INDEX IDX_Feedback5 ON dbo.Feedback2(CreateDate)
 CREATE  INDEX IDX_Feedback6 ON dbo.Feedback2(UserID, CourseID, FormID, CreateDate)
GO

CREATE TABLE Feedback3 (
	  UserID 	int 		NOT NULL
	, CourseID 	int 		NOT NULL
	, CreateDate 	datetime 	NOT NULL DEFAULT Getdate()
	, FormID 	int 		NOT NULL
	, Question 	varchar (1000)
	, ChoiceID 	int
	, ChoiceText 	varchar (2000)
)
GO

 CREATE  INDEX IDX_Feedback1 ON dbo.Feedback3(UserID)
 CREATE  INDEX IDX_Feedback2 ON dbo.Feedback3(CourseID)
 CREATE  INDEX IDX_Feedback3 ON dbo.Feedback3(FormID)
 CREATE  INDEX IDX_Feedback4 ON dbo.Feedback3(ChoiceID)
 CREATE  INDEX IDX_Feedback5 ON dbo.Feedback3(CreateDate)
 CREATE  INDEX IDX_Feedback6 ON dbo.Feedback3(UserID, CourseID, FormID, CreateDate)
GO

--SET SHOWPLAN_TEXT OFF
--SET SHOWPLAN_ALL OFF
--GO

SET NOCOUNT ON

DECLARE @x int, @y int, @z int

SELECT @x = 1, @y = 1, @z = 1

WHILE @x < 100
	BEGIN
		WHILE @y < 5
			BEGIN
				WHILE @z < 2
					BEGIN
						INSERT INTO Feedback2 (UserId, CourseId, FormId)
						SELECT @x, @y, @z
						SELECT @z = @z + 1
					END
					SELECT @z = 1, @y = @y + 1
			END
			SELECT @y = 1, @x = @x + 1
	END

    SELECT COUNT(*) FROM Feedback2

SELECT @x = 1, @y = 1, @z = 1

WHILE @x < 200
	BEGIN
		WHILE @y < 100
			BEGIN
				WHILE @z < 10
					BEGIN
						INSERT INTO Feedback3 (UserId, CourseId, FormId)
						SELECT @x, @y, @z
						SELECT @z = @z + 1
					END
					SELECT @z = 1, @y = @y + 1
			END
			SELECT @y = 1, @x = @x + 1
	END

    SELECT COUNT(*) FROM Feedback3
GO


    DELETE A
      FROM dbo.Feedback3 A
INNER JOIN (  SELECT CourseID, UserID, FormID, MAX(CreateDate) as CreateDate
		FROM dbo.Feedback3
	    GROUP BY CourseID, UserID, FormID
	    ) B 
	ON B.CourseID   = A.CourseID 
       AND B.UserID     = A.UserID 
       AND B.FormID     = A.FormID 
       AND B.CreateDate > A.CreateDate

SELECT 'Rows Deleted: ' + CONVERT(varchar(15),@@ROWCOUNT)


    DELETE A
      FROM dbo.Feedback2 A
INNER JOIN (  SELECT CourseID, UserID, FormID, MAX(CreateDate) as CreateDate
		FROM dbo.Feedback2
	    GROUP BY CourseID, UserID, FormID
	    ) B 
	ON B.CourseID   = A.CourseID 
       AND B.UserID     = A.UserID 
       AND B.FormID     = A.FormID 
       AND B.CreateDate > A.CreateDate


SELECT 'Rows Deleted: ' + CONVERT(varchar(15),@@ROWCOUNT)
GO

SET NOCOUNT OFF
DROP TABLE FeedBack2
DROP TABLE FeedBack3
GO

DELETE	A
	FROM	dbo.Feedback2 A
	INNER JOIN dbo.Feedback2 B ON A.CourseID=B.CourseID AND A.UserID=B.UserID AND A.FormID=B.FormID AND A.CreateDate < B.CreateDate

declare @T1 DATETIME
SET @t1 = GETDATE()

DELETE A
      FROM dbo.Feedback3 A
INNER JOIN (  SELECT CourseID, UserID, FormID, MAX(CreateDate) as CreateDate
		FROM dbo.Feedback3
	    GROUP BY CourseID, UserID, FormID
	    ) B 
	ON B.CourseID   = A.CourseID 
       AND B.UserID     = A.UserID 
       AND B.FormID     = A.FormID 
       AND B.CreateDate > A.CreateDate

Print DATEDIFF(MS, @t1, getdate())

SET @t1 = GETDATE()
DELETE	A
	FROM	dbo.Feedback2 A
	INNER JOIN dbo.Feedback2 B ON A.CourseID=B.CourseID AND A.UserID=B.UserID AND A.FormID=B.FormID AND A.CreateDate < B.CreateDate

Print DATEDIFF(MS, @t1, getdate())