Ticket # 3298355
It seems that starting with TD63 the performance of
SqlDisconnecthave decreased when using win32client.ws32 (SqlBase API).
Normally, the first SqlConnect takes some time. The needed routers need to be loaded and initialized.
But after that, subsequent SqlConnects/Disconnects will be faster.
Overall we have seen that, specially when using SqlBase API with DB2 (mainframe) all subsequent SqlConnects take the same (longer) time.
It seems that the routers are initialized every time when a connect is performed.
There are several scenarios tested:
1) A single SqlConnect and SqlDisconnect, repeated X times
2) Multiple (nested) SqlConnect and SqlDisconnect, repeated X times
In scenario 1, the performance issue is seen. Every connect takes the same amount of time as the first one. There is no performance gain to be seen compared to pre-TD63 versions.
Scenario 2 shows there is a performance gain. This makes us believe that when the last active SQL handle is closed on the router, it uninitializes completely.
So then when a new connection is created, the router is initialized again compared to the very first one. Which actually performs badly.
We have also seen that the overall performance of queries is also affected, but slightly.
Reason for this change could be that TD6.3 uses an unified router for all DB connections using SqlBase API. So there was a major internal (architectural) change.
Here some details:
See that the same testcase on TD6.1 and TD7.0.3 shows a huge performance difference.
See that the first connect (scenario 1) always performs badly and will keep performing badly when the last SQL handle is closed and a new one is created.
(first bars at the left in the graph).
The testcase also shows that using another router (Oracle) does not have this issue.
Also testing SqlBase as database shows performance loss. Makes a strong suggestion that the new router architecture has performance impact.
Using SqlBase API (needed dlls) from TD 6.1 in TD 7.0.3 shows that the performance issue is gone and even performs better generally than the official TD7 SqlBase API dll's.
Both TD61 and TD7 versions. It uses TDAppTools Sql Monitor to display the performance on each DB action.
How to test: change the SqlDatabase/Use/Password variables according to your local setup.
Press Sql Monitor button first. It opens TDAppTools Sql Monitor main GUI.
Press the several buttons on the test GUI and see the results in Sql Monitor.