Hi all,

I have the following:

    Public Shared Function GetStudentTeacherByTeacherIdents(ByVal teacherIdents() As Integer) As EntityCollection
        Dim Filter As IRelationPredicateBucket = New RelationPredicateBucket

        Filter.PredicateExpression.Add(PredicateFactory.CompareRange( MedfordSchoolDistrict.Elementary.GradeBook.LLBL.StudentTeacherFieldIndex.TeacherIdent, teacherIdents))

        Filter.PredicateExpression.AddWithAnd(PredicateFactory.CompareValue( MedfordSchoolDistrict.Elementary.GradeBook.LLBL.StudentTeacherFieldIndex.IsClassRoomTeacher, ComparisonOperator.Equal, 1))       ' Class Room Teachers.

        Dim PrefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.StudentTeacherEntity, Integer))
        PrefetchPath.Add(StudentTeacherEntity.PrefetchPathStudent)

        GetStudentTeacherByTeacherIdents = New EntityCollection(New StudentTeacherEntityFactory)

        _Adapter.FetchEntityCollection(GetStudentTeacherByTeacherIdents, Filter, PrefetchPath)

        Return GetStudentTeacherByTeacherIdents
    End Function

It geneates the following sql:

exec sp_executesql N'SELECT [elementarygradebook].[dbo].[StudentTeacher].[StudentTeacherIdent] AS [StudentTeacherIdent],[elementarygradebook].[dbo].[StudentTeacher].[StudentIdent] AS [StudentIdent],[elementarygradebook].[dbo].[StudentTeacher].[TeacherIdent] AS [TeacherIdent],[elementarygradebook].[dbo].[StudentTeacher].[SortKey] AS [SortKey],[elementarygradebook].[dbo].[StudentTeacher].[IsClassRoomTeacher] AS [IsClassRoomTeacher],[elementarygradebook].[dbo].[StudentTeacher].[EnterDate] AS [EnterDate],[elementarygradebook].[dbo].[StudentTeacher].[ExitDate] AS [ExitDate],[elementarygradebook].[dbo].[StudentTeacher].[Note] AS [Note],[elementarygradebook].[dbo].[StudentTeacher].[CreatedUsername] AS [CreatedUsername],[elementarygradebook].[dbo].[StudentTeacher].[CreatedIdent] AS [CreatedIdent],[elementarygradebook].[dbo].[StudentTeacher].[CreatedDate] AS [CreatedDate],[elementarygradebook].[dbo].[StudentTeacher].[UpdatedUsername] AS [UpdatedUsername],[elementarygradebook].[dbo].[StudentTeacher].[UpdatedIdent] AS [UpdatedIdent],[elementarygradebook].[dbo].[StudentTeacher].[UpdatedDate] AS [UpdatedDate] FROM [elementarygradebook].[dbo].[StudentTeacher]  WHERE ( [elementarygradebook].[dbo].[StudentTeacher].[TeacherIdent] IN (@TeacherIdent1) And [elementarygradebook].[dbo].[StudentTeacher].[IsClassRoomTeacher] = @IsClassRoomTeacher2)', N'@TeacherIdent1 int,@IsClassRoomTeacher2 bit', @TeacherIdent1 = 2766, @IsClassRoomTeacher2 = 1

And

exec sp_executesql N'SELECT [elementarygradebook].[dbo].[Student].[StudentIdent] AS [StudentIdent],[elementarygradebook].[dbo].[Student].[StudentId] AS [StudentId],[elementarygradebook].[dbo].[Student].[Note] AS [Note],[elementarygradebook].[dbo].[Student].[CreatedUsername] AS [CreatedUsername],[elementarygradebook].[dbo].[Student].[CreatedIdent] AS [CreatedIdent],[elementarygradebook].[dbo].[Student].[CreatedDate] AS [CreatedDate],[elementarygradebook].[dbo].[Student].[UpdatedUsername] AS [UpdatedUsername],[elementarygradebook].[dbo].[Student].[UpdatedIdent] AS [UpdatedIdent],[elementarygradebook].[dbo].[Student].[UpdatedDate] AS [UpdatedDate] FROM [elementarygradebook].[dbo].[Student]  WHERE ( [elementarygradebook].[dbo].[Student].[StudentIdent] IN (SELECT [elementarygradebook].[dbo].[StudentTeacher].[StudentIdent] AS [StudentIdent] FROM [elementarygradebook].[dbo].[StudentTeacher]  WHERE ( [elementarygradebook].[dbo].[StudentTeacher].[TeacherIdent] IN (@TeacherIdent1) And [elementarygradebook].[dbo].[StudentTeacher].[IsClassRoomTeacher] = @IsClassRoomTeacher2)))', N'@TeacherIdent1 int,@IsClassRoomTeacher2 bit', @TeacherIdent1 = 2766, @IsClassRoomTeacher2 = 1

The actual sql statement executes quite fast but it takes about 10 seconds to return from the Adapter call

Any ideas on this would be appreciated.

Thanks,

Fishy

If the amount of data that's returned is huge, it can take some time before the entities are all created and merged, especially with blob fields. (these are normally truncated by QA so you won't see the full data, which makes the query run faster in QA than in a real app).

What's the amount of rows returned by the queries and are there any blob/text/image fields in teh resultset?

Around 200 total rows. Only vchar, integer, bit fields.

Also, the actual query runs fast in sql profiler so I think the problem is when it's loading the data into the collection.

For reasons I cannot explain the problem has seemed to resolve itself.

Thanks,

Fishy

Fishy wrote:

For reasons I cannot explain the problem has seemed to resolve itself.

Thanks,

Fishy

It might be you had switched on tracing on a very severe level? That might slow down execution of code a lot as a lot of messages are then logged

Truth be told, I've noticed this too, but only with extremely large sets, involving 100,000 rows at a time. Times went from 6 minutes ( 40 seconds query / rest LLBLGen entity construction ) to 1 minute 12 seconds using some very involved XML XSDs. There were a dozen tables involved for each row, each with a prefetch, some with subs. Big stuff.

We were simply moving (transforming) data from one format to another, from one database structure to another. The XML really was a better option for the task. There was virtually no business rules to be applied and it was very much a bulk operation involving tens of millions of rows. We'd grab 100,000 rows, transform, then insert, rolling over the monster database, an operation that has to be done monthly.

Nothing wrong with LLBLGen, but it's just not geared towards batch/bulk operations with prefetch. This is not surprising. It kicks butt everywhere else.

swallace wrote:

Truth be told, I've noticed this too, but only with extremely large sets, involving 100,000 rows at a time. Times went from 6 minutes ( 40 seconds query / rest LLBLGen entity construction ) to 1 minute 12 seconds using some very involved XML XSDs. There were a dozen tables involved for each row, each with a prefetch, some with subs. Big stuff.

We were simply moving (transforming) data from one format to another, from one database structure to another. The XML really was a better option for the task. There was virtually no business rules to be applied and it was very much a bulk operation involving tens of millions of rows. We'd grab 100,000 rows, transform, then insert, rolling over the monster database, an operation that has to be done monthly.

Nothing wrong with LLBLGen, but it's just not geared towards batch/bulk operations with prefetch. This is not surprising. It kicks butt everywhere else.

Fetching a lot of rows into entity objects is indeed not as fast as for example a datatable. This is because a datatable fetch is simply doing GetValues() on a datareader and passes on the array directly as the datarow of the datatable, no delay. The object creation routine has at the moment a tiny bottleneck in the creation of the fields: it produces a hashtable for the entity.Fields[name] lookup. The creation of this hashtable should be done once (as for each entity of the same type, it's the same), but it's done every time. This slows down the fetch when you fetch a lot of rows at once. As changing it causes a severe architecture change, I'm a bit reluctant to do that now, so I've planned it for v2.0.