Re: What's the performance difference: 1 SELECT vs 5 SELECTS

EK

Option 1 is going to be faster on the basis that Oracle has had decades to tune it's product. If option 2 was faster then Oracle would have been laughed off stage long ago. As Jim states - benchmark it if you want to prove it to yourself. Parsing a SELECT * is going to use more CPU than parsing a one column select but not as much as parsing five single column selects. In addition option 1 only requires only 1 parse and 1 execute and 1 fetch call to Oracle whereas the others require five times that much. All this interprocess comms takes a finite albeit small amount of time and if the network is involved you can increase that time even more.

Andy

"Eino Komsi" <komsiikomsaa_at_yahoo.com> wrote in message news:10b3c267.0210242029.5d3ed40f_at_posting.google.com...
> hi,
>
> I have the following table:
>
> CREATE TABLE SOME_TABLE (
> ID NUMBER(10) PRIMARY KEY NOT NULL,
> BIG1 VARCHAR(2000),
> BIG2 VARCHAR(2000),
> BIG3 VARCHAR(2000),
> BIG4 VARCHAR(2000),
> BIG5 VARCHAR(2000)
> );
>
> Now, I perform:
> a) one SELECT like: SELECT * FROM SOME_TABLE WHERE ID = 1234;
> versus
> b) five SELECTs like: SELECT BIG1 FROM SOME_TABLE WHERE ID = 1234;
> SELECT BIG2 FROM SOME_TABLE WHERE ID = 1234;
> SELECT BIG3 FROM SOME_TABLE WHERE ID = 1234;
> SELECT BIG4 FROM SOME_TABLE WHERE ID = 1234;
> SELECT BIG5 FROM SOME_TABLE WHERE ID = 1234;
>
> questions I'd like to know:
> 1. What is the performance diffence between a and b?
> 2. What is the CPU usage on the server in a compared to b ?
>
> a and b have different transactions, one for both. I.e. all the five
> selects are in the same transaction. The table contains like 50000
> rows.
>
> regards, EK
Received on Fri Oct 25 2002 - 02:24:03 CDT