UPDATE ... WHERE CURRENT OF curX causes "Clustered index Scan"?!?

[JendaPerl]

I was checking the exported estimated execution plans for all stored procs in a database, looking for table or index scans (to find missing indexes or badly written queries) and noticed that statements like

UPDATE dbo.JobSites
   SET MediaCost = @ConvertedCost,
	ComplexExplanation = @Explanation
 WHERE CURRENT OF curJobSites;

are reported as "Clustered Index Update" + "Clustered Index Scan". With insane Estimated Subtree Cost.

It doesn't look like the procedure in question is actually that slow, but it does worry me a bit.

Is this the expected estimated query plan for UPDATE...WHERE CURRENT OF... or am I doing something wrong?

The server is Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Thanks, Jenda

[rmiao]

CURRENT OF clause uses cursor, maybe that's why.

[Hanslindgren]

Hi,

If none of those columns are in the first column(s) in your Clustered Index you have to scan all of it. It cannot do a seek. Is this the case?

Regards
Hanslindgren

[JendaPerl]

If none of those columns are in the first column(s) in your Clustered Index you have to scan all of it. It cannot do a seek. Is this the case?

Regards
Hanslindgren

None of what columns? The [MediaCost] and [ComplexExplanation]? What do they have to do with it? I'm updating them, not using them to find the row to update. The old values are not specified in the query besides they are far from unique.

Or do you mean none of the columns included in the cursor? One of the two columns of the clustered PK of that table was missing so I tried to add it ... no difference in the estimated query plan.
I also tried to simplify the query defining the cursor to include just that single table ... no difference.

Declare curJobSites CURSOR LOCAL FORWARD_ONLY
FOR SELECT JS.JdtId, JS.SiteID, PackageID, OriginalMediaCost, OriginalCurrency, Currency, JobCommission, getdate() as JobPostingDate
	  FROM dbo.JobSites as JS
--	  JOIN dbo.Jobs as J with (nolock) ON J.JdtId = JS.JdtId
	 WHERE JS.JdtId = @JdtId
FOR UPDATE OF MembershipPoints, MembershipPrice, MediaCost, ComplexExplanation, [ExpireDate];

...

	UPDATE dbo.JobSites
	   SET MediaCost = @ConvertedCost,
		ComplexExplanation = ''
	 WHERE CURRENT OF curJobSites;

Jenda