Query network traffic increased with each reopen

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
StefanDWC
Posts: 24
Joined: Mon 22 May 2017 08:42

Query network traffic increased with each reopen

Post by StefanDWC » Wed 16 May 2018 15:43

Hi there!

I found a nice feature with TUniQuery. Whenever i reopen an existing query after setting the same statement again, i get the same amount of records delivered but the amount of data which is transfered from database is always double of the amout before.
There is no data source connected. Just the Connection and the Query. I use FetchAll = False.

To reproduce that you need a table with a huge amount of records on another PC so that you can see (and feel) when the data is transfered.

Provider is SQL Server.
UniQuery.SpecificOptions.Values['FetchAll'] := 'False';

On a button Click event have i only this lines:

Code: Select all

  if UniQuery.Connection = nil then
  begin
    UniConnection.Connect;
    UniQuery.Connection := UniConnection;
    UniQuery.SpecificOptions.Values['FetchAll'] := 'False';
  end;

  UniQuery.SQL.Text := 'SELECT * FROM MyTable';
  UniQuery.Open;
So, i press the button again and again and always take it longer and longer to retrive the data.
With a tool for checking network traffic will you see that the amount of transfered data will increase every time you press the button.

It have the same effect if i set the statement once and close and open the query in the button click.

So, now the question, is this a feature or a bug?

Edit:
I am sure it is a bug. Because, at least half of the transfer happens when the statement is set or when Close is called before change of statement.

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

Re: Query network traffic increased with each reopen

Post by Stellar » Thu 17 May 2018 09:48

Unfortunately, we can't reproduce the issue on the latest version of UniDAC 7.2.7. To investigate this behavior of UniDAC, please compose a small sample demonstrating the issue and send it to us, including database objects for creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html

StefanDWC
Posts: 24
Joined: Mon 22 May 2017 08:42

Re: Query network traffic increased with each reopen

Post by StefanDWC » Thu 17 May 2018 17:50

Hello Stellar,

thanks for reply!

I use the latest version, professional, without source. Otherwise had i already a deep look into it.
I use Delphi 10.2 rev 3.
Database data will be a bit difficult. i can not share my test data. However, i'll create a plain table with some random data.
As additional information, it is an SQL Azure DB.

Thanks so far.

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

Re: Query network traffic increased with each reopen

Post by Stellar » Wed 23 May 2018 12:47

We are waiting for a sample from you, to analyze the issue of increasing network traffic when reopening a query.

StefanDWC
Posts: 24
Joined: Mon 22 May 2017 08:42

Re: Query network traffic increased with each reopen

Post by StefanDWC » Mon 04 Jun 2018 09:44

Hello Stellar,

i did, on 25th of May.
Any news in that case?

Do you have a bug tracker / or a list of know bugs?
I got a strange "EOLEDBError", otherwise will i post a messge at the board.

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

Re: Query network traffic increased with each reopen

Post by Stellar » Tue 12 Jun 2018 13:01

When executing a query and loading only a part of data into DataSet (FetchAll = False), the server sends all the data to the client anyway. If all the data was not loaded into DataSet, when DataSet is closed, we send a command to the server, that we no longer need data, but by that moment, the server could already send all the data to the client, or some part of it. Therefore, when closing DataSet, the data sent by the server can be read. Unfortunately, we cannot influence the amount of data sent by the server.

StefanDWC
Posts: 24
Joined: Mon 22 May 2017 08:42

Re: Query network traffic increased with each reopen

Post by StefanDWC » Wed 13 Jun 2018 11:46

Hello Stellar,

Thanks for reply.

I don't think that the Server sends data just for fun. Because it is a waste of ressources at the server side.
And, how comes then that the amount is increasing with each time the query is closed and opened again with the same statement?
And, if you find out that there is a command which delivers unwanted data, maybe the command is wrong at this point?
It is really such a useless condition to send an horrible increasing amount of data at time of closing a query that it can not be real.
Maybe it is a failure in the communication framework you use, so you should investigate that with the founder.

Thanks.

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

Re: Query network traffic increased with each reopen

Post by Stellar » Tue 19 Jun 2018 14:52

When closing DataSet, if all the data was not loaded into it, we notify the server that the data is no longer needed. According to the protocol, the client can interrupt and cancel the current request by sending an Attention message. After the client sends an Attention message, the client MUST read until it receives an Attention acknowledgment. Therefore, when closing DataSet, the data is fetched, unfortunately, we cannot influence the amount of data sent by the server.

More information about canceling the query execution:
msdn.microsoft.com/en-us/library/dd341449.aspx

StefanDWC
Posts: 24
Joined: Mon 22 May 2017 08:42

Re: Query network traffic increased with each reopen

Post by StefanDWC » Mon 02 Jul 2018 12:11

Hello Stellar,

thanks for the link.
I really have not expected that Microsoft waste ressources (network bandwith) in that way.
Then might it be an optimization from server side to transfer bigger packages if the same statement is requested (over the same query) again.
The problem ist not really the useless transfered data. It is the time wich i have to wait until the transfer is done.
If you know any solution to skip the waiting would me help a lot.
Do the UniQuery or UniConnection have an option to do the fetching in background, so that the Component is not waiting until end of the transfer?

Thanks.

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

Re: Query network traffic increased with each reopen

Post by Stellar » Fri 06 Jul 2018 13:37

Perhaps you will be able to increase performance by setting the special options NonBlocking and FetchAll to True for TUniQuery.

StefanDWC
Posts: 24
Joined: Mon 22 May 2017 08:42

Re: Query network traffic increased with each reopen

Post by StefanDWC » Tue 04 Sep 2018 08:10

NonBlocking use i already. I got an error because of the shared connection for more than one query. Since NonBlocking is on it is working well.
The FetchAll might be the overall solution. However, there are situations where I have to read too much records from a table at the time i open the table. So, FetchAll and FetchRows are now managed by ma table component as properties.
It might be a solution to have like a local copy of the database which is automatically synchonized with the main database in background and only filled if particular data is requested.

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

Re: Query network traffic increased with each reopen

Post by Stellar » Mon 10 Sep 2018 12:19

Thank you for the information, we know about the issue with simultaneous execution of multiple queries, when retrieving records in separate threads.
These fixes will be included in the next build of UniDAC.

Post Reply