| sql query taking too much of time [message #372384] |
Tue, 06 February 2001 21:36  |
jeya
Messages: 18
Registered: January 2001
|
Junior Member |
|
|
hi,
i am using oracle 8.0.5.0.0. and frontend is powerbulilder 6.5. i
If i run this query it will take 5 minutes time to take the result and put
into datawindow screen in powerbuilder. can anyone suggest to reduce the
time ?
SELECT "EMPL_ASSIGN_TRN_S"."CO_EMP_NO",
"EMPMST_S"."CO_RANK",
"EMPMST_S"."CO_DEPT",
"EMPMST_S"."CO_DIVISION",
"EMPMST_S"."CO_SECTION",
"CLIENT_S"."CUST_NAME",
"EMPL_ASSIGN_TRN_S"."CUST_CODE",
"EMPL_ASSIGN_TRN_S"."CO_BILL_ADDR",
"EMPL_ASSIGN_TRN_S"."CO_SVR_CODE",
"EMPL_ASSIGN_TRN_S"."CS_START_DATE",
"EMPL_ASSIGN_TRN_S"."SA_SAA_CODE",
"EMPL_ASSIGN_TRN_S"."TP_TPT_CODE",
"EXPENSE_S"."EXPENSE_NAME",
"CLIENT_SERVICE_MST_S"."EXPENSE_CLASS",
"SERVICE_S"."DESCRIPTION",
"DIVISN_S"."DESCRIPTION",
"PPDEPT_S"."DESCRIPTION",
"RANKMST_S"."DESCRIPTION",
"SECTION_S"."DESCRIPTION",
"EMPMST_S"."EMP_NAME",
"EMPL_ASSIGN_TRN_S"."START_DATE",
"EMPL_ASSIGN_TRN_S"."END_DATE",
"RANKMST_S"."RANK_ABBRE",
"CLIENT_SERVICE_MST_S"."CO_DEPT",
"CLIENT_SERVICE_MST_S"."CO_DIVISION",
"CLIENT_SERVICE_MST_S"."CO_SECTION"
FROM "CLIENT_S",
"CLIENT_SERVICE_MST_S",
"EMPL_ASSIGN_TRN_S",
"EMPMST_S",
"EXPENSE_S",
"SERVICE_S",
"DIVISN_S",
"PPDEPT_S",
"RANKMST_S",
"SECTION_S"
WHERE ( "EMPL_ASSIGN_TRN_S"."ASSIGN_ACTIVE_TAG" = 'Y' ) AND
( upper(rtrim("EMPL_ASSIGN_TRN_S"."CO_EMP_NO" )) = upper(rtrim(:ls_emp_no)) ) AND
(( :ld_date between "EMPL_ASSIGN_TRN_S"."START_DATE" and "EMPL_ASSIGN_TRN_S"."END_DATE" ) OR
(( "EMPL_ASSIGN_TRN_S"."START_DATE" <= :ld_date ) AND ( "EMPL_ASSIGN_TRN_S"."END_DATE" is null ))) AND
( upper(rtrim("EMPL_ASSIGN_TRN_S"."CO_EMP_NO")) = upper(rtrim("EMPMST_S"."CO_EMP_NO")) ) AND
( "CLIENT_SERVICE_MST_S"."CUST_CODE" = "EMPL_ASSIGN_TRN_S"."CUST_CODE" ) and
( "CLIENT_SERVICE_MST_S"."CO_SVR_CODE" = "SERVICE_S"."SERVICE_CODE" ) and
( upper(rtrim("CLIENT_SERVICE_MST_S"."EXPENSE_CLASS")) = upper(rtrim("EXPENSE_S"."EXPENSE_CLASS" )) ) AND
( upper(rtrim("CLIENT_SERVICE_MST_S"."CO_DEPT")) = upper(rtrim("PPDEPT_S"."CO_DEPT")) ) AND
( upper(rtrim("CLIENT_SERVICE_MST_S"."CO_DIVISION")) = upper(rtrim("DIVISN_S"."CO_DIVISION")) ) AND
( upper(rtrim("CLIENT_SERVICE_MST_S"."CO_SECTION")) = upper(rtrim("SECTION_S"."CO_SECTION")) ) AND
( "CLIENT_S"."CUST_CODE" = "CLIENT_SERVICE_MST_S"."CUST_CODE" ) and
( upper(rtrim("EMPMST_S"."CO_RANK")) = upper(rtrim("RANKMST_S"."CO_RANK")) ) AND
(cs_type = 'RE')
ORDER BY "EMPL_ASSIGN_TRN_S"."START_DATE" DESC
jeya.m
|
|
|
|
| Re: sql query taking too much of time [message #372387 is a reply to message #372384] |
Wed, 07 February 2001 10:14  |
Madhav Kasojjala
Messages: 42
Registered: November 2000
|
Member |
|
|
Remove
all ltrim,rtrim, upper, lower functions in your query. Indexes will not work when you apply SQL functions on them.
And then findout the difference.
I'm assuming you have indexes in the form of at least Primary Keys.
If not you need to create indexes on atomic columns of all the table used in this query.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
