1

I tried exporting a very large table (5B rows) to a csv file using both Select * and bcp out.

Regarding Select *, I am using pyodbc module to query the database and writing it to a csv file. I also tried using cli by directly querying the table and piping the result to a csv file but throughput was even slightly less than that using pyodbc.

Regarding bcp, I am using bcp out command to directly export the table to a csv file.

I read about bcp architecture here and it says that bcp uses Select * internally. I was expecting throughput for both the approaches to be near about same but experiments resulted in Select * throughput of 90GB/hr and bcp out throughput of 190GB/hr. I was just curious to know why is there such a huge difference in throughput?

1 Answer 1

3

The performance difference between the two methods is client side.

Both bcp and pyodbc use the ODBC API to send a query to SQL Server and retrieve results. SQL Server executes the query and streams results back to the client over the TDS protocol as fast as they can be consumed by the client application.

BCP is a natively compiled C++ application with more efficient static typing whereas python is interpreted with dynamic types. Also, BCP is a specialized utility with much attention to low-level performance detail with regards to common operations like SQL data type conversions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.