UniQuery and UniDirectional performance

hughespa · 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 · 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 · 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 · Post by **Dimon** » Thu 22 Apr 2010 09:25

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

hughespa · Post by **hughespa** » Thu 22 Apr 2010 10:56

I've sent you that.

Regards, Paul.

Dimon · 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 · 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 · 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.