05-25-2009 5:14 AM
Hi all,
i searched the forum but am still lost for the following.
1. If sql created with search criteria does not match the index created, will it cause performance to be slower?
2. May i know what are the things to take note when creating index for better performance?
3. How can i test and verify that my sql and index is created with better performance?
Thanks in advance.
Rgds,
Simple
05-25-2009 5:59 AM
Hi Simple,
>
> 1. If sql created with search criteria does not match the index created, will it cause performance to be slower?
> Simple
if you end up with an inapproriate execution plan: yes.
>
> 2. May i know what are the things to take note when creating index for better performance?
> Simple
try put those fields in the index that are used with = and in in the beginning.
Fields with selective ranges (LIKE PATTERN% or BETWEEN, ...) may be addeed.
choose these fields from those conditions that limit your result set most.
Think of other sqls too.... if this is not your only and/or most important sql statement.
Considering other sqls too, try to build 'good enough' indexes. Building the optimal index
for each statement would lead to a high number of indexes.
>
> 3. How can i test and verify that my sql and index is created with better performance?
> Simple
Use ST05. In the STatements Summary evaluate the avg time/ r value.
Other than that use the database specific cursor cache (ST04) and check how much pages/blocks are
read per row or execution.
Kind regards,
Hermann
05-25-2009 11:04 AM
>
> i searched the forum
Looking at your questions, I sincerely doubt that. Maybe you didn't use any index while searching?
Thomas
05-25-2009 11:06 AM
Hi,
check below link for index for better formanes
SE30
/people/siegfried.boes/blog/2007/11/13/the-abap-runtime-trace-se30--quick-and-easy
ST05
/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy
Regards,
Madhu
05-25-2009 11:25 AM
the questions which you ask don't like coming from a real problem but it is more an interview type:
+ First you should ignore the index, and determine whether performance is o.k. or not,
the above mentioned blog on the SQL tells you hwo.
Usually the time per row is used as indicator (you must verify it), whether reading is slow or
fast. Please do not forget to execute it several times, before you take the trace.
+ if it is slow you should determine why, a missing index is one of several possibilities.
Lets assume the index is the reason. Then you must check, whether you task is important
enough to create an index, a one-time event does not get an index.
Index creation for one Select is usally not so difficullt, index creation with check other accesses,
need several pages ... After creation in dictionary, the index must be created on the database
and statistics must be created.
+ To confirm the usage of the index, the SQL trace helps you again, the new index should appear in
the access path
=> but most important the execution time should be much better
Siegfried