Updating large recordsets takes long time in ms access database advance dating site for
MSSQLSERVER\MSSQL\DATA\SUCCESS Offset: 6,225,805,312, Length: 16,384, I/O Flags: Non-cached, Write Through, Priority: Normal .0884897 1760 Write File C:\Program Files\Microsoft SQL Server\MSSQL10_50. LDF SUCCESS Offset: 4,589,289,472, Length: 8,388,608, I/O Flags: Non-cached, Write Through, Priority: Normal From using DBCC PAGE it seems to be reading from and writing to fields that look like table A's (or one of its indexes), but for different B_ID that 13. Edited 2: execution plan So I cancelled the query (actually deleted the DB and its files then restored it), and checked the execution plan for: The (estimated) execution plan is the same than for any B. The WHERE clause uses an index seek on a non-clustered index of B, the JOIN uses a clustered index seek on both PKs of the tables.The clustered index seek on A uses parallelism (x7) and represents 90% of the CPU time. X field), each one with its own clustered PK, and an other index which also includes the A. X means writing the 7 views and the 7 indexes they have that include the field.The rest looks the same: same UPDATE line waiting in sp_who2, same PAGEIOLATCH_EX wait type and same heavy HD usage from Next step is to delete all indexes and views and recreate them I think.B has 40k records, A has 4M records and they are related 1-to-n via A. So basically I am pre-calculating a field for data mining purposes. ID = @Id FETCH NEXT FROM Cursor B INTO @Id END Now I can see it running with a message with the id descending.Although I changed the name of the tables for this question, I didn't change the statement, it's really that simple. What happens is it takes around 5min to go from id=40k to id = 13 And then at id 13, for some reason, it seems to hang.Edited 4: deleting then rebuilding indexes So, I deleted all the indexed views I had on the table (7 of them, 2 indexes per view including the clustered one).I ran the initial script (without cursor), and it actually ran in 5 minutes.
Since disconnected recordsets use batch updates, this bug caused my client application to crash whenever it tried to update a record. The access times are significantly reduced, and I suspect the decrease will be even larger when the recordset contains more fields.The update scenario is always faster than using a procedure.Since you are updating column X of all rows in table A, make sure you drop the index on that one first.More importantly, actually executing the query with ID 13 is immediate. X field The views are filtered (with fields that are not in this equation), so I doubt there is any way the UPDATE A would the views themselves. Although the UPDATE is expected to be slower for this, there is no reason why a specific ID would be so much longer than the others.Edited 3: index fragmentation The structure of indexes is as follows: B has one clustered PK (not the ID field), and one non-clustered unique index, which first field is B. I checked the fragmentation for all the indexes, all were at loop.
In situations like this SQL likes to hold onto the memory and not let it go...after said insert command may or may not have completed.