2

Ok, I originally started a question a ways back called "What are the best practices for ETL of data from one OLTP system to another?". And as you can probably tell from most of my questions so far, they have to deal with ETL. This is because as a developer I am seeking knowledge from those much closer to databases than I am. Also I didn't want to muddy the waters on the question I originally asked, since I did get an answer to that question. Hence, my "part 2".

With that said, I am probably going to be using custom Java code per high level design review with the architects and other key stakeholders. Now I am on to the detailed design for the ETL process. What I am wondering is what is the best way for me to insert/update the data in the target database?

Should I just use standard code looping techniques and insert/update record-by-record as I process them? Or is it better to generate a .sql flat file of all changes and then execute that at the end? Since I need to acquire target DB record keys for a key mapping database for my ETL I'm not sure the second choice is the best idea?

Any ideas? Guidelines? Help? What are the pros and cons of each? Are there any requirements (functional or non) that help you decide which option? Any help here with me understanding what I'm up against is appreciated.

Improve this question

1 Answer 1

Reset to default
3

Relational database guys always like to think in "sets" but some tasks just don't work that way. With ETL, since there is no way around the physical disk having to actually write data you just want to reduce the overhead related to each individual query as much as possible.

Speaking for MSSQL. If you use "parameterized" queries. Looping in code and updating in that fashion should be fine. The reason for this is that by using a parameterized query, the SQL engine can cache a query plan to use for each iteration of your loop and avoid the overhead of starting from scratch each time.

Google parameterized query if you are unfamiliar with that term. Speaking in Java terms you can also search for Prepared Statements.

I say "should be fine" in my previous post.... assuming that the query you are going to run repeatedly is as well written and efficient as possible.

If you are able to analyze a sample query that is going to be run repeatedly and find out what it's resulting query plan is. You may find there are indexes that aren't necessary that can be turned off in order to improve the performance of the update.

Or, on the flip side, if your update includes logic that causes a table scan on a related table you may find the addition of an index would greatly improve the performance of your update.

Improve this answer
4
  • Maybe combine your answers? Makes it easier to consider answers and/or vote on them and pick an answer.
    – Chris Aldrich
    Commented May 11, 2011 at 12:51
  • Yes, I'm still getting used to the stack exchange format.
    – RThomas
    Commented May 11, 2011 at 15:52
  • How do you know if it requires a table scan? I've got a grand total of about 30,000 records that need to be ETL'd. And that is only on the firs time. After that I only need to push updates for the few records that are created or "inactivated". Does that influence anything?
    – Chris Aldrich
    Commented May 11, 2011 at 17:40
  • 1
    If there is no index that allows it to jump right to the value. A table scan occurs when it has to search row by row for the values. If the ETL process rely's on a subquery that causes a table scan then an index would help. But if you're just doing raw inserts then no effect.
    – RThomas
    Commented May 11, 2011 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.