How to mimize execution time???????

[toprashantjha]

hello all..

 

        i have some query which takes a lot of time ... please make a look at the query given...

 

 

# Create Temp Table to store Ticket Num With min add date time

CREATE TEMPORARY TABLE TBL_TEMP_FORUMS_MIN_ADD_DATE_TIME_FOR_TICKET

(

  FLD_TICKET_NUM varchar(32) default NULL,

  FLD_MIN_ADD_DATE_TIME datetime NULL,

  FLD_TIME_DIFF_IN_HRS INT,

  INDEX(FLD_TICKET_NUM ),

  INDEX(FLD_MIN_ADD_DATE_TIME),

  INDEX(FLD_TIME_DIFF_IN_HRS)

);

 

 

 

select * FROM TBL_TEMP_FORUMS_MIN_ADD_DATE_TIME_FOR_TICKET WHERE FLD_TICKET_NUM='TLY-5079408-8233684';

 

# Insert Into Temporary Table

 

INSERT INTO TBL_TEMP_FORUMS_MIN_ADD_DATE_TIME_FOR_TICKET

(FLD_TICKET_NUM,FLD_MIN_ADD_DATE_TIME , FLD_TIME_DIFF_IN_HRS)

#EXPLAIN

SELECT SQL_CALC_FOUND_ROWS FLD_TICKET_NUM, IF( TFMM.FLD_DIRECTION = 2,

MIN(FLD_ADD_DATE_TIME), '0000-00-00 00:00:00')

AS FLD_MIN_ADD_DATE_TIME

      ,

      ( ( TIME_TO_SEC(

CASE FLD_ISSUE_CLOSED

WHEN 3 THEN TIMEDIFF(

MAX(FLD_ISSUE_STATUS_UPDATE_DATE_TIME), MIN(FLD_ADD_DATE_TIME))

ELSE TIMEDIFF(

'2008-09-26 23:59:59', MIN(FLD_ADD_DATE_TIME) ) END )/3600) - FLD_CSS_ISSUE_SLA_QTY)

AS FLD_TIME_DIFF_IN_HRS

FROM TBL_FORUMS_MSG_MAIN TFMM FORCE INDEX(FLD_ADD_DATE_TIME,FLD_TICKET_NUM)

WHERE

( ( TFMM.FLD_DIRECTION = 2 AND TFMM.FLD_PARENT_ID > 0 ) OR TFMM.FLD_REPLY_FLAG = 0 )

AND TFMM.FLD_AUTO_RESPONSE_FLAG = 0 AND TFMM.FLD_ADD_DATE_TIME >= '2008-09-22 00:00:00'

AND TFMM.FLD_ADD_DATE_TIME <= '2008-09-29 23:59:59' AND TFMM.FLD_ACC_ID IN (118,393,6)

GROUP BY FLD_TICKET_NUM;

ORDER BY FLD_TIME_DIFF_IN_HRS DESC;

limit 999 ;

 

select FOUND_ROWS();

 

 

#Create Temporary Table to Store Ticket Num Based on Selected Criteria

 

DROP TABLE TBL_FORUMS_TEMP_TICKET_NUM;

 

CREATE TEMPORARY TABLE TBL_FORUMS_TEMP_TICKET_NUM

(

  FLD_FIRST_RESPONSE_TIME_IN_HRS INT(5),

  FLD_TICKET_NUM varchar(32) NOT NULL ,

  FLD_CSS_ISSUE_SLA_QUANTITY INT(1),

  FLD_CSS_ISSUE_TAT_QUANTITY INT(1),

  FLD_INWARD_INTERACTION_COUNT INT(1),

  INDEX(FLD_TICKET_NUM)

);

 

# Insert data In Temporary Table

 

# ISSUES WHERE TAT IS CROSSED

INSERT INTO TBL_FORUMS_TEMP_TICKET_NUM (FLD_FIRST_RESPONSE_TIME_IN_HRS, FLD_TICKET_NUM,

FLD_CSS_ISSUE_SLA_QUANTITY, FLD_CSS_ISSUE_TAT_QUANTITY, FLD_INWARD_INTERACTION_COUNT )

 

SELECT

TTFMA.FLD_MIN_ADD_DATE_TIME AS FLD_MIN_ADD_DATE_TIME,

TIME_TO_SEC( TIMEDIFF(TTFMA.FLD_MIN_ADD_DATE_TIME, TFMM.FLD_ADD_DATE_TIME ) )/3600

AS FIRST_RESPONSE_TIME_IN_HRS, TFMM.FLD_TICKET_NUM, TFMM.FLD_CSS_ISSUE_SLA_QTY,

TFMM.FLD_CSS_ISSUE_TAT_QTY,

SUM(IF(TFMM.FLD_PARENT_ID >=0 AND TFMM.FLD_DIRECTION=1,1, 0)) AS INWARD_INTERACTION_COUNT

FROM TBL_FORUMS_MSG_MAIN TFMM FORCE INDEX(FLD_TICKET_NUM, FLD_ADD_DATE_TIME)

INNER JOIN TBL_TEMP_FORUMS_MIN_ADD_DATE_TIME_FOR_TICKET TTFMA FORCE INDEX ( FLD_TIME_DIFF_IN_HRS )

ON(TFMM.FLD_TICKET_NUM = TTFMA.FLD_TICKET_NUM AND TFMM.FLD_PARENT_ID = 0 )

WHERE TFMM.FLD_DIRECTION =1

#AND TFMM.FLD_CSS_ISSUE_CATEGORY > 0

AND TFMM.FLD_ADD_DATE_TIME >= '2008-09-22 00:00:00'

AND TFMM.FLD_ADD_DATE_TIME <= '2008-09-29 23:59:59'

GROUP BY TFMM.FLD_TICKET_NUM

HAVING ( FIRST_RESPONSE_TIME_IN_HRS > FLD_CSS_ISSUE_TAT_QTY

OR FLD_MIN_ADD_DATE_TIME = '0000-00-00 00:00:00'

)

ORDER BY TTFMA.FLD_TIME_DIFF_IN_HRS DESC;

 

In my application it needs to be executed sequencely as it is given... the problem is when i remove order by clause from last query then it takes only 6 sec. to execute otherwise it takes around 16 seconds...

i am not getting why its happning...

Could any body please explain me the reason..

[fenway]

Seriously? That's pretty much impossible to start with.

[corbin]

What the hell?!?!?!

 

 

I don't feel like reading through it to see what you're doing, but I would suggest indexes.  Also, if possible, get rid of the temp table.

[waynew]

Kill it with fire.