I have given up on trying to make a 'usable' stored procedure for this application.

What I will do is to embed the SQL code into the call from the .Net...

Within the stored proc I tried building the SQL command in a VARCHAR(MAX), @sCmd, variable and then, EXEC (@sCmd) - per Ten C suggestion; strangely enough it was slow.

If I...

The VARCHAR(MAX) field size is very close to 2Gb - 20k should not be a problem - a quick test would tell you, no?

The RecID is not part of the index, BUT neither are at least 2 of the other fields in the column list....

but those 2 not in the index are actually...

You could use:

LTRIM(CAST([textfield] AS VARCHAR(MAX)))

if the txt field isn't too big..... 🙂

My bad - I attached the plans to the message now.

After further review...

It seems that a small change to the TSQL select statement will make the query much slower. Just adding one more (int) field to the output column list...

When I add the local variables instead of using constants, then the TSQL is bad too! ...interesting.

Captain Scarlett:

ReadingID is a BIGINT - same as param (when used)

Thanks

3 indexes on table unit_readings:

non-clustered; unitID, [Port Number], [Reading Time]

UNIQUE non-clustered; ReadingID

UNIQUE clustered; UDay, UInterval, unitID, [Port Number]

Table is partitioned on UDay.

Attached are the 2 XML Execution plans.

Thank you for...

collie,

This might help:

CREATE TABLE #portalusers

(

IDBIGINT IDENTITY(1,1),

CPU_idINT

)

.....

DECLARE @IDBIGINT

DECLARE @MaxCountINT

DECLARE @countfilterINT

DECLARE @iINT

SET@ID = 0

SELECT@ID = MIN(ID) FROM #portalusers

WHILE ISNULL(@ID, 0) > 0

BEGIN

SELECT@CPU_id = CPU_id

FROM#portalusers

WHEREID = @ID

GO

EXEC sp_updateUsers @CPU_id

GO

SELECT@ID = MIN(ID)

FROM#portalusers

WHEREID...