If you want to do both, high speed logging of information with complex queries you can use a combination of both. Typically you make Citect log the data to the fast dBASE devices in real time, then use a second Citect which will read the dBASE devices back and then spool them into the SQL database. You then can do complex queries on the logging data without causing a performance problem for Citect logging the real time data to the SQL database.

Our performance tests have shown that dBASE devices are significantly faster when you are logging or appending new data to a database. The native dBASE devices when appending to a database are up to 50 times faster than SQL servers. This performance is because the dBASE devices can write directly to the dBASE files with none of the high quality transaction processing supplied with SQL servers. When new records are appended to SQL database the SQL server will create a transaction so that none of the database tables can get into a corrupted state. This provides a very secure database system while slowing down the performance.

The performance of a query will vary greatly depending on the complexity of the query, the size of the database tables and the performance of the SQL server. Generally SQL servers will give increased performance when the disk I/O associated with a query is significant. This is as all disk I/O is local to the server while with dBASE devices all records must be read across the network. If the database table is small or the query is very simple then the performance advantage of the SQL server may not outweigh the extra overhead of calling to the SQL server. Typically SQL servers would be required when you are searching for data in database tables containing thousands of records. If you have a recipe system containing a few hundred records then dBASE devices may give you better performance.

You may also want to use SQL servers for other reasons. For example the native dBASE devices have limitation which includes only allowing a single index on each table and only supporting the string data type. By using SQL servers you can use any of its internal indexes to speed up database queries. Also using a SQL servers more complex data types you may also improve the performance of your queries.

Our performance tests have shown that appending to SQL devices is very slow as each new record will be inserted as a transaction. This slow rate of appending records can have a degrading effect of the performance of a Citect system. For example if you log your alarms to a SQL server then the alarm server must incur this slow response time from the SQL server. Because of this we don't recommend you log alarms or other real time data directly to SQL servers. You may use the technique of logging the data to a dBASE device and then using a second Citect to read the dBASE tables and spool them into the SQL server. This will move load to the second Citect and so not effecting the performance of the Alarm server.