topic Re: Performance issue on select in Application Development and Automation Discussions https://community.sap.com/t5/application-development-and-automation-discussions/performance-issue-on-select/m-p/2461424#M552270 <HTML><HEAD></HEAD><BODY><P>HI,</P><P> IT IS CLEARLY MENTIONED IN YOUR BASIS TEAM REPORT THAT USING THE FIRST SELECT QUAERY HAS TAKEN LESS TIME THEN THE SECOND SELECT QUAERY SEE TO THE TIME FRAME</P></BODY></HTML> Tue, 26 Jun 2007 10:41:02 GMT Former Member 2007-06-26T10:41:02Z Performance issue on select https://community.sap.com/t5/application-development-and-automation-discussions/performance-issue-on-select/m-p/2461421#M552267 <HTML><HEAD></HEAD><BODY><P>Hi friends,</P><P></P><P>Please let me know which one is better for better performance.</P><P>I have checked the internal table fp_i_cleardocs is not empty in both the cases.</P><P></P><P>Code 1:</P><PRE><CODE> SELECT zbukr rzawe uzawe laufd laufi lifnr kunnr vblnr waers name1 znme1 znme4 zpstl zort1 zstra zpfac zregi zbnks zbnkn zbnky zbkon zaldt ubknt ubnkl rwbtr rwskt bkref FROM reguh INTO TABLE fp_i_reguh FOR ALL ENTRIES IN fp_i_cleardocs WHERE laufd IN l_i_rundt AND zbukr = fp_i_cleardocs-bukrs AND ( lifnr = fp_i_cleardocs-accno OR kunnr = fp_i_cleardocs-accno ) AND vblnr = fp_i_cleardocs-augbl AND rzawe = fp_i_cleardocs-zlsch. </CODE></PRE><P></P><P>Code 2:</P><P>Removing OR condition in the where clause....</P><PRE><CODE> SELECT zbukr rzawe uzawe laufd laufi lifnr kunnr vblnr waers name1 znme1 znme4 zpstl zort1 zstra zpfac zregi zbnks zbnkn zbnky zbkon zaldt ubknt ubnkl rwbtr rwskt bkref FROM reguh INTO TABLE fp_i_reguh FOR ALL ENTRIES IN fp_i_cleardocs WHERE laufd IN l_i_rundt AND zbukr = fp_i_cleardocs-bukrs AND ( lifnr = fp_i_cleardocs-accno ) AND vblnr = fp_i_cleardocs-augbl AND rzawe = fp_i_cleardocs-zlsch. SELECT zbukr rzawe uzawe laufd laufi lifnr kunnr vblnr waers name1 znme1 znme4 zpstl zort1 zstra zpfac zregi zbnks zbnkn zbnky zbkon zaldt ubknt ubnkl rwbtr rwskt bkref FROM reguh appending TABLE fp_i_reguh FOR ALL ENTRIES IN fp_i_cleardocs WHERE laufd IN l_i_rundt AND zbukr = fp_i_cleardocs-bukrs AND ( kunnr = fp_i_cleardocs-accno ) AND vblnr = fp_i_cleardocs-augbl AND rzawe = fp_i_cleardocs-zlsch. </CODE></PRE><P></P><P></P><P>Thanks </P><P>Poorna</P></BODY></HTML> Tue, 26 Jun 2007 09:58:34 GMT https://community.sap.com/t5/application-development-and-automation-discussions/performance-issue-on-select/m-p/2461421#M552267 Former Member 2007-06-26T09:58:34Z Re: Performance issue on select https://community.sap.com/t5/application-development-and-automation-discussions/performance-issue-on-select/m-p/2461422#M552268 <HTML><HEAD></HEAD><BODY><P>Why don't you just use SE30 to test it? The performance difference may depend on the system. In general I would advise that you use the OR option instead of using 2 selections, the only exception would be if the OR led to the database not using a very good index.</P><P></P><P>You can use ST05 and the button &lt;b&gt;Enter SQL Statement&lt;/b&gt; to get a selection 'explained' which means you will be told what the selection strategy will be. Then you can see if the OR option influences index selection.</P></BODY></HTML> Tue, 26 Jun 2007 10:10:34 GMT https://community.sap.com/t5/application-development-and-automation-discussions/performance-issue-on-select/m-p/2461422#M552268 KjetilKilhavn 2007-06-26T10:10:34Z Re: Performance issue on select https://community.sap.com/t5/application-development-and-automation-discussions/performance-issue-on-select/m-p/2461423#M552269 <HTML><HEAD></HEAD><BODY><P>Hi Kjetil ,</P><P></P><P></P><P>Thank you for your response.</P><P>I got the below analysis from the basis team.</P><P>Please help me if you understand anything from the below.</P><P> </P><P></P><P></P><P>SELECT zbukr</P><P> rzawe</P><P> uzawe</P><P> laufd</P><P> laufi</P><P> lifnr</P><P> kunnr</P><P> vblnr</P><P> waers</P><P> name1</P><P> znme1</P><UL><LI level="1" type="ul"><P> znme4 "UTP1797</P></LI></UL><P> znme2 "UTP1797</P><P> zpstl</P><P> zort1</P><P> zstra</P><P> zpfac</P><P> zregi</P><P> zbnks</P><P> zbnkn</P><P> zbnky</P><P> zbkon</P><P> zaldt</P><P> ubknt</P><P> ubnkl</P><P> rwbtr</P><P> rwskt</P><P> bkref</P><P> FROM reguh INTO TABLE fp_i_reguh</P><P> FOR ALL ENTRIES IN fp_i_cleardocs</P><P> WHERE laufd IN l_i_rundt</P><P> AND zbukr = fp_i_cleardocs-bukrs</P><P> AND ( lifnr = fp_i_cleardocs-accno OR kunnr = fp_i_cleardocs-accno )</P><P> AND vblnr = fp_i_cleardocs-augbl</P><P> AND rzawe = fp_i_cleardocs-zlsch.</P><P></P><P> ENDIF.</P><P></P><P></P><P>SQL Statement</P><P></P><P>SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ? UNION ALL</P><P> SELECT "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" , "ZSTRA" ,</P><P> "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" , "ZALDT" , "UBKNT" ,</P><P> "UBNKL" , "RWBTR" , "RWSKT" , "BKREF" FROM "REGUH" WHERE "MANDT" = ? AND "ZBUKR" =</P><P> ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR" = ? AND "RZAWE" = ?</P><P></P><P></P><P>Access Plan Opt Level = 5 ; Parallelism = None</P><P></P><P></P><P> 0 SELECT STATEMENT ( Estimated Costs = 1.229E+07 [timerons] )</P><P> </P><TABLE><TBODY><TR><TD><P></P><P> --- 1 RETURN</P><P></P></TD></TR></TBODY></TABLE><P> --- 2 UNION</P><P> </P><TABLE><TBODY><TR><TD><P></P><P></P></TD><TD><P>-- 3 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 4 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 5 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 6 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 7 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 8 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 9 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 10 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 11 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 12 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 13 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 14 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 15 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 16 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 17 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 18 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 19 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 20 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 21 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 22 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 23 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 24 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 25 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 26 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 27 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 28 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 29 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 30 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 31 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 32 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 33 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 34 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 35 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 36 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 37 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 38 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><P>-- 39 FETCH REGUH</P><P></P></TD><TD></TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD><HR originaltext="-----" /><P> 40 IXSCAN REGUH~Z01 #key columns: 2</P><P></P></TD></TR></TBODY></TABLE><P> --- 41 FETCH REGUH</P><P> |</P><P> -</P><HR originaltext="-----" /><P> 42 IXSCAN REGUH~Z01 #key columns: 2</P><P></P><P>Simple case</P><P></P><P>SQL Statement</P><P></P><P> SELECT</P><P> "ZBUKR" , "RZAWE" , "UZAWE" , "LAUFD" , "LAUFI" , "LIFNR" , "KUNNR" ,</P><P> "VBLNR" , "WAERS" , "NAME1" , "ZNME1" , "ZNME2" , "ZPSTL" , "ZORT1" ,</P><P> "ZSTRA" , "ZPFAC" , "ZREGI" , "ZBNKS" , "ZBNKN" , "ZBNKY" , "ZBKON" ,</P><P> "ZALDT" , "UBKNT" , "UBNKL" , "RWBTR" , "RWSKT" , "BKREF"</P><P> FROM</P><P> "REGUH"</P><P> WHERE</P><P> "MANDT" = ? AND "ZBUKR" = ? AND ( "LIFNR" = ? OR "KUNNR" = ? ) AND "VBLNR"</P><P> = ? AND "RZAWE" = ?</P><P></P><P>Access Plan Opt Level = 5 ; Parallelism = None</P><P></P><P> 0 SELECT STATEMENT ( Estimated Costs = 6.145E+05 [timerons] )</P><P> 1 RETURN</P><P> 2 FETCH REGUH</P><P> 3 IXSCAN REGUH~Z01 #key columns: 2</P><P></P><P>SQL summary</P><P></P><P>Executions Identical Duration Records Time/exec Rec/exec. AvgTime/R. MinTime/R. Length BfTp TabType Obj. name</P><P></P><P> 74 0 4288,723,396 774 57,955,722 10.5 5,540,986 2,631,290 3,488 deact TRANSP REGUH</P><P> 1 0 37,874,064 734 37,874,064 734.0 51,600 51,600 2,016 deact TRANSP BSAK</P><P> 1 0 6,639,961 319 6,639,961 319.0 20,815 20,815 2,016 deact TRANSP BSAD</P><P> 37 0 4,232,595 2,456 114,394 66.4 1,723 279 2,016 deact TRANSP BSAK</P><P> 772 0 1,176,735 780 1,524 1.0 1,509 245 3,968 deact CLUSTER REGUC</P><P></P><P></P><P>Thanks in advance</P><P>Poorna.</P></BODY></HTML> Tue, 26 Jun 2007 10:28:57 GMT https://community.sap.com/t5/application-development-and-automation-discussions/performance-issue-on-select/m-p/2461423#M552269 Former Member 2007-06-26T10:28:57Z Re: Performance issue on select https://community.sap.com/t5/application-development-and-automation-discussions/performance-issue-on-select/m-p/2461424#M552270 <HTML><HEAD></HEAD><BODY><P>HI,</P><P> IT IS CLEARLY MENTIONED IN YOUR BASIS TEAM REPORT THAT USING THE FIRST SELECT QUAERY HAS TAKEN LESS TIME THEN THE SECOND SELECT QUAERY SEE TO THE TIME FRAME</P></BODY></HTML> Tue, 26 Jun 2007 10:41:02 GMT https://community.sap.com/t5/application-development-and-automation-discussions/performance-issue-on-select/m-p/2461424#M552270 Former Member 2007-06-26T10:41:02Z Re: Performance issue on select https://community.sap.com/t5/application-development-and-automation-discussions/performance-issue-on-select/m-p/2461425#M552271 <HTML><HEAD></HEAD><BODY><P>Hi Phanindra,</P><P></P><P>The analysis sent by basis team is for one select statement only.</P><P></P><P>Any help is appreciated.</P><P></P><P>Thanks and regards</P><P>Poorna</P></BODY></HTML> Tue, 26 Jun 2007 10:56:25 GMT https://community.sap.com/t5/application-development-and-automation-discussions/performance-issue-on-select/m-p/2461425#M552271 Former Member 2007-06-26T10:56:25Z Re: Performance issue on select https://community.sap.com/t5/application-development-and-automation-discussions/performance-issue-on-select/m-p/2461426#M552272 <HTML><HEAD></HEAD><BODY><P>I suggest you talk to your basis team (again). Did you or did you not use SE30? It seems to me the basis team has used ST05 not to get the statement explained, but to dump the trace results to you.</P><P></P><P>If you compare the ST05 explanation for the two (three) cases you can see whether a better index will be used when you split the selection. If that is not the case there is no point (that I can think of now) in splitting it.</P><P></P><P>I think the entries with "IXSCAN REGUH~Z01 #key columns: 2" means index Z01 of table REGUH has been used with 2 key columns specified. So perform the other selection(s) as well and see if it is the same index that gets used.</P><P></P><P>Index Z01 is a customer index. Check its definition to see if you can specify additional fields in your selection, as this will reduce the number of entries that will be checked. However, unless you can specify all fields consecutively from the first field there won't be any significant reduction. </P><P></P><P>Example: assume your index is defined with the following fields: </P><P>1: ZBUKR</P><P>2: RZAWE</P><P>3: LIFNR</P><P>4: KUNNR</P><P></P><P>You could then possibly speed up your selection significantly by changing</P><PRE><CODE>( lifnr = fp_i_cleardocs-accno OR kunnr = fp_i_cleardocs-accno ) </CODE></PRE><P>to </P><PRE><CODE> ( lifnr = fp_i_cleardocs-accno OR lifnr = '0000000000' ) AND ( kunnr = fp_i_cleardocs-accno OR kunnr = '0000000000' ) </CODE></PRE><P></P><P>Assuming the above change is relevant, of course (i.e. the vendor number is either the account number or initial) - but I hope you get the drift. You need to study your indices and tables for possibilities.</P></BODY></HTML> Tue, 26 Jun 2007 15:34:52 GMT https://community.sap.com/t5/application-development-and-automation-discussions/performance-issue-on-select/m-p/2461426#M552272 KjetilKilhavn 2007-06-26T15:34:52Z