Search code examples
javamysqlsql-serversybase

Take customer count of all customers specific to region and

select 
                "pct_customers_count"=isnull((select  count(customer_code)  from CRM..pct_customers where status <> "R" and add_branch=t3.c_branchcode),0) 
                ,"pct_arch_customers_count"=isnull((select  count(customer_code)  from CRM..pct_customers_arch where status <> "R" and add_branch=t3.c_branchcode) ,0)
                ,"crms_customers_count"= isnull((select  count(customer_code)  from CRMS..crms_customer_master where status <> "R" and add_branch=t3.c_branchcode) ,0)
                ,"crms_customers_daily_count"=isnull((select  count(customer_code)  from CRMS..crms_custdaily_master where status <> "R" and add_branch=t3.c_branchcode),0)
            from 
                ums..admin_regions t1 
                ,ums..admin_branches t2
                ,ums..branch_master t3
            where 
                t1.reg_code ='ZZZZ'
                and t2.reg_grandpa = t1.reg_code
                and t3.c_branchcode = t2.branch_code

Below I give the total rows size of the tables I used

  • pct_customers:720227
  • pct_customers_arch: 341500
  • crms_customer_master:999005
  • crms_custdaily_master:929022

The last three tables gets the branch code and gives it to the pct_customers_table,pct_customers_arch,crms_customer_master and crms_custdaily_master fot taking the count of customers.

Problem

This query takes a big load on our database server.How can I optimize this query so that it takes less than a minute to search the database.I tried to simplify the query by joins but no luck.

Solution that I found

CRONing the count to table and using the new table I display it to new page.I know this is a good solution but it feels like it's not straight solution.So if you guys know any other solution to my problem please share.

Solution

  • You need to create one index per table; columns to be indexed are status and add_branch.