-1

I wrote query to find sum of money in a given period with one filial and it is working fast:

SELECT FILIAL_CODE,
       sum(sum_eqv)/100 AS summa
FROM   table
WHERE  substr(acc,1,5) = '65434'
and    cast(substr(acc,18,3) as integer) >= 600
and    cast(substr(account_co,18,3) as integer)<=607
AND    o_day >= to_date('01.12.2019', 'DD.MM.YYYY')
and    oday < to_date('08.12.2019', 'DD.MM.YYYY')+ INTERVAL '1' DAY
AND    FILIAL_CODE = '001234'

Above query is working fine. But when I want to use it multiple filials it is becoming more complex. Below query is needs to be fixed.

SELECT FILIAL_CODE,
       sum(sum_eqv)/100 AS summa
FROM   table
WHERE  substr(acc,1,5) = '65434'
and    cast(substr(acc,18,3) as integer) >= 600
and    cast(substr(account_co,18,3) as integer)<=607
AND    o_day >= to_date('01.12.2019', 'DD.MM.YYYY')
and    oday < to_date('08.12.2019', 'DD.MM.YYYY')+ INTERVAL '1' DAY
AND    FILIAL_CODE in (select code from city where region = '26')
group by FILIAL_CODE;

This query runs long. How can I maximize this statement. Any Help is appreciated!

APC
  • 144,005
  • 19
  • 170
  • 281
Abdusoli
  • 661
  • 1
  • 8
  • 24
  • 2
    Get the explain plan – Ed Heal Dec 09 '19 at 12:13
  • actually query is not stopping and continues long. I need to stop it manually. I think problem is happening with in statement. – Abdusoli Dec 09 '19 at 12:21
  • 1
    The first query runs quick as it doesnt start with `select`, so you get immediatly `unknow command`. In general you must provide [execution plan](https://stackoverflow.com/a/34975420/4808122) and few other thinks discussed in the link to be able to get meaningfull response to performance related problems. The guess for your case it that query on one shop goes over index, the other query swaps to `full table scan`. – Marmite Bomber Dec 09 '19 at 12:33
  • sorry I fixed query. – Abdusoli Dec 09 '19 at 12:45
  • 1
    I one of your most executed query filters rows using substr you could at least try to store data on 1st normal form and create a multicolumn index on that – Alessandro Rossi Dec 09 '19 at 12:48
  • First : You'd better user JOIN between CITY and your 'TABLE'... Second : The EXPLAIN PLAN should help you to find indexes to create. Is there an index on CITY.REGION ? Third : Use of function on columns bypasses use of index... So, in your where clause, prefer to use ACC LIKE '65434%' instead of substr.... And add an index on TABLE.ACC if not exists. – BartmanDilaw Dec 09 '19 at 14:00
  • Bro thank you I did like what you said and working fine. Actually table that I am using existing and really big table. I cannot add any index or smth else on it. I didn't think difference between ACC LIKE '65434%' and SUBSTR (t.account_co, 1, 5) = '45294' is really big. – Abdusoli Dec 10 '19 at 04:11
  • But when I give big interval of period it is becoming slower. – Abdusoli Dec 10 '19 at 04:24

1 Answers1

1

Try JOIN instead of IN, such as:

  SELECT t.filial_code, SUM (sum_eqv) / 100 AS summa
    FROM your_table t JOIN city c ON c.code = t.filial_code
   WHERE     SUBSTR (t.acc, 1, 5) = '65434'
         AND CAST (SUBSTR (t.acc, 18, 3) AS INTEGER) >= 600
         AND CAST (SUBSTR (t.account_co, 18, 3) AS INTEGER) <= 607
         AND t.o_day >= TO_DATE ('01.12.2019', 'DD.MM.YYYY')
         AND t.oday < TO_DATE ('08.12.2019', 'DD.MM.YYYY') + INTERVAL '1' DAY
         AND c.region = '26'
GROUP BY t.filial_code;

It would probably help if city.code and table.filial_code were indexed.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • It also did not help. city table is small but anyway it is taking too much time – Abdusoli Dec 09 '19 at 12:44
  • 5
    We're blindly guessing. All we have is query you posted, we have no idea about tables, indexes, whether statistics have been gathered ... nothing. One of comments said that you should provide explain plan; someone might point to the right direction, then. – Littlefoot Dec 09 '19 at 12:53