Solved Performance hit after back end encryption (1 Viewer)

[The_Doc_Man]

Interesting. After the fact of your experiment, I can offer a possible explanation. Bear with me.

When you have an encryption across the net, there is usually a "broker" task. You may prefer the term "translator" or "cryptologist" but the industry calls it a broker. This broker does the decryption of incoming data for you and there is a broker on the back end for updates that you do as well, since you are doing them to an encrypted file.

When you have a non-persistent connection, it is quite possible for Access to have to create and dissolve a broker for each query including even little things like drop-down lists of various flavors. That is because each query, in the absence of a persistent connection, must open a new socket. The socket lasts only as long as the query is open.

When you have a persistent connection, that connection does not close until you close it. Therefore the brokers remain in memory, idle most of the time - but Access does not create a new network session if one is already open to the desired location. So the brokers don't get created and destroyed for each new query. They just ride the persistent connection.

I am not guessing about this "ride the persistent connection" behavior because I saw it in an article on this forum years ago. I am making an educated guess as to the broker create/destroy cycle being a contributor to your speed issue.

 

[zeroaccess]

My thoughts are similar - it feels as if the Access front end has to negotiate the connection (and the encryption) with every form load, or even smaller events.

The hidden, bound form loads at the beginning, and while there is a slight bit of extra time taken for that, once you're up and running, you don't see those pauses anymore.

 

[gemma-the-husky]

Do you mean the data is stored in the back end as encrypted rather than plain text - therefore cannot be read directly when browsing the back end. Or is there a background service that automatically unencrypts the data for viewing.

In general, I struggle to understand conceptually how the indexes can be maintained logically with encrypted data, and therefore it makes perfect sense that there is a performance hit using encrypted data.

As an aside, does a "password" encrypt the data, or does it just limit access to the data?

 

[zeroaccess]

The back end is password protected using the built-in password feature.

 

[zeroaccess]

I ended up just hiding my login form after logging in as it can serve as the persistent connection form.

Performance is fantastic even with an encrypted back-end, with .accde front-end files.

 

[The_Doc_Man]

@zeroaccess - that is actually a good approach. I don't think it matters which form it is that goes hidden on you. The far more important part is that it gives you the means for that persistent connection. In my biggest project's case, it was a switchboard form that "hosted" the persistent connection, but which one you use really doesn't matter as long as it is reasonable for that form to stay that way.

 

[isladogs]

In general, I struggle to understand conceptually how the indexes can be maintained logically with encrypted data, and therefore it makes perfect sense that there is a performance hit using encrypted data.

As an aside, does a "password" encrypt the data, or does it just limit access to the data?

Password protecting files encrypts the entire file. Encryption is strong in ACCDB files but fairly weak in the old MDB format.
See Access file security - ACCDB vs MDB

Indexes in local and linked tables work perfectly in encrypted files. That wouldn't be the cause of any performance issue.

@zeroaccess
I would suggest using a very simple table for your persistent connection to reduce the load needed to a minimum
I often use a table tboKickout with one field and a single record and which is used to regularly check whether the system has been locked to allow for system maintenance.
In fact you can use a 'dummy' linked table with no data for this purpose.

 

[zeroaccess]

@zeroaccess
I would suggest using a very simple table for your persistent connection to reduce the load needed to a minimum
I often use a table tboKickout with one field and a single record and which is used to regularly check whether the system has been locked to allow for system maintenance.
In fact you can use a 'dummy' linked table with no data for this purpose.

At first, and per your suggestion, I did create a tblConnection with nothing in it to serve this purpose. But how big is the difference between that and tblUsers with ~20 rows? I can't imagine there being a big difference. Now I just open tblConnection when I'm in development mode as it reduces lag. I can't use tblLogin for this purpose, because that would trigger hiding the toolbars and other things.

I may yet implement a kickout (which would use the simpler table) to handle cases where maintenance or changes are needed on the back end and someone didn't close the program before leaving for the day.