[Vtigercrm-developers] Slow performing query

[Lee Valentine]

Hi Ding,

That was just applied to 5.1rc2.  However I did the query originally in 5.0.4.

Thanks,
Lee


2009/7/1 ding jianting <dfar2008 at gmail.com>:
> Hi,Lee
> What is version of your vtigerCRM?
>
> On Wed, Jul 1, 2009 at 8:21 AM, Lee Valentine <lee at leev.net> wrote:
>>
>> Hey Guys,
>>
>> We were having performance issues with the crm taking ages to load so
>> I had all the queries being executed printed to the screen with the
>> duration each query was taking.  I found:
>>
>> select distinct(setype) from vtiger_crmentity where crmid in (select
>> parent_id from vtiger_troubletickets)
>> 106.894966125 seconds.
>>
>> I tracked this down to modules/CustomView/CustomView.php line 1212 (in
>> function getSalesRelatedName)
>> $sql = "select distinct(setype) from vtiger_crmentity where crmid in
>> (select ". $adb->sql_escape_string($fieldname)." from ".
>> $adb->sql_escape_string($tablename).")";
>>
>> I replaced it with the following:
>> $sql = "select distinct(setype) from vtiger_crmentity c INNER JOIN
>> ".$adb->sql_escape_string($tablename)." t ON
>> t.".$adb->sql_escape_string($fieldname)." = c.crmid";
>>
>> And received the result:
>> select distinct(setype) from vtiger_crmentity c INNER JOIN
>> vtiger_troubletickets t ON t.parent_id = c.crmid
>> 0.0270490646362 seconds.
>>
>> This has given our crm a MAJOR performance increase.
>>
>> Thanks,
>> Lee
>> _______________________________________________
>> Reach hundreds of potential candidates - http://jobs.vtiger.com
>
>
>
> --
> ________________________________________
> 上海易客CRM 丁远
> 地址：上海市徐汇区零陵路零陵路631号爱乐大厦
> 电话：021-64876448        传真:021-64876441
> 手机：(0)13701696656     Email:dfar2008 at gmail.com
> -----------------------------------------------------------------------
>
> _______________________________________________
> Reach hundreds of potential candidates - http://jobs.vtiger.com
>