Hi,
I am working with SQLAlchemy to insert data in a MSSQL database. Typically
we need to make regular insert of say half million rows in few tables.
Right now I am using Core flavour to make batch insertion via pyodbc and I
have notice that in the DB trace I have an entry per row
declare @p1 int
set @p1=12996
exec sp_prepexec @p1 output,N'@P1 nvarchar(36),@P2 bigint,@P3 float,@P4
float,@P5 float,@P6 float,@P7 nvarchar(5),@P8 nvarchar(5),@P9
nvarchar(3),@P10 nvarchar(3),@P11 nvarchar(9),@P12 nvarchar(1),@P13
nvarchar(7),@P14 float,@P15 float,@P16 float,@P17 float,@P18 float,@P19
float,@P20 float,@P21 float,@P22 int,@P23 float,@P24 varchar(1),@P25
nvarchar(2),@P26 varchar(1),@P27 float,@P28 int,@P29 float',N'INSERT INTO
tmp.guest.[PythonAccess] ([RunID], [DemandID], [FFEPerWeek],
[WeightPerFFE], [MXNPerFFE], [TimeLimit], [PortFrom], [PortTo],
[CargoType], [CtrType], [StringCode], [Direction], [TradeLane], [20fPct],
[40fPct], [45fPct], [MaxTransshipments], [CostLimit], [FFEPerWeekSource],
[WeightPerFFESource], [TimeLimitSource], [MaxTransshipmentsSource],
[CostLimitSource], [SourceChanges], [RouteCode], [Source],
[TimeLimitSlack], [MaxTransshipmentsSlack], [CostLimitSlack]) VALUES (@P1,
@P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15,
@P16, @P17, @P18, @P19, @P20, @P21, @P22, @P23, @P24, @P25, @P26, @P27,
@P28,
@P29)',N'737f1545-a49d-4120-81d4-47fa642d4e0b',12988,0,2759662062713899,36343,736111111109,50000,50,625,N'LVRIX',N'CMDLA',N'DRY',N'DRY',N'WAFEUR999',N'S',N'EUR/WAF',0,83302701910413379,0,16697298089586618,0,1,803,52160288929122,NULL,NULL,NULL,NULL,NULL,NULL,N'W3',NULL,NULL,NULL,NULL
select @p1
what surprise me is that there is a prepare per row. A similar C++
implementation shows only an initial prepare and then only exec. And indeed
is much faster.
I have then saw I can force a prepare on the transaction
with engine.connect() as conn:
trans= conn.begin_twophase()
trans.prepare()
But the two phase transaction is not supported for pyodbc,
Why is that? Any suggestion/alternative??
Thanks a lot.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
