Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
My bet is that you are comparing a perl script to a native app. –  surfasb Sep 30 '11 at 0:36
add comment

migrated from superuser.com Sep 30 '11 at 4:34

This question came from our site for computer enthusiasts and power users.

1 Answer

There is a difference:

  • An RDBMS is optimised for such bulk row operations
  • A client app has to process row by row

You have factors like:

  • buffering of output while the client consumes it but for an RDBMS this doesn't matter
  • SQL Server + Windows integration
  • choice of engine in MySQL
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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