python - Poor performance of db. inserts using django-mssql -
i designed data warehousing application, struggle poor performance when fetching data source , saving them db. - approximately 150 kb/s.
because of limitations imposed customer, forced use django on 64bit win machine , save data ms sql express (exact versions below). using django-mssql (1.7) backend.
original data stored in .dbf file (visual foxpro), dbfread returns each row file python dict (this not issue, tested running reader discarding data). dictionary checked data quality (function sanitize_value_for_db() below), data copied django data model (attributes of object populated; tables wide, hence each table/object has 100 columns/attributes) , objects saved db. using django objects.bulk_create() (in batches of 50-100).
i run code through profiler using cprofile , pstats modules. results below. see of time spent in pywin. have no clue if there can do. hints or opinions appreciated. thanks.
configuration:
- xeon e5-2403 v2 @ 1,8 ghz, 30 gb ram
- windows server 2012 r2 (64bit)
- ms sql express 64bit, v 11.02.2100.60
- python (v3.4.3:9b73f1c3e601, feb 24 2015, 22:44:40) [msc v.1600 64 bit (amd64)] on win32#
- pywin32-219.win-amd64-py3.4
- django (1.7.10)
- django-mssql (1.7)
profile (on smaller data sample):
ncalls tottime percall cumtime percall filename:lineno(function) 2306881 332.596 0.000 332.596 0.000 {built-in method compile} 4592205 186.028 0.000 186.028 0.000 {method 'invoketypes' of 'pyidispatch' objects} 39558280 176.963 0.000 176.963 0.000 {method 'bind' of 'pyitypecomp' objects} 9889570 173.905 0.000 712.091 0.000 c:\python34\lib\site-packages\win32com\client\dynamic.py:390(_lazyaddattr_) 17464949 151.818 0.000 279.692 0.000 c:\python34\lib\site-packages\win32com\client\build.py:151(_addfunc_) 7203 142.584 0.020 2215.686 0.308 c:\python34\lib\site-packages\sqlserver_ado\dbapi.py:587(execute) 4 120.012 30.003 120.012 30.003 {built-in method sleep} 11484250 100.385 0.000 1269.561 0.000 c:\python34\lib\site-packages\win32com\client\dynamic.py:444(__getattr__) 12167658 67.229 0.000 67.229 0.000 {method 'invoke' of 'pyidispatch' objects} 22100535 50.127 0.000 53.982 0.000 <string>:12(__init__) 4599456 48.409 0.000 225.498 0.000 c:\python34\lib\site-packages\win32com\client\__init__.py:18(__wrapdispatch) 27390427 45.257 0.000 65.391 0.000 c:\python34\lib\site-packages\win32com\client\build.py:420(_resolvetype) 7189 44.294 0.006 2296.538 0.319 c:\python34\lib\site-packages\django\db\models\query.py:911(_insert) 9889570 43.621 0.000 759.155 0.000 c:\python34\lib\site-packages\win32com\client\dynamic.py:381(__lazymap__) 2306881 43.605 0.000 130.943 0.000 c:\python34\lib\site-packages\win32com\client\build.py:303(makedispatchfuncmethod) 5275612 33.300 0.000 402.786 0.000 c:\python34\lib\site-packages\win32com\client\dynamic.py:524(__setattr__) 17464949 32.793 0.000 32.793 0.000 c:\python34\lib\site-packages\win32com\client\build.py:58(__init__) 4599456 32.494 0.000 124.536 0.000 c:\python34\lib\site-packages\win32com\client\dynamic.py:120(dispatch) 2292471 30.889 0.000 843.841 0.000 c:\python34\lib\site-packages\sqlserver_ado\dbapi.py:266(_configure_parameter) 7007079 27.918 0.000 53.176 0.000 c:\python34\lib\site-packages\win32com\client\build.py:516(makepublicattributename) 2306881 27.907 0.000 46.266 0.000 c:\python34\lib\site-packages\win32com\client\build.py:483(_buildarglist) 15201179 25.593 0.000 25.593 0.000 {method 'gettypeattr' of 'pyitypeinfo' objects} 68338972 24.271 0.000 24.758 0.000 {built-in method isinstance} 2306881 23.056 0.000 538.037 0.000 c:\python34\lib\site-packages\win32com\client\dynamic.py:314(_make_method_) 17464949 21.683 0.000 21.683 0.000 {method 'getnames' of 'pyitypeinfo' objects} 7007079 20.396 0.000 20.396 0.000 c:\python34\lib\site-packages\win32com\client\build.py:546(<listcomp>) 4599456 18.166 0.000 18.166 0.000 c:\python34\lib\site-packages\win32com\client\dynamic.py:172(__init__) 3422203 15.624 0.000 20.870 0.000 c:\mri\mri\dwh\daq\daq_utils.py:152(sanitize_value_for_db)
Comments
Post a Comment