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.
mskohutAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>So the ODBC driver should not know that the results can't be greater than 200 characters.
he will, as by starting the query, oracle will tell odbc what he has to expect to get back,
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>We have tested it with different ODBC browsers.
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....
0
 
mskohutAuthor Commented:
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.
0
 
mskohutAuthor Commented:
> 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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not sure... also, if possible, ODBC should be avoided and use OleDb instead...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.