What is more efficient access to SQL or flat file?

Question

I am considering updating a real-time ASP.NET C # program that takes very frequently updated data and moves them from one database to another.

Currently, the average person’s application is used, which pulls from one and inserts into the other using SqlBulkCopy.

Is it better for the source db server to write a flat file and the average person to assemble from this flat file?

Or sql triggers? Or DTS?

Any advice would be great!

thanks

Answer 1

It really depends on the amount of data you transfer and the amount, if any, of the conversions you make. DTS / SSIS are the most scalable and feature rich options you specify.

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

Answer 2

Reading and writing to a flat file are more efficient. But if you want to use index search and other SQL functions, it is better to use SQL.

Answer 3

As far as I know, there are three different "standard" ways to do this with the SQL server:

• Replication . Typically used to synchronize tables between databases with a publisher and subscribers.
• SSIS (formerly DTS). It can be automated with a scheduled task.
• Service Broker New guy on the block.

Most likely, any of them will work better than writing to a flat file and reading it, but the only way to tell (in your environment) exactly is to check and find out different approaches.

Answer 4

Have you considered SQL Server replication or SSIS ?

Answer 5

If you like to write C # and don't like SSIS. You can look at Rhino ETL as an alternative. I found that it works very fast.

A flat file seems unnecessary as you re-write to disk when you don't need it.