This is a sort of generic question and I don't require a very specific answer, but let me ask it by describing a scenario.
You connect to a remote db in SQL Server Studio 08, and run a select * insert into a table in a local db. Assume the schemas are identical, this is not a question about SQL syntax or table design.
If, on a linux box, I write an incredibly light weight application that utilizes the Sybase driver (such as Perl and DBI) to copy only that same table. You do this with a select *, and construct one nice multi row insert into a local mysql db.
The times aren't that different until you start scaling up the rowcount many times over. Assume the hardware is identical, and also the mysql config is ultimatly optimized. The windows-to-windows transfer will always be faster. Now, perl's compile time, the little initialization time is negligible as you scale up. They are both socket based data streams.
My question is, plainly, why the SQL Server scenario so much faster? Is it the way they format data? Is it Windows' domination of the hardware for maximum optimization? How does SQL Server make so much more use of the bandwidth when it's Windows-to-Windows?
Edit: @gdn I realize its row by row, i guess what im really wondering is how these types of bulk operations are optimized as such. Coming experience with these client query based apps first, im finding hard to imagine how its done so much more efficiently.
