Hi,
Som basic tracks:- really: disable the options concerning the
use of statistics (the statistics gathering). This server tuning (use of statistics) theoretically improve, help the query optimizer; it's true, BUT this feature becomes only effective, only on huge bases (Gb), and only after having collected a lot of statistics (i.e. only when the distributions became stable.). Otherwise, before having collected a large number of query statistics, this query statistics slows everything down, for a long time before becoming effectively useful (this is globally valid for all databases).
- check if there are
"full table scan",
"the devil" of databases of course (among the IBX components, there is a Monitor component; it allows you to monitor from the client application, the execution plan of all the queries. Otherwise, I think it should be possible to do this on the database side) ==> if that's the case, add missing indexes or recreate them.
A well known reminder, concerns strings: with variable length strings, we use VARCHAR, instead of CHAR (length_of_the_longest_possible_string).
nb: I see you're doing
"SELECT * ":
- If you use small tables that don't grow much, it's okay.
- If you use large tables, you should find a way to launch filtering queries (adding a way to create "WHERE" or "HAVING" dynamic filters from the client sides's UI, returning only a subset of the table: the network and the clients will be "happy".
One last point to check: If you then apply filters over your
"SELECT *" to filter the records (by changing a Filter property of a T{IBX}TableSet, for example), the client will do a understandably
`fetch all` for the first
"SELECT *" (but remember: il will slow down the application, as the\each table grows; and all the more so, if you're doing it on multiple tables at the same time), and it will also do a "worried
`fetch all`" before each client-side application change of its filter, "worried
`fetch all`" in order to be sure, that the client application will have all the lastest updated records on the client workstation, before launching its loops on the client side to filter all the existing records ==> T{IBX}Table must be replaced, as often as possible, by T{IBX}Query.