[Vtigercrm-developers] calendar optimization

Prasad prasad at vtiger.com
Tue Aug 20 12:42:40 UTC 2013


Adam,

readAppointment function scope is limited to Vtiger 5, might not be used in
Vtiger 6.

Regards,
Prasad

*Connect with us on: *Twitter <http://twitter.com/vtigercrm> *I*
Facebook<http://www.facebook.com/pages/vtiger/226866697333578?sk=wall>
 *I* Blog <https://blogs.vtiger.com/>* I*
Wiki<http://wiki.vtiger.com/index.php/Main_Page>
 *I *Forums  <https://discussions.vtiger.com>*I*
Website<https://www.vtiger.com/>


On Wed, Aug 14, 2013 at 9:05 PM, Adam Heinz <amh at metricwise.net> wrote:

> The fundamental issue that I'm trying to solve is that the LEFT JOIN [4]
> against vtiger_recurringevents requires the WHERE OR condition -- now you
> have to check both vtiger_activity.date_start [5]
> OR vtiger_recurringevents.recurringdate [6].  My best idea right now is to
> essentially make ALL events recurring with with recurringtype = Daily,
> repeating once.  This gets you pretty close to being able to use an index
> on vtiger_recurringevents to do the bulk of the filtering.  Unfortunately,
> this bumps up against a second issue.
>
> It is currently possible to have a recurring event, say 2-4pm repeating
> every Monday.  It is also possible to have a multi-day event, say 2pm
> Monday to 4pm Tuesday.  It does not appear to be possible to have a
> recurring multi-day event.  Though vtiger_recurringevents has a field to
> hold the start date of the recurrence, it does not have a second date field
> to hold the end date of the recurrence.
>
> The end goal here is to have a single table that holds the start and end
> dates necessary to determine if a given event (or recurring instance of
> that event, or part of the multi-day event) should be displayed on a
> calendar.
>
> I did briefly attempt using UNION to split the query into recurring and
> non-recurring halves, but there is a surprising amount of code that
> modifies the SQL after the fact, so I abandoned that effort temporarily.  I
> haven't ruled it out yet.
>
> [4]
> http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/6.0.0/modules/Calendar/Appointment.php#L93
>
> [5] mysql> desc vtiger_activity;
> +------------------+--------------+------+-----+------------+-------+
> | Field            | Type         | Null | Key | Default    | Extra |
> +------------------+--------------+------+-----+------------+-------+
> | activityid       | int(19)      | NO   | PRI | 0          |       |
> | subject          | varchar(100) | NO   |     |            |       |
> | semodule         | varchar(20)  | YES  |     | NULL       |       |
> | activitytype     | varchar(200) | YES  | MUL | NULL       |       |
> | date_start       | date         | NO   | MUL | 0000-00-00 |       |
> | due_date         | date         | YES  | MUL | NULL       |       |
> | time_start       | varchar(50)  | YES  |     | NULL       |       |
> | time_end         | varchar(50)  | YES  |     | NULL       |       |
> | sendnotification | char(3)      | NO   |     | 0          |       |
> | duration_hours   | varchar(200) | YES  |     | NULL       |       |
> | duration_minutes | varchar(200) | YES  |     | NULL       |       |
> | status           | varchar(200) | YES  | MUL | NULL       |       |
> | eventstatus      | varchar(200) | YES  | MUL | NULL       |       |
> | priority         | varchar(200) | YES  |     | NULL       |       |
> | location         | varchar(150) | YES  |     | NULL       |       |
> | notime           | char(3)      | NO   |     | 0          |       |
> | visibility       | varchar(50)  | NO   |     | all        |       |
> | recurringtype    | varchar(200) | YES  |     | NULL       |       |
> +------------------+--------------+------+-----+------------+-------+
>
> [6] mysql> desc vtiger_recurringevents;
> +---------------+-------------+------+-----+---------+----------------+
> | Field         | Type        | Null | Key | Default | Extra          |
> +---------------+-------------+------+-----+---------+----------------+
> | recurringid   | int(19)     | NO   | PRI | NULL    | auto_increment |
> | activityid    | int(19)     | NO   | MUL | 0       |                |
> | recurringdate | date        | YES  | MUL | NULL    |                |
> | recurringtype | varchar(30) | YES  |     | NULL    |                |
> | recurringfreq | int(19)     | YES  |     | NULL    |                |
> | recurringinfo | varchar(50) | YES  |     | NULL    |                |
> +---------------+-------------+------+-----+---------+----------------+
>
>
>
>
> On Wed, Aug 14, 2013 at 10:35 AM, Prasad <prasad at vtiger.com> wrote:
>
>> Adam,
>>
>> Interesting - can you share the details of changes that is required for
>> based on Vtiger 6 Beta.
>>
>> Regards,
>> Prasad
>>
>> *Connect with us on: *Twitter <http://twitter.com/vtigercrm> *I* Facebook<http://www.facebook.com/pages/vtiger/226866697333578?sk=wall>
>>  *I* Blog <https://blogs.vtiger.com/>* I* Wiki<http://wiki.vtiger.com/index.php/Main_Page>
>>  *I *Forums  <https://discussions.vtiger.com>*I* Website<https://www.vtiger.com/>
>>
>>
>> On Wed, Aug 14, 2013 at 1:44 AM, Adam Heinz <amh at metricwise.net> wrote:
>>
>>> We do a ton of scheduling here, so our calendars get hammered all the
>>> time.  Some of our larger stores are starting to see performance problems
>>> due to various calendar queries running full index scans.  The most
>>> prevalent examples are readAppointment [1][2] and getEventList [3].  Due to
>>> all the OR conjunctions in the WHERE clauses, MySQL is unable to determine
>>> a useful index, even after I've removed all of the CAST/CONCAT calls.  Is
>>> anyone else having performance problems with their calendar?  I have some
>>> fairly drastic ideas on how to solve the problem.  ;)
>>>
>>> [1]
>>> http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/5.4.0/modules/Calendar/Appointment.php#L61
>>> [2]
>>> http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/6.0.0/modules/Calendar/Appointment.php#L61
>>> [3]
>>> http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/5.4.0/modules/Calendar/calendarLayout.php#L1357
>>>
>>>
>>> _______________________________________________
>>> http://www.vtiger.com/
>>>
>>
>>
>> _______________________________________________
>> http://www.vtiger.com/
>>
>
>
> _______________________________________________
> http://www.vtiger.com/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20130820/27fb89de/attachment.html>


More information about the vtigercrm-developers mailing list