SQL BE with MS Access FE - Speed up searches (1 Viewer)

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 09:30
Joined
Jan 20, 2009
Messages
12,849
Hi

Apologies for the delay in replying
I'm using SSMS 2014 Express and am unable to find the 'Recent Expensive Queries' option..

So no "Activity Monitor" when you right click on the server?
 

isladogs

MVP / VIP
Local time
Today, 22:30
Joined
Jan 14, 2017
Messages
18,186
Ah yes. Got it now. Sorry, Never noticed that before!
One query shown but it disappears after a few seconds!
Will investigate further
 

ahmed_optom

Registered User.
Local time
Today, 22:30
Joined
Oct 27, 2016
Messages
93
I wanted to join in on this thread and add my experiences.

I have a access Fe, sql 2019 be.

In some instances, especially searches , sql seems to be much slower that just a split database. Im sure this is just because I havent designed the forms/queries with sql in mind, however its important to note, out the box, this is just how it is.

People keep saying indexing is the answer for this, it doesnt help.

Here are the results of my experiments.

Table1, 1 column , which is the pk, indexed, has 3000 rows of data, eg 3000 records of one integer which is the PK.

This is a fairly "light" database, and yet still there is a major issue.

If i just open the table, it loads near on instantly, to quick to measure by the human eye.

If i open in a bound form, with just one text box, then search for a specific record, it can take around a minute to find the record. The pc specs are irrelevant, tried on multiple pcs different hardware, same, , and so is network setup, its the same on the local network as remotely etc

Obviously this is not an ideal situation.

For reference, with the same setup, a split access setup can find the record in about 1 second.

Does anyone have an advice ?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:30
Joined
Feb 19, 2013
Messages
16,553
All depends how you are searching. I’m pretty sure find does not use indexing because it will find ‘brown’ in ‘the quick brown fox’ - same as Like ‘*brown*’

with regards viewing a table, access will display the first page of records quickly, but the table is not fully loaded until the record count is populated at the bottom. For a 3k table that should not be long because no sorting or filtering is applied, see this link

access-programmers.co.uk/forums/threads/why-indexing-is-important-for-good-performance.291268/

you may also find this link useful
 

ahmed_optom

Registered User.
Local time
Today, 22:30
Joined
Oct 27, 2016
Messages
93
To correct myself and add more info, the issues are purely down to using some of Access "inbuilt" functions, like the "find".

So for example, I have written a simple query and search system, and its lightning fast, much faster than the access find with an access BE. This is where the indexing can help.

Also subforms/reports seems to really bog down on sql. Im still fixing it, but it appears using the child/parent system causes issues, and will have to do it manually using a criteria in the query.

So when people swap to a sql BE, you are going to have to stop using the "find", you are going to have to re work the subforms.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:30
Joined
Feb 19, 2013
Messages
16,553
as already stated, 'find' does not use indexing - whether it be an access or sql server backend.

with regards subforms/reports - they will if you use the basic access functionality which applies a filter. You need to think like you would for a web site - bring through the minimum amount of data as possible to reduce traffic. i.e. in the main form current event you need code to fetch the related records for the subform by using criteria, not a filter.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 09:30
Joined
Jan 20, 2009
Messages
12,849
I populate forms with data ADO Recordsets returned by command driven parameterized Stored Procedures. In the case of subforms I call the command on the OnCurrent event of the main form.
 

Users who are viewing this thread

Top Bottom