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

HelpMe:)

Registered User
Joined
Dec 4, 2019
Messages
10
Hello :)

I have built a database with the back end being on SQL Server Express and the front end on Access. The issue i have before deploying is that when we search for a record it seems to take a minute or crashes. Is there a way to resolve this?
This is my first SQL database so no doubt i've made an error somewhere!
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,320
To get the best performance when using a SS BE, you should design all queries, forms etc to only pull the data required from the server.
If you download all data then filter it in Access, performance will be poor

You should also ensure that all search fields are indexed
 

HelpMe:)

Registered User
Joined
Dec 4, 2019
Messages
10
To get the best performance when using a SS BE, you should design all queries, forms etc to only pull the data required from the server.
If you download all data then filter it in Access, performance will be poor

You should also ensure that all search fields are indexed
Thank you isladogs, that is what i thought but i'm not sure how to set it up.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,619
Hi. Just curious, how exactly are you searching for records now?
 

HelpMe:)

Registered User
Joined
Dec 4, 2019
Messages
10
Hi. Just curious, how exactly are you searching for records now?
At the moment the form is opened from a navigation menu and the user can then click the search button and type in what they are looking for e.g an asset number or serial. There are buttons to add a new record and filter by a specific department as well.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,619
At the moment the form is opened from a navigation menu and the user can then click the search button and type in what they are looking for e.g an asset number or serial. There are buttons to add a new record and filter by a specific department as well.
Right, but what does the button do exactly? If you're using code, I'd like to see it please, just in case there's something there that can be tweaked. If you're using the built-in search feature, then there's nothing to fix there and indexing would probably be your best bet.
 

HelpMe:)

Registered User
Joined
Dec 4, 2019
Messages
10
Right, but what does the button do exactly? If you're using code, I'd like to see it please, just in case there's something there that can be tweaked. If you're using the built-in search feature, then there's nothing to fix there and indexing would probably be your best bet.
It is just a Macro button at the moment, attached screenshot.
 

Attachments

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,619
It is just a Macro button at the moment, attached screenshot.
Okay, thanks for the additional information. You are using the built-in search feature, so make sure you have an index for all the fields users would commonly search. Good luck!
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,320
Using macros will significantly restrict what you can achieve.
Recommend you use the ribbon button to convert the macro to visual basic.
(similarly with all other macros)

Now go into SS and make sure any fields used in your search are indexed.
You may need to relink your tables to make this effective in the Access FE.
Run your search again. Any faster?
If not, we'll need more info about your setup
 

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,814
Other things that wreck performance:

Joining local tables to the SQL Server tables.
Access user defined functions in the left side of the Where or in a Join.

On any database, applying any functions to the left side of the the Where will slow down the select but it is extra bad with the server.

Avoid where clauses like:
Code:
WHERE Year(datefield) = someyear AND Month(datefield) = somemonth
Instead use:
Code:
WHERE datefield BETWEEN DateSerial(someyear, somemonth, 1) AND DatteSerial(someyear, somemonth + 1, 0)
If you can understand why this matters you should be able to apply the principle more generally. If not, read about SARGABLE queries,
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,320
Following on from Galaxiom's excellent advice, you may find this article on Optimising Queries to be useful

The tests in the article were performed on local Access tables. If using SQL Server, the differences would be even more significant. The poorly designed queries would be even slower whereas the optimised queries should be even faster (hopefully).
 

sonic8

AWF VIP
Joined
Oct 27, 2015
Messages
321
You are using the built-in search feature, so make sure you have an index for all the fields users would commonly search.
Isn't acCmdFind just sequentially searching the records ignoring any indexes?



Now go into SS and make sure any fields used in your search are indexed.
You may need to relink your tables to make this effective in the Access FE.
Isn't that contradictory?
If you need to relink to make Access aware of the index, doesn't that imply that Access will handle the search and thus the index cannot be used?
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,320
Isn't that contradictory?
If you need to relink to make Access aware of the index, doesn't that imply that Access will handle the search and thus the index cannot be used?
Indexing fields in SQL Server will make the search faster whether the search criteria are managed in SS using a view or done in Access using a query.
I was trying to cover both options.

For info, I've just redone my speed tests from the Optimise Queries article using the original SQL server tables. As expected, the poorly designed queries were much slower than was the case using local Access tables. The optimised queries took a similar time.

I then repeated several of the tests using linked SQL views so the filtering was done at server level. I was surprised to note that there was no reduction in time compared to filtering in Access using a query

Also, there's an interesting article on SARGABLE queries in SS at
https://www.tech-recipes.com/rx/55535/sargable-queries-in-sql-server-with-examples/
 

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,814
For info, I've just redone my speed tests from the Optimise Queries article using the original SQL server tables. As expected, the poorly designed queries were much slower than was the case using local Access tables. The optimised queries took a similar time.

I then repeated several of the tests using linked SQL views so the filtering was done at server level. I was surprised to note that there was no reduction in time compared to filtering in Access using a query
Wherever it can Access queries are translated through ODBC and run on the server.

Sometimes the translated Access queries can be seen on the server. Big ones come up in the Recent Expensive Queries list.
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,320
Wherever it can Access queries are translated through ODBC and run on the server.

Sometimes the translated Access queries can be seen on the server. Big ones come up in the Recent Expensive Queries list.
Can you remind me how/when Access queries can be run on the server? And how would I know where that had happened??

Where can I find the Recent Expensive Queries list? I'm using SQL Server Express 2014 Management Studio
 
Last edited:

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,814
Can you remind me how/when Access queries can be run on the server? And how would I know where that had happened??
My understanding is that the ODBC interface not only translates tables but can also translate a query if it is not too complex and uses standard SQL functions.

Where can I find the Recent Expensive Queries list? I'm using SQL Server Express 2014 Management Studio
Recent Expensive Queries is in Activity Monitor. Not at work today and I don't have SQL Server installed at home but I think you can get to it from a right click on the server or a button in the Standard Toolbar. You need some very high level permissions on the server to have access to it.

Like pretty much everything in SQL Server, the information is also available via queries.

https://stackoverflow.com/questions/24896483/is-there-a-way-for-viewing-the-recent-expensive-queries-in-sql-server-2005/24896843

There is also monitoring that can be turned on for all queries but it adds a lot of load to the server and writes a lot of data. I think you enable it in the configuration manager which is a separate program.

With SQL Server it seems there is always something more to learn about it no matter how much you already know. Every now and then I poke around to check out features I don't recognise and see if I can use them and often do. We upgraded to 2016 recently and I see new features again.
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,320
Hi

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

I am aware that monitoring can be switched on for all queries and that, of course, the very act of monitoring increases the time for any query to run
Similarly in Access where using the JET ShowPlan increases query times by around 14%. The use of progress bars has a similar effect

With SQL Server, I know enough to get by but because high level competence has never been necessary for developing my applications for clients, I am only too aware that there is a lot to SS that I don't know about
 
Last edited:

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom