[Vtigercrm-developers] Calendar ListView Queries

[Błażej Pabiszczak]

The entire Calendar should be rewritten from the scratch. One query
isn't a problem because there are various mechanisms for various
triggers encoded in it [it was optimized separately for loading of the
first view, separately for changing of users after AJAX and separately
for changing of a month/week/year]. Once we spent 3 days in this
Calendar and the optimizations that we performed were as follows: a
change of functions responsible for downloading data, a change of
queries, an optimization of keys and tables [we probably copied a delete
field to an activity table in handler so the main table didn't have to
be combined]. However, all we did were just half measures. 

The Calendar also proves that no one ever deployed Vtiger in a large
company that uses calendars. Vtiger has also a bad practice of
overwriting root libraries or changing data on the fly [that's why when
a huge calendar is generated, operations on html are performed again]. 

We have mentioned a problem with the Calendar many times, but no one
took care of it. If you need, I can generate a base for Vtiger 6.2 with
any number of records so you can see the scale of the problem. We
rewrote the Calendar in YF and it uses the most up-to-date fullcalendar
library, has integration with CalDAV and half of the code and it works a
lot faster. Unfortunately, our Calendar cannot be copied to Vtiger 6.2
in a relatively simple way because we optimize the engine for
functionalities. I think it might be a good idea to look at our
solutions because some of them may seem worth adding to Vtiger 6.3. 
---

Z poważaniem / Regards 

BŁAŻEJ PABISZCZAK 
_Chief Executive Officer_ 
M: +48.884999123
E: b.pabiszczak at yetiforce.com 
-------------------------

We created an innovative open source project called YetiForceCRM. You
can test it here [2], download [3] it for free or read its documentation
[4]. Follow us on Twitter [5] to get real-time info about new
functionalities and articles. 

W dniu 2015-05-18 13:10, Alan Lord napisał(a): 

> On 18/⁠05/⁠15 10:35, Vikas Jain wrote: 
> 
>> Alan,
>> 
>> It was added to force the mysql use the table index, can you try the
>> same query by removing the indexes added on activitytype and
>> modifiedtime and comment the code and see if the indexes are having any
>> influence on the query.
> 
> Hi Vikas,
> 
> This was quite interesting...
> 
> The activitytype index seems to make negligible difference to the query time.
> 
> I am a bit confused by the modifiedtime index however...
> 
> When I ran the query this morning (with the "AND vtiger_activity.activityid > 0" line included) the query ran reasonably fast with an average of around 0.4 seconds.
> 
> I then dropped the modifiedtime index and tried running the same query again. The query timed-out and never finished.
> 
> I then re-created the modifiedtime index, which took about 12 seconds to build, and tried the query again. It still times out.
> 
> I did notice that *after* mysql had said the index was created the Cardinality figure was very low (like a few hundred) and after several minutes it appeared to have increased (it's now 2673279). But the query was still not completing.
> 
> If I remove the "AND vtiger_activity.activityid > 0" clause from the query it returns nice and fast. If I include it again, the query times out.
> 
> So I am a bit confused by this index on the modifiedtime column. It almost seems like MySQL might have spent the weekend building some kind of index in the background which would then explain why it was fine before I deleted the index, and why it isn't fine after re-building it this again morning. But this would not seem to be the way MySQL works from what I have read so I am confused.
> 
> Nevertheless, I still do not see why this line is needed at all:
> 
> "AND vtiger_activity.activityid > 0"
> 
> because this join makes it totally redundant IMHO
> 
>> INNER JOIN vtiger_crmentity
>> ON vtiger_activity.activityid = vtiger_crmentity.crmid
> 
> And, more to the point, if I remove it from the query it runs fast. With it, it is too slow.
> 
> Cheers
> 
> Al
> 
> _______________________________________________
> http://www.vtiger.com/ [1]
 

Links:
------
[1] http://www.vtiger.com/
[2] https://test.yetiforce.com
[3] https://github.com/YetiForceCompany/YetiForceCRM
[4] https://yetiforce.com/en/documentation.html
[5] https://twitter.com/YetiForceEN
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20150518/017cd94d/attachment.html>