Designer version: 1.0.2005.1 Runtime libraries version: 1.0.2005.1 (using .NET 1.1) dotNET Version: 1.1 VS.NET Version: 2003 SelfServicing

We're currently evaluating LLBLGen Pro as a solution to replace our hand coded DAL. We're real excited about the prospects of LLBLGen Pro but we need to do some prototyping/benchmarking to be sure it will meet our expectations with regard to performance and stability.

• I ran the designer against our database and generated the DAL w/o any issues (very slick).
• After generating the DAL I opened the project in VS.NET 2003 and compiled it.
• I wanted to do some anecdotal testing prior to the more rigorous testing so I created winforms project in VS.NET 2003 and added the DAL compiled in the previous step as a reference along with the LLBLGen Pro runtime libraries
• I added an entity collection component from the DAL library to the toolbox
• I created a simple form with a drop down list and added the entity collection component from the previous step
• I bound the drop down to properties of the entity in the collection component (properties of the entity not of one of it's relations)
• In the load event for the form I executed the GetMulti method of the collection component instance and passed null (e.g. caseTypesCollection.GetMulti(null))

The GetMulti takes 7-10 seconds to complete. The table for the entity only has 49 rows so 7-10 seconds seems like an unreasonably long time. I added a button to the form and placed the exact same GetMulti call in the button so that I could execute it at will in an effort to try to eliminate any performance 'noise' coming from initialization of parts of the LLBLGen Pro runtime libraries. In successive clicks of the button (allowing 5-10 seconds between clicks) the call to GetMulti will execute very quickly some times and other times it will be 7-10 seconds.

It's quite likely I'm doing something wrong somewhere but I'm just not sure what that might be. We're really interested in using the product so I'm very hopeful that I can get this worked out. I've included the trace output below with an annotation for where the 7-10 seconds manifests itself.

Method Enter: EntityCollectionBase.OnClear Method Exit: EntityCollectionBase.OnClear 'GBS.CourtTRAC.TestDataClient.exe': Loaded 'c:\development\my llblgen projects\gbs.courttrac.testdataclient\bin\debug\sd.llblgen.pro.dqe.sqlserver.net11.dll', No symbols loaded. Method Enter: DaoBase.PerformGetMultiAction Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[CaseTypes].[CaseTypeId], [dbo].[CaseTypes].[SectionId], [dbo].[CaseTypes].[CaseCode], [dbo].[CaseTypes].[SeriesId], [dbo].[CaseTypes].[DisplayPart], [dbo].[CaseTypes].[Description], [dbo].[CaseTypes].[Inactive], [dbo].[CaseTypes].[NewCaseStandardFeeId], [dbo].[CaseTypes].[NewCaseDepositAmount], [dbo].[CaseTypes].[CertifiedMailReturnDueDays], [dbo].[CaseTypes].[SupremeCourtCaseTypeId], [dbo].[CaseTypes].[JournalSeriesId] FROM [dbo].[CaseTypes] Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. Method Enter: DaoBase.ExecuteMultiRowRetrievalQuery 'GBS.CourtTRAC.TestDataClient.exe': Loaded 'c:\windows\assembly\gac\system.enterpriseservices\1.0.5000.0__ b03f5f7f11d50a3a\system.enterpriseservices.dll', No symbols loaded. 'GBS.CourtTRAC.TestDataClient.exe': Loaded 'c:\windows\assembly\gac\system.enterpriseservices\1.0.5000.0__ b03f5f7f11d50a3a\system.enterpriseservices.thunk.dll', No symbols loaded. 7-10 seconds shows up here Method Exit: DaoBase.ExecuteMultiRowRetrievalQuery Method Exit: DaoBase.PerformGetMultiAction

everettm wrote:

Designer version: 1.0.2005.1 Runtime libraries version: 1.0.2005.1 (using .NET 1.1) dotNET Version: 1.1 VS.NET Version: 2003 SelfServicing

We're currently evaluating LLBLGen Pro as a solution to replace our hand coded DAL. We're real excited about the prospects of LLBLGen Pro but we need to do some prototyping/benchmarking to be sure it will meet our expectations with regard to performance and stability.

• I ran the designer against our database and generated the DAL w/o any issues (very slick).
• After generating the DAL I opened the project in VS.NET 2003 and compiled it.
• I wanted to do some anecdotal testing prior to the more rigorous testing so I created winforms project in VS.NET 2003 and added the DAL compiled in the previous step as a reference along with the LLBLGen Pro runtime libraries
• I added an entity collection component from the DAL library to the toolbox
• I created a simple form with a drop down list and added the entity collection component from the previous step
• I bound the drop down to properties of the entity in the collection component (properties of the entity not of one of it's relations)
• In the load event for the form I executed the GetMulti method of the collection component instance and passed null (e.g. caseTypesCollection.GetMulti(null))

The GetMulti takes 7-10 seconds to complete. The table for the entity only has 49 rows so 7-10 seconds seems like an unreasonably long time. I added a button to the form and placed the exact same GetMulti call in the button so that I could execute it at will in an effort to try to eliminate any performance 'noise' coming from initialization of parts of the LLBLGen Pro runtime libraries. In successive clicks of the button (allowing 5-10 seconds between clicks) the call to GetMulti will execute very quickly some times and other times it will be 7-10 seconds.

That's indeed very weird. I can fetch 50,000 entities in roughly 6 seconds here . Your 49 rows, do they contain blobs which are very big?

If you fetch the data in an entity collection which isn't bound to a grid, is it fast then? (I don't think it will make a difference, see below, but just in case)

It's quite likely I'm doing something wrong somewhere but I'm just not sure what that might be. We're really interested in using the product so I'm very hopeful that I can get this worked out. I've included the trace output below with an annotation for where the 7-10 seconds manifests itself.

Method Enter: EntityCollectionBase.OnClear Method Exit: EntityCollectionBase.OnClear 'GBS.CourtTRAC.TestDataClient.exe': Loaded 'c:\development\my llblgen projects\gbs.courttrac.testdataclient\bin\debug\sd.llblgen.pro.dqe.sqlserver.net11.dll', No symbols loaded. Method Enter: DaoBase.PerformGetMultiAction Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[CaseTypes].[CaseTypeId], [dbo].[CaseTypes].[SectionId], [dbo].[CaseTypes].[CaseCode], [dbo].[CaseTypes].[SeriesId], [dbo].[CaseTypes].[DisplayPart], [dbo].[CaseTypes].[Description], [dbo].[CaseTypes].[Inactive], [dbo].[CaseTypes].[NewCaseStandardFeeId], [dbo].[CaseTypes].[NewCaseDepositAmount], [dbo].[CaseTypes].[CertifiedMailReturnDueDays], [dbo].[CaseTypes].[SupremeCourtCaseTypeId], [dbo].[CaseTypes].[JournalSeriesId] FROM [dbo].[CaseTypes] Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. Method Enter: DaoBase.ExecuteMultiRowRetrievalQuery 'GBS.CourtTRAC.TestDataClient.exe': Loaded 'c:\windows\assembly\gac\system.enterpriseservices\1.0.5000.0__ b03f5f7f11d50a3a\system.enterpriseservices.dll', No symbols loaded. 'GBS.CourtTRAC.TestDataClient.exe': Loaded 'c:\windows\assembly\gac\system.enterpriseservices\1.0.5000.0__ b03f5f7f11d50a3a\system.enterpriseservices.thunk.dll', No symbols loaded. 7-10 seconds shows up here Method Exit: DaoBase.ExecuteMultiRowRetrievalQuery Method Exit: DaoBase.PerformGetMultiAction

So during the execution of the query, it takes 7-10 seconds till the query returns? (between execute multirowretrievalquery enter and exit) ? Could it be a network issue / db busy issue? (although a slow db which takes 7 seconds to execute a straightforward query would be very extreme). I think it could be a network issue because you report it sometimes is very quick.

If you enable more tracing options, where exactly is the delay occuring? (so if you enable the trace flag ORMGeneral and set it to level 3, do you get more info about where the delay occurs? If you set it to level 4, you'll get trace messages of every entity added to the collection, which is slow but gives insight in what happens).

Be aware that if you enable tracing, execution will be significant slower and if you run the app then also in debug mode, it will even more slow down due to the redirection of the output to vs.net.

Firstly, to answer your questions.. - none of the tables contain BLOBs - whether or not the collection component is bound does not seem to matter - the logging is turned all the way on and is reflected in the listing at the bottom of this post - the 7-10 second lapse occurs between DaoBase.ExecuteMultiRowRetrievalQuery and DaoBase.ExecuteMultiRowRetrievalQuery as seen in the tracing at the end of this post - with regard to the role of the network, this is a test environment so it's not likely that the network is the issue.

Ok, I've done some additional testing. To the previous application I've added a Grid and a list box along with 2 collection components. The entity associated with the collection component bound to the grid represents a table with nearly 100, 000 rows. I have set the max rows to 50 to limit the results returned. The entity associated with the collection component bound to the list box represents a table with only 10 rows. The 'GetMulti' method on the collection bound to the grid always returns nearly immediately. The first call to 'GetMulti' on either of the other 2 collections consistently takes 7-10 seconds. This is true regardless of whether the LLBLGen Pro runtime has been excercised yet or not.

Method Enter: EntityCollectionBase.OnClear Method Exit: EntityCollectionBase.OnClear Method Enter: DaoBase.PerformGetMultiAction Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[CaseTypes].[CaseTypeId], [dbo].[CaseTypes].[SectionId], [dbo].[CaseTypes].[CaseCode], [dbo].[CaseTypes].[SeriesId], [dbo].[CaseTypes].[DisplayPart], [dbo].[CaseTypes].[Description], [dbo].[CaseTypes].[Inactive], [dbo].[CaseTypes].[NewCaseStandardFeeId], [dbo].[CaseTypes].[NewCaseDepositAmount], [dbo].[CaseTypes].[CertifiedMailReturnDueDays], [dbo].[CaseTypes].[SupremeCourtCaseTypeId], [dbo].[CaseTypes].[JournalSeriesId] FROM [dbo].[CaseTypes] Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. Method Enter: DaoBase.ExecuteMultiRowRetrievalQuery 7-10 Seconds Here Method Exit: DaoBase.ExecuteMultiRowRetrievalQuery Method Exit: DaoBase.PerformGetMultiAction

everettm wrote:

Firstly, to answer your questions.. - none of the tables contain BLOBs - whether or not the collection component is bound does not seem to matter - the logging is turned all the way on and is reflected in the listing at the bottom of this post - the 7-10 second lapse occurs between DaoBase.ExecuteMultiRowRetrievalQuery and DaoBase.ExecuteMultiRowRetrievalQuery as seen in the tracing at the end of this post - with regard to the role of the network, this is a test environment so it's not likely that the network is the issue.

Ok, I've done some additional testing. To the previous application I've added a Grid and a list box along with 2 collection components. The entity associated with the collection component bound to the grid represents a table with nearly 100, 000 rows. I have set the max rows to 50 to limit the results returned. The entity associated with the collection component bound to the list box represents a table with only 10 rows. The 'GetMulti' method on the collection bound to the grid always returns nearly immediately. The first call to 'GetMulti' on either of the other 2 collections consistently takes 7-10 seconds. This is true regardless of whether the LLBLGen Pro runtime has been excercised yet or not.

I've reread this a couple of times, but I'm a little confused .

Am I correct that: grid + collection: 100,000 rows table, you load 50, loads immediately listbox + collection: 10 rows table, you load all of them, takes 10 seconds.

Which is the other collection in 'the other 2 collections' ?

Could you please enable more tracing flags and see if opening the connection takes the 10 seconds, or the adding of the entities ?

As said, my test routines fetch 50,000 entities of 12 fields with strings, ints and the like in 6 seconds (over the network), so I'm a bit lost why it takes 7 seconds in your app, even for 10 rows!

If possible, also please post some code snippets of what you're doing as something weird is going on...

I believe I have all the logging turned on. The following is the relevant portion of my application config file...

    <switches>
        <add name="SqlServerDQE" value="4" />
        <add name="ORMGeneral" value="4" />
        <add name="ORMStateManagement" value="4" />
        <add name="ORMPersistenceExecution" value="4" />
    </switches>

The following link may help make the behavior I've been seeing more apparent...

http://foundry.gbscorp.com/llblgen.doc

Ok, so if you FIRST call Load Grid it doesn't matter, it is always fast.? And the first call of either Load combo or load list is slow?

It still looks like a problem to setup the connection. Once it's setup, it's in the pool so it's re-used very fast, which explains why subsequent calls are fast.

To test, please do the following: - set all tracing to 0 do this in Load List call:

Transaction trans = new Transaction(IsolationLevel.ReadCommitted, "Test");
trans.Add(myCollection); // add the collection to fetch to the transaction
myCollection.GetMulti(null);
trans.Commit();

• place a breakpoint at the first line, thus the transaction creation line
• run your app in debug mode, click the Load List button FIRST.
• Stepping over the transaction create line will take 7-10 seconds, if I'm correct. It creates a connection and opens it.
• the getmulti call will be fast as the connection is already open.

For the sake of anyone who may be evaluating LLBLGen Pro and trying to do research in the forums on performance, the lag described in this thread was indeed associated with opening the connection for the first time.

We continue to use LLBLGen and have upgraded to version 2. If you haven't yet checked this product out you should, it's excellent.

Thanks for the positive feedback.