topic Re: SQL and Index in Application Development and Automation Discussions

SQL and Index

Former Member — Mon, 25 May 2009 04:14:07 GMT

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

Re: SQL and Index

HermannGahm — Mon, 25 May 2009 04:59:31 GMT

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

Re: SQL and Index

ThomasZloch — Mon, 25 May 2009 10:04:16 GMT


> i searched the forum

Looking at your questions, I sincerely doubt that. Maybe you didn't use any index while searching?

Thomas

Re: SQL and Index

Former Member — Mon, 25 May 2009 10:06:38 GMT

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

Re: SQL and Index

Former Member — Mon, 25 May 2009 10:25:11 GMT

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