Performance issue with lazy loading
Posted: Fri 18 Jan 2013 19:34
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)?
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)?