Speeding up drop down list generating in an open query

[thechazm]

Hello all,

It's been a while and hope everyone is doing ok. I have a question and it's maybe more on a basic level but when I open a query that has results with more than 200K records when I click on the date field it takes a while before it gives me the selection of the dates to filter with.

This is just a basic query but it's going to seem large due to the normalization I am doing but ultimatly is there anyway to speed up the list generation when you click on the drop down in the open query?

I have already tried indexing the date field and it did nothing.

Thanks

SELECT Shops.Shops, 
[Shop Commits].Badge, 
[Project Name Ref].[Project Name] AS [CWeek Proj], 
TSDS.TSD AS CWeekTSD, 
TSDS.Title AS [C Title], 
TSDS.Nuclear AS CWeekNUC, 
[Request Types].[Request Type] AS CWeekPriority, 
[Project Name Ref_1].[Project Name] AS AProj, 
TSDS_1.TSD AS ATSD, 
TSDS_1.Title AS [A Title], 
TSDS_1.Nuclear AS ANUC, 
[Request Types_1].[Request Type] AS APriority, 
[Project Name Ref_2].[Project Name] AS A1WeekProj, 
TSDS_2.TSD AS A1WeekTSD, 
TSDS_2.Title AS [A1 Title], 
TSDS_2.Nuclear AS A1NUC, 
[Request Types_2].[Request Type] AS A1Priority, 
[Project Name Ref_3].[Project Name] AS A2Proj, 
TSDS_3.TSD AS A2TSD, 
TSDS_3.Title AS [A2 Title], 
TSDS_3.Nuclear AS A2NUC, 
[Request Types_3].[Request Type] AS A2Priority, 
[Project Name Ref_4].[Project Name] AS A3Proj, 
TSDS_4.TSD AS A3TSD, 
TSDS_4.Title AS [A3 Title], 
TSDS_4.Nuclear AS A3NUC, 
[Request Types_4].[Request Type] AS A3Priority, 
[Shop Commits].[C Date]
FROM [Request Types] RIGHT JOIN ([Request Types] AS [Request Types_4] 
RIGHT JOIN ([Request Types] AS [Request Types_3] 
RIGHT JOIN ([Request Types] AS [Request Types_2] 
RIGHT JOIN ([Request Types] AS [Request Types_1] 
RIGHT JOIN (TSDS AS TSDS_4 
RIGHT JOIN (TSDS AS TSDS_3 
RIGHT JOIN (TSDS AS TSDS_2 
RIGHT JOIN (TSDS AS TSDS_1 
RIGHT JOIN ([Project Name Ref] AS [Project Name Ref_4] 
RIGHT JOIN ([Project Name Ref] AS [Project Name Ref_3] 
RIGHT JOIN ([Project Name Ref] AS [Project Name Ref_2] 
RIGHT JOIN ([Project Name Ref] AS [Project Name Ref_1] 
RIGHT JOIN (Shops INNER JOIN ([Project Name Ref] 
RIGHT JOIN (TSDS RIGHT JOIN [Shop Commits] ON TSDS.ID = [Shop Commits].CTSD) 
ON [Project Name Ref].ID = [Shop Commits].CProject) 
ON Shops.ID = [Shop Commits].Shop) 
ON [Project Name Ref_1].ID = [Shop Commits].AProject) 
ON [Project Name Ref_2].ID = [Shop Commits].A1Project) 
ON [Project Name Ref_3].ID = [Shop Commits].A2Project) 
ON [Project Name Ref_4].ID = [Shop Commits].A3Project) 
ON TSDS_1.ID = [Shop Commits].ATSD) 
ON TSDS_2.ID = [Shop Commits].A1TSD) 
ON TSDS_3.ID = [Shop Commits].A2TSD) 
ON TSDS_4.ID = [Shop Commits].A3TSD) 
ON [Request Types_1].ID = [Shop Commits].[AColor Type]) 
ON [Request Types_2].ID = [Shop Commits].[A1Color Type]) 
ON [Request Types_3].ID = [Shop Commits].[A2Color Type]) 
ON [Request Types_4].ID = [Shop Commits].[A3Color Type]) 
ON [Request Types].ID = [Shop Commits].[CColor Type];

 

[CJ_London]

I don't recommend using queries in this way except whilst under development.

Your query is pretty detailed with lots of joins so when you use the dropdown in this way it scans through every row before presenting the list.

If you create a form, you can achieve the same thing by having a combobox with a rowsource of
"Select Distinct MyDateField From MyTable Order By MyDateField"

Providing MyDateField is indexed it should be pretty quick - more importantly it only needs to be done once when you open the form

 

[thechazm]

CJ I had thought about using a form but the employee's like using queries because they can take any of the data and copy and paste what they want into excel to do other comparisons or charts with thus the reason why I designed a slew of what I call Reference queries that open in a read only mode for the users to do whatever they need. So unless I code a form to produce the same type of functionality they probably won't go with it. I could however maybe get them to bite off on a subform being a datasheet with a combo box for each field to filter with but I have done that before in the past as well and it tends to overcomplicate things a bit. I'll have to give it some more thought.


rzw0wr - It's just a read only query that I have opening to answer one of the above questions and I was only refering to the SQL code being large not saying the large code is slowing it down just apologiesing ahead of time because it's a little hard to read to some. Nothing to do with performance of the query.

The users click the little upside down triangle at the top of the query window and when they do they have to wait for the result picker to popup and this is the slow down we have been experiancing that I am trying to find a good solution for.

Hope that clears things up a bit.

Thanks

 

[rzw0wr]

When I opened you post the code did not show.
That was why I was confused.
You may notice I deleted my post.

Dale

 

[thechazm]

No worries it's all good

 

[CJ_London]

they can take any of the data and copy and paste what they want into excel

If you have a main form with a subform in datasheet (the equivalent of the query view) or continuous view they can still copy and paste the same way.

Queries aren't forms so you do not have the same functionality available in a form.

Would be interested to know how you set a query to be read only? Ah - Snapshot? if so there is still nothing to stop the user going into design mode and changing it

 

[thechazm]

Yes that sounds right. Going to investigate.

As to try and answer your question here is the all the code in the simple query reference form where users click on which query they want to see and opens in read only.

Private Sub Form_Load()
Dim db As Database, qdf As QueryDef
 
Set db = CurrentDb
 
For Each qdf In db.QueryDefs
    If InStr(1, qdf.Name, "(Reference)") > 0 Then
        Me.lstMain.AddItem qdf.Name
    End If
Next qdf
Set db = Nothing
End Sub
 
Private Sub lstMain_Click()
 
DoCmd.OpenQuery Me.lstMain.Value, acViewNormal, acReadOnly
 
End Sub

And to try and further answer the other question on design mode or whatever I disable special keys, compile to accde, disable the bypass key, disable the Display Navigation Pane option and finally I disable the Allow Full Menu's option.

That's pretty much it in a nutshell Not hard.