[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