CODEWITHSUNDEEP
c#sql-serverado.net
Michael Wick
Michael Wick

Reputation: 5

Updating currently-read row using ADO.NET

I need to update a column in a large table (over 30 million rows) that has no primary key. A table row has a unique email address column. The update involves generating a value that must occur in C# and appending it to a column value. So the row must be read, the column value updated, and written back out.

I was hoping there was a concept of cursoring in ADO.NET but I do not see this. I can read the rows quickly enough, but the update call, using a WHERE clause for the email address, takes forever. After researching this most answers seem to be "put in a primary key!" but that is not an option here. Any thoughts?

Upvotes: 0

Views: 257

Answers (1)

krtek
krtek

Reputation: 402

For a 30mil rows heap, there's not many options. Without any index you can do basically nothing to speed it up.

Only solution is to check a fragmentation of a heap. You should add a clustered index to alleviate the table fragmentation, then drop it immediately. But if you cannot affect that table in any way, it could be faster to move all the data into a new table :-)

Upvotes: 1

Related Questions