Today, special thanks go out to my buddy Jeremy. He set me straight on some things, and helped me streamline my code in my service management dashboard.
I’m quickly learning that my system works great, except when it comes to volume testing. (I’m working on this in a separate initiative). But back to my original thought. The select statement. I found out recently that using the SELECT with an ENDSELECT Statement to close it off, and then add some logic in the middle is terrible when it comes to database performance. Take this example:
Select vbeln posnr from VBAP into (lv_vbeln, lv_posnr)
for all entries in table LT_TABLE
where pstyv = ‘TAC’.
<add some logic here>
endselect.
While this code works, and will produce the correct results, as the table VBAP grows, so will the runtime. Now, the better approach (thanks Jer), is to build and internal table first, then loop through it… like this.
define a type and a table for lt_result with the fields you want (vbeln & posnr)
Select vbeln posnr from VBAP lt_result
for all entries in table LT_TABLE
where pstyv = ‘TAC’.
loop at lt_result into wa_result.
<add some logic here>
endloop.
Now, this will improve performance, but the real trick to employ if you need to improve performance is to provide an index on the table you are seeing poor performance. In essence, the index on a table is a lot like changing the key fields set for the table, and allows for faster searching. This obvious involves a basis person and some additional storage space. So, if you need to improve performance use the following guidelines.
1. try to set the key fields in the where statement.
2. avoid the select/endselect combination.
3. if using inner join, make sure you need to do it, and try to limit the number of inner joins to 2 or 3 tables max.
if you follow these guidelines, you code should work in nearly any enviroment.
Again, thanks Jer. And for everyone else, Thanks for reading.
Mike