Primary keys vs indexes

[ssanfu]

Should I spin mine off for a separate question? I don't want a duplicate thread, but I also don't want to hijack a thread either...

Well, yes, you should have started your own thread, for a few reasons:
1) You have hijacked this thread.
2) A lot fewer people will see your question because your question is not under your name.
3) In the future, it will be harder to find your question (again), because it is not under your name.
4) What happens if the OP marked/marks the thread solved? No one will see/find your question.



Having said that, yes, it is a big, hairy thing.

How fast is the query if you run it without the WHERE clause and without the ORDER BY clause? It is faster?
I know that using "LIKE" in the criteria is slower because of the checking it has to do to see if the field meets the criteria.

Why are you using

(([2016 & 2017 Master File].[Submit Yes or No]) Like "Yes")

in the criteria ? The field [Submit Yes or No] is a TEXT field with "YES" or "NO" as values?
Maybe try the equals sign instead of "LIKE"

(([2016 & 2017 Master File].[Submit Yes or No]) = "Yes")

What field type is [2016 & 2017 Master File].Complete?? Is it a Y/N (boolean) type? Maybe try

(([2016 & 2017 Master File].Complete)<>TRUE))

.........The query is displayed to the user in a split form as some prefer to use a data sheet view and some like the form that shows one record at a time. The Query in question is on a front end of the split database and is in .accdr format since my users only have the runtime and not the full version of Access 2010 (I don't want them poking around and changing things anyway). Their application has a front form that they can use to specify a vendor to narrow things down. I know the dang thing is big, hairy, and inelegant, but this is what I have.......

So everyone has a copy of the FE on their local computer? Or in their own account on the Network?



It looks like the field ID Number (primary key) is a text field since it has a slash and a dash in the value. I would use an Autonumber field as the PK field.

AutoNumber
----------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.

Also see: Microsoft Access Tables: Primary Key Tips and Techniques



Then, I would also spend the time to fix the object names - table and fields. The Access Gnomes can get very persnickety when object names begin with a number.

Naming Conventions
One source about how to name things


Object names should be only letters and numbers.
Do not begin an object name with a number.
NO spaces, punctuation or special characters (exception is the underscore) in object names


The first thing I would do is fix the names.
For example, instead of "[SAP Claim #]", better would be "[SAP_ClaimNum]"
Instead of "[Spend Type (007)],", better would be "[SpendType007],"

[Accepted/Denied] -->> [AcceptedDenied]
[Balance net-paid] -->> [BalanceNetPaid]

"Desc" is a reserved word in Access and shouldn't be used for object names. maybe use "Descript" .



I don't know what the data looks like, but why have a table that covers only 2 specific years? Each time you have to add a year or two, you have to add a new table, recreate the queries , recreate the forms, recreate the reports, recreate any VBA code.... a lot of work to add new years......


Just because the SAP data has the field names with the spaces/special characters/punctuation doesn't mean you have to keep those names.

[sgtdetritus]

Thanks for the help so far. I am in process of testing some things and came across something interesting. It takes, on average, to run the query I shared about 6 minutes 45 seconds, but that is only for the first query after opening the database. After the first query has been run, it only takes about 15 seconds to run the same query on a different Vendor. Further, other queries perform about the same (10 to 15 seconds, with one at about 3 seconds that retrieves a lot less from the table).

I then tested by removing the wildcards from my query. the first run, right after opening came in at 5:58, about a 30 second improvement, but at the cost of user requested functionality from when I first built this thing a few years ago.

So why did the second run queries come in so much faster?

Anyway, tomorrow I get to learn how to index the vendor column on this beast and test for performance improvements.

Sadly, things like getting the IT department to make improvements to the infrastructure just aren't going to happen any time soon. I'm so far down the food chain that any appeal I may make to the Great Old Ones is not going to come to anything. I have been trying to get the ability to use citrix to gain access to an onsite computer since the beginning of the covid crisis with no joy.

[CJ_London]

user requested functionality

I never offer it as an automatic option - I use

like something*

and tell users to input the initial * if they don't know what the vendor name begins with.

Also see this link about why indexing matters https://www.access-programmers.co.uk...rmance.291268/

[sgtdetritus]

So here is an Update for my particular problem. In the DB I am testing it on, I have added an Index to the Vendor field and eliminated the wildcards. In order to keep the user functionality I added a separate set of queries and forms that are designed to be run wide open with a big honking caveat that using them will be very slow. I know, it's just managing expectations, but...

Anyway, the query that used to take 6 and a half minutes is now only taking about 2 minutes. The wide open queries are still taking 5 plus minutes, but hey, the users were warned.

This is a pretty dramatic improvement. I'll be doing more testing with some of the Users tomorrow to see if the performance increases are consistent in different environments.

[CJ_London]

2 minutes is still a long time if there are only 40k records - should be pretty much instantaneous if properly constructed and indexed.

[sgtdetritus]

Ajax,
Fair point. Even without the improvements it was pretty instantaneous when we were all on-site.
I'm still pretty much a novice with Access and my most recent class on building relational databases was back in 1997, so I still have a lot to re-learn in order to make serious improvements. I'll take the incremental win

Thanks a lot for the hints, articles, and everything else, they were an enormous help.