ODM Data Loader: Loading takes a long time especially when adding large numbers of data files

kimschreuders[CodePlex]
One of our users, Pradnya Khutafale is having an issue with long Data Loader times. I have attached a .zip file with 2 sample data files and 2 batch files, and info on his log file. I suggested that he change his recovery model to simple, and then shrink
the unused space out of the log file to ensure that a continuously growing log file would bog him down. The rest of this message contains the thread of our discussion on this issue:
nbsp
Pradnya,
nbsp
I am working with the developers of the software to see if we can identify the source of the problem.
nbsp
I know that I personally manage an ODM with 56 million DataValue records, and while some of the tools are not real speedy, for the most part they are fast enough to be usable. Those 56 million records represent an initial batch of 5 years of historical data
and was loaded in about 2 weeks. Now I will say that my SeriesCatalog only has 996 records. Soon I will configure the database to stream in updates on an hourly basis. So, I know the tools work well enough to manage something like this.
nbsp
Looking at the bit of log file you included below, you loaded 2 batches of 70,000 records each. The first batch took 55 minutes and the second 56. At that rate, loading 100 million records would take about 142 hours, or 6 days. Even if you take your initial
estimate of 7500 minutes to load a month of data (from the first message in this thread), 7500 minutes = 5.2 days. That does not appear to be anywhere near the months that you are concerned about. Is the length of time changing? Do you have any numbers on
this?
nbsp
I will say that every once in a while, I have found a function that runs slower than I am willing to work with, editing with ODM Tools is my favorite example of a tool with a very slow function. In those cases, I write a SQL script to perform the function manually
to provide an immediate fix until the developers can address the problem in the tools.
nbsp
I have created a formal issue report on this issue. You can see the details at:
http://hydroserver.codeplex.com/workitem/8264.

Kim
nbsp
From: Pradnya Khutafale
Sent: Tuesday, May
Subject: Re: Degraded Performance
nbsp
Hi Yoori,
Any update on the perfomance issue?

Thanks.
Pradnya
On Thu, May 12, 2011 at 10:33 AM, Pradnya Khutafale gt wrote:
Hi Kim/
The performance of ODM tool has degraded. As shown below, now it takes around 50-55 minutes to upload one file (around 70,000 records). Currently, SeriesCatalog has - 15138 records and DataValues has 4488856 records.
nbsp
The performance is major concern for us, since we have millions of records to upload for model data and at this rate, it will take several months to upload the data.
nbsp
Log @ Wednesday, May 11, 2011 6:11:44 PM
Opening .\OUTPUT_4D\TEMPERATURE\Datavalues.csv ...
nbsp
Log @ Wednesday, May 11, 2011 6:12:00 PM
.\OUTPUT_4D\TEMPERATURE\Datavalues.csv Opened.
nbsp
Log @ Wednesday, May 11, 2011 6:12:00 PM
You are loading quotDataValuesquot
nbsp
Log @ Wednesday, May 11, 2011 6:12:00 PM
Finding New Methods
nbsp
Log @ Wednesday, May 11, 2011 6:12:00 PM
Finding New OffsetTypes
nbsp
Log @ Wednesday, May 11, 2011 6:12:01 PM
Finding New DataValues
nbsp
Log @ Wednesday, May 11, 2011 6:20:15 PM
70000 rows committed to DataValues
nbsp
Log @ Wednesday, May 11, 2011 6:20:16 PM
Updating Series Catalog
nbsp
Log @ Wednesday, May 11, 2011 7:06:32 PM
4624 rows committed to SeriesCatalog
nbsp
Log @ Wednesday, May 11, 2011 7:06:32 PM
Committed 74624 rows to the database.
nbsp
Log @ Wednesday, May 11, 2011 7:06:50 PM
Opening .\OUTPUT_4D\TEMPERATURE\Datavalues.csv ...
nbsp
Log @ Wednesday, May 11, 2011 7:07:06 PM
.\OUTPUT_4D\TEMPERATURE\Datavalues.csv Opened.
nbsp
Log @ Wednesday, May 11, 2011 7:07:06 PM
You are loading quotDataValuesquot
nbsp
Log @ Wednesday, May 11, 2011 7:07:06 PM
Finding New Methods
nbsp
Log @ Wednesday, May 11, 2011 7:07:07 PM
Finding New OffsetTypes
nbsp
Log @ Wednesday, May 11, 2011 7:07:07 PM
Finding New DataValues
nbsp
Log @ Wednesday, May 11, 2011 7:15:15 PM
70000 rows committed to DataValues
nbsp
Log @ Wednesday, May 11, 2011 7:15:15 PM
Updating Series Catalog
nbsp
Log @ Wednesday, May 11, 2011 8:02:58 PM
4624 rows committed to SeriesCatalog
nbsp
Log @ Wednesday, May 11, 2011 8:02:59 PM
Committed 74624 rows to the database.
nbsp
nbsp
Thanks.
Pradnya
---------- Forwarded message ----------
From: Pradnya Khutafale gt
Date: Tue, May 10, 2011 at 10:51 AM
Subject: Re: Concern : Slow Model Data Upload
To: Kim Schreuders lt
nbsp
nbsp
Hi Kim,
nbsp
The OD_log.ldb had size 894MB. I followed the instructions provided in the link to change the recovery model. And also executed the SQL you provided.(After executing the 2nd SQL, snap shot of the output attached) .
nbsp
Now the OD_log.ldb shows size as 8.18 MB. However, there is no change in the performance. Below is the log while uploading the model data output(4624 records) to the server which takes around 25 minutes for one file.
nbsp
nbsp
Log @ Tuesday, May 10, 2011 10:16:15 AM
Opening .\OUTPUT_2D\Datavalues.csv ...
nbsp
Log @ Tuesday, May 10, 2011 10:16:15 AM
.\OUTPUT_2D\Datavalues.csv Opened.
nbsp
Log @ Tuesday, May 10, 2011 10:16:15 AM
You are loading quotDataValuesquot
nbsp
Log @ Tuesday, May 10, 2011 10:16:15 AM
Finding New Methods
nbsp
Log @ Tuesday, May 10, 2011 10:16:15 AM
Finding New OffsetTypes
nbsp
Log @ Tuesday, May 10, 2011 10:16:15 AM
Finding New DataValues
nbsp
Log @ Tuesday, May 10, 2011 10:17:13 AM
4624 rows committed to DataValues
nbsp
Log @ Tuesday, May 10, 2011 10:17:13 AM
Updating Series Catalog
nbsp
Log @ Tuesday, May 10, 2011 10:40:21 AM
4624 rows committed to SeriesCatalog
nbsp
Log @ Tuesday, May 10, 2011 10:40:21 AM
Committed 9248 rows to the database.
nbsp
nbsp
Even the ODM tool 1.1.3 is taking around same time for loading the records.
nbsp
Pradnya
nbsp
nbsp
nbsp
nbsp
On Fri, May 6, 2011 at 5:00 PM, Kim Schreuders lt wrote:
Pradnya,

Here’s some background info on Recovery Models and Transaction Log Management:
http://msdn.microsoft.com/en-us/library/ms366344.aspx.

I think this SQL (once you insert the database and transaction log names) will change the recovery model to simple, and then shrink the unused space out of the log file:

USE master
ALTER DATABASE database_name SET RECOVERY SIMPLE -- set database to simple recovery mode which has minimal logging
USE DatabaseName
DBCC SHRINKFILE(lttransactionLog_namegt, 1) -- shrinks log file to eliminate unused space

Let me know if you have any questions.

Kim

From: Pradnya Khutafale []
Sent: Thursday, May 05, 2011 9:39 PM
To: Kim Schreuders
Cc: Yoori Choi; David Tarboton; Jeff Horsburgh; Ding Wei
Subject: Re: Concern : Slow Model Data Upload

Thanks Kim for your prompt reply. I am not in town right and so not able to acess the SQL Server .So I am not sure about the logging/backup model being used.
nbsp
http://technet.microsoft.com/en-us/library/ms179478.aspx
nbsp
So do I have to follow instruction 6 and 15 in the link above to change the logging/backup model?
nbsp
Thanks.
Pradnya
nbsp
On Thu, May 5, 2011 at 11:07 AM, Kim Schreuders ltgt wrote:
Pradnya,

How big is your .ldb file? What logging/backup model are you using? Is is possible that your .ldb file has gotten inordinately large? That would cause the system to slow down dramatically. Especially when loading large batches of data, you can probably use
the simple backup model to truncate the .ldb file after each transaction. If your problem is overly large/complex .ldb files, changing the backup mode could speed things up dramatically.

Kim

From: Yoori Choi []
Sent: Thursday, May 05, 2011 7:47 AM
To: David Tarboton; Jeff Horsburgh; Kim Schreuders
Cc: 'Pradnya Khutafale'; 'Ding Wei'
Subject: FW: Concern : Slow Model Data Upload

Dear Dave, Jeff, and Kim:

UMass Boston group finally completed to filling in all template spreadsheets (i.e. sites, datavalues, source, variables) and they started to loading them into ODM DB by using Data Loader. But there are some performance issue. If you see the message from Pradnya
at the below, it took pretty long time to loading them in DB. As I understand, this performance issue (handling a large files) in DL is already known, but I would like to ask you if there is any way to improve performance, but need to use Data Loader. If you
can give any comments on this, it would be great. Thank you.

Yoori Choi, User Support Specialist
CUAHSI
196 Boston Ave., Suite 2100
Medford, MA 02155

From: Pradnya Khutafale []
Sent: Thursday, May 05, 2011 8:23 AM
To: Yoori Choi
Cc: Ding Wei
Subject: Concern : Slow Model Data Upload

Hi Yoori,
nbsp
I have started uploading the model data using batch file. So in this batch mode, validations, adding records to the DataValues table is done within a minute. However, writing to Series Catalog table takes about 20-30 minutes for one file. I have around 250
files for 1 month data. Each file has records in range (5000-70,000). So at this rate( 250*30 minutes = 7500 minutes) , it will take me around 8-9 days to upload 1 month data. (DataValues table has around 13 lakhs records and series Catalog that I checked
yesterday evening(while data was uploading) had 11,000 records.)
nbsp
I have attached few sample DataValues-metainformation .csv(for temperature and bathymetry) and the batch file I used for those.
Can you or expert from your team comment on why its taking so much time? Is their any way to improve it?
nbsp
nbsp
Thanks.
Pradnya

Start with the attached sample CSV and batch files, then trace the Data Loader path that writes DataValues and updates SeriesCatalog. Use the supplied timing logs and database sizes to isolate the slow operation; done means the bottleneck is identified and loading performance is measurably improved.

Written by the indexing model from the issue text.