Re: [Phpgroupware-developers] SQL garbage in contacts backend

[Markus Kaemmerer]

Alex, 

thank you for the quick response. 

>Indexes shouldve been created upon setup....where yout testing from a
>clean vanilla install or what kind of upgrade path did it follow?

This was a clean installation, but some days old. I do not know, how
old. I recreated my databases yet and found, that there are some
indicis for some tables. I found that phpgw_contact_person has no
index at all, but I can not decide if e.g. the main key person_id
should have an index. This should be carfully verified for all tables.

>Anyhow, this is fixed by adding indexes.

This should not be a big problem and adds overall speed.

>> The first 5 SQL statements are fetching the WHOLE table and the last
>> 3500 bytes SQL statement is nealy self describing :-).  In this
>> statement there are things like: "(CASE WHEN ' ' IS NULL THEN '' ELSE
>> ' ' END)". The SQL builder should suppress such things.
>
>1.- 
>ITs the way its handling the empty strings and all.... whomever you
>appoint to this part of your project should get in touch with me so we
>can work out a solution for sql builder working well for NULLS in
>multiple databases (thats what its trying to automatically fix in that
>case).

I think that this part of the SQL string does not make any sense. This
"(CASE WHEN last_name IS NULL THEN '' ELSE last_name
END))" should work. 

Is there a reason why not add a "nullable => false and default => ''
in database description? Here in my SQL book this definition seems to
be a standard SQL command and should be supported from every database
(we can verify this). 

When the table definition is updates this way, it should not be needed
anymore to test the field against NULL. This is no big perfomance
problem, but reduces complexity quite much.

>I disagreee that this query is unacceptably slow. I would call it
>acceptably unreadable, but not necesarily inneficient (it has to run in
>all databases).

I'll do some additional tests, how slow this statement is. But I need
more test data for this. We will do a profile on sync in the next days
or weeks.

>Communication types and contact types are catalogs (really small ones,
>tops 50 entries). In the actual addressbook code, the whole contents of
>them are cached. So, this is just a 'first hit' performance hit

Because our synchronisition works with XML-RPC calls, this 'first hit'
hits us every call. I do not see a reason, why this tables should be
cached. In addition I think this can lead to unconsistency of the
cached values and the database values.

>Catalog, should be small, executed upon contacts_sql instantiation. You
>can also tell it to lazyly not-cache it, but that would be stupid.

is there a switch or something like this we can set if we get our data
trough IPC and XML-RPC?

>About the CASE and all... first understand why they are there: 
>- We dont have a full name field, the concat and case gets the full name
>if youre asking for it. By the way, if youre trying to sync the full
>name back to phpgw, then you have a problem....:). 

Dirk is working on that, he is fixing the vCard interface at the
moment. Maybe he can say more about this topic.

>Recomendation Marcus:
>
>Get your facts straight before just calling other peoples work
>'garbage'. Ill be the first to admit the design has problems and that
>the way we tackled those problems was not through a KISS design. 

It was not my intention to attac your person or such things. I know,
this is not a simple topic, but we have to do much work to fix the
remaining problems.

>Thx again though for pointing out the indexes problem (chances are thats
>90% of your problem). 

I added some (quick choosen) indici manually with phpMyAdmin, this
gave me a 30% performance boost but does not solved all my problemes.
The sync is working fine, but really slow. There performance problems
are on many parts of phpGW and we have to find a way to strip them
down as much as possible, because I think no user has time to wait 30
minutes before his 300 adresses are synchronized :)

Markus

--
Markus Kämmerer         Team Software Solutions
pro|business AG, EXPO Plaza 1, 30539 Hannover
E-Mail: address@hidden,  Phone.: 0511/60066-0
WWW: http://www.probusiness.de/,    Mobile: 0177/5990932

**********************************************
           address@hidden 2004

*       Halle 1, Stand 3k4 (Magirus Deutschland GmbH)
*       Halle 1, Stand 7f2 (EMC Deutschland GmbH)
*       Halle 6, Stand D46 (Land Niedersachsen)
**********************************************