I have tried many methods, including using SQLBindCol interface and setting up SQL_ ATTR_ ROW_ SET_ SIZE, using BLOCKFETCH, BLOCKSIZE, and so on, it seems that only SQLBindCol can bring significant performance improvements(Compared to SQLGetData, the query time has been reduced by half), as SQLBindCol does indeed reduce the number of interactions with the database when obtaining LOB data, and interaction in such a network environment is expensive.
It seems that there is no other way to further reduce the number of interactions.
Fortunately, the efficiency improvement of using multiple ODBC connections for parallel queries is very significant.
Original Message:
Sent: Thu January 11, 2024 06:18 AM
From: Satid Singkorapoom
Subject: IBM i access ODBC for Linux low network performance
BR L
>>>> And the RTT for communication with PUB400 is about 200 ms, so approximately 5 rows of records can be obtained per second? <<<<
Using PING response time as a reference is NOT a sensible way. PING works on ICMP protocol which is different from TCP and IP that ODBC uses.
>>>> However, when fetching multiple rows (set SQL_ATTR_ROW_ARRAY_SIZE to 100), it seems that the number of interactions with the database is the same as when fetching a single row. <<<<
ODBC also has protocol exchanges between client and server that contributes handsomely to performance overhead when it goes on wide area network such as internet. MTU size that is not large used by IP gateways along the way can be another negative performance factor. In summary, you do not have much control over these external factors.
------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.
Original Message:
Sent: Wed January 10, 2024 09:58 AM
From: BR L
Subject: IBM i access ODBC for Linux low network performance
No, it still very slow.
200 rows, isql use about 45 seconds
Perhaps the query speed is related to network latency?
And the RTT for communication with PUB400 is about 200 ms, so approximately 5 rows of records can be obtained per second?
However, when fetching multiple rows (set SQL_ATTR_ROW_ARRAY_SIZE to 100), it seems that the number of interactions with the database is the same as when fetching a single row.
------------------------------
BR L
Original Message:
Sent: Wed January 10, 2024 07:24 AM
From: Robert Berendt
Subject: IBM i access ODBC for Linux low network performance
Try this:
Create a table which contains only the rows you want.
Then, using ODBC, select the rows from that table.
Was it significantly faster? If so, then I suspect my earlier question about 'linked server' comes into play.
Basically, without a linked server, all rows from the table are downloaded to the client and the WHERE clause is executed locally. So you have that download time to deal with. With a linked server, the WHERE clause is executed on the server and only the rows which meet that where clause are returned to the client. Much less data transmission.
------------------------------
Robert Berendt IBMChampion
Original Message:
Sent: Wed January 10, 2024 04:31 AM
From: BR L
Subject: IBM i access ODBC for Linux low network performance
odbc.ini:
Do query:
This query contains 200 rows and takes 45 seconds
Bandwidth:
Embeded SQL:
Static SQL:
EXEC SQL DECLARE clog CURSOR FOR SELECT CURRENT_TIMESTAMP, ENTRY_TIMESTAMP, SEQUENCE_NUMBER, JOURNAL_CODE, JOURNAL_ENTRY_TYPE, ENTRY_DATA, NULL_VALUE_INDICATORS, OBJECT, COMMIT_CYCLE FROM TABLE(QSYS2.DISPLAY_JOURNAL(:schema, :jnrname, STARTING_SEQUENCE => :curSeq1, ENDING_SEQUENCE => :curSeq1 + 200, STARTING_RECEIVER_LIBRARY => :rcvlib, STARTING_RECEIVER_NAME => :rcvname));
Program out put:
And bandwidth
I think the problem that may lead to low query efficiency is network latency rather than bandwidth.
So is there any way to reduce the number of interactions, similar to receive multiple rows at once.
------------------------------
BR L
Original Message:
Sent: Wed January 10, 2024 01:50 AM
From: Richard Schoen
Subject: IBM i access ODBC for Linux low network performance
It would be interesting to see how the isql utility performs. Do a few test queries with that.
It could come down to bandwidth as Satid mentioned.
------------------------------
Richard Schoen
Owner/President
MobiGoGo LLC
Minneapolis MN
612-315-1745
Original Message:
Sent: Fri January 05, 2024 02:05 AM
From: BR L
Subject: IBM i access ODBC for Linux low network performance
Hi all. I'm looking for someone with a better knowledge about ODBC performance, who can help me in this situation.
Environment:
Centos7.9
ibm-iaccess-1.1.0.27-1.0.x86_64
unixODBC-2.3.1-14.el7.x86_64
unixODBC-devel-2.3.1-14.el7.x86_64
Scenario
Connect to PUB400 and send a SQL like (SELECT DATA FROM TESTTAB LIMIT 200)
When I use embedded SQL, this query statment can return all data in 1 seconds, and the network speed can reach 100KB/s.
But when I use ODBC to query, it may take more than 20 seconds, and the network speed only 10KB/s or more slow.
Even I set attribute below:
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)20, 0); // get 20 rows on 1 SQLFetch(stmt);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, (SQLPOINTER)SQL_BIND_BY_COLUMN, 0);
SQLBindCol(hstmt, 1, SQL_C_BINARY, dbData, 1024, NULL);
In odbc.ini
[pub400]
Description = System i Access ODBC Driver DSN for i5/OS
Driver = IBM i Access ODBC Driver 64-bit
System =
UserID =
Password =
Naming = 0
DefaultLibraries =
Database =
ConnectionType = 0
CommitMode = 2
ExtendedDynamic = 1
DefaultPkgLibrary =
DefaultPackage = A/DEFAULT(IBM),2,0,1,1,512
AllowDataCompression = 1
LibraryView = 0
AllowUnsupportedChar = 0
ForceTranslation = 0
Trace = 0
ExtendedColInfo = 1
MULTICONTEXT = 1
CONNECTTYPE = 1
MaxFieldLength = 2097152
QueryTimeout = 1
------------------------------
BR L
------------------------------