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!.Did you see execution plan?. Did you test if the query use indexes?
  3. HanShi New Member

    Look at the queryplan of the query! Are proper indexes set on the tables?
    What is the datatype of column calling_party_num?
    Is it possible for you to change the WHERE clause for this column, because a LIKE or NOT LIKE operator is a slow process. Try and use a FULLTEXT index on this column...
  4. FrankKalis Moderator

    I wonder why you expect good performance from that query anyway.
    These lots of NOT LIKE ... along with the OR conditions in the WHERE clause are killing this query. I would probably try to rewrite these NOT LIKE's to a NOT EXISTS clause

Share This Page