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

[Alex Borges]

El vie, 27-02-2004 a las 04:29, Markus Kaemmerer escribió:
> Alex Borges <address@hidden> wrote:,
> 
> >person_id is a primary key so the index should be implicitly created (or
> >thats the rationale, if this is not the case, then we should change this
> >to reflect the right thing).
> 
> in ./phpgwapi/setup/tables_current.inc.php is no primary key defined.
> There is an index to person_id defined, but in my fresh installation
> (MySQL) the table has no index.

Well thats ugly....mhm... it should be an index (im remembering)...
cause its a 1-1 relationship to contacts. The index should be there (in
pgsql it is created) and in orgs and org_person.

> 
> >> 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. 
> >Yes, you are right.... i got mislead myself. This is actually for the
> >full name implementation since the table has no full name field. 
> 
> Is this a bug in the SQL builder or in the application and the SQL
> builder does not remove unnecessary tokens?

Not shure. But im pretty shure its not gratuitus. Im investigating.

> 
> >> 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). 
> >
> >Most is handled the right way, i dont have the details right now.
> 
> Maybe you should update all tables to the current and correct
> definition to solve this and remove all unnecessary tests for NULL. 
> 

Ill look into this

<Snip>
> I don't think it is a good idea to cache databases (even small ones)
> in session data. See my other mail about performance tests. This test
> show, that this does not speed up. Maybe you should do your own test
> regarding this topic. 

will do, it may very well be the case.

> 
> I think it would be better to let the database cache such things,
> databases are made for this. Another problem may be database
> inconsistency. 
> 
Yes, mishandling of this would make it inconsistant. However, thats not
a problem in this case, although it ammounts to more code.

> >> >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?
> >
> >Yes. Upon instantiation of sql_contacts you can tell it not to prefetch
> >the catalogs. 
> 
> How do I do this?
> 
Send false to contacts_sql upon instantiation. Youll need to look at
read_sessiondat() method to look at what we are doing there.

Youll need to lazyly fill those attributes so that later queries work in
contacts_sql. This means the queries will still need to be made.

Still, if youre thinking you can get away with only one query joining to
catalogs for fetching of contact communication data, notes data or
addresses, you best look into the design further. There is NO way to
accomplish this in a generic way.

> >Although im not shure thats the behaviour you want.
> >Catalogs are cached in the session cache. Those queries should be
> >executed only once per whole phpgw session. Actually, their prefetching
> >could be done in the login hook.
> 
> I think that I saw theses SQL statements more than one in my log in a
> synchronization session. Our XML-RPC implementation holds one phpGW
> session for a complete synchronization process, so this caching should
> occur only once. 

This is something we should fix.... the only way to get rid of the
multiple queries to the catalogs is to cache them. The cache is not
working, lets fix the cache.

> 
> >ITs like this. We have no full_name in the person table. Just a
> >first_name, last name, middle name...etc. This query concatenates them
> >when you ask for the full_name field in any of the functions wanting to
> >get anything from contacts
> 
> Dirk found, that vCard export only works, when there is a first and a
> last name. But I have many contacts in my Palm where I have only last
> names. I don't now, if Dirk has changed this until now. 

Yes, we carried this from 14's vcard implementation. The standard is not
limiting in that way, this limitation can be removed. If anyone has
anything to say about this, speak now, or forever hold your peace.

> I think that enterprise applications like groupware systems should be
> written in a real programming language for a real application server,
> but that's another topic and now it is a bit late to translate
> phpGroupware in JavaGroupware or groupware.NET :-).
> 
Lets not go into that.

> AFAIK MySQL from 4.0 and the other databases are supporting sub
> selects in queries, but this does not help, because we need MySQL 3.x
> support, right? But most of the 'normal' queries are optimized from a
> good database so they should be not much slower than queries with sub
> selects.
> 

Thats just the thing.... almost no queries to contacts are 'normal',
much less the ones used for syncing. You want to get rid of that? send
no full name to the backend, split first_name, last_name yourself....

And yeah, behold the day when we can throw mysql 3 out the door.

> >Well, we want groupware. And all that, makes for a big ass database, and
> >potentially, a need to manage large ammounts of memory in the php app
> >(look at acl fetching when you login). 
> 
> My be it would be much better to use shared memory to cache things
> like acl and little tables than sessions. This would help in really
> big installations a lot more than caching in session variables.

This is a great idea! Yes, this has been in my mind for some time. But
im not shure of how to go on implementing it.... i havent looked into
it. 
But hey, shure, a class to set_shm_var($name,$data) get_shm_var($name)
would really be cool (there is a lot of data fetched from the database
that is fetched for all users all the time).

<snip>
> I see the major problem in the overhead for every single request (see
> other mail). 

Yes, of course memmory per hit and latency (complexity) per hit are the
critical variables to tame.

> You now your database at best, so you should decide which field needs
> and index. Adding more index to fields that are not used only slow
> down things. 
> 

If i knew it best, and needed no help, the indexes would already be
there now wouldnt they? If you achieved 30% performance increase by
adding indexes, then we best as hell know which indexes where those. 

Other things may come out then, like the indexes being declared but not
created for your particular database.... 


> >Another tip....use php4 sessions, not database sessions. Db sessions
> >ammount to a, potentially large,  extra hit to the database.
> 
> In my measurements this doesn't make a noticeable difference.
> 
> I made a little database diagram for all contact tables (see
> attachment). You can see, that contact_org_person, contact_org and
> contact_person does not have an relation to other databases (reverse
> engineered from my current installation).
> 

Didnt get any attachment. Send off list or post a link...:)...

I want to take this oportunity to state that we all are very interested
in seeing sync come to life in the most perfect way possible. I want to
check out what we can do with the xml-rpc in the phpgroupware side.

For this, i need to see deeper into your implementation, so that we can
put in a test farm here and see where can we start trimming the problems
that are making vcard syncyng so slow.

As ive said, it may have performance problems, but 300 contact == 30
minutes (or 15 minutes, or 10 or 5) is not acceptable for anyone. If you
can import a vcard list of 600 in less than a minute, then the
bottleneck is  nowhere near the core of the contacts backend.

That means that the import/xport code needs to be worked for the xml-rpc
interface. We can help do that, no problem, im gonna study the design of
the sync stuff closer to see where is the bottleneck (the addressbook is
slow, yeah, maybe, but there is a bottleneck somewhere, we need to find
that one before redesigning the addressbook wouldnt you say?).

Think about how can you enable me to do testing that will render
positive results for your sync project. Tell me the details of what you
are doing so we can build a client xml-rpc script to do performance
tunning and testing here, we can then approach the best solution.


> 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)
> **********************************************
> 
> ______________________________________________________________________
> _______________________________________________
> Phpgroupware-developers mailing list
> address@hidden
> http://mail.gnu.org/mailman/listinfo/phpgroupware-developers