A.M wrote: >>I dare hardly suggest this, but might it not be better to use Python's >>database functionality to perform the task? The language can access both >>databases, and you might find it quicker. Then again, if your database >>experience is limited, you may not ... >> >>regards >> Steve >>-- >>Steve Holden +44 150 684 7255 +1 800 494 3119 >>Holden Web LLC/Ltd http://www.holdenweb.com >>Love me, love my blog http://holdenweb.blogspot.com >>Recent Ramblings http://del.icio.us/steve.holden >> > > > Hi Steven, > > > > Based on my experience, the fastest possible way to import raw data into > Oracle is SQL*Loader. Similarly, the fastest way to extract raw data from > SQL server is BCP. > > > > My script transfers 40,000,000 records (actually big records) from sql > server to oracle in 20 Min. I tried ODBC to do the same work. I turned off > all record locking and transactions through query hints. The actual program > was a C# program. After 12 hours, I just stopped the program. > I'm not that surprised - it was just an inquiry. Usually the database bulk dump and load utilities take advantage of every trick in their respective books to provide speed. A native driver might have done better than ODBC, but you are probably correct on going that route for speed. > > > I created DOS batch files to control BCP and SQL*Loader steps. It is faster > than any fancy GUI tools. Now I am using Python. > > I am thinking to add comprehensive logging to the ETL (extract transform > load) process. All details command line outputs will be stored in database. > System administrators can query database and watch how the ETL job is > working. > > At this point I have quite challenge with capturing BCP's stdout/stderr > output to string variables in Python program. > > I'll post the final outcome here. > OK, good luck.
regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Love me, love my blog http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
