Re: [MSAccessProfessionals] slow search records, Access 2010 front end, SQL Server 2019 back end

hi Wes,

> "performance issue in searching records"

for queries, this is often related to the way that tables are joined. Are you using text fields? If so, have you specified a more reasonable size than 255? Most efficient is AutoNumber-Long Integer since that takes just 4 bytes to store.

> "joins 3 tables sorted by a date"

if the join is the date, then realize that dates are stored as floating point numbers, which is not a precise data type, and, therefore, don't always match properly. However, if the problem is the sort, then perhaps adding an index in SQL Server might make it faster.

kind regards,
crystal

free code you can use in your projects
https://msaccessgurus.com/code.htm

free video tutorials:
http://www.YouTube.com/LearnAccessByCrystal

On 6/18/2020 2:21 PM, Wei Qian via groups.io wrote:

Hi, 

I'm new to the group.  I'm having an Access challenge I hope some experienced Access developers can give me some help.  

We have an Access application that has been running for many years.  It's a split database, both front and back end are Access files, with linked tables.  In recently years, with more data and more users (50+), the system has more frequent "locked up" or "crashes" and users are frustrated. I was given a task of converting the database to SQL Server, which I did it with SSMA, I then changed linked tables from Access to SQL Server.  It seems to be working fine, but I hit a performance issue in searching records. The data source of one of the front end forms is a query that joins 3 tables sorted by a date.  One table very small, the other two have 40-50k records. When I run the query directly, it seems to be returning records quickly.  But when searching a record (looks like using an build-in 'find and replace' function to me), it seems the Access front end requests record processing one by one (from SQL Server Profiler), so if I search a newly entered record (bottom of the sorted list), it could take 2 minutes to come back. I tried to change the query to pass-through query and make it run on SQL server, it's a bit faster but it becomes read-only so the user can't edit record. I tried to add a button to by pass the 'find and replace' function, call a stored proc in SQL Server to return only one record, but it seems I can't assign values to fields because they are bound to the query.  

Could someone give me some ideas in how to fix this issues?  I'm an experienced SQL Server db admin/developer but I don't have experience in front end app development, and we don't have developers in the team. Any input is greatly appreciated. Thanks very much!

~ Wei

--
~ Wei