This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi guys, Installed the latest nightly build a couple of nights ago (3.1.2714.17343) and promptly watched our systems blow up :) I've pulled down the build from 8/6 as well with the same result. I don't know how far back this stretches.
Here's the repro:
using (HosPharmDomainUnitOfWork unitOfWork = SqlRepositoryHelper.SqlContext.CreateUnitOfWork()) { unitOfWork.Context.Logger = logger; var persistedEntity = unitOfWork.FindById<Patient>(patientId); var company = unitOfWork.FindById<Company>(persistedEntity.CompanyId.Value); persistedEntity.Company = company; persistedEntity.Ward = unitOfWork.FindById<Ward>(persistedEntity.WardId.Value); } Assert.AreEqual (5, logger.NumberOfStatements);
I know the above is probably really bad, but that's the way its currently done for various reasons (looks like the intention above was to force a lazy load). Where Company->Patient is 1-Many and Ward->Patient is 1-Many (no connection between company and ward). What previously happened (unfortunately, my previous build I was working with was old - 3.1.2122.15142) was that assigning an entity to a property (i.e. .Company = company) would populate both sides of the collection (so would select all patients for that company)... ouch, but I could live with that. So setting both Company and Ward would create an extra sql (select * from patient where wardid = @wardid).
Now... It is generating 1000 statements (the number of patients who belong to that company and that ward) for the second assign. So the first assign (company in this case) still generates our 1 statement, but the second generates the number of patients who belong to both sets. Note that each sql query is the same (select * from patient where wardid = @wardid). So we end up selecting all patients (1000's) thousands of times, just to assign a property.
Is the above (assigning properties) such bad practice that it should never be done (and we should remove all similar code), or should we wait for the fix?
Cheers, Mark |
|
|
Hi Mark,
I dont quite understand why you would be doing this: persistedEntity.Ward = unitOfWork.FindById<Ward>(persistedEntity.WardId.Value);
Which in effect is a bit of a no-op, although behind the scenes it certainly isnt currently, we will certainly have a look into that.
If the intention is to make sure the Ward was already loaded (e.g. eager loaded) then you can set this on the association. If you want to conditionally have this occur you can use named aggregate to control when this kicks in, you can set this as the Collection Aggregate Name on the association in the designer. Likewise with the Company assignment. I am presuming you want to avoid the lazy loads after you fall out of the scope of your UnitOfWork, but you can certainly use named aggregates to force a conditional eager load in this case to achieve the same result(and avoid the associated query overhead!). Let us know if this is not the case and there is some other reason why you need to make the assignments in this manner.
Will let you know once we have some more diagnosis etc on the spurious queries on assignment as well of course.
Jeremy |
|
|
Hi Jeremy,
I've ended up pulling out all of the code doing the above. Essentially it was on the server side and forcing a lazy load before sending objects back over WCF. We've replaced it either with named Aggregates (which were already heavily used) or by doing a property get (i.e. var ward = entity.Ward) to force it to be lazy loaded. So in some ways, thanks for the change (probably buggy though) as it highlighted what was an issue in the codebase. When assigning a new value, is best practice to just assign the Id (i.e. CompanyId) or setting the whole entity (i.e. .Company = company)? I haven't tested to see the behaviour of this. Cheers, Mark |
|
|
In general it would be better to assign the entity if you have it to hand. If you only have an Id at hand and you only need to make the assignment and not subsequently use that property then it is definitely better just to assign the Id since it will save you the extra load.
Jeremy |
|
|
For what it's worth the issue with extraneous loads should be fixed in the next nightly -- though hopefully it won't matter to you now! Oh, and congratulations, you now have a LightSpeed test named after you! |
|
|
Thanks Ivan - have just got the latest and confirmed the large number of extra loads has been resolved.
A question that's come out of this - why is the other side of a collection populated when assigning an entity to a property? e.g. setting patient.Ward = newWard causes ward.Patients to be populated (or at least the equivalent sql to be run). (where company->patient is 1->many).
Is this standard, or due to how we have entities configured? Is there any way of preventing this behaviour (I tried detaching newCompany) as otherwise I can imagine performance blowing out as the size of the database increases.
Regards, Mark
btw, might have to fork out for a source code license just to see my name in there ;-) |
|
|
For reference, (related to above) the following ways of calling don't result in the extra sql call:
1. Select ward using a different unit of work and assign by id.
using (HosPharmDomainUnitOfWork unitOfWork2 = SqlRepositoryHelper.SqlContext.CreateUnitOfWork()) { using (HosPharmDomainUnitOfWork unitOfWork = SqlRepositoryHelper.SqlContext.CreateUnitOfWork()) { var patient = unitOfWork.FindById<Patient>(patientId); var ward = unitOfWork2.FindById<Ward>(wardId); patient.WardId = ward.Id; } }
2. Use the same unit of work, but detach the ward then assign by id
using (HosPharmDomainUnitOfWork unitOfWork = SqlRepositoryHelper.SqlContext.CreateUnitOfWork()) { var patient = unitOfWork.FindById<Patient>(patientId); var ward = unitOfWork.FindById<Ward>(wardId);
unitOfWork.Detach(ward);
patient.WardId = ward.Id; }
3. Use the same unit of work, but detach both entities and assign:
using (HosPharmDomainUnitOfWork unitOfWork = SqlRepositoryHelper.SqlContext.CreateUnitOfWork()) { var patient = unitOfWork.FindById<Patient>(patientId); var ward = unitOfWork.FindById<Ward>(wardId); unitOfWork.Detach(ward); unitOfWork.Detach(patient);
patient.Ward = ward; }
|
|
|
The reason we do this is to ensure that the reverse collection is properly updated with the entity that is attaching itself. (Previously, if the Patients collection had not been loaded, then when it was loaded, it only included the patients that were associated with ward in the database, not the newly associated patient.) It is therefore not currently possible to disable this behaviour. In a future build we would like to get rid of this and have the "patient needs to be added to ward.Patients" as a sort of deferred action that would only come into play once you actually references the ward.Patients collection, but this introduces all sorts of exciting complexities. [quote user="kiwidev"]btw, might have to fork out for a source code license just to see my name in there[/quote] Hmm, this could be the beginning of a beautiful new business model. |
|