Why is bcp out much faster than select * even when bcp uses select * internally?

Question

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?

Answer 1

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.