Options for speeding up populating form based on union query

[bd528]

Hi all,

I have a dashboard with 15 textboxes. In short this layout can't change.

I was populating the boxes with one query each when the form opened, and they populated fairly quickly. In order to reduce the number of queries, I merged them all into one union. I populated the textboxes by setting their default values to certain elements of the union query. The database is tidier, but it took around 20 seconds for all the boxes to be populated.

Now when the form opens, I'm populating the textboxes from the same union, but using vba. The dashboard now populates in around 15 seconds, which still seems too long.

Are there any other options available to me that may speed the process up?

Thanks in advance

 

[jdraw]

You have told readers HOW you have done something and that isn't working as well as you intended. We need to know WHAT you are trying to do in simple English - no jargon. There may be many
ways to accomplish that, but we really need more info in order to offer focused advice/suggestions/help.

Good luck.

 

[bd528]

You have told readers HOW you have done something and that isn't working as well as you intended. We need to know WHAT you are trying to do in simple English - no jargon. There may be many
ways to accomplish that, but we really need more info in order to offer focused advice/suggestions/help.

Good luck.

Each of the textboxes contains a numerical value. Each of the queries within the union is a count, which counts records in a table of around 6000 records if certain criteria are true - usually based around dates.

So, I have 4 rows for criteria A, B, C, D. And columns for today, this week, this month, this year, and last year.

 

[Galaxiom]

Ensure any fields used for criteria are indexed.

 

[jdraw]

Numerical values, counts, criteria etc.... of what. What is the business involved?

Please describe to readers in simple English- no jargon - what this is about?
You can safely assume we know nothing of you, your environment nor your application.
So in simple terms --as you would tell an 8 year old -- what is this proposed database about?

 

[bd528]

Numerical values, counts, criteria etc.... of what. What is the business involved?

Please describe to readers in simple English- no jargon - what this is about?
You can safely assume we know nothing of you, your environment nor your application.
So in simple terms --as you would tell an 8 year old -- what is this proposed database about?

You'll have to excuse my trepidation on going into too much detail. In the past ( not on this forum) I have spent a lot of time asking a question only to receive one reply telling me to read a book on VBA, etc, etc

Anyway, my environment is Sales essentially, and the dashboard is used to display the number of inquires and sales made per period. The database is a CRM system I've had to build from scratch with little knowledge of Access. Everything else so far works as expected. I'd just like this dashboard to open more quickly.

The union only queries one table....

 

[CJ_London]

suggest show your union query code.

And also confirm that all fields that are filtered and/or sorted are indexed. Lack of indexing often leads to poor performance.

 

[jdraw]

Perhaps you could show us
-the table structure
-the query sql
-the dashboard (jpg)

 

[bd528]

Here is my query

SELECT Count([t1.DateTPIAgreementSigned]) AS DataCount, "WhiteboardTPIToday" AS Code
FROM tblBrokeragesNew as t1
WHERE (((t1.DateTPIAgreementSigned)=Date()))
UNION 
SELECT Count([t1.DateTPIAgreementSigned]) AS DataCount, "WhiteboardTPIWeek" AS Code
FROM tblBrokeragesNew as t1
WHERE (((t1.DateTPIAgreementSigned)>=Date()-Weekday(Date())+2 And (t1.DateTPIAgreementSigned)<=Date()-Weekday(Date())+8))
UNION 
SELECT Count([t1.DateTPIAgreementSigned]) AS DataCount, "WhiteboardTPIMonth" AS Code
FROM tblBrokeragesNew as t1
WHERE (((t1.DateTPIAgreementSigned)>=DateSerial(Year(Date()),Month(Date()),1) And (t1.DateTPIAgreementSigned)<=DateSerial(Year(Date()),Month(Date())+1,0)));
UNION 
SELECT Count([t1.DateTPIAgreementSigned]) AS DataCount, "WhiteboardTPIYear" AS Code
FROM tblBrokeragesNew as t1
WHERE (((t1.DateTPIAgreementSigned) Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),12,31)));
UNION
SELECT Count([t1.DateTPIAgreementSigned]) AS DataCount, "WhiteboardTPILastYear" AS Code
FROM tblBrokeragesNew as t1
WHERE (((t1.DateTPIAgreementSigned) Between DateSerial((Year(Date())-1),1,1) And DateSerial((Year(Date())-1),12,31)));
UNION
SELECT Count([Date_sent_to_Registrations]) AS DataCount, "WhiteboardSuppDirectToday" AS Code
FROM tblQuotesNew as t1
WHERE ((((t1.Date_sent_to_Registrations)=Date())) AND ((t1.Sales_Route)="Sales Mailbox"));
UNION
SELECT Count([Date_sent_to_Registrations]) AS DataCount, "WhiteboardSuppDirectWeek" AS Code
FROM tblQuotesNew as t1
WHERE (((t1.Date_sent_to_Registrations)>=Date()-Weekday(Date())+2 And (t1.Date_sent_to_Registrations)<=Date()-Weekday(Date())+8) AND ((t1.Sales_Route)="Sales Mailbox"));
UNION
SELECT Count([Date_sent_to_Registrations]) AS DataCount, "WhiteboardSuppDirectMonth" AS Code
FROM tblQuotesNew as t1
WHERE (((t1.Date_sent_to_Registrations)>=DateSerial(Year(Date()),Month(Date()),1) And (t1.Date_sent_to_Registrations)<=DateSerial(Year(Date()),Month(Date())+1,0)) AND ((t1.Sales_Route)="Sales Mailbox"));
UNION
SELECT Count([Date_sent_to_Registrations]) AS DataCount, "WhiteboardSuppDirectYear" AS Code
FROM tblQuotesNew as t1
WHERE (((t1.Date_sent_to_Registrations) Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),12,31)) AND ((t1.Sales_Route)="Sales Mailbox"));
UNION 
SELECT Count([Date_sent_to_Registrations]) AS DataCount, "WhiteboardSuppDirectLastYear" AS Code
FROM tblQuotesNew as t1
WHERE (((t1.Date_sent_to_Registrations) Between DateSerial((Year(Date())-1),1,1) And DateSerial((Year(Date())-1),12,31)) AND ((t1.Sales_Route)="Sales Mailbox"));
UNION
SELECT Count([Date_sent_to_Registrations]) AS DataCount, "WhiteboardSuppTPIToday" AS Code
FROM tblQuotesNew as t1
WHERE ((((t1.Date_sent_to_Registrations)=Date())) AND ((t1.Sales_Route)="TPI"));
UNION
SELECT Count([Date_sent_to_Registrations]) AS DataCount, "WhiteboardSuppTPIWeek" AS Code
FROM tblQuotesNew as t1
WHERE (((t1.Date_sent_to_Registrations)>=Date()-Weekday(Date())+2 And (t1.Date_sent_to_Registrations)<=Date()-Weekday(Date())+8) AND ((t1.Sales_Route)="TPI"));
UNION
SELECT Count([Date_sent_to_Registrations]) AS DataCount, "WhiteboardSuppTPIMonth" AS Code
FROM tblQuotesNew as t1
WHERE (((t1.Date_sent_to_Registrations)>=DateSerial(Year(Date()),Month(Date()),1) And (t1.Date_sent_to_Registrations)<=DateSerial(Year(Date()),Month(Date())+1,0)) AND ((t1.Sales_Route)="TPI"));
UNION
SELECT Count([Date_sent_to_Registrations]) AS DataCount, "WhiteboardSuppTPIYear" AS Code
FROM tblQuotesNew as t1
WHERE (((t1.Date_sent_to_Registrations) Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),12,31)) AND ((t1.Sales_Route)="TPI"));
UNION 
SELECT Count([Date_sent_to_Registrations]) AS DataCount, "WhiteboardSuppTPILastYear" AS Code
FROM tblQuotesNew as t1
WHERE (((t1.Date_sent_to_Registrations) Between DateSerial((Year(Date())-1),1,1) And DateSerial((Year(Date())-1),12,31)) AND ((t1.Sales_Route)="TPI"));

As for indexing, etc - the data is stored as SharePoint lists, so I have to be careful with changing the table design, but I will look into ths.

 

[MarkK]

It is also common that people have table design and normalization problems that they try to overcome using UNION queries. In that case you can gain yuuuuge speed improvements by fixing the tables. #yuuuge

 

[CJ_London]

the first observation is to use count(*) rather than count(t1.Date...)

Not tested but I believe using BETWEEN rather than >= and <= is faster.

Also look at this

WHERE (((t1.DateTPIAgreementSigned) Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),12,31)));

might be quicker as

WHERE (year(t1.DateTPIAgreementSigned)=Year(Date());

similarly

WHERE (((t1.DateTPIAgreementSigned) Between DateSerial((Year(Date())-1),1,1) And DateSerial((Year(Date())-1),12,31)));

would become

WHERE (year(t1.DateTPIAgreementSigned)=Year(Date()-1);

you'll need to check the brackets!

a quick look at your criteria indicates that some can be combined so you don't apply the same criteria multiple times

looks like you have 3 groups - Sales mailbox, TPI and 'all/agreement'

and 5 date criteria of each group - of which two groups are based on date sent to registrations and 1 (all) based on dateTPIagreement signed.

So you could have a query along the lines of

SELECT abs(sum(sales_route="Sales Mailbox")) as whiteboardsuppdirect..., abs(sum(sales_route="TPI")) as whiteboardsuppTPI...
FROM tblBrokeragesNew as t1
WHERE ...date criteria

which reduces 10 queries down to 5

clearly your form would need a bit of a redesign, but not that difficult to do

the first five I think would have to remain since the criteria is working off a different date field field

 

[Galaxiom]

WHERE (((t1.DateTPIAgreementSigned) Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),12,31)));

might be quicker as

WHERE (year(t1.DateTPIAgreementSigned)=Year(Date());

Quite the opposite. Using Year() requires every record have the function applied before it can be selected. Using the date range only requires the calculation to be done once and the selection based on the field index.

 

[CJ_London]

Quite the opposite

I did say might so happy to be corrected

there is a question over whether the field is indexed - OP is checking

 

[bd528]

the first observation is...

Firstly, thank you for your suggestions.

I've modified my union query into 2 separate unions :-

 SELECT Count([t1.DateTPIAgreementSigned]) AS DataCount, "WhiteboardTPIToday" AS Code
FROM tblBrokeragesNew as t1
WHERE (((t1.DateTPIAgreementSigned)=Date()))
UNION 
SELECT Count([t1.DateTPIAgreementSigned]) AS DataCount, "WhiteboardTPIWeek" AS Code
FROM tblBrokeragesNew as t1
WHERE t1.DateTPIAgreementSigned between Date()-Weekday(Date())+2 And Date()-Weekday(Date())+8
UNION 
SELECT Count([t1.DateTPIAgreementSigned]) AS DataCount, "WhiteboardTPIMonth" AS Code
FROM tblBrokeragesNew as t1
WHERE t1.DateTPIAgreementSigned between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)
UNION 
SELECT Count([t1.DateTPIAgreementSigned]) AS DataCount, "WhiteboardTPIYear" AS Code
FROM tblBrokeragesNew as t1
WHERE t1.DateTPIAgreementSigned Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),12,31)
UNION SELECT Count([t1.DateTPIAgreementSigned]) AS DataCount, "WhiteboardTPILastYear" AS Code
FROM tblBrokeragesNew as t1
WHERE t1.DateTPIAgreementSigned Between DateSerial((Year(Date())-1),1,1) And DateSerial((Year(Date())-1),12,31);

and

SELECT nz(Abs(Sum(sales_route="Sales Mailbox")),0) AS AcceptDirect,  nz(Abs(Sum(sales_route="3rdParty")),0) AS Accept3rdParty, nz(Abs(Sum(sales_route="TPI")),0) AS AcceptTPI, nz(Abs(Sum(sales_route="Sales Mailbox"))+nz(Abs(Sum(sales_route="3rdParty")),0)+Abs(Sum(sales_route="TPI")),0) AS AcceptTotal, "Today" AS xPeriod
FROM tblQuotesNew AS t1
WHERE (((t1.Date_sent_to_Registrations)=Date()));
union
SELECT nz(Abs(Sum(sales_route="Sales Mailbox")),0) AS AcceptDirect,  nz(Abs(Sum(sales_route="3rdParty")),0) AS Accept3rdParty, nz(Abs(Sum(sales_route="TPI")),0) AS AcceptTPI, nz(Abs(Sum(sales_route="Sales Mailbox"))+nz(Abs(Sum(sales_route="3rdParty")),0)+Abs(Sum(sales_route="TPI")),0) AS AcceptTotal, "Week" AS xPeriod
FROM tblQuotesNew AS t1
WHERE (((t1.Date_sent_to_Registrations) between Date()-Weekday(Date())+2 And Date()-Weekday(Date())+8));
union
SELECT nz(Abs(Sum(sales_route="Sales Mailbox")),0) AS AcceptDirect,  nz(Abs(Sum(sales_route="3rdParty")),0) AS Accept3rdParty, nz(Abs(Sum(sales_route="TPI")),0) AS AcceptTPI, nz(Abs(Sum(sales_route="Sales Mailbox"))+nz(Abs(Sum(sales_route="3rdParty")),0)+Abs(Sum(sales_route="TPI")),0) AS AcceptTotal, "Month" AS xPeriod
FROM tblQuotesNew AS t1
WHERE t1.Date_sent_to_Registrations between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0);
union
SELECT nz(Abs(Sum(sales_route="Sales Mailbox")),0) AS AcceptDirect,  nz(Abs(Sum(sales_route="3rdParty")),0) AS Accept3rdParty, nz(Abs(Sum(sales_route="TPI")),0) AS AcceptTPI, nz(Abs(Sum(sales_route="Sales Mailbox"))+nz(Abs(Sum(sales_route="3rdParty")),0)+Abs(Sum(sales_route="TPI")),0) AS AcceptTotal, "Year" AS xPeriod
FROM tblQuotesNew AS t1
WHERE t1.Date_sent_to_Registrations between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0);
UNION 
SELECT nz(Abs(Sum(sales_route="Sales Mailbox")),0) AS AcceptDirect,  nz(Abs(Sum(sales_route="3rdParty")),0) AS Accept3rdParty, nz(Abs(Sum(sales_route="TPI")),0) AS AcceptTPI, nz(Abs(Sum(sales_route="Sales Mailbox"))+nz(Abs(Sum(sales_route="3rdParty")),0)+Abs(Sum(sales_route="TPI")),0) AS AcceptTotal, "LastYear" AS xPeriod
FROM tblQuotesNew AS t1
WHERE t1.Date_sent_to_Registrations Between DateSerial((Year(Date())-1),1,1) And DateSerial((Year(Date())-1),12,31) AND ((t1.Sales_Route)="Sales Mailbox");

The dashboard now populates very slightly faster.

One other thing, I populate the textboxes with (for example) -

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

Can this method be improved upon?

 

[CJ_London]

I would still change this

Count([t1.DateTPIAgreementSigned])

to

Count(*)

domain functions are significantly slower than sql queries

The main thing that will affect performance is indexing. take a look at this link as to why

https://www.access-programmers.co.uk/forums/showthread.php?t=291268

so I have to be careful with changing the table design, but I will look into ths.

do look into it and if not indexed, index the relevant fields

 

[Minty]

One other thing, I populate the textboxes with (for example) -

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

Can this method be improved upon?

I would create another query based on your union to provide all the textbox values in one hit, it will be a lot quicker than the separate DSum()'s you are using.

 

[bd528]

I would create another query based on your union to provide all the textbox values in one hit, it will be a lot quicker than the separate DSum()'s you are using.

Could you explain a little more? Wouldn't I just be DSumming again on the new query?

 

[Minty]

No - provided I haven't misread your data layouts, use a totals query to do the summation it will be much more efficient.

 

[bd528]

No - provided I haven't misread your data layouts, use a totals query to do the summation it will be much more efficient.

Sorry, what I mean is, how will I get the values into the textboxes without using separate DSums?

Or do you mean still use separate DSums, but on the totals query?

 

[Minty]

What other data is on the form? If it is currently all unbound txtboxes just ensure everything you want to display is in the final query and use that as the recordsource for the form.