How to optimize the usage of TMSQuery on remote connection?

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

How to optimize the usage of TMSQuery on remote connection?

Post by brace » Wed 09 Sep 2009 14:12

Hello,

I am hitting a performance wall when using my client/server application with a remote connection. Typical scenario is User has Client EXE on Laptop, he uses a mobile connection and with VPN he connects to company LAN to access SQLServer and document folders.

(same scenario with VPN and with direct connection to SQL server through external IP can occur, nothing changes for my example)

The problem is that every time I run a query, there is a latency. The latency depends on connection speed. But to give you an idea with a normal internet connection I expereince something like 200ms of latency, while with slower connection the latency is more than 1000ms.

For latency I mean that even if the query returns a single value (for example in the simple query

SELECT CUSTOMER_NAME
FROM CUSTOMERS_TABLE
WHERE CUSTOMER_ID = 10

)

takes a lot of time to execute, 200ms.

A query that returns a lot of more data, like the following 100 records query:

SELECT CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_ADDRESS, CUSTOMER_PHONE, CUSTOMER_LONG_DESCRIPTION
FROM CUSTOMERS_TABLE
WHERE CUSTOMER_ID <=100

takes 240ms. So the big amount of data returned (more than 1000 more data) has a minimal difference in time.

So this is why I speak about latency.

Anyway it seems this is SQL Server behaviour, I would like to understand why there is all this latency. I tried with Management Studio and query execution time are the same (I mean I tried with management studio from remote connection).

So the point is, my application, super simplified, is like this:

1) after login There is a TMSConnection.Connect to the DB
2) during the software execution I have many time data blocks kile this:

procedure DummyProcedure;
begin
MSQuery1.Open;
MSQuery2.Open;
MSQuery3.Open;
MSQuery4.Open;
MSQuery5.Open;
MSQuery6.Open;
MSQuery7.Open;
end;

MSQuery1 to 7 basically return few data tipically, but since every MSQuery has the latency, if latency is 200 ms the above DummyProcedure will take 200*7 =1,4 seconds to execute, while in LAN it is instantanious...

Can you comment on this?

Is there a way to avoid that latency I am speaking about?

I run queries also for setting some popupmenuitem's visibility, so even the display time of some popupmenus is sometimes not acceptable.

Thanks.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 10 Sep 2009 08:21

This problem is connected with SQL Server work, not with SDAC. Please learn SQL Server Books Online.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 10 Sep 2009 08:21

Please check that the TMSConnection.Options.DisconnectedMode property is set to false.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Thu 10 Sep 2009 11:02

Dimon wrote:This problem is connected with SQL Server work, not with SDAC. Please learn SQL Server Books Online.
Ok, but do you have a SDAC sample? I mean do you have a demo that demonstrate a good behaviour of a GUI application that interacts with remote server?

(TMSConnection.Options.DisconnectedMode is false, I let you noitce that this property is not documented in SDAC help)

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Thu 10 Sep 2009 12:40

I would also VERY appreciate some optimization possibility for such batches of queries - just anything like TMSConnection.OpenMoreDatasets(const d: array of TMSDataset); it could internally connect the sql commands of the queries on one helper query, send as one batch and then redistrubute the results back to original dataset components. I know, it has some pitfalls with more complicated queries. But in 99% of cases, it would be really helpful.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Fri 11 Sep 2009 12:41

Yes this is definitely a great idea that would probably solve 85% of my problems.

On Embarcadero Delphi's forum (https://forums.embarcadero.com/message. ... eID=158883) about the same problem I was told that:
MS SQL Server allows you to send multiple queries as a 'single' SQL request and returns all the results together. You may want to check if the components that you are using support this mode.
may you comment on this?

If this is true I could run all the queries I need to run in a single request, this would be very useful for example when I open a customer in my application, instead of sending 10 queries to load all custom data (customer personal info, list of docuemnts, list of projects, list of contacts, ...) I could do this in 1 single step, minimizing the need of going through more "dramatic" changes like moving from 2 to 3 tier architecture.

Thanks.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Fri 11 Sep 2009 14:22

I also made this simple test using Management Studio: running this single batch query

"
select * from TestTable -- 1
select * from TestTable -- 2
select * from TestTable -- 3
[...]
select * from TestTable -- 100
" (100 times the same select statement)

and running this multi batch one:

"
select * from TestTable -- 1
GO
select * from TestTable -- 2
GO
select * from TestTable -- 3
GO
[...]
select * from TestTable -- 100
GO
" (100 times the same SELECT statement + GO)

===================================

The first one is about running more queries together, the second one is more similar to what we do with SDAC when doing
query1.Open;
query2.Open;
query3.Open;
...
query100.Open;

====================================

I made tests by connecting to a remote DB and i noticed that the single batch was quite fast, I had this results:

LOCAL DB: 4 seconds without GO / 5 seconds with GO (basically no significant difference)
REMOTE DB: 8 seconds without GO / 28 seconds with GO (basically dramatic difference: 350% ... and this number is proportional to the slowliness of connection: This means that the slowest the connection the greatest the benefit of using this approach!)

How is it possible to achieve this? Is what Ludek suggests (or a variation of his idea) doable?

Thanks!

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Mon 14 Sep 2009 12:04

We will investigate the possibility of adding this functionality in the near future. As soon as we solve this question we will let you know.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Tue 22 Sep 2009 15:00

This would be a really good add-on and a important SDAC unique feature over competitors.
Please keep me informed.
Thanks.

tonisanta
Posts: 55
Joined: Wed 04 Apr 2007 17:25

Post by tonisanta » Thu 08 Oct 2009 15:25

SDAC support multiple selects in the same query. Did you just try with
if msQuery1.HasNextResultSet then
msquery1.OpenNext;
could be copying every resultset to an internal VirtualTable could solve your problem.
best regards
Toni

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Thu 08 Oct 2009 18:46

Thanks for the hint. I didn't know this feature, for sure I'll try it out.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 17 Nov 2009 15:06

New build of SDAC version 4.80.0.53 is available for download now. The TMSConnection.OpenDatasets method was added in this version for opening several datasets as one batch.

tonisanta
Posts: 55
Joined: Wed 04 Apr 2007 17:25

Post by tonisanta » Tue 24 Nov 2009 19:47

Calling first TMSConnection.OpenDatasets([qry1,qry2]) and than TMSConnection.OpenDatasets([qry3, qry4]) an exception "Query does not return next result set" is raized. Seems the internal FDataSet used to launch the compound SQL must be moved to the begin.
Another issue could be an optional parameter to not open already opened DS (like msQuery.Open does nothing if just opened).
best regards
Toni

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Wed 25 Nov 2009 08:17

Yes! I noticed the same BUG!

Anyway GREAT FEATURE! This is amazing. Fix it and opening multiple queries on a slow connection will be a problem of the past.

Thanks a lot for implementing this feature.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 25 Nov 2009 09:09

Thank you for information. We have reproduced this problem and fixed it. This fix will be included in the next SDAC build.

Post Reply