Testing SQLs

[Former Member]

Hi Everybody,

I have to prepare a report which has extensive usage of SQLs and the performance will be an issue. On top of that many of the SQLs I want to test before being implemented in program.

Is there a place besides SQVI (since my SQLs are very complex) where I can check that my SQLs run fine????

Regards,

Deva.

[Former Member · Accepted solution]

A temporary program will show you that the SQL is "clean" in terms of producing the desired output. However, it will not give you an exact determination of performance... as database table sizes in different environments will dictate performance.

You could use SE30 - Run-time Analzyer to identify areas that have inefficient reads, etc.

[Former Member]

Hi,

Execution of ST04 transaction code displays the statistics relating to database performance in terms of Memory usage, space, SQL requests etc. But this could be analyzed better by basis people.

[Former Member]

Deva, hi

Use transaction ST05 - SQL trace. On the first screen there is a pushbutton 'Enter SQL statement'. This takes you to a screen where you can enter SQL statements and then use the Explain button. It will give you an indication of the index access path that is likely to be used. If you get a 'No explain!' message then it can't understand your SQL statement, however, it is case-sensitive, make sure you enter the SQL syntax in upper case or it will always give you the 'No explain!' message, i.e.

'SELECT BELNR FROM BSIS WHERE BUKRS = '1000'' not 'select belnr from bsis where bukrs = '1000''

Once you have a statement explained, use the 'uncompressed explain' pushbutton to get estimated rows, estimated I/O, and estimated CPU.

Any problems, post back to this thread.

Kind regards

Mark

[Former Member]

hi Mark thanks for the tips.

it will help me also a lot

thanks.

[Former Member]

No other way Deva !!

[RichHeilman]

Can you just test them in a temporary program?

Regards,

Rich Heilman