0

Trying this: 

update _PECO_Detail set RunId = 37

I get:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout

I've seen posts about this that say that this is a network issue, but other simple queries work fine. Only this one. It is trying to update 1.2 million rows (approx.)

I don't care if it takes 10 hours to run (although I would like it to be faster), but I don't want it to time out.

The table has been properly indexed, has a pk, and there is an index on RunId field as well.

It is an Azure managed instance. It has been set up in a minimalistic cost fashion. I am not too familiar with Azure database costing.

Kam Mistry
  • 105
  • 8
  • 1
    Side note: "The table has been properly indexed, has a pk, and there is an index on RunId field as well." -- In absence of a `WHERE` (or `ON` or maybe `HAVING`) clause an index won't make anything faster here. It isn't used to get the required rows, they're all of the table anyways. On the contrary an index on the column that is to be updated rather slows things down as it also has to be updated. – sticky bit Mar 18 '20 at 21:49
  • *"A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 "* normally means that the connection was cut, rather than the application timed out. Where are you running this query? – Thom A Mar 18 '20 at 22:26
  • Feedback based on comments 1) I am running on Sql server management studio against a database table 2) I agree that indexes, in this case, don't make things any faster, but they have to be there for other types of queries against the db. – Kam Mistry Mar 18 '20 at 23:39
  • Lamu, what you seem to be saying is that the connections cuts from time to time and maybe a long-running command or query may not necessarily be the cause, but because it cuts from time to time, it will fail..... Not sure, though because I have ran long queries on this db, and continue to do so, successfully (queries that take 1+ hour to run). I think that the Update is resource intensive, but not a single row updates. Select count(*) where RunId <> 37 shows that the number never goes down... – Kam Mistry Mar 18 '20 at 23:44

1 Answers1

0

I have been running this for a long time now. It hasn't crashed yet. I think this is the answer:

Declare @RowsEffected INT = 999;
WHILE (@RowsEffected > 0)   
BEGIN
        UPDATE TOP (100000) _PECO_Detail set RunId = 37
        WHERE RunId <> 37

        SET @RowsEffected = @@ROWCOUNT
END
Kam Mistry
  • 105
  • 8
  • it is working, i got the same error but 600K rows updated. i will run it again, and it will probably finish. select count(*) from _peco_detail where runid <> 37 – Kam Mistry Mar 19 '20 at 01:19