Sample Header Ad - 728x90

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

1 vote
1 answer
1037 views
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](http://www.yaldex.com/sql_server_tutorial_3/ch05lev1sec2.html#:~:text=The%20export%20operation%20retrieves%20data%20by%20executing%20a%20SELECT%20command%20on%20the%20target%20table%20and%20then%20writes%20the%20data%20into%20a%20file) 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?
Asked by toros (23 rep)
Oct 29, 2022, 04:29 AM
Last activity: Oct 29, 2022, 02:29 PM