Paging + Prefetch + Adapter seems to generate bad results

Posts   
 
    
tzarger
User
Posts: 87
Joined: 14-Jun-2007
# Posted on: 22-Jun-2007 20:16:03   

LLBLGen Pro v2.0, SQL Server 2K5

I am writing an application to port an older database and doing a fair amount of manipulation to fit that data into the new schema, something I think LLBLGen would be better at than SSIS due to the nature of the conversion.

When I use paging, it seems to be broken, and first off, i am a newbie to LLBLGen, so it most likely is broken because I did something wrong, so please feel free to point out the errors in my way...

I am paging by 100 and on the 2nd and subsequent go arounds, I see this kind of SQL in the SQL Profiler:

exec sp_executesql N'WITH __actualSet AS (SELECT TOP 201 [OldDb].[dbo].[Members].[Member_ID] AS [MemberId], [OldDb].[dbo].[Members].[Member_Username] AS 
[MemberUsername], [OldDb].[dbo].[Members].[Member_Sponsor_ID] AS [MemberSponsorId], [OldDb].[dbo].[Members].[Member_IsAdmin] AS [MemberIsAdmin], 
[OldDb].[dbo].[Members].[Member_IsDubbsReplicatedActive] AS [MemberIsDubbsReplicatedActive], [OldDb].[dbo].[Members].[Member_IsUnsubscribed] AS 
[MemberIsUnsubscribed], [OldDb].[dbo].[Members].[Member_IsGiveAway] AS [MemberIsGiveAway], [OldDb].[dbo].[Members].[Member_Status] AS [MemberStatus], 
[OldDb].[dbo].[Members].[Member_FirstName] AS [MemberFirstName], [OldDb].[dbo].[Members].[Member_LastName] AS [MemberLastName], 
[OldDb].[dbo].[Members].[Member_Password] AS [MemberPassword], [OldDb].[dbo].[Members].[Member_Email] AS [MemberEmail], 
[OldDb].[dbo].[Members].[Member_Address] AS [MemberAddress], [OldDb].[dbo].[Members].[Member_Address2] AS [MemberAddress2], 
[OldDb].[dbo].[Members].[Member_City] AS [MemberCity], [OldDb].[dbo].[Members].[Member_State_ID] AS [MemberStateId], 
[OldDb].[dbo].[Members].[Member_ZipCode] AS [MemberZipCode], [OldDb].[dbo].[Members].[Member_Country_ID] AS [MemberCountryId], 
[OldDb].[dbo].[Members].[Member_Phone] AS [MemberPhone], [OldDb].[dbo].[Members].[Member_SignDate] AS [MemberSignDate], 
[OldDb].[dbo].[Members].[Member_UserHash] AS [MemberUserHash], [OldDb].[dbo].[Members].[Member_IP] AS [MemberIp], 
[OldDb].[dbo].[Members].[Member_GoogleCode] AS [MemberGoogleCode], [OldDb].[dbo].[Members].[Member_PaymentProcessingDomain] AS 
[MemberPaymentProcessingDomain], [OldDb].[dbo].[Members].[Member_Session] AS [MemberSession], [OldDb].[dbo].[Members].[Member_OtherState] AS 
[MemberOtherState] , ROW_NUMBER() OVER(ORDER BY [OldDb].[dbo].[Members].[Member_ID] ASC) AS __rowcnt FROM  [OldDb].[dbo].[Members] ) SELECT * FROM 
__actualSet WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd ORDER BY [__rowcnt] ASC',N'@__rownoStart int,@__rownoEnd 
int',@__rownoStart=100,@__rownoEnd=200

Notice the "@__rownoStart=100,@__rownoEnd=200" while this seems correct, it should only be pulling back the 100-200th row as I am paging by 100.

Then on my prefetch call, I get this SQL:

exec sp_executesql N'SELECT [OldDb].[dbo].[SentEmails].[SentEmail_ID] AS [SentEmailId], [OldDb].[dbo].[SentEmails].[SentEmail_Member_ID] AS 
[SentEmailMemberId], [OldDb].[dbo].[SentEmails].[SentEmail_Program_ID] AS [SentEmailProgramId], [OldDb].[dbo].[SentEmails].[SentEmail_IsAdminBroadcast] AS 
[SentEmailIsAdminBroadcast], [OldDb].[dbo].[SentEmails].[SentEmail_MemberStatus] AS [SentEmailMemberStatus], 
[OldDb].[dbo].[SentEmails].[SentEmail_SendDate] AS [SentEmailSendDate], [OldDb].[dbo].[SentEmails].[SentEmail_DateSent] AS [SentEmailDateSent], 
[OldDb].[dbo].[SentEmails].[SentEmail_Subject] AS [SentEmailSubject], [OldDb].[dbo].[SentEmails].[SentEmail_Body] AS [SentEmailBody], 
[OldDb].[dbo].[SentEmails].[SentEmail_Memo] AS [SentEmailMemo] FROM [OldDb].[dbo].[SentEmails]  WHERE ( [OldDb].[dbo].[SentEmails].[SentEmail_Member_ID] 
IN (@SentEmailMemberId1, @SentEmailMemberId2, @SentEmailMemberId3, @SentEmailMemberId4, @SentEmailMemberId5, @SentEmailMemberId6, @SentEmailMemberId7, 
@SentEmailMemberId8, @SentEmailMemberId9, @SentEmailMemberId10, @SentEmailMemberId11, @SentEmailMemberId12, @SentEmailMemberId13, @SentEmailMemberId14, 
@SentEmailMemberId15, @SentEmailMemberId16, @SentEmailMemberId17, @SentEmailMemberId18, @SentEmailMemberId19, @SentEmailMemberId20, @SentEmailMemberId21, 
@SentEmailMemberId22, @SentEmailMemberId23, @SentEmailMemberId24, @SentEmailMemberId25, @SentEmailMemberId26, @SentEmailMemberId27, @SentEmailMemberId28, 

...[Snip Snip]...


@SentEmailMemberId184, @SentEmailMemberId185, @SentEmailMemberId186, @SentEmailMemberId187, @SentEmailMemberId188, @SentEmailMemberId189, 
@SentEmailMemberId190, @SentEmailMemberId191, @SentEmailMemberId192, @SentEmailMemberId193, @SentEmailMemberId194, @SentEmailMemberId195, 
@SentEmailMemberId196, @SentEmailMemberId197, @SentEmailMemberId198, @SentEmailMemberId199, @SentEmailMemberId200))',N'@SentEmailMemberId1 
int,@SentEmailMemberId2 int,@SentEmailMemberId3 int,@SentEmailMemberId4 int,@SentEmailMemberId5 int,@SentEmailMemberId6 int,@SentEmailMemberId7 
int,@SentEmailMemberId8 int,@SentEmailMemberId9 int,@SentEmailMemberId10 int,@SentEmailMemberId11 int,@SentEmailMemberId12 int,@SentEmailMemberId13 

...[Snip Snip]...


int,@SentEmailMemberId188 int,@SentEmailMemberId189 int,@SentEmailMemberId190 int,@SentEmailMemberId191 int,@SentEmailMemberId192 int,@SentEmailMemberId193 
int,@SentEmailMemberId194 int,@SentEmailMemberId195 int,@SentEmailMemberId196 int,@SentEmailMemberId197 int,@SentEmailMemberId198 int,@SentEmailMemberId199 
int,@SentEmailMemberId200 
int',@SentEmailMemberId1=1,@SentEmailMemberId2=2,@SentEmailMemberId3=3, @SentEmailMemberId4=4,@SentEmailMemberId5=5,@SentEmailMemberId6=6,@SentEmailMemberId7=7,
@SentEmailMemberId8=8,@SentEmailMemberId9=9,@SentEmailMemberId10=17, @SentEmailMemberId11=18,@SentEmailMemberId12=34,@SentEmailMemberId13=35,
@SentEmailMemberId14=36,@SentEmailMemberId15=37,@SentEmailMemberId16=38, @SentEmailMemberId17=39,@SentEmailMemberId18=40,@SentEmailMemberId19=42,
@SentEmailMemberId20=43,@SentEmailMemberId21=47,@SentEmailMemberId22=48, @SentEmailMemberId23=49,@SentEmailMemberId24=56,@SentEmailMemberId25=58,
@SentEmailMemberId26=59,@SentEmailMemberId27=60,@SentEmailMemberId28=66, @SentEmailMemberId29=67,@SentEmailMemberId30=68,@SentEmailMemberId31=69,

...[Snip Snip]...


@SentEmailMemberId192=478,@SentEmailMemberId193=479,@SentEmailMemberId194=480, @SentEmailMemberId195=487,@SentEmailMemberId196=488,
@SentEmailMemberId197=490,@SentEmailMemberId198=492,@SentEmailMemberId199=493, @SentEmailMemberId200=494

And as you can see, it is calling all 200, not 100-200... this builds and builds until I get the excpetion saying it cannot have over 2100 parameters (basically that is error, not exact language).

I have not touched the .ParameterisedPrefetchPathThreshold so I am assuming this defaults to 50...? But even after that, I adjust it to 50 just for testing, and same result... So that does not seem to work. I assume the reason why, is I read that with prefetching, you have to use parameters, okay... as long as it really respect the 100 page size.

Here is my code:

         OldDb.Migrate.Data.DatabaseSpecific.DataAccessAdapter.SetSqlServerCompatibilityLevel( SqlServerCompatibilityLevel.SqlServer2005 );
         oldAdapter.ParameterisedPrefetchPathThreshold = 50;

         // First get the total number of Members we need to port
         IRelationPredicateBucket filter = new RelationPredicateBucket();
         filter.PredicateExpression.Add( MembersFields.MemberIsGiveAway == false );
         int totalRows = (int)oldAdapter.GetDbCount( new MembersEntityFactory().CreateFields(), filter, null, false );
         int pages = (totalRows / 100 ) +1;

         OldDb.Migrate.Data.HelperClasses.EntityCollection<MembersEntity> members = new OldDb.Migrate.Data.HelperClasses.EntityCollection<MembersEntity>( new MembersEntityFactory() );

         prefetch = new PrefetchPath2( (int)EntityType.MembersEntity );
         prefetch.Add( MembersEntity.PrefetchPathSentEmails );
         prefetch.Add( MembersEntity.PrefetchPathMemberPrograms );
         prefetch.Add( MembersEntity.PrefetchPathMemberNotes );
         prefetch.Add( MembersEntity.PrefetchPathMemberMarketingMethods );
         prefetch.Add( MembersEntity.PrefetchPathTempMemberToUserDetail ).SubPath.Add( OldDb.Migrate.Data.EntityClasses.TempMemberToUserDetailEntity.PrefetchPathUserDetails ).SubPath.Add( UserDetailsEntity.PrefetchPathAspnetUsers ).SubPath.Add( AspnetUsersEntity.PrefetchPathAspnetUsersInRoles );
         prefetch.Add( MembersEntity.PrefetchPathMemberPaymentProviders );

         // we need to process the payments separately as we need to remap both the payee and payer MemberId

         IPrefetchPathElement2 tempPath = MembersEntity.PrefetchPathTempMemberIdToUsername;
         tempPath.SubPath.Add( TempMemberIdToUsernameEntity.PrefetchPathUsers ).SubPath.Add( UsersEntity.PrefetchPathSubscribers );
         tempPath.SubPath.Add( TempMemberIdToUsernameEntity.PrefetchPathCounters );
         prefetch.Add( tempPath );

         for( int i = 1; i <= pages; i++ )
         {

            oldAdapter.FetchEntityCollection( members, null, 0, null, prefetch, i, 100 );
            [...]
          }

The real problem seems to me that it is actually returing all the MembersEntity (the parent) and thus it generates the prefetch sql with all the MembersEntities returned... Is this correct?

Some other notes about why I am doing this with paging, I have about 90,00 members to move, I have over 10 million associated counter records, and other areas would have 500,000 records to 1 million records etc. Obivously I cannot bring all that in to memory as I get an OutOfMemoryException.

Do I need to bring back all the fields in each entity, yes, as this is a migration. Why am I prefetching everything, well since I cannot do IDENTITY inserts, I know there will be cases where the MemberId will be changing and thus I need to process all related material at the same time.

Granted I have thought about creating a temp table, that maps the old MemberId and the new MemberId and get that back in a TypedList and do lookups on each of the other related tables and process each one independently and forget about the prefectching.. but it seems like a great feature and that is one of the strong points of LLBLGen, so great work on it!

Thanks for your help!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Jun-2007 20:43:28   

Could you try the latest runtimes please? they have an updated paging routine.

Also could you try sqlserver2000 compatibility (setting on the DQE) to see if a temptable approach gives different results?

Frans Bouma | Lead developer LLBLGen Pro
tzarger
User
Posts: 87
Joined: 14-Jun-2007
# Posted on: 22-Jun-2007 21:40:17   

Otis wrote:

Could you try the latest runtimes please? they have an updated paging routine.

Also could you try sqlserver2000 compatibility (setting on the DQE) to see if a temptable approach gives different results?

Thanks for your quick reply.

I was using the demo, but went ahead and purchased... logged into the members area, downloaded the June build of v2.0 and in SQL2000 and SQl 2005 mode, I still get the same result.

tzarger
User
Posts: 87
Joined: 14-Jun-2007
# Posted on: 22-Jun-2007 22:20:22   

Otis wrote:

Could you try the latest runtimes please? they have an updated paging routine.

Also could you try sqlserver2000 compatibility (setting on the DQE) to see if a temptable approach gives different results?

By the way, if I run this code directly myself, it does only return the correct 100 rows...

exec sp_executesql N'WITH __actualSet AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt FROM (SELECT TOP 200 
[OldDb].[dbo].[Members].[Member_ID] AS [MemberId], [OldDb].[dbo].[Members].[Member_Username] AS [MemberUsername], 
[OldDb].[dbo].[Members].[Member_Sponsor_ID] AS [MemberSponsorId], [OldDb].[dbo].[Members].[Member_IsAdmin] AS [MemberIsAdmin], 
[OldDb].[dbo].[Members].[Member_IsDubbsReplicatedActive] AS [MemberIsDubbsReplicatedActive], [OldDb].[dbo].[Members].[Member_IsUnsubscribed] AS 
[MemberIsUnsubscribed], [OldDb].[dbo].[Members].[Member_IsGiveAway] AS [MemberIsGiveAway], [OldDb].[dbo].[Members].[Member_Status] AS [MemberStatus], 
[OldDb].[dbo].[Members].[Member_FirstName] AS [MemberFirstName], [OldDb].[dbo].[Members].[Member_LastName] AS [MemberLastName], 
[OldDb].[dbo].[Members].[Member_Password] AS [MemberPassword], [OldDb].[dbo].[Members].[Member_Email] AS [MemberEmail], 
[OldDb].[dbo].[Members].[Member_Address] AS [MemberAddress], [OldDb].[dbo].[Members].[Member_Address2] AS [MemberAddress2], 
[OldDb].[dbo].[Members].[Member_City] AS [MemberCity], [OldDb].[dbo].[Members].[Member_State_ID] AS [MemberStateId], 
[OldDb].[dbo].[Members].[Member_ZipCode] AS [MemberZipCode], [OldDb].[dbo].[Members].[Member_Country_ID] AS [MemberCountryId], 
[OldDb].[dbo].[Members].[Member_Phone] AS [MemberPhone], [OldDb].[dbo].[Members].[Member_SignDate] AS [MemberSignDate], 
[OldDb].[dbo].[Members].[Member_UserHash] AS [MemberUserHash], [OldDb].[dbo].[Members].[Member_IP] AS [MemberIp], 
[OldDb].[dbo].[Members].[Member_GoogleCode] AS [MemberGoogleCode], [OldDb].[dbo].[Members].[Member_PaymentProcessingDomain] AS 
[MemberPaymentProcessingDomain], [OldDb].[dbo].[Members].[Member_Session] AS [MemberSession], [OldDb].[dbo].[Members].[Member_OtherState] AS 
[MemberOtherState] FROM [OldDb].[dbo].[Members] ) AS _tmpSet) SELECT * FROM __actualSet WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd ORDER 
BY [__rowcnt] ASC',N'@__rownoStart int,@__rownoEnd int',@__rownoStart=100,@__rownoEnd=200

So with that in mind, why does FetchEntityCollection seem to return all 200 (in this example) and build prefetch paths of the entire collection of 200, not the 100 SQL Server should return.

tzarger
User
Posts: 87
Joined: 14-Jun-2007
# Posted on: 22-Jun-2007 22:46:52   

I think I have the answer, here was my code:

OldDb.Migrate.Data.DatabaseSpecific.DataAccessAdapter.SetSqlServerCompatibilityLevel( SqlServerCompatibilityLevel.SqlServer2005 );
         oldAdapter.ParameterisedPrefetchPathThreshold = 50;

         // First get the total number of Members we need to port
         IRelationPredicateBucket filter = new RelationPredicateBucket();
         filter.PredicateExpression.Add( MembersFields.MemberIsGiveAway == false );
         int totalRows = (int)oldAdapter.GetDbCount( new MembersEntityFactory().CreateFields(), filter, null, false );
         int pages = (totalRows / 100 ) +1;

         OldDb.Migrate.Data.HelperClasses.EntityCollection<MembersEntity> members = new OldDb.Migrate.Data.HelperClasses.EntityCollection<MembersEntity>( new MembersEntityFactory() );

         prefetch = new PrefetchPath2( (int)EntityType.MembersEntity );
         prefetch.Add( MembersEntity.PrefetchPathSentEmails );
         prefetch.Add( MembersEntity.PrefetchPathMemberPrograms );
         prefetch.Add( MembersEntity.PrefetchPathMemberNotes );
         prefetch.Add( MembersEntity.PrefetchPathMemberMarketingMethods );
         prefetch.Add( MembersEntity.PrefetchPathTempMemberToUserDetail ).SubPath.Add( OldDb.Migrate.Data.EntityClasses.TempMemberToUserDetailEntity.PrefetchPathUserDetails ).SubPath.Add( UserDetailsEntity.PrefetchPathAspnetUsers ).SubPath.Add( AspnetUsersEntity.PrefetchPathAspnetUsersInRoles );
         prefetch.Add( MembersEntity.PrefetchPathMemberPaymentProviders );

         // we need to process the payments separately as we need to remap both the payee and payer MemberId

         IPrefetchPathElement2 tempPath = MembersEntity.PrefetchPathTempMemberIdToUsername;
         tempPath.SubPath.Add( TempMemberIdToUsernameEntity.PrefetchPathUsers ).SubPath.Add( UsersEntity.PrefetchPathSubscribers );
         tempPath.SubPath.Add( TempMemberIdToUsernameEntity.PrefetchPathCounters );
         prefetch.Add( tempPath );

         for( int i = 1; i <= pages; i++ )
         {

            oldAdapter.FetchEntityCollection( members, null, 0, null, prefetch, i, 100 );
            [...]
         }

You notice that I create a new EntityCollection<MembersEntity> up near the top of the code block... well it seems that FetchEntityCollection does not actually create anything new, it appends to the existing EntityCollection...

The way I made it work, was to actually include the create of the EntityCollection inside for the for loop and send in a black EntityCollection each time.

Is this by design? Or should FetchEntityCollection actually only populate based on what was pulled out SQL Server (or choose your db provider here) ... ?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Jun-2007 12:22:09   

That's by design simple_smile . It doesn't clear the collection for you.

Frans Bouma | Lead developer LLBLGen Pro