Flat File Processing vs. Sql

[Elizabeth Erdman]

I am trying to convince my company that sql has a faster processing speed
than flat file processing. Is this correct? Does anyone know where I can
find some pros or cons for both?

[Michael Levy]

Can you provide some specifics? What are you going to be doing?

-Mike
--
Michael Levy MCSD, MCDBA, MCT
Consultant
GA Sullivan
michaell@gasullivan.com

"Elizabeth Erdman" <eerdman@wausaufs.com> wrote in message
news:390f3d36$1@news.devx.com...
>
> I am trying to convince my company that sql has a faster processing speed
> than flat file processing. Is this correct? Does anyone know where I can
> find some pros or cons for both?

[Colin McGuigan]

Elizabeth Erdman wrote in message <390f3d36$1@news.devx.com>...
>
>I am trying to convince my company that sql has a faster processing speed
>than flat file processing. Is this correct? Does anyone know where I can
>find some pros or cons for both?

Er. It depends. Flatfile _is_ faster than SQL if all you do is ever access
the same data in the same order. That's it. No searching, no deletions,
and inserts only at the end. If that describes your business, stick with
flatfiles.

That aside, if you're dealing with small amounts of data (< 1,000 or so),
there's not going to be much noticable difference 'twixt the two. Assuming
that your company is not familiar with databases (which it sounds like they
are not), the pain of convincing them would not be offset by any great
improvements.

For everything else, SQL's going to be faster for just about anything,
especially searching amongst a large amount of data. Speed differs based on
machine, design of the database, method used to access the database, phase
of the moon, etc.

--
Colin McGuigan

[Dimitris Ristas]

Dear Elizabeth,

we made the same question to ourselves a few weeks ago, if the Topspeed
files (a KSAM system) we are using from Clarion applications, are faster in
response than the Sql Server 7 files. So we made a test using data from our
production files with the following capacities :

Customers 40000
Policies 120000
Receipts 1500000
FundsTrans 2000000
Funds 40
Salesmen 2000
Offices 40

and we also increase users from 1 up to 20 concur on both systems doing
mainly queries with multiple criteria.
Up to 10 users the response time was almost the same, but from that point
and up topspeed files start losing as the response time became longer
reaching in some case the 3/1.

Most of the delay is coming from the data traveling over the network to
reach the workstation which is doing the report do be filtered(sqlserver is
sending to the workstation only the answer to the request of the
workstation) and the fact that the server has to read the data from the
harddisks compare to the memory caching sqlserver is using are the reasons
from the problem on user incrising.

Security and Maintance procedure are also to big issues on administrating
Databases

Hope I gave you something on your fight.
Dimitris


Elizabeth Erdman <eerdman@wausaufs.com> wrote in message
news:390f3d36$1@news.devx.com...
>
> I am trying to convince my company that sql has a faster processing speed
> than flat file processing. Is this correct? Does anyone know where I can
> find some pros or cons for both?

[RN]

The Pick MVDBMS is essentially a flat-file that runs circles around SQL based
databases and is easy to scale with very little space limitations.

[stuartnaylor]

"Elizabeth Erdman" <eerdman@wausaufs.com> wrote:
>
>I am trying to convince my company that sql has a faster processing speed
>than flat file processing. Is this correct? Does anyone know where I can
>find some pros or cons for both?

Firstly by the means of Flat files if you mean a collection of text files
delimitted by comma's or fixed row. The following is probally true.
Firstly when you open a file for reporting you have the data but who controls
what type of data ie double, boolean decimal, text.
Ever opened a Excel spreadsheet and found that the first row of a text field
which contained just a decimal point now causes the rest of the records to
think that they are decimals.
SQL or any database firstly stores data but also has a defined data type
(No Mistakes)
Your source programs that open these files probally run on the client when
you want to run a small sub query IE. a singular accounts sales you have
to open the whole sales file move that across the network then filter for
the required data.
Who controls recordlocks and access security probally you programatically
this can never be as successful as a server provided scenario. Simply because
how do the clients know which clients have record locks. Further tables for
this need to be transported accross the network.
You want to provide outside access to these files through XML, SOAP internet
based activities. Provide business -> business -> customer commerce. SQL
I can tell you how, flat files ?
Relationships and table joins do you control them yourselves ?
Business rules on data entry enforced by yourself ?

One main question that business has is what is the contribution of my products.
Ie whats my profit after wages, overheads, sales, administration & processing
with material costs removed.
SQL I could give you guidelines on how to accomplish the above in a development
time of 6 to 12 months. With flat files and client executables I would not
like to try.

Yes SQL is faster.

[Erik]

The answer to the question - always - is it depends. If the business process
is a transactional file to be applied to a master file - creating the next
generation of a master file - and the transactions can be sorted - then I'd
be performing flat files. This scenario removes the need for concern over
locking, etc. if the business process is a batch oriented transaction posting
process. Add into it the tricks of the trade - memory mapped files, etc,
- and you can make a good case for flat files. And with less money and some
3rd party apps like SyncSort and SuperDisk (in memory disk like the RamDisks
of the late 80's) - and you have a batch process that screams. More complex
business processes call for SQL. As always - it depends.

[Jacob Silberstein, MCP]

"Elizabeth Erdman" <eerdman@wausaufs.com> wrote:
>
>I am trying to convince my company that sql has a faster processing speed
>than flat file processing. Is this correct? Does anyone know where I can
>find some pros or cons for both?

For about a couple of months ago, our company decided to move from Btrieve
(a file based database) to SQL Server 7. In the begining I wasn't sure that
it is the right move, but after a couple of months we already see the benfits.
The following is a list of advantages:

1) Reporting, some of the reports that used to take us 5 minutes to preview,
now it takes between 2-4 seconds.
2) Constraints, with file based databases it is very difficult, if possible,
to enforce referential integrity, specify defaults, join tables or specify
a list of valid data (check constraints).
3) File based databases are becoming obsolete, and most of them are not being
supported. Your company will have to upgrade in a couple of years, anyway,
and by that time it will cost them 2-5 times as much (because they will need
to create a very complex application, to just import the data).
4) Support, when you have a problem with SQL Server, your chances of finding
help on the WWW, is 10 times then other not-so-popular databases. In corporate
language it means, less development time, which means lower IT cost.

This is just off the top of my head, but the list is much longer.