Update query never finishes once table becomes large

Question

This update statement runs just fine when the table has a few hundred or even a few thousand rows, but once it reaches 100,000 rows in the subfields table it never finishes running.

UPDATE 
Records
SET Author = (SELECT Data
          FROM Subfields
          LEFT OUTER JOIN Fields f on f.FieldID = Subfields.FieldID
          WHERE f.RecordID = Records.RecordID AND f.TagNumber = '100' and Code = 'a')

This is the execution plan. It's definitely using indexes, so I'm not sure how I could possibly make it faster.

selectid    order   from    detail
0   0   0   SCAN TABLE Records
0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 0
0   0   0   SEARCH TABLE Subfields USING INDEX Code_FieldID (Code=?)
0   1   1   SEARCH TABLE Fields AS f USING INTEGER PRIMARY KEY (rowid=?)

I'd greatly appreciate it if someone could help me solve my speed issues. Thanks in advance!

CREATE TABLE [Fields](
    [FieldID] integer PRIMARY KEY ASC AUTOINCREMENT NOT NULL, 
    [RecordID] nvarchar(2147483647) NOT NULL, 
    [TagNumber] nvarchar(2147483647) NOT NULL, 
    [Ind1] char, 
    [Ind2] char, 
    [ControlData] nvarchar(2147483647), 
    FOREIGN KEY([RecordID]) REFERENCES Records([RecordID]) ON DELETE CASCADE ON UPDATE RESTRICT);

CREATE TABLE [Records](
    [RecordID] integer PRIMARY KEY ASC AUTOINCREMENT NOT NULL, 
    [DateAdded] datetime NOT NULL, 
    [DateChanged] datetime, 
    [Author] nvarchar(2147483647), 
    [Title] nvarchar(2147483647), 
    [CopyrightDate] integer, 
    [Barcode] nvarchar(2147483647), 
    [Classification] nvarchar(2147483647), 
    [MainEntry] nvarchar(2147483647), 
    [Custom1] nvarchar(2147483647), 
    [Custom2] nvarchar(2147483647), 
    [Custom3] nvarchar(2147483647), 
    [Custom4] nvarchar(2147483647), 
    [Custom5] nvarchar(2147483647), 
    [ImportErrors] nvarchar(2147483647));

CREATE TABLE [Settings](
    [RecordListAtTop] bool, 
    [ClearDatabaseOnExit] bool, 
    [ExportFormat] char(1), 
    [CustomTag1] nvarchar(3), 
    [CustomCode1] nvarchar(1), 
    [CustomData1] nvarchar(2147483647), 
    [CustomTag2] nvarchar(3), 
    [CustomCode2] nvarchar(1), 
    [CustomData2] nvarchar(2147483647), 
    [CustomTag3] nvarchar(3), 
    [CustomCode3] nvarchar(1), 
    [CustomData3] nvarchar(2147483647), 
    [CustomTag4] nvarchar(3), 
    [CustomCode4] nvarchar(1), 
    [CustomData4] nvarchar(2147483647), 
    [CustomTag5] nvarchar(3), 
    [CustomCode5] varchar(1), 
    [CustomData5] nvarchar(2147483647));

CREATE TABLE [Subfields](
    [SubfieldID] integer PRIMARY KEY ASC AUTOINCREMENT NOT NULL, 
    [FieldID] bigint NOT NULL, 
    [Code] char NOT NULL, 
    [Data] nvarchar(2147483647) NOT NULL, 
    FOREIGN KEY([FieldID]) REFERENCES Fields([FieldID]) ON DELETE CASCADE ON UPDATE RESTRICT);

CREATE INDEX [Code_FieldID]
ON [Subfields](
    [Code], 
    [FieldID]);

CREATE INDEX [FieldID]
ON [Subfields](
    [FieldID] ASC);

CREATE INDEX [RecordID]
ON [Fields](
    [RecordID] ASC);

**Start by looking at**: how many indexes are there on the table? And how many include the `Author` column? Maybe you want to update just some of the data? (use a `WHERE` clause) Are there any FK dependencies that also get updated? (`CASCADE`-ing maybe?) Or maybe you just don't have enough memory to update all the rows in a single transaction? (check resource manager to see available RAM memory, if there's any left) — Radu Gheorghiu, May 04 '16 at 14:26
There are no indexes on the Author column. I don't want to just update some of the data, it needs to update the whole thing. I've tried doing a limit of 1000 and looping, but that still takes an incredibly long time. Each 1000 is about 45 seconds of updating. — Frozen Solid, May 04 '16 at 14:30
Maybe take a look at [this answer, for inspiration](http://stackoverflow.com/questions/3845718/sql-how-to-update-table-values-from-another-table-with-the-same-user-name). — Radu Gheorghiu, May 04 '16 at 14:34
I actually used that post for inspiration to get this far :( I updated the post with the DDL if that helps. — Frozen Solid, May 04 '16 at 14:59

score 1 · Accepted Answer · answered May 04 '16 at 14:34

1

You didn't provide the tables DDL so I don't know what indexes are missing, please update your question with that info.

I can suggest something else, create a temporary table to update this one , something like this:

CREATE TABLE TMP_FOR_UPDATE AS
(SELECT Data,f.RecordID
 FROM Subfields
 LEFT OUTER JOIN Fields f on f.FieldID = Subfields.FieldID
 WHERE f.TagNumber = '100' and Code = 'a');

UPDATE Records t
SET t.Author = (SELECT Data FROM TMP_FOR_UPDATE s
                WHERE s.RecordID = t.RecordID);

DROP TABLE TMP_FOR_UPDATE;

answered May 04 '16 at 14:34

sagi

40,026
6
59
84

I just added a pastebin of the DDL. – Frozen Solid May 04 '16 at 14:39
You should post it here, this site is blocked for me. Did you try my answer? @FrozenSolid – sagi May 04 '16 at 14:41
Updated the post with the DDL. I was having problems getting it formatted right for stackoverflow. I'm running your query now, but it's still running after 5-6 minutes. – Frozen Solid May 04 '16 at 14:50
That made it take about 45 minutes to do the whole update, which at least finished, but is still extremely slow. – Frozen Solid May 04 '16 at 16:30
You should also make index on the temp table on both columns, this should help @frozensolid – sagi May 04 '16 at 21:27

Update query never finishes once table becomes large

1 Answers1