mskohut
asked on
Poor SELECT VARCHAR2(4000) performance with Oracle ODBC
We have a database table with a varchar2(4000) text column. All of the rows have less than 200 chars in the text column. We observed that
- SELECT text FROM tablename
is MUCH slower than
- SELECT SUBSTR(text, 1, 200) FROM tablename
(although the result is the same).
We have tested it with different ODBC browsers.
When we examine both versions of the SELECT with a network sniffer, we observe, that version 1 of the SELECT produces more TNS packets than version 2. Also, in version 1 of the SELECT you have much more requests at the network layer than in version 2. I think this is the reason, why a SELECT of 2.500 rows takes 13 seconds with version 1, and 1 second with version 2. Our customer has a slow network with a big latency time.
The Costomer uses Oracle 10.2.0.2.
- SELECT text FROM tablename
is MUCH slower than
- SELECT SUBSTR(text, 1, 200) FROM tablename
(although the result is the same).
We have tested it with different ODBC browsers.
When we examine both versions of the SELECT with a network sniffer, we observe, that version 1 of the SELECT produces more TNS packets than version 2. Also, in version 1 of the SELECT you have much more requests at the network layer than in version 2. I think this is the reason, why a SELECT of 2.500 rows takes 13 seconds with version 1, and 1 second with version 2. Our customer has a slow network with a big latency time.
The Costomer uses Oracle 10.2.0.2.
ASKER
But I use
SELECT SUBSTR(text, 1, 200) FROM tablename
and NOT
SELECT {fn SUBSTRING(text, 1, 200)} FROM tablename
So the ODBC driver should not know that the results can't be greater than 200 characters.
SELECT SUBSTR(text, 1, 200) FROM tablename
and NOT
SELECT {fn SUBSTRING(text, 1, 200)} FROM tablename
So the ODBC driver should not know that the results can't be greater than 200 characters.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
> he will, as by starting the query, oracle will tell odbc what he has to expect to get back
That sounds interesting. Is there any deeper information about that somewhere in the internet?
That sounds interesting. Is there any deeper information about that somewhere in the internet?
not sure... also, if possible, ODBC should be avoided and use OleDb instead...
odbc is "limited" in the basic functionality to 255 characters, so requesting data for a field with potentially more than 255 characters will make it do a certain overhead....