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

HelpMe:)

Registered User.
Local time
Today, 18:50
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

MVP / VIP
Local time
Today, 18:50
Joined
Jan 14, 2017
Messages
18,186
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.
Local time
Today, 18:50
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
Staff member
Local time
Today, 11:50
Joined
Oct 29, 2018
Messages
21,358
Hi. Just curious, how exactly are you searching for records now?
 

HelpMe:)

Registered User.
Local time
Today, 18:50
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.
 

HelpMe:)

Registered User.
Local time
Today, 18:50
Joined
Dec 4, 2019
Messages
10
Here is a link that describes some indexing techniques. Fortunately indexing is not something I have to do (we have other staff to do that).




thanks I'll check it out
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:50
Joined
Oct 29, 2018
Messages
21,358
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.
Local time
Today, 18:50
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

  • cmdFIND.PNG
    cmdFIND.PNG
    8.9 KB · Views: 151

theDBguy

I’m here to help
Staff member
Local time
Today, 11:50
Joined
Oct 29, 2018
Messages
21,358
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

MVP / VIP
Local time
Today, 18:50
Joined
Jan 14, 2017
Messages
18,186
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
Local time
Tomorrow, 05:50
Joined
Jan 20, 2009
Messages
12,849
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

MVP / VIP
Local time
Today, 18:50
Joined
Jan 14, 2017
Messages
18,186
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
Local time
Today, 19:50
Joined
Oct 27, 2015
Messages
998
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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:50
Joined
Oct 29, 2018
Messages
21,358
Isn't acCmdFind just sequentially searching the records ignoring any indexes?
Hi Phil. I have no idea. Maybe you're right. I don't have any reference to see what it does.
 

isladogs

MVP / VIP
Local time
Today, 18:50
Joined
Jan 14, 2017
Messages
18,186
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
Local time
Tomorrow, 05:50
Joined
Jan 20, 2009
Messages
12,849
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

MVP / VIP
Local time
Today, 18:50
Joined
Jan 14, 2017
Messages
18,186
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
Local time
Tomorrow, 05:50
Joined
Jan 20, 2009
Messages
12,849
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...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

MVP / VIP
Local time
Today, 18:50
Joined
Jan 14, 2017
Messages
18,186
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

Top Bottom