Super slow Between 3

[eatwork]

Hi, I am having a problem with my sql query. I am not sure why it is so slow after I add a between or >= comparison on the load date of my sql. The query I have created below returns between 3500 - 6000 records, that I use to populate a listbox rowsource. I want to allow the user to filter the returned data by date if they wish to, or find the related records that are in between two values selected by the user. When I add a " [loadDate] between #05/01/2006# AND #09/20/2006# " it takes an extra 5 minutes to display the data. If I don't add that condition the query runs quite fast and almost without any delays.

Original query:

SELECT tbl_Job.jobId, tbl_Load.loadId, tblLdHist.loadHistId, tblDispHist.dispatchId, tblDispHist.dispatchHistId, tbl_Company.companyId AS companyId, tbl_Company.companyName AS Company, tbl_Job.careOfCompanyId, tbl_Company_1.companyName AS COCompany, [contactFirstName] & IIf(IsNull([contactLastName]) Or [contactLastName]="",""," ") & [contactLastName] AS [Job Cont], tbl_Contact.contactPrimaryPhone AS [Cont Phone], tbl_LoadLoadUnload.loadLoadDate AS [Load Date], tbl_LoadLoadUnload.loadUnloadDate AS [Unload Date], join_tbl_Load_JobCommodity.loadCommodityOriginLocation AS [Origin Location], join_tbl_Load_JobCommodity.loadCommodityDestinationLocation AS [Destination Location], tbl_Commodity.commodityName & IIf(nz([commoditySerialNum],'')='','','(S/N:' & [commoditySerialNum] & ')') & IIf(nz([commodityUnitNum],'')='','',' - ' & [commodityUnitNum]) & IIf(nz([commodityCurrentLSD],'')='','',' - ' & [commodityCurrentLSD]) AS commodity, tbl_CommodityType.commodityType, tblLdHist.invoiceId, tblDispHist.invoiceId, tblLdHist.loadHistDescription, tblDispHist.dispatchHistDescription, tblDispHist.dispatchHistUsingUnitNum, tblLdHist.trailerCombinationType, tblLdHist.loadHistType
FROM tbl_LoadLoadUnload RIGHT JOIN ((((tbl_Contact RIGHT JOIN (tbl_Company AS tbl_Company_1 RIGHT JOIN (tbl_Company RIGHT JOIN tbl_Job ON tbl_Company.companyId = tbl_Job.companyId) ON tbl_Company_1.companyId = tbl_Job.careOfCompanyId) ON tbl_Contact.contactId = tbl_Job.jobContactId) LEFT JOIN (tbl_Load LEFT JOIN tbl_LoadHist AS tblLdHist ON (tbl_Load.jobId = tblLdHist.jobId) AND (tbl_Load.loadId = tblLdHist.loadId)) ON tbl_Job.jobId = tbl_Load.jobId) LEFT JOIN (join_tbl_Load_JobCommodity RIGHT JOIN ((tbl_Commodity LEFT JOIN tbl_CommodityType ON tbl_Commodity.commodityTypeId = tbl_CommodityType.commodityTypeId) RIGHT JOIN join_tbl_LoadCommodity_LoadUnload ON tbl_Commodity.commodityId = join_tbl_LoadCommodity_LoadUnload.commodityId) ON (join_tbl_Load_JobCommodity.jobId = join_tbl_LoadCommodity_LoadUnload.jobId) AND (join_tbl_Load_JobCommodity.loadId = join_tbl_LoadCommodity_LoadUnload.loadId) AND (join_tbl_Load_JobCommodity.commodityId = join_tbl_LoadCommodity_LoadUnload.commodityId)) ON (tblLdHist.jobId = join_tbl_LoadCommodity_LoadUnload.jobId) AND (tblLdHist.loadId = join_tbl_LoadCommodity_LoadUnload.loadId) AND (tblLdHist.loadHistId = join_tbl_LoadCommodity_LoadUnload.loadHistId)) LEFT JOIN (tbl_Dispatch AS tblDisp LEFT JOIN tbl_DispatchHist AS tblDispHist ON (tblDisp.jobId = tblDispHist.jobId) AND (tblDisp.loadId = tblDispHist.loadId) AND (tblDisp.loadHistId = tblDispHist.loadHistId) AND (tblDisp.dispatchId = tblDispHist.dispatchId)) ON (tblLdHist.jobId = tblDisp.jobId) AND (tblLdHist.loadId = tblDisp.loadId) AND (tblLdHist.loadHistId = tblDisp.loadHistId)) ON tbl_LoadLoadUnload.loadLoadUnloadId = join_tbl_LoadCommodity_LoadUnload.loadLoadUnloadId
ORDER BY tbl_Job.jobId DESC , IIf(IsNull(tbl_load.loadId),Null,CInt(tbl_load.loadId)) DESC , IIf(IsNull(tblLdHist.loadHistId),Null,CInt(tblLdHist.loadHistId)) DESC , tbl_Company.companyName, tbl_Company_1.companyName, tbl_LoadLoadUnload.loadLoadDate, tbl_LoadLoadUnload.loadUnloadDate, IIf(IsNull(tbldisp.dispatchId),Null,CInt(tbldisp.dispatchId)) DESC , IIf(IsNull(tblDispHist.dispatchHistId),Null,CInt(tblDispHist.dispatchHistId)) DESC;

Code that I use to populate my listbox

Select * from qrySearchListLoad  where [Load Date] >= #09/20/2006# AND  ((dispatchId)='01' Or (dispatchId) Is Null)  AND ((dispatchHistId)='01' Or (dispatchHistId) Is Null)

 

[dhookom]

Is [Load Date] indexed?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[eatwork]

Hello dhookom,
Thank you for your reply, I have indexed the loadLoadDate field, but am unsure of how to index the [Load Date] field in the query? After indexing I am still having the same issue with a slow process. Would you happen to have any other suggestions?Thank you

 

[lespaul]

you need to index the field in the table. Go to the table design, select the field and select Index 'Yes Duplicates OK'




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[eatwork]

Hi lespaul thank you for your post,
I have gone to the table and indexed the field as you had described. Could there be anything else slowing down the query?

 

[genomon]

Have you tried the performance analyzer in Access? Infrequently it actually suggests good ideas. Click on Tools|Analyze|Performance.....

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[eatwork]

Hi genomon,
Thanks for your post.
I have run that both in the database file and my forms file and neither have improved the query. I just don't understand why it would take so long to filter the rows, upwards of 5minutes, when I can populate all of the data from the query in 3secs.

 

[dhookom]

I tried to count the number of tables, fields, and joins in this query but gave up. I would make sure all join fields are properly indexes and see what would happen if you tried the criteria in qrySearchListLoad.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[eatwork]

Hello dhookom,
Thank you for the post, I have tried the criteria in the acutal query and it does not seem to be slow at all. It appears the lag seems to come when I try to set the rowsource of the listbox, and that is only when I add the conditional arguments, otherwise it populates the listbox quickly. Is there any thing else I could do to improve the performnance? Thank you

Also I have a question regarding the indexes. SHould I go through my entire db and change all of the field values to indexed? will that increase performance? Thank you again

 

[dhookom]

Why don't you use a verson of the main query as the Row Source for the list box? Do you really need all the fields and tables?

Don't index every field. There is a limit to the number of indices in a table. Do index fields used in JOINS, sorting, and criteria.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[eatwork]

Hi dhookom,
thank you for your post and reply.
I need the query to be dynamic. The query is going to be used to allow the user to search for items in the database based on their selections. This is the main reason in why I require all of the of the fields, tables, and joins. Its huge, but its the only way I can show the user the fields they wish to see. For example, the between statement is only added once the user enters a before and after date. The weird thing is that the query runs pretty smoothly when I use [load date]=#date#.

Great, thank you for the info on indexes, I am kind of new to this indexing stuff and had no idea what to do with it. I'll go through the db and change the fields that I can. Thank you again