Re: cpu usage is %10 while running query on ignite

[Ilya Kasnacheev <il...@gmail.com>]

Hello!

This looks like a huge query which should be optimized in parts. Please try
to simplify it until it is no longer slow, then you will find the step
which is not optimal.

Regards,
-- 
Ilya Kasnacheev


пт, 29 янв. 2021 г. в 08:10, cihad <ci...@intellica.net>:

> thank you for quick reply
>
> here is some part of my query it is longer then this but rest also same
> with
> small change
>
> SELECT 'FreeSql' AS FIRST_TABLE_NAME, nvl(to_char(a.id),'') AS
> FIRST_TABLE_KEYS, 'id' AS FIRST_KEY_1,
> null AS FIRST_KEY_2, null AS FIRST_KEY_3, 'user_id' AS FIRST_COLUMN,'null'
> AS FIRST_CONDITION, to_char(a.user_id) AS FIRST_VALUE,
> '1000000' AS FIRST_TABLE_COUNT, 'FreeSql' AS SECOND_TABLE_NAME,
> nvl(to_char(b.id),'') AS SECOND_TABLE_KEYS, 'id' AS SECOND_KEY_1,
>  null AS
> SECOND_KEY_2,null AS SECOND_KEY_3, 'user_id' AS SECOND_COLUMN, 'null' AS
> SECOND_CONDITION, to_char(b.user_id) AS SECOND_VALUE,'1000000' AS
> SECOND_TABLE_COUNT
> FROM
> "472372156301753_PEFDE_472372156301754_15070131349876".T_15070131349876
> a, "472372156301753_PEFDE_472372156301754_15115146921672".T_15115146921672
> b
> WHERE a.id = b.id AND
> UPPER(nvl2(to_char(a.user_id),to_char(a.user_id),'nvl2')) <>
> UPPER(nvl2(to_char(b.user_id),to_char(b.user_id),'nvl2'))
> UNION
> SELECT 'FreeSql' AS FIRST_TABLE_NAME, nvl(to_char(a.id),'') AS
> FIRST_TABLE_KEYS, 'id' AS FIRST_KEY_1,
> null AS FIRST_KEY_2, null AS FIRST_KEY_3, 'username' AS FIRST_COLUMN,'null'
> AS FIRST_CONDITION, to_char(a.username) AS FIRST_VALUE,
> '1000000' AS FIRST_TABLE_COUNT, 'FreeSql' AS SECOND_TABLE_NAME,
> nvl(to_char(b.id),'') AS SECOND_TABLE_KEYS, 'id' AS SECOND_KEY_1,
>  null AS
> SECOND_KEY_2,null AS SECOND_KEY_3, 'username' AS SECOND_COLUMN, 'null' AS
> SECOND_CONDITION, to_char(b.username) AS SECOND_VALUE,'1000000' AS
> SECOND_TABLE_COUNT
> FROM
> "472372156301753_PEFDE_472372156301754_15070131349876".T_15070131349876
> a, "472372156301753_PEFDE_472372156301754_15115146921672".T_15115146921672
> b
> WHERE a.id = b.id AND
> UPPER(nvl2(to_char(a.username),to_char(a.username),'nvl2')) <>
> UPPER(nvl2(to_char(b.username),to_char(b.username),'nvl2'))
> UNION
> SELECT 'FreeSql' AS FIRST_TABLE_NAME, nvl(to_char(a.id),'') AS
> FIRST_TABLE_KEYS, 'id' AS FIRST_KEY_1,
> null AS FIRST_KEY_2, null AS FIRST_KEY_3, 'password' AS FIRST_COLUMN,'null'
> AS FIRST_CONDITION, to_char(a.password) AS FIRST_VALUE,
> '1000000' AS FIRST_TABLE_COUNT, 'FreeSql' AS SECOND_TABLE_NAME,
> nvl(to_char(b.id),'') AS SECOND_TABLE_KEYS, 'id' AS SECOND_KEY_1,
>  null AS
> SECOND_KEY_2,null AS SECOND_KEY_3, 'password' AS SECOND_COLUMN, 'null' AS
> SECOND_CONDITION, to_char(b.password) AS SECOND_VALUE,'1000000' AS
> SECOND_TABLE_COUNT
> FROM
> "472372156301753_PEFDE_472372156301754_15070131349876".T_15070131349876
> a, "472372156301753_PEFDE_472372156301754_15115146921672".T_15115146921672
> b
> WHERE a.id = b.id AND
> UPPER(nvl2(to_char(a.password),to_char(a.password),'nvl2')) <>
> UPPER(nvl2(to_char(b.password),to_char(b.password),'nvl2'))
>
>
> -From your response I understood that query paralelizm not solving my
> problem because Idont use methods like sum,
>
> I already use distributed joins and lazy true. but I might try lazy false
> and see how much memory it is consuming because my data is big
>
> I should search explain statement, I didnt know that feature.
>
> Thank you soo much. If you have any other suggestion pls let me know
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>