Page 1 of 1

Reduce time to open database

Posted: 10.11.2006, 11:18
by leodinei
Hello. Somebody can help me to reduce the time needed to open a large database?
I know if a set Filtered to True this help.
Disable calc Fields? Lookup fields take much time?
Something else?
Thanks.

Posted: 10.11.2006, 11:27
by swierzbicki
Please be more precise.

What Database are you running ? , you can compress the data when running on MySQL.

How optimized is your SQL Query ?

Posted: 10.11.2006, 11:36
by leodinei
swierzbicki wrote:Please be more precise.

What Database are you running ? , you can compress the data when running on MySQL.

How optimized is your SQL Query ?
Ops. Sorry.
I'm using Postgres. I'm using a TZTable. I need all the data for each row, but I need just one row each time. I need navigation(next, prior).

Posted: 10.11.2006, 13:14
by gto
Use a ZQuery

Set Filtered = True

CalcFields are complex as your procedure OnCalcFields. If you can, try to don't use it. If you have a field called total which receive the result from an operation with the fields in same row, use SQL to do it. It's quite more quick.

LookupFields are slow, too. Mostly lookup fields can be rewrited on SQL using joins.

Well, if you can post details about your query (table name, fields in question, etc..) we may help ;)

[]'s

Posted: 10.11.2006, 13:26
by leodinei
The lookup fields use data in memory. I can't use SQL for them. The question is: Lookup is more slow than calc fields?
A Zquery with "select * from table" is more fast than a Ztable?
The table in question is customers. Have 6000 rows.
And I have another table with some extra data, like a master-detail. But the relation is 1-1.
Thanks.

Posted: 13.11.2006, 21:37
by zippo
Another trick is to select as many columns as you can: Instead of

Select * From TBL1

Try to use

Select FLD1,FLD2 From TBL1

It can drammatically improve speed.

Posted: 14.11.2006, 02:25
by gto
leodinei wrote:The lookup fields use data in memory. I can't use SQL for them. The question is: Lookup is more slow than calc fields?
A Zquery with "select * from table" is more fast than a Ztable?
The table in question is customers. Have 6000 rows.
And I have another table with some extra data, like a master-detail. But the relation is 1-1.
Thanks.
Well, lookups and calculated fields are quite different. Calc fields are complex as the commands that OnCalcFields procedure holds. If it's a simple "field receive field", it will be almost transparent on speed. But I have already seen a HUGE procedure, reading values from ini files, doing locates and creating objects into OnCalcFields. And yes, it was drammatically slow.

Lookups are not *so* slow. You may take lookups as a filed that search for it's value on another table. Think in something like a locate, unsing the keyfield, into the lookup dataset. If the field you are looking for is indexed, that looukup may be very fast. And if depends on text search or non indexed fields, it may be very slow. Each case is a case on lookups. Less is better, always.

ZQuery with "select * from" and ZTable will be almost the same thing. The trick is that with query you can only select the fields that you will use, or the big trick, use keywords to improve the results. Almost all lookups (data -> data, not in memory) can be replaced by a join clause, with a big performance result.

6000 rows is VERY small. Tables with 150.000+ rows open almost instantly here. How many time your takes to be opened?

If you can post some code/example, or if it's part from a big project, isolate that case, and post something, then we may help you better :)

Posted: 16.11.2006, 19:58
by leodinei
Sorry for the delay.
So I have some great problem.
I think the problem is with the network or database config.
My table with 6000 rows take > 30s to load in the network.
My idea: with I have a table with the key and another in master-detail with all the data. When a navigate in the master, the detail will load the new data? So I have just on big record in the memory?
Thanks

Posted: 17.11.2006, 06:12
by fduenas
what is your zeos version and what database server are you using and what version? and what dll version ?

Posted: 17.11.2006, 11:11
by leodinei
fduenas wrote:what is your zeos version and what database server are you using and what version? and what dll version ?
Zeos: 6.5.1 alpha CVS as of 13/10/2005
Postgres 8.1.4
libpq.dll 8.1.4.6142
Thanks.