Devart Forums

Hi, I have this issue where reading a row from oracle using dotConnect is very slow while the corresponding SQL select statement is really fast.

Here's the detail:
Let's suppose I have an object that is a student and that this object contains a collection of programs in which the student has registered. When I read a student from the database I obtain the associated student object with an empty collection of programs. This is fine because I set the program collection to be lazily loaded. The following SQL is generated to read the student:

SELECT ...
FROM STUDENT t1
WHERE (t1.PERM_CD = :p0) AND (t1.ETA_CD = :p1) AND (t1.DECL_AATRIM = :p2) AND (ROWNUM <= 1)
[2013-01-18 14:02:17] -- p0: Input Char (Size = 12; DbType = String) [BELC31625608]
[2013-01-18 14:02:17] -- p1: Input Char (Size = 2; DbType = String) [02]
[2013-01-18 14:02:17] -- p2: Input Char (Size = 5; DbType = String) [19932]
[2013-01-18 14:02:17] -- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: an Build: 3.1.43.0

Then depending from where I access the student object I might or might not want to access the programs collection. So let's say in the current case, I want to access the collection of programs. My dataContext is still in place, so DotConnect detects that I try to access the collection and loads the associated rows of the database. This is where it is not clear to me what DotConnect does, but this operation takes about 10 seconds which is not an acceptable response time.

On the other hand, the sql select statement generated to read the associated program executes on Oracle in about 86 msecs.

SELECT ...
FROM program t1
WHERE ((:np0 = t1.PERM_CD) OR ((:np0 IS NULL) AND (t1.PERM_CD IS NULL))) AND ((:np1 = t1.ETA_CD) OR ((:np1 IS NULL) AND (t1.ETA_CD IS NULL))) AND ((:np2 = t1.DECL_AATRIM) OR ((:np2 IS NULL) AND (t1.DECL_AATRIM IS NULL)))
[2013-01-18 14:02:48] -- np0: Input Char (Size = 12; DbType = String) [BELC31625608]
[2013-01-18 14:02:48] -- np1: Input Char (Size = 2; DbType = String) [02]
[2013-01-18 14:02:48] -- np2: Input Char (Size = 5; DbType = String) [19932]
[2013-01-18 14:02:48] -- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: an Build: 3.1.43.0

So, I really need to increase the performances to get to those programs. My guess is that the problem is not on the DB side. What is going on between the 86 msecs it takes to execute the query and the end of the 10 seconds delay knowing that the query returns only 1 row (1 program)?

We couldn't reproduce this issue on our environment. We are sending you a simple test project to the e-mail address you provided in your forum profile. Please make changes to it so that the issue could be reproduced and send it back to us, or send us your sample project.
Also, you could try profiling your application with any performance profiler.

Looking forward to your reply.

Hi Mariial, thanks for your answer.

I have not received the test project, can you send or re-send it please?
(I double-checked my profile's email adress and it's good.)

As for profiling my application, I did try this option before posting about my problem. The critical location is in the DevArt classes (DevArt.Data.Oracle) and this is a black box for me. I believe I cannot go any further, so this is why I decided to ask for your help. Can I do more for you on that side? Maybe send a screenshot of the call trace...

Thanks!

We've sent you the sample again. Please check that the letter is not blocked by your mail filter.

If there are any problems with this, please specify some other mail address where we can send the sample (you can contact us at 'support at devart dot com' for this).

Also, there were multiple fixes related to performance since the 3.1.43 version of LinqConnect. Please try upgrading to the latest version of LinqConnect or dotConnect for Oracle, and tell us if this helps.

If it doesn't, please send us the profiling information and specify the exact version and product you are using.

Hi, I upgraded to the latest versions of dotConnect and LinqConnect as you suggested and indeed there is a great improvement in performances. Thanks a lot!