My database (SQL Server) contains a table structure that mimics a spreadsheet.

The parent table is a worksheet.

The child table is a row.

The grandchild table is a cell.

I have defined POJO objects to represent each table; the worksheet object contains a set of row objects, each row object contains a set of cell objects.

When my program saves a large worksheet object (e.g. > 1,000 rows), all other users are locked out of the application until the save completes (takes about 15-20 seconds per 1,000 row objects and the more cell objects, the longer it takes).

Rather than saving the worksheet object, would it be better to iterate over the row objects and commit after a number of row object saves? MAybe I should commit after saving each row object?

Is there another/better way to save this object structure?

Thanks in advance,

Steven.

consider another user, who looks at your spreadhseet in that 20 second period while it is being saved. When you commit row by row, then such a user will only see the rows already committed, but won't see the other rows. If such a situation is acceptable, then committing row by row is recommended.

1. What is your data selection criteria? Are reading only commited data?
2. One more point to add is the data integrity. If you save row by row then other users are able to see the data which not complete.
3. These kind of requirements are governed by business functions rather than technical boundaries