[Phpgroupware-developers] SQL garbage in contacts backend

[Markus Kaemmerer]

Hi,

while working on synchonization support we found, that sync of 300
Items from my PocketPC (connected through USB) to my local phpGW
installation takes about 30 minutes (the same with ActiveSync and
Outlook takes about 30 seconds). 

One reason for this is, that the contacts backend. During the tests we
found, that there are the indizis missing on nearly all tables. An
later I found that for one vCard export there the following SQL
statements are executed.

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.

These things sould be fixed before phpGW can be used in larger
environments or with synchronizing. 

--- 

SELECT contact_comm_descr.comm_descr_id AS comm_descr_id,
contact_comm_descr.descr AS comm_description,
contact_comm_descr.comm_type_id AS comm_type FROM
phpgw_contact_comm_descr AS contact_comm_descr

SELECT contact_comm_type.comm_type_id AS comm_type_id,
contact_comm_type.type AS comm_type_description,
contact_comm_type.active AS comm_active, contact_comm_type.class AS
comm_class FROM phpgw_contact_comm_type AS contact_comm_type

SELECT contact_addr_type.addr_type_id AS addr_type_id,
contact_addr_type.description AS addr_description FROM
phpgw_contact_addr_type AS contact_addr_type

SELECT contact_note_type.note_type_id AS note_type_id,
contact_note_type.description AS note_description FROM
phpgw_contact_note_type AS contact_note_type

SELECT contact_types.contact_type_id AS contact_type_id,
contact_types.contact_type_descr AS contact_type_descr,
contact_types.contact_type_table AS contact_type_table FROM
phpgw_contact_types AS contact_types

SELECT contact_central.owner AS owner FROM phpgw_contact AS
contact_central WHERE ((contact_central.contact_id = 233))

SELECT contact_central.contact_id AS contact_id,
contact_central.access AS access, contact_central.owner AS owner,
concat((CASE WHEN first_name IS NULL THEN '' ELSE first_name END),
(CASE WHEN ' ' IS NULL THEN '' ELSE ' ' END), (CASE WHEN middle_name
IS NULL THEN '' ELSE middle_name END), (CASE WHEN ' ' IS NULL THEN ''
ELSE ' ' END), (CASE WHEN last_name IS NULL THEN '' ELSE last_name
END)) AS per_full_name, contact_person.first_name AS per_first_name,
contact_person.middle_name AS per_middle_name,
contact_person.last_name AS per_last_name, contact_person.suffix AS
per_suffix, contact_person.prefix AS per_prefix,
contact_person.birthday AS per_birthday, contact_person.pubkey AS
per_pubkey, contact_person.title AS per_title,
contact_person.department AS per_department, contact_person.initials
AS per_initials, contact_person.sound AS per_sound,
contact_person.active AS per_active, contact_person.modified_by AS
per_modby, contact_person.modified_on AS per_modon, contact_org.name
AS org_name, contact_addr.contact_addr_id AS key_addr_id,
contact_addr.add1 AS addr_add1, contact_addr.add2 AS addr_add2,
contact_addr.add3 AS addr_add3, concat((CASE WHEN contact_addr.add1 IS
NULL THEN '' ELSE contact_addr.add1 END), (CASE WHEN contact_addr.add2
IS NULL THEN '' ELSE contact_addr.add2 END), (CASE WHEN
contact_addr.add3 IS NULL THEN '' ELSE contact_addr.add3 END)) AS
addr_address, contact_addr.postal_code AS addr_postal_code,
contact_addr.city AS addr_city, contact_addr.state AS addr_state,
contact_addr.country AS addr_country, contact_addr.preferred AS
addr_preferred, contact_addr_type.description AS addr_description,
contact_comm_descr.descr AS comm_description, contact_comm_type.type
AS comm_type_description, contact_comm.comm_data AS comm_data,
contact_note.contact_note_id AS key_note_id, contact_note.note_text AS
note_text, contact_note_type.description AS note_description,
contact_others.other_value AS other_value, contact_others.other_name
AS other_name FROM phpgw_contact AS contact_central LEFT  JOIN
phpgw_contact_note AS contact_note ON contact_note.contact_id =
contact_central.contact_id LEFT  JOIN phpgw_contact_note_type AS
contact_note_type ON contact_note_type.note_type_id =
contact_note.note_type_id LEFT  JOIN phpgw_contact_addr AS
contact_addr ON contact_addr.contact_id = contact_central.contact_id
RIGHT  JOIN phpgw_contact_addr_type AS contact_addr_type ON
contact_addr_type.addr_type_id = contact_addr.addr_type_id LEFT  JOIN
phpgw_contact_comm AS contact_comm ON contact_comm.contact_id =
contact_central.contact_id LEFT  JOIN phpgw_contact_comm_descr AS
contact_comm_descr ON contact_comm_descr.comm_descr_id =
contact_comm.comm_descr_id LEFT  JOIN phpgw_contact_comm_type AS
contact_comm_type ON contact_comm_type.comm_type_id =
contact_comm_descr.comm_type_id LEFT  JOIN phpgw_contact_others AS
contact_others ON contact_others.contact_id =
contact_central.contact_id INNER  JOIN phpgw_contact_person AS
contact_person ON contact_person.person_id =
contact_central.contact_id LEFT  JOIN phpgw_contact_org_person AS
contact_org_person ON contact_org_person.person_id =
contact_person.person_id LEFT  JOIN phpgw_contact_org AS contact_org
ON contact_org.org_id = contact_org_person.org_id WHERE
contact_central.contact_id = 233 




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)
**********************************************