Re: Q: Performance

From: vertis <vertis_at_xs1.xs4all.nl>
Date: 1995/08/02
Message-ID: <3vn7qh$lkd_at_news.xs4all.nl>#1/1

martinj_at_pi.net wrote:

: Hello,
 

: Because of daily usage of large tables, I have some performance questions I'd
: like answers to:
 

: 1. What's faster:
: -----------------
: A. select rownum from A where A.field1 NOT IN
: (select fieldA from B)
: B. select rownum from A where NOT EXISTS
: (select 'x' from B where A.field1 = B.fieldA)
: C. Anything else

Sorry, but there is no single answer to that: A is evaluated by:

- fetching all rows from B (full table)
- ordering the rows
- loop : fetch a row from A and check the condition

B is evaluated by:

- fetch a row from A
- check condition in B (possibly indexed, first row found stops subquery)
- next row from A

Thus, if table B is large query A will probably sort on disk (slow)

Table B small, table A small: don't care
Table B large, A small: query A slow (sort on disk), B okay if indexed
Table B small, A large: query A okay, B slow (subquery many times)
Table B large, A large: query A slow, B slow (depends on size of sort 

area in memory, is B indexed on fieldA). My impression is that with big tables B performs better (I've seen examples of a factor 10). Appearently, sorting on disk is more a performance problem than redoing a query.

: 2. Are there performance differences between:
: ---------------------------------------------
: A. Select count(*) ...
: B. Select count(rownum) ...
: C. Select count('x') ....

A will fetch the complete row then count, C will fetch 'x', B I don't know.

: 3. What's faster:
: -----------------
: A. Insert into ... values (....) as select ...
: B. Insert with a cursor

A is faster especially in client server environments:

A will cause almost no network traffic (just parse and execute calls) B will fetch the rows from the server to the client (parse once, fetch many times) and send insert statements back to the server (parse, execute for each fetched row).

Hopes this helps,

Teijo Doornkamp (doornkampt_at_vertis.nl) Received on Wed Aug 02 1995 - 00:00:00 CEST