[Java lista] Jasper report mezőbe SQL query?

[Zoltán Gáspár]

Tesztet, tesztet! :) Nem nagy táblák (~1000 sor), de talán látszik a
különbség.

Scalar.sql:

select c.id, c.title, (select name from content_type ct where ct.id=c.type_id
) ct_name from content c

Join.sql:

select c.id, c.title, ct.name from content c, content_type ct
where ct.id = c.type_id

SQL> set autotrace traceonly

SQL> @scalar

1056 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3276196598

--------------------------------------------------------------------------------
-----

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------
-----

| 0 | SELECT STATEMENT | | 1056 | 95040 | 6 (0)| 00:00:01 |

|* 1 | INDEX UNIQUE SCAN| PK_CONTENT_TYPE | 1 | 41 | 0 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| CONTENT | 1056 | 95040 | 6 (0)| 00:00:01 |

--------------------------------------------------------------------------------
-----


Predicate Information (identified by operation id):
---------------------------------------------------

  1 - access("CT"."ID"=:B1)


Statistics
----------------------------------------------------------
  0 recursive calls
  0 db block gets
  95 consistent gets
  0 physical reads
  0 redo size
  77483 bytes sent via SQL*Net to client
  1155 bytes received via SQL*Net from client
  72 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1056 rows processed

SQL> @join

1056 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 4022228688

--------------------------------------------------------------------------------
------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time  |

--------------------------------------------------------------------------------
------

| 0 | SELECT STATEMENT | | 1056 | 135K| 6 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1056 | 135K| 6 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| CONTENT | 1056 | 95040 | 6 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN| PK_CONTENT_TYPE | 1 | 41 | 0 (0)| 00:00:01 |

--------------------------------------------------------------------------------
------


Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("CT"."ID"="C"."TYPE_ID")


Statistics
----------------------------------------------------------
  0 recursive calls
  0 db block gets
 163 consistent gets
  0 physical reads
  0 redo size
  84423 bytes sent via SQL*Net to client
  1155 bytes received via SQL*Net from client
  72 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1056 rows processed

SQL>

Szerintem Oracle alatt legalább is a scalar subquery jobb. Hogy a
subreportról ne is beszéljünk 1+N hívás jobb, mint 1?

Roger

On 6/4/08, Gergely Hodicska <felho at avalon.aut.bme.hu> wrote:
>
> > és még performace-ra is király lesz
>
> Ez a fajta subquery kiváltható joinnal, ami gyorsabb is lesz.
>
>
> Üdv,
> Felhő
>
> _______________________________________________
> Javalist mailing list
> Javalist at javagrund.hu
> http://javagrund.hu/mailman/listinfo/javalist
>
--------- következő rész ---------
Egy csatolt HTML állomány át lett konvertálva...
URL: http://javagrund.hu/pipermail/javalist/attachments/20080604/fb1b409f/attachment-0001.html