Options for speeding up populating form based on union query

[MarkK]

I think you could improve performance considerably if you used a series of subqueries rather than a series of union queries. Consider the difference between this...

[COLOR="Green"]'subqueries deliver data to fields in a main query[/COLOR]
SELECT DISTINCT 
   ( SELECT Count(*) FROM tTestData WHERE ID < 16 ) AS Test1, 
   ( SELECT Count(*) FROM tTestData WHERE ID > 16 ) AS Test2
FROM tTestData;

...and this...

[COLOR="Green"]'queries are unioned together.  [/COLOR]
SELECT Count(*) FROM tTestData WHERE ID < 16
UNION
SELECT Count(*) FROM tTestData WHERE ID > 16

I tested queries almost exactly like those shown above--but with 10 subqueried fields and 10 unions--and the multiple UNION solution took just over twice as long to run.

 

[Galaxiom]

I populate the textboxes with (for example) -

Me.txtWhiteboardSuppDirectToday.Value = Nz(DSum("AcceptDirect", "qryDataCountSupps", "xperiod = 'Today'"), 0)

Can this method be improved upon?

Definitely. Each DSum() will independently rerun the same union query. Run the query once as the RecordSource for a form and populate the textboxes with a Bound ControlSource. This will probably increase the performance tenfold.

Personally I would change the way the data is structured. Move the date data to a related table with the dates all in the same field with another field for Code.

Use a single aggregate query grouped on the Code to return the counts for all codes at once. This will probably gain another tenfold increase in speed.

Whatever you choose to do the absolutely vital aspect is to ensure there are indexes on the date and Code fields in the table.

 

[CJ_London]

one other thing that might speed up the union query is to use UNION ALL

my rationale is that UNION will remove duplicates, which means you are undertaking a 'check' for duplicates. In your case there are none, so adding the ALL should remove this operation