How much of a speed improvement can be had by using SQL as a back end in a multi-user environment? I am sure it depends on the application, but just in general.
-
-
Re: Multi User and SQL
"In general," I don't think there is a meaningful (general) answer. It would depend on a whole lot of things. For example, using "active link" tables could be slower, again depending on the app. And how many users (at the same time) are we talking about? What are the users typically doing? How large are the tables? In many cases, dbf tables still make more sense, and in many cases typical operations are very fast, or plenty fast enough. It all depends on so many things.
Raymond Lyons -
Re: Multi User and SQL
If you "move" the logic behind some elaboration to the database server (with stored procedures) you can have some speed improvement, because there's no need for the data of the tables to be sent to the client for the elaboration, but only the resulting set of the procedure. The cons: if you change your backend db server you must rewrite all the procedures...
Sergio*** 38911 basic bytes free ***Comment
-
Re: Multi User and SQL
Another one... generally speaking, if you create a view (on db backend), the database generate also the best access path to read the tables. If you execute a query by code (running a 'SELECT x FROM y'), the access path will be rebuilt every time you execute it. With many tables involved (joined) and recurrent queries, the difference can be significant.
Remember also that sometimes it is better to create a function on the db to perform some management than elaborate by code the same records. If you use (with attention) the db triggers you can save a lot of time avoiding the repeating of the same code in all the forms that performs some insert or update operation...
In general: more logic in the db means more performances but the portability across db backend will suffer (for example you can write triggers on some db that works at record level, but in others only at "set" level - the entire set involved in the sql statement executed - and the code will be necessarly different between the two), more logic on the application means less performances but more db portability.
If you know in advance that you will never change the db server, you can think to optimize the performance in that way (if in the future you change the frontend the work involved will be less).
Hope it helps.
Ciao.*** 38911 basic bytes free ***Comment
-
Re: Multi User and SQL
I would suggest to use SQL databases for critical operations due to several reasons:
1) Querys are usually processed on the server not in the client, the client only gets the results. This gives you a speed gain. However only Alpha Five Server gets the data in/out directly from SQL database, the desktop edition uses .dbf tables like temp tables for SQL database.
2) You get rid of indexes problems, SQL are usually mission critical and most SQL versions recover automatically from shut downs.
3) It is possible to implement transactions which ensure the operations are completed all or nothing but never on stay on the middle.
4) Backup may be created online without having to stop database
5) There are some more handy features like the ability to go back to a certain time, etc.
Jorge.Comment
-
Re: Multi User and SQL
I have adopted Alpha Front end and MySQL backend for the following reasons:
a. interaction between different applications (sharing data) is simplified.
b. licensing of apps shared by various users remotely located (geographically) is simplified
c. you have access to more than one reporting application
there are other reasons but the above, to me, are important.Comment
Comment