Performance: collection with prefetch path versus direct db query

Posts   
1  /  2
 
    
Joop avatar
Joop
User
Posts: 43
Joined: 07-Aug-2008
# Posted on: 12-Mar-2010 09:02:52   

Currently I am diving into some performance issues with my code.

Let's say I execute this query:

SELECT * FROM Administrator AS A
INNER JOIN Person AS P ON A.ID = P.ID
INNER JOIN History AS H ON P.HistoryID = H.ID

The code (LLBLGen Pro 2.6):

IPrefetchPath prefetchPath = new PrefetchPath((int)EntityType.AdministratorEntity);
prefetchPath.Add(AdministratorEntity.PrefetchPathPerson).SubPath.Add(PersonEntity.PrefetchPathHistory);

AdministratorCollection ac = new AdministratorCollection();
ac.GetMulti(null, prefetchPath);

Now when I just run the query directly on the DB it takes 10 (almost always) to 300ms (sometimes). When I execute the code with a stopwatch wrapped around it, it takes 900 to 1300ms. Both fetch 240 records from the DB.

I can't come up with a solution for the code. Hopefully you can give me a push in the right direction?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 12-Mar-2010 09:16:12   

The above mentioned query has nothing to do with prefetchPaths. It's a simple fetch with a filter and a couple of relations (joins).

Joop avatar
Joop
User
Posts: 43
Joined: 07-Aug-2008
# Posted on: 12-Mar-2010 09:19:04   

Okay. But how can I make my code as fast as the query?

ChBaeumer
User
Posts: 175
Joined: 23-Oct-2003
# Posted on: 12-Mar-2010 09:27:53   

Walaa wrote:

The above mentioned query has nothing to do with prefetchPaths. It's a simple fetch with a filter and a couple of relations (joins).

Thats is not quite right. The difference is that the sql server shows one result set for the query which is blown up since every row for the Administrator is duplicated and for the Person, too.

Whereas LLBLGen makes actually three calls to the database with different filters in order build the object model.

Christoph

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 12-Mar-2010 09:32:30   

And so do you need a flat resultSet or PrefecthPaths to build the graph?

Joop avatar
Joop
User
Posts: 43
Joined: 07-Aug-2008
# Posted on: 12-Mar-2010 09:36:48   

I need it to build my object graph. Administrator inherits Person and Person inherits History. However in the performance monitoring I am not doing that yet. Just using the collection to fetch the 240 rows.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 12-Mar-2010 11:05:58   

Do you have any blob (text, image) field in any of the fetched entities? How many entities are fetched in each level?

Joop avatar
Joop
User
Posts: 43
Joined: 07-Aug-2008
# Posted on: 12-Mar-2010 11:14:58   

No I don't. Just to be clear (mditem was in my previous example history):

CREATE TABLE `administrator` (
  `Id` int(11) unsigned NOT NULL,
  `Password` char(150) NOT NULL,
  `PasswordIsExpired` tinyint(1) NOT NULL DEFAULT '0',
  `LastLoggedIn` datetime DEFAULT NULL,
  `LoggedInCount` int(11) NOT NULL DEFAULT '0',
  `IsActive` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`),
  CONSTRAINT `administrator_fk1` FOREIGN KEY (`Id`) REFERENCES `person` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `person` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `TypeId` int(11) unsigned NOT NULL COMMENT '1=Administrator; 2=User; 3=Contactperson',
  `FirstName` char(100) NOT NULL,
  `MiddleName` char(20) DEFAULT NULL,
  `LastName` char(100) NOT NULL,
  `Gender` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'True: male; False: female',
  `Description` char(100) DEFAULT NULL,
  `Birthday` date DEFAULT NULL,
  `Telephone` char(15) DEFAULT NULL,
  `Mobile` char(15) DEFAULT NULL,
  `MailAddress` char(50) DEFAULT NULL,
  `MDItemId` int(11) unsigned NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`),
  UNIQUE KEY `MDItemId` (`MDItemId`),
  CONSTRAINT `person_fk1` FOREIGN KEY (`MDItemId`) REFERENCES `mditem` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=316 DEFAULT CHARSET=latin1

CREATE TABLE `mditem` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `CreatedOn` datetime NOT NULL,
  `CreatedBy` int(10) unsigned NOT NULL,
  `LastModifiedOn` datetime DEFAULT NULL,
  `LastModifiedBy` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`),
  KEY `CreatedBy` (`CreatedBy`),
  KEY `LastModifiedBy` (`LastModifiedBy`),
  CONSTRAINT `entity_fk1` FOREIGN KEY (`CreatedBy`) REFERENCES `person` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `entity_fk2` FOREIGN KEY (`LastModifiedBy`) REFERENCES `person` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=316 DEFAULT CHARSET=latin1

Full code:

IPrefetchPath prefetchPath = new PrefetchPath((int)EntityType.AdministratorEntity);
prefetchPath.Add(AdministratorEntity.PrefetchPathPerson).SubPath.Add(PersonEntity.PrefetchPathMditem);

AdministratorCollection ac = new AdministratorCollection();
ac.GetMulti(null, prefetchPath);

List<Administrator> administrators = new List<Administrator>();
foreach (AdministratorEntity e in ac)
{
    administrators.Add(new Administrator(e));
}

//contructors
internal Administrator(AdministratorEntity entity): base(entity.Person)
{
    _entity = entity;
}

internal Person(PersonEntity entity): base(entity.Mditem)
{
    _entity = entity;
}

internal MDItem(MditemEntity entity)
{
    _entity = entity;
}

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 12-Mar-2010 11:26:11   

Executing the SQL Queries generated for each entity type directly against the database, how many entities are there in each resultSet?

Joop avatar
Joop
User
Posts: 43
Joined: 07-Aug-2008
# Posted on: 12-Mar-2010 13:00:19   

Administrator: 236 Person: 297 MDItem: 297

Is is true what ChBaeumer says that LLBLGen makes three DB calls to build the whole entity collection model?

ChBaeumer
User
Posts: 175
Joined: 23-Oct-2003
# Posted on: 12-Mar-2010 14:58:27   

You should enable tracing to see which queries are sent to the database

See http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/gencode_troubleshootingdebugging.htm

The only switch you have to set depends on your database. For me I would have to add to app.config following snippet since I use the microsoft sql server:


<system.diagnostics>
    <switches>
        <add name="SqlServerDQE" value="4" />
    </switches>
</system.diagnostics>

Joop avatar
Joop
User
Posts: 43
Joined: 07-Aug-2008
# Posted on: 12-Mar-2010 15:39:15   

Trace output:

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT `administrator`.`Id`, `administrator`.`Password`, `administrator`.`PasswordIsExpired`, `administrator`.`LastLoggedIn`, `administrator`.`LoggedInCount`, `administrator`.`IsActive` FROM `administrator`

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT `administrator`.`Id` FROM `administrator`

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT `administrator`.`Id` FROM `administrator`

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT `person`.`Id`, `person`.`TypeId`, `person`.`FirstName`, `person`.`MiddleName`, `person`.`LastName`, `person`.`Gender`, `person`.`Description`, `person`.`Birthday`, `person`.`Telephone`, `person`.`Mobile`, `person`.`MailAddress`, `person`.`MDItemId` AS `MditemId` FROM `person` WHERE ( ( `person`.`Id` IN (SELECT `administrator`.`Id` FROM `administrator`) AND `person`.`Id` IN (SELECT `administrator`.`Id` FROM `administrator`)))

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT `administrator`.`Id` FROM `administrator`

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT `administrator`.`Id` FROM `administrator`

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT `person`.`MDItemId` AS `MditemId` FROM `person` WHERE ( ( ( `person`.`Id` IN (SELECT `administrator`.`Id` FROM `administrator`) AND `person`.`Id` IN (SELECT `administrator`.`Id` FROM `administrator`))))

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT `mditem`.`Id`, `mditem`.`CreatedOn`, `mditem`.`CreatedBy`, `mditem`.`LastModifiedOn`, `mditem`.`LastModifiedBy` FROM `mditem` WHERE ( ( `mditem`.`Id` IN (SELECT `person`.`MDItemId` AS `MditemId` FROM `person` WHERE ( ( ( `person`.`Id` IN (SELECT `administrator`.`Id` FROM `administrator`) AND `person`.`Id` IN (SELECT `administrator`.`Id` FROM `administrator`)))))))

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.

The last query looks way too complex in my point of view. Still haven't any idea how to boost the performance. Hope this trace gives you an idea though.

ChBaeumer
User
Posts: 175
Joined: 23-Oct-2003
# Posted on: 12-Mar-2010 15:53:25   

The trace confirms that there a 3 queries to the database.

Btw you said that the entites inherit from each other


Administrator -> Person -> Mditem

What happens if you define this inheritance in the designer and fetch the administrator without any prefetch since this would be handled by llblgen?

Is this faster? I have never tested this.

Joop avatar
Joop
User
Posts: 43
Joined: 07-Aug-2008
# Posted on: 12-Mar-2010 15:56:19   

Isn't there any possible workaround? LLBLGen also can do a join I would say. Executing 3 queries isn't optimal to say the least.

I will give it a shot to make the entities inherit in the designer. Will be back soon simple_smile

Joop avatar
Joop
User
Posts: 43
Joined: 07-Aug-2008
# Posted on: 12-Mar-2010 16:09:09   

That does make a difference since it becomes one query. However in my project that isn't the solution I am looking for. Person is used as sub-entity for other entities too. The same for MDItem. My logic layer would become more complex if I have to implement it like that. Still would like to see a solution for the initial problem.

Inheritance set in the designer: Test 1: 954 Test 2: 529 Test 3: 36 Test 4: 52 AVG: 392

Without inheritance set Test 1: 1226 Test 2: 108 Test 3: 1222 Test 4: 1234 AVG: 947

ChBaeumer
User
Posts: 175
Joined: 23-Oct-2003
# Posted on: 12-Mar-2010 16:40:43   

LLBLgen can't handle your initial problem by design.

One way out could be to define a typed list (more or less your initial query) and map this list to your entities. From your previous post I see that you have defined you own entities anyway which hold a reference to the LLBLGen entity. Now they should hold at least the PK of the LLBLGen entity.

There is one think you should consider:

Saving changes back to the database. Before saving the changes just fetch the real LLBLGen entities from the DB, change them and save them.

Any other ideas?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 15-Mar-2010 05:07:12   

I agree. TypedList/DynamicList/TypedView would help to increase performance. You also can consider read Optimizing Prefetch Paths.

David Elizondo | LLBLGen Support Team
Joop avatar
Joop
User
Posts: 43
Joined: 07-Aug-2008
# Posted on: 22-Mar-2010 16:08:27   

Back again. Still having some issues with the speed of a query directly against the database and fetching a collection with LLBLGen. I think it is related to my previous issue.

Query copied from trace:

SELECT `brand`.`Id`, `brand`.`Name`, `brand`.`Telephone`, `brand`.`MailAddress`, `brand`.`Website` FROM `BRAND`;

Code to fetch the collection:

BrandCollection lc = new BrandCollection();
lc.GetMulti(null);

Time for 192 rows: Query: between 2 and 5 milliseconds Code: between 400 and 700 milliseconds

As you see it's a really big difference between the query and the code.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 22-Mar-2010 16:21:33   

When you query the database from code, some time is spent openning a connection or retrieving it from the connection pool.

What you should compare is using LLBLGen code vs. using ADO.NET code, and post the comparison here is you wish.

Joop avatar
Joop
User
Posts: 43
Joined: 07-Aug-2008
# Posted on: 22-Mar-2010 16:34:50   

That is exactly what I was thinking about. Therefor I just tried to accomplish that in my code. The result is:

Test 1: Opening connection: 129 milliseconds Total time fetching collection: 777 milliseconds

Test 2: Opening connection: 3 milliseconds Total time fetching collection: 9 milliseconds

Test 3: Opening connection: 2 milliseconds Total time fetching collection: 695 milliseconds

Test 4: Opening connection: 4 milliseconds Total time fetching collection: 498 milliseconds

Test 5: Opening connection: 3 milliseconds Total time fetching collection: 10 milliseconds

All 5 runs are done by using ADO.Net driver for MySQL.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 22-Mar-2010 16:46:33   

And the corresponding LLBLGen numbers?

Joop avatar
Joop
User
Posts: 43
Joined: 07-Aug-2008
# Posted on: 22-Mar-2010 16:52:05   

I did some extra tests. Within the same connection I did run the same query twice. See the results. Can't wrap my head around it anymore.

Connection open: 164 Run 1: 579 Run 2: 4 Connection close: 1

Connection open: 13 Run 1: 683 Run 2: 5 Connection close: 0

Connection open: 13 Run 1: 591 Run 2: 5 Connection close: 0

Connection open: 2 Run 1: 695 Run 2: 4 Connection close: 0

Connection open: 3 Run 1: 591 Run 2: 4 Connection close: 0

All times in milliseconds and ADO.Net driver for MySQL. No use of LLBLGen collections. Starting to think it has something to do with connection pooling or something else with the ADO.Net (6.2.2.0) driver.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 22-Mar-2010 17:20:21   

Most probably due to connection instantiation and query cashing which enhances the performane on repeatedly called queries.

Joop avatar
Joop
User
Posts: 43
Joined: 07-Aug-2008
# Posted on: 22-Mar-2010 18:18:08   

Possible, yes. However there is still a big difference between the query executed within the MySQL tool I use and the code executed with ADO.Net. As far as I know connection pooling is by default enabled.

Do you have some other suggestions? Thanks for your help though.

Joop avatar
Joop
User
Posts: 43
Joined: 07-Aug-2008
# Posted on: 23-Mar-2010 09:37:26   

It looks like my tool is fooling me. Even when I limit the query to return just 1 row it takes 2ms. Anyway, it always take 2ms. Also for 200 rows to return.

I tried the exact same thing with my code and limiting the result was making a big difference. Seems that I was worrying for nothing.

Thanks for your support.

1  /  2