Oracle client appears to be transmitting all 4000 varchar characters for each row

Question

I have an Oracle database that I connect to over a somewhat slow network link. In there, I have a simple table, similar to this one:

create table EMPLOYEES (
    employee_id INTEGER PRIMARY KEY,
    first_name VARCHAR2(4000)
);

And I'm using a rather boring query to retrieve all rows from it:

select employee_id, first_name from EMPLOYEES

So far so good. However, I'd noticed that when I ask Toad to export all rows resulting from that query, it takes about 4 seconds. In my .NET application, it takes 130 seconds. After quite a bit of hacking, I've noticed that the longest value in the FIRST_NAME column is 50 characters long, so I've altered my query as follows:

select employee_id, substr(first_name, 1, 50) from EMPLOYEES

Now, it is very fast in both Toad and my C#/.NET program. I have tried this using both Microsoft's and Oracle's data provider libraries, with the same result.

What is going on here ? Is Oracle -- as I suspect -- really sending 4000 bytes for each row, leaving it up to the client to cut them down to the right length ? What's the point of "var" in "varchar", then ? Also, how is Toad getting around this problem, and how can I do the same thing ?

Answer 1

No, Oracle is not sending 4,000 bytes. Let's assume you really are spooling all records in Toad. All Toad is doing is fetching the records and printing them to the grid on your screen? What is your code doing? The SQL could be running in the same time, but your code is doing some other processing that is adding the time.

To really know what is going on, you need to do one or two things.

First, do a SQLNet client trace. If that doesn't provide the answer, you need to do a server trace for you session.