Jay-45986 - Tuesday, December 26, 2017 1:18 PM

Lynn Pettis - Tuesday, December 26, 2017 1:00 PM

Jay-45986 - Tuesday, December 26, 2017 12:38 PM

Hi all, 

This has me stumped. I'm performing mass updates to a table. I'm using Data Masker by Redgate to de-identify the data. It's basically a wide update, involving 8+ columns. The table has around 662K+ rows. I have the constraints disabled, along with the indexes, except for the clustered indexed pk column. The tool takes around 22 rows/sec executation rate to updates all the data. Now, what has me stumped is that when I make an EXACT copy of the same table, with the exact number of indexes, rows and constraints and try the exactly same update, it takes 645 rows/sec execution rate. 

So to summarize. One table, 662K rows, 22 rows/sec to update all the records. Exact same copy of the aforementioned table, the performance boost is ten folds and it's executing at much much higher rate of 645 rows/sec. Could there be a buffer issue related to the existing table? or anything else you all could think of? 

Thanks.

You could still be comparing apples to oranges.  The data in the "fast" table may actually be stored differently than the data in the "slow" table.  We have very little information other than the number of rows of data and updates involving 8+ columns.  There could be a lot of data movement in the "slow" table and little data movement in the "fast" table.

Thank you for your reply Lynn. 

To provide you more information. This is in a database that's on a sandbox and no other users and/or applications accessing it. It's just myself and I 🙂  To add more, both of these tables belong to the exact same file groups as well. There is 0 other activity in this database and into these tables, except for when I run queries to load and test.  

Same # of rows, same exact structure (pks, indexes, constraints, filegroups etc.), 0 activity. Just the names are different.