topic Re: Dump in JOIN Query in Application Development and Automation Discussions

Dump in JOIN Query

Former Member — Sun, 21 Dec 2014 00:06:33 GMT

Hello guys, I have a report. in this report I have some queries, if I execute my report with parameters, this does work well, but if I run my report without parameters to filter in query, the report its very slow and I get a Dump. My query where I get the error in code below:


SELECT   d~j_3gbelnri
              d~j_3gposnri
              d~eqart  " Categoria del Equipo
              d~vbeln  " Documento Venta
              d~posnr  "Posicion
              d~netwr  "Valor Neto
              d~waerk  "Moneda
              d~zmeng
              e~vbeln AS vbeln2 "vbeln de la VBRP
              f~knumv
              g~fkdat
              g~rfbsk
      


       FROM vbap AS d
       INNER JOIN vbrp AS e ON e~j_3gbelnri = d~j_3gbelnri
       AND e~vgpos = d~posnr
       INNER JOIN vbak AS f ON d~vbeln = f~vbeln
      INNER JOIN vbrk AS g ON  g~vbeln = e~vbeln
      "INNER JOIN t37ok as h ON
      INTO TABLE it_alv2
       WHERE d~j_3gbelnri IN p_etm.

The dump in image below:

Could anyone can help to let me know if this error its for TimeOut or other? please help me or please tell me if you can see any error in my query.

Re: Dump in JOIN Query

Former Member — Sun, 21 Dec 2014 02:08:58 GMT

This dump usually means that the roll buffer has been exhausted.

Refer note: 185185 - Investigating memory bottlenecks w/o the Memory Inspector

Re: Dump in JOIN Query

matt — Sun, 21 Dec 2014 07:20:47 GMT


Luis Roberto franco wrote:

Hello guys, I have a report. in this report I have some queries, if I execute my report with parameters, this does work well, but if I run my report without parameters to filter in query, the report its very slow and I get a Dump.

If you'd bothered to search, or apply a little thought, you would have realise the dump is an "out of memory " error. That means you've selected too much data for your session to hold. The clue as to why this is happening I've highlighted above.

Re: Dump in JOIN Query

Former Member — Sun, 21 Dec 2014 21:23:21 GMT

I solved my issue with query below:


SELECT   d~j_3gbelnri
              d~j_3gposnri
              d~eqart  " Categoria del Equipo
              d~vbeln  " Documento Venta
              d~posnr  "Posicion
              d~netwr  "Valor Neto
              d~waerk  "Moneda
              d~zmeng "
              e~vbeln AS vbeln2 "vbeln de la VBRP
              f~knumv
              g~fkdat
              g~rfbsk
              "h~eqart as EARTX


       FROM vbap AS d
       INNER JOIN vbrp AS e ON e~j_3gbelnri = d~j_3gbelnri
       AND e~vgpos = d~posnr
       INNER JOIN vbak AS f ON d~vbeln = f~vbeln
      INNER JOIN vbrk AS g ON  g~vbeln = e~vbeln
      "INNER JOIN t37ok as h ON
      INTO TABLE it_alv2
       "WHERE d~j_3gbelnri IN p_etm.
         FOR ALL ENTRIES IN IT_ALV
         WHERE d~j_3gbelnri EQ IT_ALV-j_3gbelnri.

Regards

Re: Dump in JOIN Query

matt — Mon, 22 Dec 2014 07:12:28 GMT

You've not exactly fixed it - you've just put in another selection to narrow the result set.

FOR ALL ENTRIES is in most cases worse performing than a properly constructed INNER JOIN. Sometimes, it is unavoidable - e.g. if you've already got the reference table (in this case IT_ALV) filled from another source over which you've not much control.

If you do use it, you should always check that IT_ALV is not empty - otherwise you again have no narrowing selection.