select
* from Claims this_
left outer join Items this_1_ on this_.ClaimID=this_1_.ClaimID
inner join Doctors sureclaim2x2_ on this_.DoctorID=sureclaim2x2_.DoctorID
inner join Patients sureclaim2x3_ on this_.PatientID=sureclaim2x3_.PatientID
where Items.ItemID=1 and this_.DateCreated>?
This query has terrible performance.. The join columns are all indexed.
|
Because there is an Items.ItemID=1 condition, HXTT Access will optimize left out join to inner join, so that your sql becomes
select * from Claims this_, Items this_1_, Doctors sureclaim2x2_,Patients sureclaim2x3_ where this_.ClaimID=this_1_.ClaimID and this_.DoctorID=sureclaim2x2_.DoctorID and this_.PatientID=sureclaim2x3_.PatientID and Items.ItemID=1 and this_.DateCreated>?
It should work normal. Please download the latest package and try it. If it's slow still, please let us know how many rows in Claims, Items, Doctors, and Patients.
|
Items actually has a composite key of ClaimID, ItemID. There isn't any optimization going on here because ItemID alone is not part of the key. However setting ItemID to 1 makes sure that we have a one-to-one mapping of Claims and Items.
Hope this clarifies.
|
|
Please download the latest package which has supported optimization on this_.ClaimID=this_1_.ClaimID and Items.ItemID=1 on a composite key of ClaimID, ItemID.
|
Main
webmaster@hxtt.com