• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1306
  • Last Modified:

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.
0
mskohut
Asked:
mskohut
  • 3
  • 2
1 Solution
 
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
 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now