VBA script runs slow the first time, then fast after that. Why? (1 Viewer)

cricketbird · Jul 26, 2017

I have a VBA script that (a) deletes the contents of 2 tables and (b) runs 4 VBA-encoded queries that insert records into the same tables (i.e. refreshes the contents of those two tables). The first time I run the script after opening the database, it takes about 2 minutes to run. If I run the same script again, though, it only takes a few seconds. What might be causing this difference, and is there anything I can do to speed it up the first time around?

Thanks!
CB

RuralGuy · Jul 26, 2017

How about posting the script so we can see it?

Orthodox Dave · Jul 26, 2017

I assume you're aware that emptying and refilling tables causes database bloat. Have you been compacting the database regularly?

Minty · Jul 26, 2017

As well as posting the code - how many records are you inserting and deleting?

cricketbird · Jul 26, 2017

Many thanks for helping take a look at this. I'm deleting/inserting about 2000 records (1500 in one table, 500 in another). Basically, this is generating prep instructions/recipes for specialized clinical diets based on a gazillion conditions for folks working in multiple locations. The general idea behind doing this off a table vs. off a query is that the query would always be "live" whereas the table needs to stay the same the rest of the day (while the database keeps changing as diet changes come in and out). However, sometimes we refresh the tables in the middle of the day if something big happens and we need to reassign tasks.

The users always start with a fresh "front end" each day (copied to their hard drive via a .bat file), and the tables created/removed are local, not part of the backend, so hopefully not too much bloat is happening, but I'm open to suggestions on improving this process.

Code:

DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE * FROM [TODAY-DIET];"
DoCmd.RunSQL "DELETE * FROM [TODAY-DIET SUB];"

aSQL = " INSERT INTO [TODAY-DIET SUB] ( DIETID, FOODID, GROUP_AMOUNT, UNITID, SORT, TOTE, FOOD, UNIT, LINENOTES, [TABLEID], MEAT, TARGETTABLE, DC_SORTORDER ) " & _
" SELECT DISTINCT [DIET TABLE].DIETID, [DIET SUB TABLE].FOODID, [DIET SUB TABLE].GROUP_AMOUNT, [DIET SUB TABLE].UNITID, [DIET SUB TABLE].SORT, [DIET SUB TABLE].TOTE, [FOOD TABLE].FOOD, [UNIT TABLE].UNIT, [DIET SUB TABLE].LINENOTES, [DIET TABLE].TABLEID, [FOOD TABLE].MEAT, IIf([MEAT]=-1,1,[TABLEID]) AS Target, [DELIVERY CONTAINER TABLE].SortOrder " & _
" FROM (([LOCATION TABLE] RIGHT JOIN ((([DIET TABLE] LEFT JOIN [DIET SUB TABLE] ON [DIET TABLE].DIETID = [DIET SUB TABLE].DIETID) LEFT JOIN [SPECIES TABLE] ON [DIET TABLE].SPECIESID = [SPECIES TABLE].SPECIESID) LEFT JOIN [DELIVERY CONTAINER TABLE] ON [DIET TABLE].DCID = [DELIVERY CONTAINER TABLE].DCID) ON [LOCATION TABLE].LOCATIONID = [DELIVERY CONTAINER TABLE].LocationID) LEFT JOIN [FOOD TABLE] ON [DIET SUB TABLE].FOODID = [FOOD TABLE].FOODID) LEFT JOIN [UNIT TABLE] ON [DIET SUB TABLE].UNITID = [UNIT TABLE].UNITID " & _
" WHERE ((([DIET SUB TABLE].LINENOTES) Not Like ""*ZK*"") AND (([DIET SUB TABLE]." & myDay & ")=True) AND (([DIET TABLE].CURRENT)=True) AND (([DIET TABLE].NCPREPARES)=True) AND (([DIET TABLE].GroupID)=1) AND ((Round((Round((" & myDate & "+3)/7,0) Mod ([DIET SUB TABLE]![FRQ_WEEKS]))+1,0)=[FRQ_ROTATION])=-1)) OR ((([DIET SUB TABLE].LINENOTES) Is Null) AND (([DIET SUB TABLE]." & myDay & ")=True) AND (([DIET TABLE].CURRENT)=True) AND (([DIET TABLE].NCPREPARES)=True) AND (([DIET TABLE].GroupID)=1) AND ((Round((Round((" & myDate & "+3)/7,0) Mod ([DIET SUB TABLE]![FRQ_WEEKS]))+1,0)=[FRQ_ROTATION])=-1));"


DoCmd.RunSQL aSQL
'Debug.Print aSQL
Forms!TouchScreenWelcome.Prog1.Visible = True 'shows a progress bar
DoEvents 'necessary to ensure the progress bar is visible



bSQL = " INSERT INTO [TODAY-DIET SUB] ( DIETID, FOOD, FOODID, UNIT, UNITID, SORT, TOTE, GROUP_AMOUNT, TARGETTABLE, DC_SORTORDER, LINENOTES ) " & _
" SELECT DISTINCT [SUBENCLOSURE TABLE].GroupID, [FOOD TABLE].FOOD, [DIET SUB TABLE].FOODID, [UNIT TABLE].UNIT, [DIET SUB TABLE].UNITID, Min([DIET SUB TABLE].SORT) AS MinOfSORT, Min([DIET SUB TABLE].TOTE) AS MinOfTOTE, Sum([DIET SUB TABLE].GROUP_AMOUNT) AS SumOfGROUP_AMOUNT, IIf([MEAT]=-1,1,[TABLEID]) AS Target, [DELIVERY CONTAINER TABLE].SortOrder, [DIET SUB TABLE].LINENOTES " & _
" FROM ((((([DIET TABLE] LEFT JOIN [DIET SUB TABLE] ON [DIET TABLE].DIETID = [DIET SUB TABLE].DIETID) LEFT JOIN [SUBENCLOSURE TABLE] ON [DIET TABLE].GroupID = [SUBENCLOSURE TABLE].SE_ID) LEFT JOIN [LOCATION TABLE] ON [SUBENCLOSURE TABLE].LocationID = [LOCATION TABLE].LOCATIONID) LEFT JOIN [FOOD TABLE] ON [DIET SUB TABLE].FOODID = [FOOD TABLE].FOODID) LEFT JOIN [UNIT TABLE] ON [DIET SUB TABLE].UNITID = [UNIT TABLE].UNITID) INNER JOIN [DELIVERY CONTAINER TABLE] ON [DIET TABLE].DCID = [DELIVERY CONTAINER TABLE].DCID " & _
" WHERE ((([DIET SUB TABLE].LINENOTES) Not Like ""*ZK*"") AND (([DIET TABLE].GroupID)<>1) AND (([DIET TABLE].CURRENT)=True) AND (([DIET TABLE].NCPREPARES)=True) AND (([DIET SUB TABLE]." & myDay & ")=True) AND ((Round((Round((" & myDate & "+3)/7,0) Mod ([DIET SUB TABLE]![FRQ_WEEKS]))+1,0)=[FRQ_ROTATION])=-1)) OR ((([DIET SUB TABLE].LINENOTES) Is Null) AND (([DIET TABLE].GroupID)<>1) AND (([DIET TABLE].CURRENT)=True) AND (([DIET TABLE].NCPREPARES)=True) AND (([DIET SUB TABLE]." & myDay & ")=True) AND ((Round((Round((" & myDate & "+3)/7,0) Mod ([DIET SUB TABLE]![FRQ_WEEKS]))+1,0)=[FRQ_ROTATION])=-1)) " & _
" GROUP BY [SUBENCLOSURE TABLE].GroupID, [FOOD TABLE].FOOD, [DIET SUB TABLE].FOODID, [UNIT TABLE].UNIT, [DIET SUB TABLE].UNITID, IIf([MEAT]=-1,1,[TABLEID]), [DELIVERY CONTAINER TABLE].SortOrder, [DIET SUB TABLE].LINENOTES, [DIET TABLE].GroupID, [DIET TABLE].CURRENT, [DIET TABLE].LABEL, [DIET TABLE].NCPREPARES, [DIET SUB TABLE]." & myDay & ";"


DoCmd.RunSQL bSQL
Forms!TouchScreenWelcome.Prog2.Visible = True
DoEvents


cSQL = " INSERT INTO [TODAY-DIET] ( DataDate, DIETID, SPECIES, DCID, [TABLEID], NOTEID, LABEL, DC, LOCATION, SortOrder, GroupID, IMAGELINK ) " & _
" SELECT Nz([Forms]![TouchScreenWelcome].[LabelDate]) AS DataDate, [TODAY-DIET SUB].DIETID, [SPECIES TABLE].SPECIES, [DIET TABLE].DCID, [TODAY-DIET SUB].TARGETTABLE, [DIET TABLE].NOTEID, [DIET TABLE].LABEL, [DELIVERY CONTAINER TABLE].DC, [LOCATION TABLE].LOCATION, [TODAY-DIET SUB].DC_SORTORDER, [DIET TABLE].GroupID, [SPECIES TABLE].IMAGELINK " & _
" FROM ((([TODAY-DIET SUB] INNER JOIN [DIET TABLE] ON [TODAY-DIET SUB].DIETID = [DIET TABLE].DIETID) INNER JOIN [SPECIES TABLE] ON [DIET TABLE].SPECIESID = [SPECIES TABLE].SPECIESID) INNER JOIN [DELIVERY CONTAINER TABLE] ON [DIET TABLE].DCID = [DELIVERY CONTAINER TABLE].DCID) INNER JOIN [LOCATION TABLE] ON [DELIVERY CONTAINER TABLE].LocationID = [LOCATION TABLE].LOCATIONID " & _
" GROUP BY [TODAY-DIET SUB].DIETID, [SPECIES TABLE].SPECIES, [DIET TABLE].DCID, [TODAY-DIET SUB].TARGETTABLE, [DIET TABLE].NOTEID, [DIET TABLE].LABEL, [DELIVERY CONTAINER TABLE].DC, [LOCATION TABLE].LOCATION, [TODAY-DIET SUB].DC_SORTORDER, [DIET TABLE].GroupID, [SPECIES TABLE].IMAGELINK;"

DoCmd.RunSQL cSQL
Forms!TouchScreenWelcome.Prog3.Visible = True
DoEvents


dSQL = " INSERT INTO [TODAY-DIET] ( DIETID, DC, SPECIES, NOTEID, LOCATION, [TABLEID], SortOrder, DataDate, GroupID, IMAGELINK, LABEL ) " & _
" SELECT [TODAY-DIET SUB].DIETID, [SUBENCLOSURE TABLE].Subenclosure AS DC, ""GROUP DIET"" AS SPECIES, ""Group Diet"" AS NOTEID, [LOCATION TABLE].LOCATION, [TODAY-DIET SUB].TARGETTABLE, [TODAY-DIET SUB].DC_SORTORDER, Nz([Forms]![TouchScreenWelcome].[LabelDate]) AS DataDate, [SUBENCLOSURE TABLE].GroupID, ""group.jpg"" AS File, True AS LABELS " & _
" FROM [TODAY-DIET SUB] INNER JOIN ([LOCATION TABLE] INNER JOIN [SUBENCLOSURE TABLE] ON [LOCATION TABLE].LOCATIONID = [SUBENCLOSURE TABLE].LocationID) ON [TODAY-DIET SUB].DIETID = [SUBENCLOSURE TABLE].GroupID " & _
" GROUP BY [TODAY-DIET SUB].DIETID, [SUBENCLOSURE TABLE].Subenclosure, ""GROUP DIET"", ""Group Diet"", [LOCATION TABLE].LOCATION, [TODAY-DIET SUB].TARGETTABLE, [TODAY-DIET SUB].DC_SORTORDER, [SUBENCLOSURE TABLE].GroupID, ""group.jpg"", True;"

DoCmd.RunSQL dSQL
Forms!TouchScreenWelcome.Prog4.Visible = True
DoEvents


DoCmd.SetWarnings True

RuralGuy · Jul 26, 2017

Is this a Split Database (FrontEnd/BackEnd), or is it all in just the FE? If is is split, do you maintain a Persistent Connection to the BE? http://www.granite.ab.ca/access/performanceldblocking.htm

The_Doc_Man · Jul 26, 2017

Interesting. When your users copy the fresh front end, are those FE tables empty or populated? If they start empty and the tables in question potentially have long records (as well as having hundreds of records), the FE might need to expand dynamically in terms of how much system memory it consumes.

To do that, it must change the swap space allocation in your system swap area. If your free swap space is not very high then you might have issues. I don't know about Win10 and am not sure about Win7, but earlier Windows workstations had to outswap your program completely, rebuild the memory management map in RAM, and then inswap you every time you increased your virtual program space. There is an algorithm on how much space to give you on multiple in/out swaps when your program expands frequently, so there will be a point when your memory is big enough and your performance gets much better. IF this is what is going on, you might be able to prove it by looking at the Resource Monitor to see if your program suddenly starts growing in terms of virtual memory use - or if the free swap space takes a nose-dive.

The other suggestions like "persistent DB connection" also make sense and cannot be discounted. I don't know that I am right about your case being sudden initial growth demands because I just can't see your situation.

isladogs · Jul 26, 2017

Suggest you add code to show the current time at the start and after each step.
Use Debug.Print Now() so the values are shown in the Immediate window.
Better still use Debug.Print CDbl(Now())

Run the script with the 'fresh' copy and again later in the day.
Compare the results and identify whether each stage is slow in the 'fresh' copy or the delay is caused by specific bits of code

That may allow you to identify what code to deal with to improve performance

By doing something similar, I managed to make improvements which reduced the time for a complex routine from 35 minutes to around 7 seconds.

Orthodox Dave · Jul 27, 2017

The users always start with a fresh "front end" each day (copied to their hard drive via a .bat file), and the tables created/removed are local, not part of the backend, so hopefully not too much bloat is happening, but I'm open to suggestions on improving this process.

Are you compacting the front end daily before sending it out? Although the tables are local and in the front end, they are still liable to considerable bloating, especially if you are emptying and refilling a table with 2000 records daily. Moreover, bloating in the front end is arguably worse than in the back end because all the data manipulation happens in the front end.

Bloating is a one-way thing. When you add records, the database gets larger. When you delete them, it doesn't get smaller - you have to compact it to do that.

If you are not already doing this, you should see a marked improvement in performance if you do.

Minty · Jul 27, 2017

Actually reading the responses to this - it may well be that compacting the master copy before sending it out is NOT the best way in this instance, particularly if Doc's observations are correct.

The delay may well be the expansion of the small file. And this tables concerned are all local so the data is being stored locally.

static · Jul 27, 2017

The code could be simplified.
Warnings are turned off. Are you sure the queries run ok?

TODAY-DIET inserts...
SELECTed column names don't match the INSERTs.
The FROMs are exactly the same but little difference in the data = unnecessary duplication.

Code:

'show a progress bars
with Forms!TouchScreenWelcome
	!Prog1.Visible = True 
	!Prog2.Visible = True
	!Prog3.Visible = True
	!Prog4.Visible = True
end with
DoEvents 'necessary to ensure the progress bar is visible

with currentdb
	'STOP '<- uncomment this. code will stop here. Hit f8 to step through. Delete this line when done
	.execute "DELETE * FROM [TODAY-DIET]", dbfailonerror
	.execute "DELETE * FROM [TODAY-DIET SUB]", dbfailonerror
	.execute "...", dbfailonerror
	...	
end with

INSERT INTO [TODAY-DIET] (
DataDate,
DIETID,
SPECIES,
no DCID ?
DC,
[TABLEID],
NOTEID,
LOCATION,
SortOrder,
GroupID,
IMAGELINK )
LABEL
)
SELECT
Nz([Forms]![TouchScreenWelcome].[LabelDate]) AS DataDate,
[TODAY-DIET SUB].DIETID,
""GROUP DIET"" AS SPECIES,
(DCID)
[SUBENCLOSURE TABLE].Subenclosure AS DC,
[TODAY-DIET SUB].TARGETTABLE, <-- as TABLEID
""Group Diet"" AS NOTEID,
[LOCATION TABLE].LOCATION,
[TODAY-DIET SUB].DC_SORTORDER, <-- as SortOrder
[SUBENCLOSURE TABLE].GroupID,
""group.jpg"" AS File, <-- as IMAGELINK
True AS LABELS <-- as LABEL ??

cricketbird · Jan 1, 2019

Belated reply. NOT compacting before I send out did the trick. I was always compacting to "tidy it up" before distributing, but that is actually what slowed it down. Now I compact, run the update queries, and distribute and it works much better.

Also, thank you to Static for the hints on cleaning up the queries. I didn't know about the "as sortorder" thing - that's very helpful!

VBA script runs slow the first time, then fast after that. Why? (1 Viewer)

cricketbird

Registered User.

RuralGuy

AWF VIP

Orthodox Dave

Home Developer

Minty

AWF VIP

cricketbird

Registered User.

RuralGuy

AWF VIP

The_Doc_Man

Immoderate Moderator

isladogs

MVP / VIP

Orthodox Dave

Home Developer

Minty

AWF VIP

static

Registered User.

cricketbird

Registered User.

Similar threads

Users who are viewing this thread