Home » SQL & PL/SQL » SQL & PL/SQL » Taking more time to execute query
Taking more time to execute query [message #415741] Wed, 29 July 2009 01:28 Go to next message
GURAVHARSHA
Messages: 9
Registered: July 2009
Location: Mumbai,India
Junior Member
Hi

while running below query, it take time to run in Toad. Is there any adjustment in query so that my query will be run quickly giving desired output.


SELECT 
                 0 ENTRYNO,
                 0 BRKNO,
                 0 MISBRKNO,
				 TXNDT,
			    SRCTYPE,
				NULL CHQNO,
				NULL VCHNO,
				'OPENING   BALANCE' VCHREMARKS,
				DEBIT,
				CREDIT,
				GLACCODE,
				GLSUBACCODE,
				GLACNAME,
				PARTYNAME,
				APPROVE1,
				APPROVE2
FROM
	(SELECT    
				A.GLACCODE,
				A.GLSUBACCODE, 
				SUM(A.DRAMT) DEBIT, 
				SUM(A.CRAMT) CREDIT,
                CS_COMSELECTS.ACCSELECT(A.GLACCODE) GLACNAME,
             	CS_COMSELECTS.PARTYSELECT(A.GLSUBACCODE) PARTYNAME,
				A.SRCTYPE,
				A.TXNDT,
				A.APPROVE1,
				A.APPROVE2
                FROM 
		FTLEDGERMUL A
               --WHERE 	A.COCODE = :COCODE 
               --AND 	A.DIVCODE IN &DIVSEL
               --AND 	((((TXNDT >=:FBDATE AND TXNDT<:DATEFROM) OR (SRCTYPE = '00'  AND 	TXNDT = :FBDATE) AND :FINAL = 'N')) 
               --OR(:FINAL= 'Y' AND TXNDT >=:FBDATE))
               --AND ((SRCTYPE != 'MJ'  AND :MEMJV =  'N')  OR  (:MEMJV = 'Y'))
               --AND 	((A.GLACCODE  IN 	&ACC   AND  :ALL_ACC = 'N')  OR (:ALL_ACC = 'Y'))
               --AND  	((A.GLSUBACCODE IN &PARTY  AND 	:SUBACCFLAG =  'Y'  AND :ALLPARTY   =  'N') 
			   --OR (:SUBACCFLAG  = 'N') OR  (:ALLPARTY = 'Y' 	AND :SUBACCFLAG = 'Y'))
               --AND  ((:approve = '1' AND ((A.APPROVE1 IS NULL) OR (A.APPROVE1 IS NOT NULL AND A.APPROVE2 IS NULL))) OR 
              --(:approve = '2' AND A.APPROVE2 IS NOT NULL) OR 
              --(:approve = '0') )
GROUP BY 
	  	 	  A.GLACCODE,
			  A.GLSUBACCODE,
			  A.TXNDT,
			  A.SRCTYPE,
			  A.APPROVE1,
				A.APPROVE2) 
UNION ALL   
SELECT
	  			HDR.ENTRYNO, 
                HDR.BRKNO, 
                HDR.MISBRKNO,
				HDR.TXNDT,
				HDR.SRCTYPE,
				HDR.CHQNO,
				HDR.VCHNO,
				HDR.VCHREMARKS,
				DRAMT DEBIT, 
				CRAMT CREDIT,
				HDR.GLACCODE,
				HDR.GLSUBACCODE,
				CS_COMSELECTS.ACCSELECT(HDR.GLACCODE) GLACNAME,
				CS_COMSELECTS.PARTYSELECT(HDR.GLSUBACCODE) PARTYNAME,
				HDR.APPROVE1,
				HDR.APPROVE2
				
FROM       
		   		FTLEDGERMUL HDR,
				FMSTACS B
	WHERE  		--HDR.COCODE	=	:COCODE 
--AND 			HDR.DIVCODE 	IN 	&DIVSEL 
--AND 			((SRCTYPE	!=	'MJ'  AND :MEMJV	= 'N')  OR (:MEMJV	 = 'Y'))
--AND        		((HDR.GLACCODE 	 IN 	&ACC   AND        :ALL_ACC = 'N') OR (:ALL_ACC          =     'Y'))
--AND 			((HDR.GLSUBACCODE IN &PARTY  AND :SUBACCFLAG	=  'Y'  AND :ALLPARTY =  'N') OR (:SUBACCFLAG = 'N')
--OR		 		(:ALLPARTY =	'Y' AND :SUBACCFLAG = 'Y'))
--AND 			HDR.TXNDT BETWEEN :DATEFROM AND :DATETO 	  
			 			B.SUMMARISE	=	'N'   
AND 			SRCTYPE		!=	'00'
--AND 			(( :FINAL	=  'Y') OR  (:FINAL	=	'N'))
AND        		HDR.GLACCODE =  B.GLACCODE 
--AND        		((:approve = '1' AND ((HDR.APPROVE1 IS NULL) OR (HDR.APPROVE1 IS NOT NULL AND HDR.APPROVE2 IS NULL))) OR 
--                (:approve = '2' AND HDR.APPROVE2 IS NOT NULL) OR (:approve = '0') ) 
UNION ALL
SELECT 
	   	 		0 ENTRYNO,
                0 BRKNO,
                0 MISBRKNO,
				LAST_DAY(TXNDT) TXNDT,
				A.SRCTYPE,
				NULL,
				NULL,
				'SUMMARY' VCHREMARKS,
				SUM(NVL(DRAMT,0)) DEBIT, 
				SUM(NVL(CRAMT,0)) CREDIT,
				A.GLACCODE,
				A.GLSUBACCODE,
				CS_COMSELECTS.ACCSELECT(A.GLACCODE) GLACNAME,
				CS_COMSELECTS.PARTYSELECT(A.GLSUBACCODE) PARTYNAME,
				A.APPROVE1,
				A.APPROVE2
FROM     	FTLEDGERMUL A,
			FMSTACS B 
WHERE  			--A.COCODE	=	:COCODE 
--AND			 	A.DIVCODE 	IN 	&DIVSEL
--AND 			((SRCTYPE	!=	'MJ'  AND 	:MEMJV	=	'N')  OR (:MEMJV	=	'Y'))
--AND 			((A.GLACCODE 	 IN 	&ACC   AND        :ALL_ACC = 'N')             OR (:ALL_ACC          =     'Y'))
--AND  			((A.GLSUBACCODE IN &PARTY AND :SUBACCFLAG = 'Y' 	AND :ALLPARTY =  'N') 
--OR				 (:SUBACCFLAG	 =  'N') OR (:ALLPARTY = 'Y' AND :SUBACCFLAG = 'Y'))
 			 B.SUMMARISE	=	'Y'  
AND 			 SRCTYPE		!=	'00'
--AND 			 ((:FINAL	=	'Y') 	OR (:FINAL	=              'N'))
--AND 			 A.TXNDT BETWEEN :DATEFROM AND :DATETO  
AND 			 A.GLACCODE		=	B.GLACCODE  
--AND  			 ((:approve = '1' AND ((A.APPROVE1 IS NULL) OR (A.APPROVE1 IS NOT NULL AND A.APPROVE2 IS NULL))) OR 
--          		 (:approve = '2' AND A.APPROVE2 IS NOT NULL) OR 
--          		 (:approve = '0') )
GROUP BY 		 A.GLACCODE,
	  		    A.GLSUBACCODE, 
	    		B.GLACNAME,
                CS_COMSELECTS.PARTYSELECT(A.GLSUBACCODE),
			    SRCTYPE,
	    		LAST_DAY(TXNDT),
				A.APPROVE1,
				A.APPROVE2
				
				ORDER BY 11,12,4,7



Re: Taking more time to execute query [message #415744 is a reply to message #415741] Wed, 29 July 2009 01:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Maybe. Depends on a lot of things we don't know.

Read the Sticky Post in the performance tuning forum on how to trace a query, how to get an explained execution plan, and what informations are REQUIRED so that a query can be analysed and tuned.
Re: Taking more time to execute query [message #415750 is a reply to message #415741] Wed, 29 July 2009 02:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You want us to investigate a query that you find too difficult to tune.
I'd say the first and logical step would be to remove the commented lines. Don't you agree that that would simplify getting a good view?
Re: Taking more time to execute query [message #415757 is a reply to message #415750] Wed, 29 July 2009 02:52 Go to previous messageGo to next message
GURAVHARSHA
Messages: 9
Registered: July 2009
Location: Mumbai,India
Junior Member
Thanks Frank.

Here is i m posting modifying Query .


SELECT 
                 0 ENTRYNO,
                 0 BRKNO,
                 0 MISBRKNO,
		 TXNDT,
		 SRCTYPE,
		NULL CHQNO,
		NULL VCHNO,
		'OPENING   BALANCE' VCHREMARKS,
		DEBIT,
		CREDIT,
		GLACCODE,
		GLSUBACCODE,
		GLACNAME,
		PARTYNAME,
		APPROVE1,
		APPROVE2
FROM
	(SELECT    
		A.GLACCODE,
		A.GLSUBACCODE, 
		SUM(A.DRAMT) DEBIT, 
		SUM(A.CRAMT) CREDIT,
                CS_COMSELECTS.ACCSELECT(A.GLACCODE) GLACNAME,
             	CS_COMSELECTS.PARTYSELECT(A.GLSUBACCODE) PARTYNAME,
		A.SRCTYPE,
		A.TXNDT,
		A.APPROVE1,
		A.APPROVE2
                FROM 
		FTLEDGERMUL A
               
GROUP BY 
	  	A.GLACCODE,
       	       A.GLSUBACCODE,
	       A.TXNDT,
	       A.SRCTYPE,
	       A.APPROVE1,
		A.APPROVE2) 
UNION ALL   
SELECT
	  	HDR.ENTRYNO, 
                HDR.BRKNO, 
                HDR.MISBRKNO,
		HDR.TXNDT,
		HDR.SRCTYPE,
		HDR.CHQNO,
		HDR.VCHNO,
		HDR.VCHREMARKS,
		DRAMT DEBIT, 
		CRAMT CREDIT,
		HDR.GLACCODE,
		HDR.GLSUBACCODE,
		CS_COMSELECTS.ACCSELECT(HDR.GLACCODE) GLACNAME,
		CS_COMSELECTS.PARTYSELECT(HDR.GLSUBACCODE) PARTYNAME,
		HDR.APPROVE1,
		HDR.APPROVE2
				
FROM       
		 FTLEDGERMUL HDR,
		FMSTACS B
	WHERE  			  
		B.SUMMARISE ='N'   
AND 		SRCTYPE	!='00'

AND        	HDR.GLACCODE =  B.GLACCODE 

UNION ALL
SELECT 
	   	 0 ENTRYNO,
                0 BRKNO,
                0 MISBRKNO,
		LAST_DAY(TXNDT) TXNDT,
		A.SRCTYPE,
		NULL,
		NULL,
		'SUMMARY' VCHREMARKS,
		SUM(NVL(DRAMT,0)) DEBIT, 
		SUM(NVL(CRAMT,0)) CREDIT,
		A.GLACCODE,
		A.GLSUBACCODE,
		CS_COMSELECTS.ACCSELECT(A.GLACCODE) GLACNAME,
		CS_COMSELECTS.PARTYSELECT(A.GLSUBACCODE) PARTYNAME,
		A.APPROVE1,
		A.APPROVE2
FROM     	FTLEDGERMUL A,
		FMSTACS B 
WHERE  			

 		B.SUMMARISE ='Y'  
AND 		SRCTYPE !='00'

AND 		A.GLACCODE =	B.GLACCODE  

GROUP BY 	A.GLACCODE,
	  	A.GLSUBACCODE, 
	        B.GLACNAME,
                CS_COMSELECTS.PARTYSELECT(A.GLSUBACCODE),
		 SRCTYPE,
	    	LAST_DAY(TXNDT),
		A.APPROVE1,
		A.APPROVE2
				
				ORDER BY 11,12,4,7




Re: Taking more time to execute query [message #415759 is a reply to message #415757] Wed, 29 July 2009 03:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well. Based on the information we have the only way to make sure that the query runs faster would be to buy faster hardware.

The first thing you need to do is look at the execution plan.

The next step would be using SQL Trace and TKPROF
Re: Taking more time to execute query [message #415824 is a reply to message #415757] Wed, 29 July 2009 08:09 Go to previous message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Are the statistics upto date?
What are the indexes on the tables used in the query..

Also as ThomasG sir asked
Quote:

What is the plan for the sql...
The next step would be using SQL Trace and TKPROF

Previous Topic: columns to rows
Next Topic: DBMS_JOB.SUMBIT not working
Goto Forum:
  


Current Time: Sat Dec 10 06:56:19 CST 2016

Total time taken to generate the page: 0.09222 seconds