SQL Overview

PERFECT FIT 7 currently uses FILE SHARING technology to store data in the “Native Data File” (df1)
The Omnis and PERFECT FIT environment can support SQL technology simultaneously.

File Sharing PROS:
Cheap
Easy to setup
Inexpensive to program
Easy to move data files
Proprietary data structure is hard to hack.

File Sharing CONS:
Slow on large data files
Subject to corruption due to network failures
ODBC Access is Read Only

SQL PROS
Fast – A 1 minute report over the network will take 1 second using SQL.
Reliable – Expect to see a reduction in diagnostic errors.
Network independent – Data survives low quality connections, including WiFi.
Works over VPN – Database can be on a remote server with PF installed locally at any location.
Platform independent – Windows and Macintoshes can connect at the same time, no remote desktop needed.
Improved integration – Open structure and universal language makes it easier for other programs to read and process data.
Live backups – Backup while users are connected.
High security – Users cannot copy or delete the data file.

SQL CONS
Increased development costs.

Technology

The difference in technology can be explained this way.

Using 2 computers, think of 1 as the server hosting the data, and the other as the client, where the user sits.

With File sharing, when the client does a complex search, such as Customer Memo Contains the word “sold”, all the customer records and all the fields (data) must be sent over the network to the client computer, and then the client computer filters out the customers to view. This creates lots of network traffic. The network becomes a bottleneck.

With an SQL database, the database is “smart”, as it is a program running on the server. The database receives the instructions to find all customers with the Memo Containing the word “sold”, and only the ones that match this criteria are sent back over the network to the client computer. The actual filtering, or processing, happens on the server, and less data is sent over the network, thus making it much faster.

There is way more than this, but when moving from a file sharing database to SQL this explains the major reason for speed improvement.

This difference in technology will make no change in speed when FINDING a customer due to indexed fields (fast already), but it will be a huge change when running reports that take longer than 5 seconds. I have seen searches on the FTR file with over a million records that took 3 minutes on the PF Data file take 1 second on an SQL file.

Processing (inserts, edits, updates, etc.) happens on the server, not over the network. If the network connection is faulty, a process will complete once started. This dramatically reduces the chances of data file corruption.

Our database is using PostgreSQL, aka Postgres.

Who else is using the Postgres database:

Revised 6/17/24 by HS