Sky Software
Help and User guide
 
×
Menu
Index

Searching SQL

Accfin Sky uses an SQL database, specifically PostgreSQL.
 
An SQL database is a query based database unlike a flat relational database like Foxpro which is the database used in Pro Series. The SQL database is more conducive to uses across LAN, WAN and more specifically cloud based applications.
 
A database contains many separate tables that are related to one another.
 
When the application is launched a connection only is made with the database, no data is loaded (except for the required data for logon , credentials, access)
 
When a form is loaded within the Sky application, only a subset of the data called a foundset of the table data is loaded, initially only 200 records. Any other data from related tables for these records are also loaded. Depending on the connectivity speed there may be a slight delay while the screen refreshes.
 
Where there are many more records in the table and you do a search or scroll down passing the last loaded record, the application than requests and loads the remainder of the records in batches of 200 adding to the data already loaded. This can be done until all the records in the system are loaded.
 
When a refresh of the form is required or database calculations need updating, ALL of the now loaded records and its related table info are then re-queried and reloaded from the database server. Clearly the more records you have loaded will have an impact on the connectivity speed.
 
Therefore where you have large databases in play its best to work with subsets of the data like partner and manager etc
 
For example let ’s say there is a client base of 1000 clients.
When the form initially loads, the 1st 200 clients’ information (with all related info, links, tax, sec, wip debtors etc) will be queried from the server and loaded on the form in a foundset.
 
Any refreshing or database calculations are performed only on these 200 records. Queries to the database server is only on these 200 records.
 
When you then select a client not in this 200 records either by search or scroll down, than the rest of the clients are loaded into the foundset.
 
You now have 1000 clients loaded on the form’s foundset.
 
Any refreshing or database calculations is now performed on all 1000 records and its related info. Queries to the server has to be on ALL 1000 records.
 
A better and more streamlined approach to using the system is to divide the clients into groups either by partner or manager or both.
 
Eg 1000 clients to 4 partners / Managers
A  with 300 client
B with 250 clients
C with 200 clients
D with 250 clients
 
When working with the data select which Partner/manager you want to work on.
This will than load only the data for that Partner/manager into the foundset. All searches/scrolling will then only apply to this subset.
 
At any time the max records loaded will be 300 as per above when partner A is selected.
 
When a Partner/manager is changed the new foundset will then be loaded replacing the old data and not adding to it.
 
All refreshing / re-querying again will be only on this subset, hence speeding up the processing and traffic to and from the server.
This amount of records (200 – 300) together with its dependant related data is the optimal amount for better performance in regards to speed and processing.
 
The above example of the client file and form applies to all forms where the list of clients is available for selection.
 
It is better to use the predefined filtering on partner/manager or even client types (Company, cc, individual trust) to limit the subset of clients that you are working on thereby reducing the overhead of traffic and data transfer to and from the server.