I may be doing this completely wrong, but maybe someone can guide me to a better solution. I am using llblgen with an access database for fantasy (american) football drafting software. Well I fill a league entity with all of the information needed to make some calculations.

PrefetchPath path = new PrefetchPath((int)EntityType.LeagueEntity);
path.Add(LeagueEntity.PrefetchPathPlayerPoints).SubPath.Add( PlayerPointsEntity.PrefetchPathPlayer).SubPath.Add(PlayerEntity.PrefetchPathPlayerStats);
path.Add(LeagueEntity.PrefetchPathScoring).SubPath.Add(ScoringEntity.PrefetchPathScoringType);
LeagueEntity league = new LeagueEntity(Globals.gCurrentLeagueID, path);

I go through make the calculations and these results in new values for the players that are saved in the playerpoints entities.

I then do a recursive save for league.

league.save(true);

and after a long time all of the values are updated. Is there anything I can do to speed along the update for access or have I made a mistake in my database design possibly for making this efficient.

Your prefetch path is multiple levels deep and can potentially contain thousands of objects. If all of these change or a majority of them, saving them again costs some time, as they're all saved inside a transaction.

You can switch on tracing to see how many queries there are created. Access tends to downgrade in performance if more and more objects are participating in a transaction, perhaps that's what you're seeing.

I'm not sure what the calculations are. If they're for example bulk updating thousands of entities with an operation like : field = field + 10, you can also think of updating the entities directly, which will generate a single update query for updating entities in the db.

I did narrow it down to one collection, but those can't be mass updated in the calculations. The update is for about 500 rows.

So you're updating 500 entities in an access db and how long does that take? I can try to reproduce it here, a 500 entity update on access, to see how long it takes here. You're storing large binaries in the database btw? Or large pieces of text?

No it's a small amount of data. I just found something interesting though. I have just started using the ANTS Profiler and that seems to have been the reason why the query was taking an astronomical time. It is still running slow at about 10 seconds, but not the 200+ that was occuring with ANTS in the mix. Sorry to waste your time with that.

bclubb wrote:

No it's a small amount of data. I just found something interesting though. I have just started using the ANTS Profiler and that seems to have been the reason why the query was taking an astronomical time. It is still running slow at about 10 seconds, but not the 200+ that was occuring with ANTS in the mix. Sorry to waste your time with that.

aha! Yes profilers slow down execution tremendously, so only do that with small sets of data to find a problem after you've measured with large sums of data which are for example not that fast.

10 seconds for 500 row update could be ok for access. I've to check that out.