absurd performance with odbc vs direct connection

[freakout42]

connecting to MS SQLServer on the same machine when comparing same query with tsql (freetds-1.4.10 tool) and isql (ODBC tool) i get massive performance differences 0.664 seconds vs 0.005 seconds - any idea? In my application this makes it unusable.

tsql:

Setting rehofpprd2 as default database in login packet
locale is "C"
locale charset is "ANSI_X3.4-1968"
using default charset "ISO-8859-1"
SQL> select * from Arbeitnehmer where ANNr=1901
SQL> go
MDNr  ANNr  LohnAN  Titel  Vorname...
20    1901  1       NULL   Axel  ...
21    1901  1       NULL   Axel  ...
(2 rows affected)

real  0m0.054s
user  0m0.001s
sys 0m0.005s

isql:

1 SQL> select * from Arbeitnehmer where ANNr=1901;
+------------+------------+-------+-------+------------------------------------+-...
| MDNr       | ANNr       | LohnAN| Titel | Vorname                            | ...
+------------+------------+-------+-------+------------------------------------+-...
| 20         | 1901       | 1     |       | Axel                               | ...
| 21         | 1901       | 1     |       | Axel                               | ...
+------------+------------+-------+-------+------------------------------------+-...
SQLRowCount returns 2
2 rows fetched

real  0m2.932s
user  0m0.009s
sys 0m0.664s

thanks for your help.

[lurcher]

On 19/01/2024 07:57, Axel K. Reinhold wrote:

connecting to MS SQLServer on the same machine when comparing same query with tsql (freetds-1.4.10 tool) and isql (ODBC tool) i get massive performance differences 0.664 seconds vs 0.005 seconds - any idea? In my application this makes it unusable.

Hmm, well, other than checking that logging is not on and the standard "isql is intended as a test tool" not sure what to suggest. tsql will just be sending the execute packet, then decoding what comes back and printing it. isql prepares, executes and then fetches a row and each column. Will be faster (maybe) using SQLExecDirect (-e), or writing code to use SQLBindCol().

[freakout42]

i used isql "as a test tool" - i have same bad performance when i use the odbc-connection with a C-program (activerecord odbc-connector). The issue is with all sort of queries. Any idea how to track down this?

[freakout42]

Sorry - it was the ODBC-log - for this special connection it affected the performance so much - rails makes an inventory of all tables on connect and this database has 2000 tables. thanks for your help.