August 9, 2006 at 1:45 am
Please help me in tuning the following queries used in SP,
BEGIN
SELECT PAYEEID, PAYEE_NAME ,opt_consumer_cd, CITYCODE , CITYNAME , NOOFTRAN , VALUE
INTO GROSSREP_Temp FROM (
SELECT PM2.PAYEE_ID AS 'PAYEEID',
PM2.PAYEE_NAME AS PAYEE_NAME ,
opt_consumer_cd ,
SUBSTRING( P.CUST_BANK_ID , 1 , 3) AS 'CITYCODE',
COUNT(*) AS 'NOOFTRAN' ,
SUM(TXN_AMT) AS 'VALUE' ,
CM.CITY_NAME AS 'CITYNAME'
FROM RUNPAYMENTS1 P with(nolock) INNER JOIN PAYEE_MASTER PM with(nolock) ON P.PAYEE_ID = PM.PAYEE_ID
INNER JOIN PAYEE_MASTER PM2 with (nolock) ON PM.MASTER_PAYEE_ID = PM2.PAYEE_ID
INNER JOIN ECS_CYCLE EC with(nolock) ON SUBSTRING(P.CUST_BANK_ID , 1 ,3 ) = EC.CITY_CODE
AND P.ECS_DT = EC.ECS_DATE
INNER JOIN CITY_MASTER CM ON SUBSTRING( P.CUST_BANK_ID , 1 , 3) = CM.CITY_CODE
WHERE EC.DEBIT_DATE =@ECSDATE
AND CUST_BANK_ID IS NOT NULL AND RTRIM(CUST_BANK_ID) != ''
AND PMT_STAT NOT IN ('D', 'X', 'P' , 'M')
AND P.PAYEE_ID IN ('250')
GROUP BY PM2.PAYEE_ID ,
PM2.PAYEE_NAME ,
opt_consumer_cd,
CM.CITY_NAME ,
SUBSTRING( P.CUST_BANK_ID , 1 , 3) )A
GROUP BY PAYEEID, PAYEE_NAME ,opt_consumer_cd, CITYCODE , NOOFTRAN , VALUE , CITYNAME
ORDER BY PAYEEID , CITYCODE
END
BEGIN
INSERT INTO GROSSREP_Temp
SELECT PAYEEID, PAYEE_NAME ,bill_ref_info, CITYCODE , CITYNAME , NOOFTRAN , VALUE
FROM (
SELECT PM2.PAYEE_ID AS 'PAYEEID',
CASE PM2.PAYEE_ID WHEN '021' THEN 'Bharti Infotel Ltd, Haryana'
WHEN '170' THEN 'Bangalore Golf club.'
WHEN '172' THEN 'ICICI -A/C PIS MUMBAI(COLLECTION'
WHEN '173' THEN 'ICICI -A/C PIS MUMBAI(COLLECTION'
WHEN '193' THEN 'ICICI Home Finance Centralised PDC Collection a/c'
WHEN '194' THEN 'ICICI Home Finance Centralised PDC Collection a/c'
WHEN '056' THEN 'Spice Telecom'
WHEN '046' THEN 'Patriot Automation Projects Ltd.'
WHEN '047' THEN 'Patriot Automation Projects Ltd.'
WHEN '152' THEN 'Rediff.com India Ltd.'
WHEN '251' THEN 'BULK PRINCIPAL PNB ASSET MANAGEMENT'
WHEN '267' THEN 'BULK PRU ICICI AMC - REPRESENT'
ELSE PM2.PAYEE_NAME END AS PAYEE_NAME ,
SUBSTRING( P.CUST_BANK_ID , 1 , 3) AS 'CITYCODE',
SUM(0) AS 'bill_ref_info',
COUNT(*) AS 'NOOFTRAN' ,
SUM(TXN_AMT) AS 'VALUE' ,
CM.CITY_NAME AS 'CITYNAME'
FROM RUNPAYMENTS1 P with(nolock) INNER JOIN PAYEE_MASTER PM with(nolock) ON P.PAYEE_ID = PM.PAYEE_ID
INNER JOIN PAYEE_MASTER PM2 with(nolock) ON PM.MASTER_PAYEE_ID = PM2.PAYEE_ID
INNER JOIN ECS_CYCLE EC with(nolock) ON SUBSTRING(P.CUST_BANK_ID , 1 ,3 ) = EC.CITY_CODE
AND P.ECS_DT = EC.ECS_DATE
INNER JOIN CITY_MASTER CM with(nolock) ON SUBSTRING( P.CUST_BANK_ID , 1 , 3) = CM.CITY_CODE
WHERE EC.DEBIT_DATE =@ECSDATE
AND CUST_BANK_ID IS NOT NULL AND RTRIM(CUST_BANK_ID) != ''
AND PMT_STAT NOT IN ('D', 'X', 'P' , 'M')
AND P.PAYEE_ID IN ( '158' , '167', '171' ,
'185' , '206', '207', '222', '225','231',
'233', '236' , '238', '251','267','327',
'266','153', '355', '357','337','363')
GROUP BY PM2.PAYEE_ID ,PM2.PAYEE_NAME , CM.CITY_NAME ,
SUBSTRING( P.CUST_BANK_ID , 1 , 3) )A
GROUP BY PAYEEID, PAYEE_NAME ,bill_ref_info, CITYCODE , NOOFTRAN , VALUE , CITYNAME
ORDER BY PAYEEID , CITYCODE
END
August 9, 2006 at 2:44 am
Just to begin:
SUBSTRING( P.CUST_BANK_ID , 1 , 3) = CM.CITY_CODE
causes full table scan. Follow normalization rules and don't store rubbish in tables.
AND CUST_BANK_ID IS NOT NULL AND RTRIM(CUST_BANK_ID) != ''
means CUST_BANK_ID>''
And it's not clear why you need subquery with double GROUP BY.
Remove everything outside subquery but leave ORDER BY as is.
_____________
Code for TallyGenerator
August 9, 2006 at 5:34 am
Like Sergiy, I'm rather wondering where to start. In addition to what he suggests, take out those hints for the time-being, and use a proper temporary table instead of using that horrible SELECT..INTO.
Without getting into the detail, it looks as if you are having to report on tables over whose structure and integrity you have no control. When faced with this sort of problem, I put a cleaned-up version of the data into rational normalised temporary tables, and break the whole process up into discrete logical steps. Trying to do it all into two huge statements may look neater, but will run a lot slower and will be harder to appraise for optimisation purposes.
Incidentally, why not use Simple-talk's nice SQL Prettifier to make the code easier for us to read? It probably looked quite good in your Query Analyser!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply