Day: 1 May 2006

Performance Problems with sending data 1000 miles

Well, its not a bank holiday in Switzerland, so while you are all having fun in the sun or spending your day in the pub as is traditional for the May bank holidays, I'm sitting here working on an interesting performance problem. We are trying to transfer 200,000 rows of data from SAP in Lausanne to MS SQL Server in the Ukraine (1000 miles away?). Its averaging 0.5 seconds per row and so will complete in roughly 27 hours. That causes the customer a few problems because its scheduled for 3am and has to be complete by 6am... The techy implementation is to use JDBC to do an insert/update statement depending upon if the row already exists, so we are actually doing several JDBC calls per row of data. Originally we thought that the bandwidth to the Ukraine together with a crackily/dodgy/unreliable phone line was the problem, but a quick calculation for the 1MB ADSL line that services the Ukrainian network, showed that it should be transferring in the range of several gigabytes in 27 hours, not a measly 25 megabytes... So along came our DB expert who suggested that calling a stored proc would be better because it would involve sending less bytes of data (the call to a stored proc does not name the columns being updated) as well as be optimised by the engine. Furthermore, it could do the check to see if the row already exists, reducing the amount of calls we had to do. The…

Read more