Speed issue, Query takes 7-8 min

[Voodoo223]

I'm running this query off a table of about 1.5mil records. If I leave the date parameter off It comes up under a minute. When I add it back, it takes 5 minutes before the query even starts running. All the fields used in filtering the query are indexed. Any ideas to speed this up?

SELECT CallsEntered.[Work Order Nbr], CallsEntered.[Date Entered], CallsEntered.[Time Entered], CallsEntered.[Primary Locator Code] AS [ASC], CallsEntered.Headend, CallsEntered.Node, CallsEntered.[Grid Id], CallsEntered.[Q Code], CallsEntered.[Problem Code 01], CallsEntered.[Primary Finding Code], CallsEntered.[Primary Solution Code], CallsEntered.[Cancel Code], CallsEntered.[Scheduled Date], CallsEntered.[Wo Status], CallsEntered.[Date CheckIn], CallsEntered.[Assigned Installer], Calendar.Week, Calendar.Year
FROM Calendar INNER JOIN CallsEntered ON Calendar.Date=CallsEntered.[Date Entered]
WHERE (((CallsEntered.[Date Entered]>=Forms!frmServiceCalls!txtStartDate Or Forms!frmServiceCalls!txtStartDate Is Null)=True) And ((CallsEntered.[Date Entered]<=Forms!frmServiceCalls!txtEndDate Or Forms!frmServiceCalls!txtEndDate Is Null)=True) And ((CallsEntered.Node=Forms!frmServiceCalls!txtnode Or Forms!frmServiceCalls!txtnode Is Null)=True) And ((CallsEntered.[Primary Locator Code]=Forms!frmServiceCalls!cboASC Or Forms!frmServiceCalls!cboASC Is Null)=True) And ((CallsEntered.[Q Code]=Forms!frmServiceCalls!cboQCode Or Forms!frmServiceCalls!cboQCode Is Null)=True));

Thanks

 

[Jon K]

You can use VBA to build the SQL statement of the query on the fly, incorporating only the necessary criteria in the statement. And when both txtStartDate and txtEndDate are not null, use Between ... And ... instead of >= and <=.
.

 

[grnzbra]

Be careful with the Between statement. If the contents of the Date field contain a time element, you will not get the last day unless you add a time element to your criteria. If they do not, you will. Dates are really the number of seconds since Jan 1, 1890 (or some date like that). Any date you enter into the criteria assumes midnight. If the date contains a time element, it will be after midnight and will not be picked up.

 

[Lister]

One other point may be, are the tables on a network or part of the db? Or some on network backend some local?
Network traffic, security and location can slow a query down greatly.