Sql Query Optimization

• B S V

  SSC Veteran

  Points: 201

  More actions

  July 14, 2006 at 3:18 am

  #112859

  Please help me in optimizing the following query.

  Please show me the way how to optimize such kind of queries

  SELECT P.PAYEE_ID , PM.PAYEE_NAME ,'x' as bill_ref_info,

  SUBSTRING( P.CUST_BANK_ID , 1 , 3)AS 'LOCATION CODE' , CM.CITY_NAME ,

  COUNT (*) AS 'NO. OF TRANS.', SUM (P.TXN_AMT) AS 'VALUE OF TRANS.'

  FROM PAYMENTS P

  INNER JOIN CITY_MASTER CM ON SUBSTRING( P.CUST_BANK_ID , 1 , 3) = CM.CITY_CODE

  INNER JOIN PAYEE_MASTER PM ON P.PAYEE_ID = PM.PAYEE_ID

  WHERE P.R_MOD_TIME >= '7/8/2006' AND P.R_MOD_TIME < '7/9/2006' AND PMT_STAT = 'E'
  and user_id not in ( 'pay/A', 'pay/B', 'pay/C',
  'pay/D', 'pay/E', 'pay/F', 'pay/G',
  'pay/H','pay/I' )
  and P.PAYEE_ID not in ('342','337')
  AND (USER_ID NOT IN (
  SELECT ID FROM TEST_USERS WHERE TYPE = 'U' AND STATUS_FLG= 'N' )
  AND SUBSTRING(USER_ID,1,3) NOT IN
  (SELECT ID FROM TEST_USERS WHERE TYPE = 'A' AND STATUS_FLG= 'N') OR USER_ID LIKE 'BAF/%')
  GROUP BY P.PAYEE_ID , PM.PAYEE_NAME , SUBSTRING( CUST_BANK_ID , 1 , 3) ,
  CM.CITY_NAME

  UNION
  SELECT P.PAYEE_ID , PM.PAYEE_NAME ,p.bill_ref_info,
  SUBSTRING( P.CUST_BANK_ID , 1 , 3)AS 'LOCATION CODE' , CM.CITY_NAME ,
  COUNT (*) AS 'NO. OF TRANS.', SUM (P.TXN_AMT) AS 'VALUE OF TRANS.'
  FROM PAYMENTS P
  INNER JOIN CITY_MASTER CM ON SUBSTRING( P.CUST_BANK_ID , 1 , 3) = CM.CITY_CODE
  INNER JOIN PAYEE_MASTER PM ON P.PAYEE_ID = PM.PAYEE_ID
  WHERE P.R_MOD_TIME >= '7/8/2006' AND P.R_MOD_TIME < '7/9/2006' AND PMT_STAT = 'E'
  and user_id not in ( 'pay/A', 'pay/B', 'pay/C',
  'pay/D', 'pay/E', 'pay/F', 'pay/G',
  'pay/H','pay/I' )
  and P.PAYEE_ID in ('000000000342') AND (USER_ID NOT IN
  ( SELECT ID FROM TEST_USERS WHERE TYPE = 'U' AND STATUS_FLG= 'N' ) AND SUBSTRING(USER_ID,1,3)
  NOT IN (SELECT ID FROM TEST_USERS WHERE TYPE = 'A' AND STATUS_FLG= 'N')
  R USER_ID LIKE 'BAF/%')
  GROUP BY P.PAYEE_ID , PM.PAYEE_NAME , p.bill_ref_info, SUBSTRING( CUST_BANK_ID , 1 , 3) ,
  CM.CITY_NAME

  UNION

  SELECT P.PAYEE_ID , PM.PAYEE_NAME ,substring(p.consumer_cd,13,6) as bill_ref_info,
  SUBSTRING( P.CUST_BANK_ID , 1 , 3)AS 'LOCATION CODE' , CM.CITY_NAME ,
  COUNT (*) AS 'NO. OF TRANS.', SUM (P.TXN_AMT) AS 'VALUE OF TRANS.'
  FROM PAYMENTS P
  INNER JOIN CITY_MASTER CM ON SUBSTRING( P.CUST_BANK_ID , 1 , 3) = CM.CITY_CODE
  INNER JOIN PAYEE_MASTER PM ON P.PAYEE_ID = PM.PAYEE_ID
  WHERE P.R_MOD_TIME >= '7/8/2006' AND P.R_MOD_TIME < '7/9/2006' AND PMT_STAT = 'E'
  and user_id not in ( 'pay/A', 'pay/B', 'pay/C', 'pay/D',
  'pay/E', 'pay/F', 'pay/G', 'pay/H', 'pay/I' )
  and P.PAYEE_ID in ('337') AND (USER_ID NOT IN (
  SELECT ID FROM TEST_USERS WHERE TYPE = 'U' AND STATUS_FLG= 'N' ) AND
  SUBSTRING(USER_ID,1,3) NOT IN (SELECT ID FROM TEST_USERS
  WHERE TYPE = 'A' AND STATUS_FLG= 'N') OR USER_ID LIKE 'BAF/%')
  GROUP BY P.PAYEE_ID , PM.PAYEE_NAME , substring(p.consumer_cd,13,6),
  SUBSTRING( CUST_BANK_ID , 1 , 3) , CM.CITY_NAME
  ORDER BY P.PAYEE_ID , p.bill_ref_info, SUBSTRING( CUST_BANK_ID , 1 , 3) , CM.CITY_NAME

• Johan Bijnens

  SSC Guru

  Points: 135353

  More actions

  July 14, 2006 at 6:05 am

  #649212

  just my 2 Ct

  -

  Avoid predicates containing functions !!! (e.g. SUBSTRING( P.CUST_BANK_ID , 1 , 3) )

  (in On-clauses or where clauses, ..)

  -

  make sure you have indexes on :

  CM

  .CITY_CODE

  PM

  .PAYEE_ID

  P

  .PAYEE_ID

  P

  .R_MOD_TIME

  P

  .[user_id]

  TU

  .[user_id]

   

  SELECT

  P.PAYEE_ID , PM.PAYEE_NAME

  ,

  'x' as bill_ref_info,

  SUBSTRING

  ( P.CUST_BANK_ID , 1 , 3)AS 'LOCATION CODE'

  ,

  CM.CITY_NAME

  ,

  COUNT (*) AS 'NO. OF TRANS.'

  ,

  SUM (P.TXN_AMT) AS 'VALUE OF TRANS.'

  FROM

  PAYMENTS P

  INNER

  JOIN CITY_MASTER CM

  ON SUBSTRING( P.CUST_BANK_ID , 1 , 3) = CM.CITY_CODE

  INNER

  JOIN PAYEE_MASTER PM

  ON P.PAYEE_ID = PM.PAYEE_ID

  WHERE

  P.R_MOD_TIME >= '7/8/2006'

  AND

  P.R_MOD_TIME < '7/9/2006'

  AND

  PMT_STAT = 'E'

  and

  P.[user_id] not in ( 'pay/A', 'pay/B', 'pay/C', 'pay/D', 'pay/E', 'pay/F', 'pay/G', 'pay/H','pay/I' )

  and

  P.PAYEE_ID not in ('342','337')

  AND

  ( NOT exists ( SELECT *

  FROM TEST_USERS TU

  WHERE STATUS_FLG= 'N'

  AND ((TYPE = 'U'

  and TU.[user_id] = P.[user_id] )

  or ( TYPE = 'A'

  and TU.[user_id] = SUBSTRING( P.[user_id],1,3))

  OR P.[user_id] LIKE 'BAF/%' )

  GROUP

  BY P.PAYEE_ID , PM.PAYEE_NAME , SUBSTRING( CUST_BANK_ID , 1 , 3) , CM.CITY_NAME

  UNION

  ALL -- if you're sure there are no duplicates in the individual selects of the union

  SELECT

  P.PAYEE_ID , PM.PAYEE_NAME ,p.bill_ref_info,

  SUBSTRING

  ( P.CUST_BANK_ID , 1 , 3)AS 'LOCATION CODE' , CM.CITY_NAME ,

  COUNT

  (*) AS 'NO. OF TRANS.', SUM (P.TXN_AMT) AS 'VALUE OF TRANS.'

  FROM

  PAYMENTS P

  INNER

  JOIN CITY_MASTER CM

  ON SUBSTRING( P.CUST_BANK_ID , 1 , 3) = CM.CITY_CODE

  INNER

  JOIN PAYEE_MASTER PM

  ON P.PAYEE_ID = PM.PAYEE_ID

  WHERE

  P.R_MOD_TIME >= '7/8/2006' AND P.R_MOD_TIME < '7/9/2006' AND PMT_STAT = 'E'

  and

  P.[user_id] not in ( 'pay/A', 'pay/B', 'pay/C',

  'pay/D', 'pay/E', 'pay/F', 'pay/G',

  'pay/H','pay/I' )

  and

  P.PAYEE_ID in ('000000000342')

  AND

  ( NOT exists ( SELECT *

  FROM TEST_USERS TU

  WHERE STATUS_FLG= 'N'

  AND ((TYPE = 'U'

  and TU.[user_id] = P.[user_id] )

  or ( TYPE = 'A'

  and TU.[user_id] = SUBSTRING( P.[user_id],1,3))

  OR P.[user_id] LIKE 'BAF/%' )

  GROUP

  BY P.PAYEE_ID , PM.PAYEE_NAME , p.bill_ref_info, SUBSTRING( CUST_BANK_ID , 1 , 3) ,

  CM

  .CITY_NAME

  UNION

  ALL -- if you're sure there are no duplicates in the individual selects of the union

  SELECT

  P.PAYEE_ID , PM.PAYEE_NAME ,substring(p.consumer_cd,13,6) as bill_ref_info,

  SUBSTRING

  ( P.CUST_BANK_ID , 1 , 3)AS 'LOCATION CODE' , CM.CITY_NAME ,

  COUNT

  (*) AS 'NO. OF TRANS.', SUM (P.TXN_AMT) AS 'VALUE OF TRANS.'

  FROM

  PAYMENTS P

  INNER

  JOIN CITY_MASTER CM ON SUBSTRING( P.CUST_BANK_ID , 1 , 3) = CM.CITY_CODE

  INNER

  JOIN PAYEE_MASTER PM ON P.PAYEE_ID = PM.PAYEE_ID

  WHERE

  P.R_MOD_TIME >= '7/8/2006' AND P.R_MOD_TIME < '7/9/2006' AND PMT_STAT = 'E'

  and

  user_id not in ( 'pay/A', 'pay/B', 'pay/C', 'pay/D',

  'pay/E', 'pay/F', 'pay/G', 'pay/H', 'pay/I' )

  and

  P.PAYEE_ID in ('337')

  AND

  ( NOT exists ( SELECT *

  FROM TEST_USERS TU

  WHERE STATUS_FLG= 'N'

  AND ((TYPE = 'U'

  and TU.[user_id] = P.[user_id] )

  or ( TYPE = 'A'

  and TU.[user_id] = SUBSTRING( P.[user_id],1,3))

  OR P.[user_id] LIKE 'BAF/%' )

  GROUP

  BY P.PAYEE_ID , PM.PAYEE_NAME , substring(p.consumer_cd,13,6),SUBSTRING( CUST_BANK_ID , 1 , 3) , CM.CITY_NAME

   

  ORDER

  BY P.PAYEE_ID , p.bill_ref_info, SUBSTRING( CUST_BANK_ID , 1 , 3) , CM.CITY_NAME

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• stax68

  SSChampion

  Points: 11711

  More actions

  July 14, 2006 at 6:43 am

  #649220

  Clarification: UNION will eliminate duplicates in the output recordset, not just within the individual selects.

  e.g.:

  select

  1

  union

  select

  1

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant

• stax68

  SSChampion

  Points: 11711

  More actions

  July 14, 2006 at 7:31 am

  #649239

  You can put the three into a single statement. You can probably get further optimisation, too. What table does user_id come from?

   

  SELECT

  case P.PAYEE_ID , PM.PAYEE_NAME ,

  case

  p.payeeid

  when 342 then p.bill_ref_info

  when 337 then substring(p.consumer_cd,13,6)

  else 'x'

  end

  as bill_ref_info,

  SUBSTRING

  ( P.CUST_BANK_ID , 1 , 3)AS 'LOCATION CODE' , CM.CITY_NAME ,

  COUNT

  (*) AS 'NO. OF TRANS.', SUM (P.TXN_AMT) AS 'VALUE OF TRANS.'

  FROM

  PAYMENTS P

  JOIN

  CITY_MASTER CM ON SUBSTRING( P.CUST_BANK_ID , 1 , 3) = CM.CITY_CODE

  JOIN

  PAYEE_MASTER PM ON P.PAYEE_ID = PM.PAYEE_ID

  WHERE

  P.R_MOD_TIME >= '7/8/2006' AND P.R_MOD_TIME < '7/9/2006'

  AND

  PMT_STAT = 'E'

  and

  user_id not in ( 'pay/A', 'pay/B', 'pay/C', 'pay/D', 'pay/E', 'pay/F', 'pay/G', 'pay/H','pay/I' )

  and

  P.PAYEE_ID is not null

  /*

  --current version - two non-correlated subqueries ------------------

  AND (USER_ID NOT IN (SELECT ID FROM TEST_USERS WHERE TYPE = 'U' AND STATUS_FLG= 'N' )

  AND SUBSTRING(USER_ID,1,3) NOT IN (SELECT ID FROM TEST_USERS WHERE TYPE = 'A' AND STATUS_FLG= 'N')

  OR USER_ID LIKE 'BAF/%')

  --------------------------------------------------------------------

  */

  --suggested version (not tested) - single correlated subquery-------

  AND

  (NOT EXISTS (SELECT * FROM TEST_USERS T WHERE T.STATUS_FLG= 'N'

  AND ( (T.TYPE = 'U' AND T.ID = USER_ID)

  OR

  (T.TYPE = 'A' AND T.ID = SUBSTRING(USER_ID,1,3)) ) )

  OR USER_ID LIKE 'BAF/%')

  --------------------------------------------------------------------

  GROUP

  BY P.PAYEE_ID , PM.PAYEE_NAME ,

  case

  p.payeeid

  when 342 then p.bill_ref_info

  when 337 then substring(p.consumer_cd,13,6)

  else 'x'

  end

  ,

  SUBSTRING

  ( CUST_BANK_ID , 1 , 3) , CM.CITY_NAME

  ORDER

  BY P.PAYEE_ID , p.bill_ref_info, SUBSTRING( CUST_BANK_ID , 1 , 3) , CM.CITY_NAME

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant