0

I’ve been tasked with the job of writing a Java process to move some information from a legacy as400 system to our new SQL Server 2008 installation. Because the information is managed on the as400 system, this needs to be an “on demand” solution (IE someone kicks it off once or twice a day and it should populate SQL Server with the newest version of the data). It's a pretty heavily used set of data so I think it would be a good idea to use a transaction to clear the table and rebuild it. The transaction should prevent anybody from accidentally picking up an incomplete list by requesting the data halfway through my process. Is this the correct way to manage a situation like this?

Further explanation if needed:

I’m one of the newer developers on the team and one of the much more senior developers is trying to insist we perform an "in-memory replacement" process. He wants to update every record that exists in both tables, insert the new ones which didn’t exist, and then delete the old ones which no longer exist. I’m not convinced this is a good idea, particularly because any requests midway through the process will get returned a mishmash of data. I just want to know how strongly I should push back from a technical standpoint.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
thedan
  • 1,230
  • 2
  • 9
  • 13
  • *any requests midway through the process will get returned a mishmash of data*. Not if you put the load process in a transaction – Conrad Frix Sep 12 '12 at 21:59
  • can you explain a little further? – thedan Sep 12 '12 at 22:54
  • I don't know much about Java but any data access library will allow you to issue multiple DML statements inside of a single transaction. During the transaction no other requests are not typically allowed to see uncommitted changes. See [Using Transactions](http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html) for a JDBC example. – Conrad Frix Sep 13 '12 at 01:19

1 Answers1

1

Depending on how long it takes to populate, you may not have acceptable performance from a user perspective if the table is locked and they can't access it.

Consider trying this (Assuming you have no child tables). Create two tables with the same structure. Create view that selacts all the columns from table1. Have your process populate table2. When it is done, change the view to reference table2 (should take seconds or milliseconds). Have all application code reference the view instead of the base tables. When you do this, you can build a large table, and rebuild the indexes and never will the users even know it because from their perspective the only delay will be the time it takes to recreate the view. We've done this a few times when recreating the table would take minutes or even hours. Of course then the next time you reverse it and populate table one and then change the view back.

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • thats not a bad idea actually. i'll have to see how much work this would be. I'm not sure we have enough data to justify it though. (<2k rows max) – thedan Sep 12 '12 at 22:52
  • Is changing the definition of a view on the fly similar to creating one on the fly? [This was cautioned](http://stackoverflow.com/a/1044210/119477) against. Seriously though do you have any idea what happens if a transaction is in flight and the view changes? – Conrad Frix Sep 14 '12 at 22:02
  • This is for tables that will have no transactions against them when you change teh view as they are populated only through an import which you control. NO other users should be changing dat inteh tables. – HLGEM Sep 15 '12 at 18:37