Hi, we are using OTL library for reading/writing to MSSQL Server from our C++ applications. We are looking for a way to optimize our bulk uploads (loading millions of rows in bulk). Our DBA recommended using native MSSQL bulk copy API . We would really like to use OTL rather than calling ODBC C functions directly, but I don't think OTL currently supports MSSQL bulk copy. Is it possible to add this support or is there anything we can use in OTL that will give comparable performance for bulk inserts. Reference to the API: https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-how-to/bulk-copy/bulk-copy-data-from-program-variables-odbc?view=sql-server-ver15
Thanks in advance!
Natalia
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I looked at that a long time ago but I found that there wasn't enough demand for it at that point in time. At this point in time I don't have "the bandwidth" (too much stuff to do at work and C++ compiler development being "agile" these days doesn't help) to implement anything as complex as bulk insert. I'd suggest you to take a closer look at bulk copy utility (BCP) or T-SQL bulk insert command.
Sorry to disappoint you,
Sergei
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Sergei, thanks a lot for the quick response. We are actually using bulk insert now but trying to get away from file based uploads because the data that we generate is in memory and saving it to file is an unnecessary overhead. In the case of bulk insert the problem is complicated by the fact that the fs has to be mounted on both the client generating the file and the sql server, which is hard to achieve in cloud environments, bcp has its drawbacks too. Could you estimate how hard it would be to enhance OTL to support bulk copy if we could find time? Since we need to solve the problem in some way on our side, I wonder if we could work out an arrangement to collaborate on adding the feature and testing it on our side :)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi, we are using OTL library for reading/writing to MSSQL Server from our C++ applications. We are looking for a way to optimize our bulk uploads (loading millions of rows in bulk). Our DBA recommended using native MSSQL bulk copy API . We would really like to use OTL rather than calling ODBC C functions directly, but I don't think OTL currently supports MSSQL bulk copy. Is it possible to add this support or is there anything we can use in OTL that will give comparable performance for bulk inserts. Reference to the API:
https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-how-to/bulk-copy/bulk-copy-data-from-program-variables-odbc?view=sql-server-ver15
Thanks in advance!
Natalia
Natalia,
I looked at that a long time ago but I found that there wasn't enough demand for it at that point in time. At this point in time I don't have "the bandwidth" (too much stuff to do at work and C++ compiler development being "agile" these days doesn't help) to implement anything as complex as bulk insert. I'd suggest you to take a closer look at bulk copy utility (BCP) or T-SQL bulk insert command.
Sorry to disappoint you,
Sergei
Hi Sergei, thanks a lot for the quick response. We are actually using bulk insert now but trying to get away from file based uploads because the data that we generate is in memory and saving it to file is an unnecessary overhead. In the case of bulk insert the problem is complicated by the fact that the fs has to be mounted on both the client generating the file and the sql server, which is hard to achieve in cloud environments, bcp has its drawbacks too. Could you estimate how hard it would be to enhance OTL to support bulk copy if we could find time? Since we need to solve the problem in some way on our side, I wonder if we could work out an arrangement to collaborate on adding the feature and testing it on our side :)