what's more efficient, SQL or flat file access?

I am looking at upgrading a realtime program ASP.NET C#, that takes very frequently updated data and moves it from one database to another.

Cur开发者_JS百科rently using a middle man app, that pulls from one and inserts into another using SqlBulkCopy.

Is it better to have the source db server write a flat file and the middle man collect from that flat file?

Or sql triggers? Or DTS?

Any advice would be great!

Thanks

It really depends on the amount of data you are passing and the amount, if any, transformations you are making. DTS/SSIS are the most scalable and feature rich options you listed.

What kind of load to you expect? Are you doing complex transformations? If you have the resources to use SSIS, I would recommend that as it will scale as high as you want to go.

Reading and writing to flat file is more efficient. But if you want to use index search and other SQL features, it's better to use SQL.

As far as I know there are three different "standard" ways of doing this with SQL server:

• Replication. Normally used for keeping tables across databases synchronized, with a publisher and subscribers.
• SSIS (formerly DTS). Can be automated with a scheduled job.
• Service Broker. New kid on the block.

Chances are good that any of these would perform better than writing out to a flat file and reading it, but the only way to tell for sure (in your environment) it to test and time the different approaches.

Have you considered SQL Server Replication or SSIS?

If you like writing C# and dislike SSIS. You could look at Rhino ETL as an alternative. I've found it to perform very quickly.

The flat file does seem unnecessary as you a re writing to disk when you don't need to.