Re: copy large amounts of data to MSSQL
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: Jeff Chirco <backseatdba@xxxxxxxxx>, Shane Borden <sborden76@xxxxxxxxx>
- Date: Sun, 08 Nov 2020 19:55:45 -0500
On Sun, 2020-11-08 at 16:55 -0500, Mladen Gogala wrote:
On Sat, 2020-11-07 at 13:30 -0500, Mladen Gogala wrote:
cursor.fast_executemany = True
I feel desecrated after using Windows. The above flag is only
supported with Microsoft's ODBC driver:
https://github.com/mkleehammer/pyodbc/wiki/Features-beyond-the-DB-API
With the FreeTDS driver, that I normally use on Linux, this feature
produces a very fast core dump. However, the FreeTDS package also
contains an executable called "freebcp" which allows blinding fast
load over the network. It is essentially a free version of the "bcp"
utility. I am installing MS ODBC for Linux right now and will try
when it's done.
Microsoft ODBC driver for Linux works like a charm:
mgogala@umajor:~/tmp$ time ./load_csv.py -u scott/tiger@MS-scott -t
test_tab -i /tmp/test_tab.csv -n 32768
5000000 rows loaded
real 0m50.139s
user 0m13.676s
sys 0m2.672s
mgogala@umajor:~/tmp$
The "fast_executemany" flag doesn't cause core dump with the original
Microsoft ODBC driver. With FreeTDS, I was unable to do load 150M in
less than 35 min. With the "fast_executemany" flag and Microsoft ODBC,
I can do it in less than a minute.
Other related posts:
