| Performance Issue [Merged] [message #623426] |
Wed, 10 September 2014 05:20  |
 |
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
|
Senior Member |
|
|
Hi,
I have performance issue in a Program,
actually the program has 2 delete statements
and one insert command.
well i tried to analyze that queries, i find that
in a delete statement they are using(in the Project)
substring 3 times in where condition, i like to know
how to avoid substring, instead what else we can use of it.
example query:
select * from emp where
substr(hiredate,1,7) < to_char(add_months(to_date(substr('2014-08-18',1,7),'YYYY-MM'),-16), 'YYYY-MM')
and substr(hiredate,6,2) = '12'
any idea?
|
|
|
|
|
|
|
|
| Re: Performance Issue [message #623433 is a reply to message #623426] |
Wed, 10 September 2014 05:37  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
This is because of your poor design.
A DATE column should always be DATE DATA TYPE. Since you have HIREDATE column as character type, you are/and will always face issues. The first action item for you is to change the data type to DATE as soon as possible.
|
|
|
|
|
|
|
|
| Re: Performance Issue [message #623440 is a reply to message #623432] |
Wed, 10 September 2014 05:42 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
Assuming hiredate is a date that's obviously better to the original but I doubt it'll make any difference to the performance problem.
|
|
|
|
| Re: Performance Issue [message #623442 is a reply to message #623435] |
Wed, 10 September 2014 05:44 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
stalin4d wrote on Wed, 10 September 2014 11:38Its Varchar
Then you're stuck with substr, poor performance and high probability of bugs.
Dates should always be stored in date columns.
|
|
|
|
|
|
|
|
| Re: Performance Issue [message #623448 is a reply to message #623443] |
Wed, 10 September 2014 06:01 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
stalin4d wrote on Wed, 10 September 2014 16:20
Im getting an error when applying this above code;
ORA-3007 Invalid extract field for extract source,
Cause: The extract source does not contain the specified Extract field
SQL> select extract(month from sysdate) from dual;
EXTRACT(MONTHFROMSYSDATE)
-------------------------
9
Now, can you tel the reason why your code throws an error but my code works?
|
|
|
|
| Re: Performance Issue [message #623449 is a reply to message #623447] |
Wed, 10 September 2014 06:02 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
stalin4d wrote on Wed, 10 September 2014 16:29
What to do then, actually the customer/client needs to store the date for some specific reason,
in some specific format for their reports
What is that specific reason and specific format?
|
|
|
|
| Re: Performance Issue [message #623455 is a reply to message #623449] |
Wed, 10 September 2014 06:14 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If the date needs to be in a specific format for a certain purpose then it should be formatted when selecting it for that purpose. That is not a good reason to store it as a varchar.
Michel's code doesn't work for you since he assumed the column was a date.
You could add function based index on substr(hiredate,1,7) and substr(hiredate,6,2), but really you should make the column the correct datatype.
[Updated on: Wed, 10 September 2014 06:14] Report message to a moderator |
|
|
|
|
|
| Re: Performance Issue [message #623462 is a reply to message #623461] |
Wed, 10 September 2014 07:00 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Don't bother.
Extract is function used on dates (as is trunc). You don't have a date. So there's no point you trying to use it. Stick with the substr or change the datatype of the column.
|
|
|
|
|
|
|
|
| Re: Performance Issue [message #623472 is a reply to message #623470] |
Wed, 10 September 2014 08:10 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
trunc fails for exactly the same reason as extract.
But you're missing the point. extract and trunc are not more efficient than substr. They are correct tools to use when working with dates, but you don't have a date. Using trunc and extract in your case will not improve the performance of the query at all.
If substr is the cause of your performance issue then it'll be for the same reason as using any function in a where clause is a performance issue - it stops you using indexes. Converting the varchar to a date and using trunc and extract isn't going to fix that at all.
If the column was a date then there would be ways of writing the query to allow normal index use, but that isn't the case.
That said, as I already pointed out, you can use a function based index.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to Remove Substring Value [message #623857 is a reply to message #623856] |
Mon, 15 September 2014 08:37 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
It has been already suggested to you to change the data type from VARCHAR2 to DATE. Then you won't have to use SUBSTR function at all. As of now, if I remember COM already suggested a workaround to create a function-based index.
|
|
|
|
| Re: How to Remove Substring Value [message #623860 is a reply to message #623857] |
Mon, 15 September 2014 09:46 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
@OP, the two topics have been merged since they address the same issue as described by you. Please don't open new topic, rather use this topic itself.
You have not provided any feedback to the suggestions given to you above. What did you try so far? Did you implement the suggestions?
|
|
|
|
|
|
| Re: How to Remove Substring Value [message #623959 is a reply to message #623883] |
Tue, 16 September 2014 14:38 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
What version of the database are you using?. you can make a virtual column in versions 11 and 12 where the database would generate a column containing the date value, which you could index and use in your query.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to Remove Substring Value [message #624098 is a reply to message #624012] |
Wed, 17 September 2014 14:26 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If you can't add indexes then substr is probably as good as it gets.
Substr is pretty efficient, so the only likely ways to speed it up are:
a) use less functions - you'll need to make the column date datatype for that.
b) use function based indexes
Since neither option is available to you you're probably stuck with bad performance.
|
|
|
|
| Re: How to Remove Substring Value [message #624099 is a reply to message #624004] |
Wed, 17 September 2014 14:29 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
stalin4d wrote on Wed, 17 September 2014 04:47I didnt tried a function based index, as instructed and strictly adviced by
the client we cant modify the indexes in that table, because it may
affect the production because the same table is used in many programs,
so we are trying at query level.
adding a function based index will NOT alter the other queries unless the query exactly matches your where clause which will make that particular query run faster.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
