I need to insert a large number of rows to a temporary table prior to running a query that will use them (making joins with other data on the database). Any advise on how to accomplish this maintaining reasonable performance?
I know sqlserver has the concept of a bulk insert, although i'm still not sure which are its limitations, and if it would be useful here. In either case, is this outside the scope of pymssql?
Thanks,
Filipe Correia
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
BCP is currently not implemented in pymssql. You can try plain inserts - remember to do them in one transaction (enclosed in BEGIN TRAN … COMMIT TRAN) and on a temporary table in tempdb (not as much log penalty) with no indexes. After commiting transaction just add necessary indexes and you'll be fine. If you have more than a few millions of rows and/or they are very wide (sum of columns more than few dozen bytes), it WILL take time anyway, most of which will be network and disk i/o. I don't want to bore anyone with details here.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
I need to insert a large number of rows to a temporary table prior to running a query that will use them (making joins with other data on the database). Any advise on how to accomplish this maintaining reasonable performance?
I know sqlserver has the concept of a bulk insert, although i'm still not sure which are its limitations, and if it would be useful here. In either case, is this outside the scope of pymssql?
Thanks,
Filipe Correia
BCP is currently not implemented in pymssql. You can try plain inserts - remember to do them in one transaction (enclosed in BEGIN TRAN … COMMIT TRAN) and on a temporary table in tempdb (not as much log penalty) with no indexes. After commiting transaction just add necessary indexes and you'll be fine. If you have more than a few millions of rows and/or they are very wide (sum of columns more than few dozen bytes), it WILL take time anyway, most of which will be network and disk i/o. I don't want to bore anyone with details here.