TD-23747: Connect/Disconnect using win32client.ws32 very slow

Post found bugs and possible workarounds.
Dave Rabelink
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 261
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

TD-23747: Connect/Disconnect using win32client.ws32 very slow

Post by Dave Rabelink » 09 Oct 2017, 06:40

This issue has been registered as:
Ticket # 3298355

Issue description
It seems that starting with TD63 the performance of SqlConnect and SqlDisconnect have 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:

OveralPerformance.png

See that the same testcase on TD6.1 and TD7.0.3 shows a huge performance difference.

OveralPerformance_graph.png

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.


Testcase:
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.

ConnectDisconnect_PerformanceTest.zip
You do not have the required permissions to view the files attached to this post.
Regards,
Dave Rabelink

Image
Articles and information on Team Developer Tips & Tricks Wiki
Download samples, documents and resources from TD Sample Vault
Videos on TDWiki YouTube Channel

Dave Rabelink
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 261
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

Re: TD-23747: Connect/Disconnect using win32client.ws32 very slow

Post by Dave Rabelink » 11 Oct 2017, 09:49

Video showing testcase in action on SqlBase.


phpBB [video]
Regards,
Dave Rabelink

Image
Articles and information on Team Developer Tips & Tricks Wiki
Download samples, documents and resources from TD Sample Vault
Videos on TDWiki YouTube Channel

Return to “Bug Reports”

Who is online

Users browsing this forum: Ccbot [Crawler] and 0 guests