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?