SQL Server Performance

why the query is retreiving very slow

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Bahram Shagiwal, Oct 6, 2008.

  1. Bahram Shagiwal New Member

    select count(charegeable_duration), sum ( case when length(charegeable_duration) = 6 then
    to_number((substr(charegeable_duration,1,2)*60)*60) + to_number(substr(charegeable_duration,3,2)*60) + to_number(substr(charegeable_duration,5,2))
    when length(charegeable_duration) = 5 then
    to_number((substr(charegeable_duration,1,1)*60)*60) + to_number(substr(charegeable_duration,2,2)*60) + to_number(substr(charegeable_duration,4,2))
    when length(charegeable_duration) = 4 then
    to_number(substr(charegeable_duration,1,2)*60) + to_number(substr(charegeable_duration,3,2))
    when length(charegeable_duration) = 3 then
    to_number(substr(charegeable_duration,1,1)*60) + to_number(substr(charegeable_duration,2,2))
    when length(charegeable_duration) = 2 then
    to_number(substr(charegeable_duration,1,2))
    when length(charegeable_duration) = 1 then
    to_number(substr(charegeable_duration,1,1))
    else 0
    end ) seconds, substr(incoming_rout,1,3),date_start_charge

    FROM MSC_SEPTEMBER_2008
    WHERE
    (INCOMING_ROUT like 'AWCC%'
    AND (calling_party_num not like '11009370%' AND calling_party_num not like '11070%')
    AND ( calling_party_num not like '12070%' AND calling_party_num not like '13070%' AND calling_party_num not like '14070%')
    AND ( calling_party_num not like '1170%' AND calling_party_num not like '1270%' AND calling_party_num not like '1370%')
    AND (calling_party_num not like '1470%' AND calling_party_num not like '129370%' AND calling_party_num not like '139370%')
    AND ( calling_party_num not like '119370%' AND calling_party_num not like '149370%' AND calling_party_num not like '12009370%')
    AND (calling_party_num not like '13009370%' AND calling_party_num not like '14009370%' AND calling_party_num not like '12010070%')
    AND (calling_party_num not like '13010070%' AND calling_party_num not like '14010070%' AND calling_party_num not like '11010070%')
    AND (calling_party_num not like '1201070%' AND calling_party_num not like '1301070%' AND calling_party_num not like '1401070%')
    AND (calling_party_num not like '1101070%' AND calling_party_num not like '12010009370%' AND calling_party_num not like '13010009370%')
    AND (calling_party_num not like '14010009370%' AND calling_party_num not like '11010009370%' AND calling_party_num not like '120109370%')
    AND (calling_party_num not like '110109370%' AND calling_party_num not like '130109370%' AND calling_party_num not like '140109370%' )
    AND (call_type = 'transit'))
    OR
    (INCOMING_ROUT like 'ROSH%' AND
    (calling_party_num not like '11009379%' AND calling_party_num not like '11079%')
    AND ( calling_party_num not like '12079%' AND calling_party_num not like '13079%' AND calling_party_num not like '14079%')
    AND ( calling_party_num not like '1179%' AND calling_party_num not like '1279%' AND calling_party_num not like '1379%')
    AND (calling_party_num not like '1479%' AND calling_party_num not like '129379%' AND calling_party_num not like '139379%')
    AND ( calling_party_num not like '119379%' AND calling_party_num not like '149379%' AND calling_party_num not like '12009379%')
    AND (calling_party_num not like '13009379%' AND calling_party_num not like '14009379%' AND calling_party_num not like '12010079%' )
    AND (calling_party_num not like '13010079%' AND calling_party_num not like '14010079%' AND calling_party_num not like '11010079%')
    AND (calling_party_num not like '1201079%' AND calling_party_num not like '1301079%' AND calling_party_num not like '1401079%' )
    AND (calling_party_num not like '1101079%' AND calling_party_num not like '12010009379%' AND calling_party_num not like '13010009379%')
    AND (calling_party_num not like '14010009379%' AND calling_party_num not like '11010009379%' AND calling_party_num not like '120109379%')
    AND (calling_party_num not like '110109379%' AND calling_party_num not like '130109379%' AND calling_party_num not like '140109379%' )
    AND (call_type = 'transit'))


    OR

    (INCOMING_ROUT = 'AFTLI' AND
    (calling_party_num IS NULL) AND
    (call_type = 'transit'))
    or

    (INCOMING_ROUT like 'MED%'
    AND call_type = 'transit')
    or
    (INCOMING_ROUT like 'PTC%'
    AND call_type = 'transit')

    OR


    (INCOMING_ROUT like 'ETI%'
    AND (calling_party_num not like '11009378%' AND calling_party_num not like '11078%')
    AND ( calling_party_num not like '12078%' AND calling_party_num not like '13078%' AND calling_party_num not like '14078%')
    AND ( calling_party_num not like '1178%' AND calling_party_num not like '1278%' AND calling_party_num not like '1378%')
    AND (calling_party_num not like '1478%' AND calling_party_num not like '129378%' AND calling_party_num not like '139378%')
    AND ( calling_party_num not like '119378%' AND calling_party_num not like '149378%' AND calling_party_num not like '12009378%' )
    AND (calling_party_num not like '13009378%' AND calling_party_num not like '14009378%' AND calling_party_num not like '12010078%' )
    AND (calling_party_num not like '13010078%' AND calling_party_num not like '14010078%' AND calling_party_num not like '11010078%')
    AND (calling_party_num not like '1201078%' AND calling_party_num not like '1301078%' AND calling_party_num not like '1401078%' )
    AND (calling_party_num not like '1101078%' AND calling_party_num not like '12010009378%' AND calling_party_num not like '13010009378%')
    AND (calling_party_num not like '14010009378%' AND calling_party_num not like '11010009378%' AND calling_party_num not like '120109378%')
    AND (calling_party_num not like '110109378%' AND calling_party_num not like '130109378%' AND calling_party_num not like '140109378%' )
    AND (call_type = 'transit'))

    GROUP BY DATE_START_CHARGE,substr(incoming_rout,1,3)
  2. Luis Martin Moderator

    Welcome to the forum!.Kindly don't duplicate posts.

Share This Page