Using the NonBlocking specific option with UniDAC 7.0.2, SQL Server and Delphi 7

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Christiano Kiss
Posts: 4
Joined: Mon 26 Jun 2017 17:26

Using the NonBlocking specific option with UniDAC 7.0.2, SQL Server and Delphi 7

Post by Christiano Kiss » Mon 12 Nov 2018 13:45

Hello.

I'm using UniDAC 7.0.2 with Delphi 7 and SQL Server 2014.

I'm trying to use the NonBlocking specific option but the main form still freezes if I open a "slow" query. To reproduce this behavior, I've started a transaction in SQL Server and locked a entire table. I'm selecting the same table in a TUniQuery component with NonBlocking = True.

The form is a simple VCL TForm with TDBGrid, TDataSource, TUniConnection and TSQLServerUniProvider.

I'm unsure if I understood the "non blocking" concept correctly. Does the component creates an implicit thread to load the records in background or do I really need to create my own TThread?

Thanks in advance.

Christiano Kiss
Posts: 4
Joined: Mon 26 Jun 2017 17:26

Re: Using the NonBlocking specific option with UniDAC 7.0.2, SQL Server and Delphi 7

Post by Christiano Kiss » Tue 13 Nov 2018 14:44

Just an update:

1. I've tested the same scenario with SDAC 4.35.1.16 (Delphi 7 and SQL Server 2014) and it worked perfectly fine. The SQL command ran asynchronously as expected and the form didn't freeze. I know UniDAC and SDAC are different components, but I thought it was important to point it out.

2. I've also tested UniDAC 7.0.2 with Delphi 10.2 Tokyo and got the same "freezing" issue, which means that the query isn't being executed in a separete thread.

The provider mode in TUniConnection specific options for SQL Server is prAuto. Since I have SQL Server Native Client installed, I assume that TUniConnection is using it instead of direct mode (which I've also tested, just in case, but it didn't work either).
Last edited by Christiano Kiss on Fri 16 Nov 2018 15:08, edited 1 time in total.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Using the NonBlocking specific option with UniDAC 7.0.2, SQL Server and Delphi 7

Post by Stellar » Wed 14 Nov 2018 11:25

We tested the operation of SDAC and the issue with blocking the main thread when using NonBlocking is reproduced including SDAC 4.35.1.16 (Delphi 7 and SQL Server 2014). To investigate this behavior of SDAC, please compose a small sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html

Christiano Kiss
Posts: 4
Joined: Mon 26 Jun 2017 17:26

Re: Using the NonBlocking specific option with UniDAC 7.0.2, SQL Server and Delphi 7

Post by Christiano Kiss » Fri 16 Nov 2018 15:05

My mistake, Stellar. SDAC does hang if you just lock the table with a transaction or use the "waitfor delay '00:00:05'" to simulate a sleep in the query, even after setting the "NonBlocking" property to "True".

The difference between SDAC and UniDAC is when you use "NonBlocking" to load lots of data. In SDAC, the TMSQuery component loads the result set in small chunks without locking the main thread. In UniDAC, TUniQuery fetches only the first 25 records, therefore I cannot guarantee that it didn't freeze the main thread.

However, I still think that the "NonBlocking" property isn't working as expected. If I lock the entire table with a transaction or set a delay in the query, the main thread should be free to process UI stuff while the query is running.

I consider UniDAC a superior component if compared to other Database Access Components, even Embarcadero's FireDAC. However, TFDQuery from FireDac has the option of executing the query in a separete thread, by using "TFDQuery.ResourceOptions.CmdExecMode" as "amAsync". This is a native feature which would be nice to have it working in UniDAC.

I think that TFDQuery from FireDAC may reproduce the same behavior as TUniQuery by setting "TFDQuery.ResourceOptions.CmdExecMode" as "amNonBlocking", but I'm not sure. Still, I want to be sure if I understood the "NonBlocking" concept correctly and wait for a bug correction or if it's the case to manually create a TThread to query asynchronously.

P.S.: I can still send both examples with SDAC and UniDAC to devart.com/company/contactform.html, if you think it's relevant.

Again, thanks for the help.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Using the NonBlocking specific option with UniDAC 7.0.2, SQL Server and Delphi 7

Post by Stellar » Mon 19 Nov 2018 14:59

When working with SQL Server, UniDAC has the same behavior as SDAC, this is also true for NonBlocking.
When executing an SQL statement that is supposed to return SDAC dataset and UniDAC does not use NonBlocking, i.e., the first request to the server is executed in the same thread. This was done so in order for the client to wait for a response from the server and based on the retrieved data, metadata were generated, a list of fields was created, and so on. Further data retrieving is performed in a separate thread. Perhaps this is not what you need. If this behavior is critical, you should execute a query in a separate thread.

Christiano Kiss
Posts: 4
Joined: Mon 26 Jun 2017 17:26

Re: Using the NonBlocking specific option with UniDAC 7.0.2, SQL Server and Delphi 7

Post by Christiano Kiss » Mon 26 Nov 2018 11:59

Ok. Thanks for the quick response. So, for async operations with UniDAC, the programmer must implement his own threads manually.

I want to take this opportunity and ask in which version does UniDAC and SDAC behave equally with the NonBlocking option. Like I said in my previous post, the old SDAC version 4.35.1.16 loads the entire data in small chunks (without locking the main thread), while UniDAC 7.0.2 loads just 25 rows and then stops loading.

Does the latest version of SDAC behave like UniDAC? Because, from my point of view, the NonBlocking property of SDAC 4.35.1.16 works fine, while UniDAC appears to have a bug.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Using the NonBlocking specific option with UniDAC 7.0.2, SQL Server and Delphi 7

Post by Stellar » Mon 03 Dec 2018 13:55

SDAC and UniDAC have the same behavior when working with MS SQL Server. To investigate this behavior of SDAC, please give us a small working sample, which uses SDAC version 4.35.1.16 where the main thread is not blocked when using the NonBlocking option.
You can send the sample using the contact form at our site: devart.com/company/contactform.html

Post Reply