Query Optmization Issue

SuiteCRM Forum - English Language
1

DECLARE @topCount INT SET @topCount = ‘’ SELECT TOP (@topCount) sbm_financialaccount.,sbm_financialaccount_cstm. FROM sbm_financialaccount LEFT JOIN sbm_financialaccount_cstm ON sbm_financialaccount.id = sbm_financialaccount_cstm.id_c WHERE sbm_financialaccount.id = N’’ AND sbm_financialaccount.deleted=’*****’

This Query is take to much time how to Optimize above mention query please help me. It is dynamically generated by suitecrm.

2

Welcome to the community, @azharambitsoftware !!
In order to optimize a query you need to identify:

  • How many data are fetched by table
  • Which fields are defined on the JOINs and WHERE clauses
  • Possible corrupted table which may cause slowness on fetching data
  • Database optimization configuration. It seems you are working on MSSQL, right?
    You need to double check if the fields specified on the JOINs and WHERE clauses have indexes properly configured.
3

Hey
i wrote this function a long time ago. it filters out the extra joins if they are not being used in the where they are not included in the join. This is upgrade unsafe code but it has been doing the job for a couple of years

    public function getTotalCount($main_query)
    {
        if (!empty($this->count_query)) {
            $count_query = $this->count_query;
            $count_query2 = $this->count_query;
        } else {
            $count_query = $this->seed->create_list_count_query($main_query);
            $count_query2 = $this->clean_count_query($count_query);

            $GLOBALS['log']->error("Orignal Count Query: ".$count_query);
            $GLOBALS['log']->error("Modified Count Query: ".$count_query2);

        }
        $result = $this->db->query($count_query2);
        if ($row = $this->db->fetchByAssoc($result)) {
            return $row['c'];
        }
        else{
            $result = $this->db->query($count_query);
            if ($row = $this->db->fetchByAssoc($result)) {
                return $row['c'];
            }
        }
        return 0;
    }

    public function clean_count_query($query){
        $query=strtolower($query);


        $before_where=explode("where",$query);

        $orig_part=$before_where[0];
        $end_part=str_replace($orig_part,'',$query);

        $from=explode("from",$orig_part)[1];
        $select=explode("from",$orig_part)[0];

        $all_froms=explode("left join",$from);


        $new_froms=[];
        $to_include=array_fill(0,count($all_froms),true);
        for($i=count($all_froms)-1;$i>=1;$i--){
            $sub_from=$all_froms[$i];
            $parts=explode(' ',trim($sub_from));

            $next_subs="";
            for($j=$i+1;$j<count($all_froms);$j++){
                if($to_include[$j]){
                    $next_subs.=" ".$all_froms[$j];
                }
            }
            if(strpos($end_part,$parts[0].".")!==false
                ||strpos($end_part,$parts[1].".")!==false
                ||strpos($next_subs,$parts[0].".")!==false
                ||strpos($next_subs,$parts[1].".")!==false
                ||strpos($sub_from,"join")!==false
                ||strpos($sub_from,"union")!==false
            ){
            }
            else{
                $to_include[$i]=false;
            }
        }
        for ($i=0;$i<count($all_froms);$i++){
            if($to_include[$i]){
                $new_froms[]=$all_froms[$i];
            }
        }
        $new_from=implode("left join",$new_froms );

        $new_select_from=$select."from".$new_from;

        $return_query=$new_select_from.$end_part;

        return $return_query;



    }

File is
include/ListView/ListViewData.php