UniQuery and UniDirectional performance

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
hughespa
Posts: 81
Joined: Sat 23 Aug 2008 08:36
Location: W. Australia

UniQuery and UniDirectional performance

Post by hughespa » Thu 22 Apr 2010 02:18

Hi,

D2010, SQL Express 2008.

I'm used to setting UniDirectional = True when I'm getting data to iterate through without performing updates. I used to set this as a matter of course for BDE/Paraox data access in old applications which I'm now converting to UniDAC.

Using UniQuery, I thought this should also give better performance but in a simple test case I was looking at recently, setting UniDirectional to True actually slows down the loop by almost 5 times using the same query compared to when UniDirectional is false.

Can you explain this please? Are there any 'best practices' for how or when to use UniDirectional?

I see also that FetchAll must be false when unidirctional = True, this would seem to slow down the performance too.


Regards, Paul.

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

Post by Dimon » Thu 22 Apr 2010 07:59

This is a very strange situation.
Please check if this problem still exists when UniDirectional = False and FetchAll = False. Also try to set the Readonly property to True.

hughespa
Posts: 81
Joined: Sat 23 Aug 2008 08:36
Location: W. Australia

Post by hughespa » Thu 22 Apr 2010 09:14

Hi,

Yes, the performance is still slow when UniDirectional = false and FetchAll = false and ReadOnly = false

Test times

UniDir = false, FetchAll = true, ReadOnly = false : 1110 MS
UniDir = true, FetchAll = true, ReadOnly = false : 9500 MS
UniDir = false, FetchAll = false, ReadOnly = false : 7050 MS
UniDir = true, FetchAll = false, ReadOnly = false : 7000 MS

Setting ReadOnly = true did not really change the above times very much.

Do you want me to send you the test project and tables?

Regards, Paul.

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

Post by Dimon » Thu 22 Apr 2010 09:25

Yes, please send me a sample to dmitryg*devart*com to demonstrate the problem.

hughespa
Posts: 81
Joined: Sat 23 Aug 2008 08:36
Location: W. Australia

Post by hughespa » Thu 22 Apr 2010 10:56

I've sent you that.

Regards, Paul.

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

Post by Dimon » Thu 22 Apr 2010 12:54

We have received your sample.
The point is that when you set UniDirectional to True, then OLE DB creates additional sessions for any query executing and blocks current session. In your case additional session is implicity created for every record of the first query, and this takes much time.
To solve the problem set the MultipleActiveResultSets specific option to True, like this:

Code: Select all

  UniConnection.SpecificOptions.Values['MultipleActiveResultSets'] := 'True';

hughespa
Posts: 81
Joined: Sat 23 Aug 2008 08:36
Location: W. Australia

Post by hughespa » Thu 22 Apr 2010 13:09

OK, I see now (I think), thank you.

Are there likely to be any side effects if MultipleActiveResultSets is true throughout the application (always on) since my main application has many queries, tables, etc?

I would hope to avoid having to set this on and off only for the various situations that would benefit from it.

Regards, Paul.

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

Post by Dimon » Thu 22 Apr 2010 13:40

Set MultipleActiveResultSets to True when connection has more than one pending request, and, in particular, has more than one active default result set.

Post Reply