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, I have a seemingly simple query criteria of [Date] > #12/01/08# AND [Date] < #02/29/08#. I am also Eager Loading some related tables, however when LightSpeed is creating the SQL it seems to be creating unnecassary selects in the EXISTS clauses of the related tables. Take a look at the following SQL trace: Pay special note that in the main select the WHERE clause for both days is ANDed together correctly, but in the related table there are essentially two selects where there good be one. I really love the EL feature, it is a great performance enhancer. However, when there is more than criteria LS is currently creating a duplicate SELECT in each EXISTS. And the problem compounds with more criteria and more ELs. SELECT [do_Patient_SCH_Appt].[Id], [do_Patient_SCH_Appt].[ED], [do_Patient_SCH_Appt].[CreatedOn], [do_Patient_SCH_Appt].[DeletedOn], [do_Patient_SCH_Appt].[DurationString], [do_Patient_SCH_Appt].[Type] FROM [do_Patient_SCH_Appt] WHERE (([do_Patient_SCH_Appt].[JustStartDate] >= '12/1/2007 12:00:00 AM' AND [do_Patient_SCH_Appt].[JustStartDate] <= '2/29/2008 12:00:00 AM') AND [do_Patient_SCH_Appt].[DeletedOn] IS NULL) ORDER BY [do_Patient_SCH_Appt].[StartOn]; SELECT [do_Patient].[Id], [do_Patient].[ED], [do_Patient].[CreatedOn], [do_Patient].[DeletedOn], [do_Patient].[LockVersion], [do_Patient].[UpdatedOn], [do_Patient].[DOB], [do_Patient].[Gender], [do_Patient].[PreferredContact], [do_Patient].[Address_EmergencyId], [do_Patient].[Address_HomeId], [do_Patient].[Address_MailId], [do_Patient].[Address_WorkId], [do_Patient].[Email_PrimaryId], [do_Patient].[EmergContact], [do_Patient].[EmergType], [do_Patient].[Employer], [do_Patient].[IDNumber], [do_Patient].[Name_PrimaryId], [do_Patient].[Occupation], [do_Patient].[Phone_EmergencyId], [do_Patient].[Phone_HomeId], [do_Patient].[Phone_MobileId], [do_Patient].[Phone_WorkId], [do_Patient].[Race], [do_Patient].[Site_PrimaryId], [do_Patient].[Status] FROM [do_Patient] WHERE (((EXISTS ( SELECT [do_Patient_SCH_Appt].* FROM [do_Patient_SCH_Appt] WHERE [do_Patient_SCH_Appt].[PatientId] = [do_Patient].[Id] AND [do_Patient_SCH_Appt].[JustStartDate] >= '12/1/2007 12:00:00 AM' ) AND EXISTS ( SELECT [do_Patient_SCH_Appt].* FROM [do_Patient_SCH_Appt] WHERE [do_Patient_SCH_Appt].[PatientId] = [do_Patient].[Id] AND [do_Patient_SCH_Appt].[JustStartDate] <= '2/29/2008 12:00:00 AM' )) AND EXISTS ( SELECT [do_Patient_SCH_Appt].* FROM [do_Patient_SCH_Appt] WHERE [do_Patient_SCH_Appt].[PatientId] = [do_Patient].[Id] AND [do_Patient_SCH_Appt].[DeletedOn] IS NULL )) AND [do_Patient].[DeletedOn] IS NULL);
|
|
|
Hi Kavan, This shouldn't be happening as we have an optimizer that merges this stuff together. Something is causing it to not work in your particular case. We'll try an repo it at our end and get back to you soon. Cheers, Andrew. |
|
|
Hi Kavan, This is fixed in 1.2 Cheers, Andrew. |
|
|
Hi Andrew, I just noticed that I am seeing a similar problem with tonights nightly build (3/Feb). I presume tonights build should include the fix - my queries have a few more criteria so maybe your fix doesn't solve all situations? Anyway, here are the queries that Lightspeed is generating: SELECT SELECT Cheers, |
|
|
Hi Greg, What does your LightSpeed query look like? Cheers, Andrew. |
|
|
Hi Andrew, The query I'm using is: return Repository.Find<Contract>(Entity.Attribute("DueDate") <= DateTime.Today &&(Entity.Attribute("ContractStatus") != ContractStatus.Repaid.Id && Entity.Attribute("ContractStatus") != ContractStatus.Sold.Id && Entity.Attribute("ContractStatus") != ContractStatus.Extended.Id)); Hope this helps, Cheers, Greg
|
|