X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Very Poor Performance -SQL Server Linked Tables, Access 2010    
post Nov 29 2017, 07:16 AM

Posts: 1,832
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa

Hello Community,

I have an existing ms Access DB split and on a file server(shared) and currently with over 10 concurrent users.

Performance very good.

For testing purposes, i have migrated the BE to SQL server.

But performance very very poor.

Just opening a data entry form takes over 20 seconds.

Opening a form with a sub-form with say 60 records, takes more than one minute.
Opening a form with more than 1000 records which has a subform makes the db hang....and may open after 5 minutes.

A combo box takes more than 30 seconds to load...

I am just testing ..but i am scared....this will not definitely work.

The SQL server is administered by IT department...

Using mgt studio, i have noticed that all tables the PK does not have a clustered index which make all queries do a table scan....

Any hints on how to improve performance ?

Go to the top of the page
post Nov 29 2017, 07:34 AM

UtterAccess VIP
Posts: 7,685
Joined: 24-May 10
From: Downeast Maine

I too am working on migrating to SQL Server. I am quite new to it, but here are a few things I have discovered.

I use pass through queries for combo boxes. If the combo box needs further filtering, such as with cascading combo boxes, I apply the criteria to the pass through query.

Native Access queries used for things such as a form's Record Source, where it is necessary to write and edit records, can bog down if the fields include calculations, particularly (in my observation) if the calculations include calls to VBA functions including user defined functions. In this case I do as much as possible of the joining, calculating, and so forth in a SQL Server view. The view can then be sorted and filtered via Access functions.

SQL Server functions can be used for many of the calculations, I believe, but I am still working on that. I haven't yet used SQL Server functions in my experimentation so far, other than a few simple tests. but I believe they will prove to be very useful.
Go to the top of the page
Jeff B.
post Nov 29 2017, 07:37 AM

UtterAccess VIP
Posts: 10,142
Joined: 30-April 10
From: Pacific NorthWet

What I found during migration to SQL-Server was that my Access apps tried to pull ALL the records across the network. After I modified the Access front-end to allow the user to select a single record of interest, the speed picked back up.
Go to the top of the page
post Nov 29 2017, 08:24 AM

UA Admin
Posts: 33,802
Joined: 20-June 02
From: Newcastle, WA

What I think of as "traditional "Access architecture seems to work fine most of the time, but usually falls down quivering when the tables are migrated to a Server, SQL Server, Oracle, MySQL, whatever. And if you move to a remotely hosted server-based database, e.g. SQL Azure, it's usually even more of a problem.

Follow the suggestions offered by Bruce and Jeff. Rethink how you pull data down that long wire from the server. Do you ask for all 80,000 records in a table for a form, and then apply a filter to display just one after you get them? Not good.

Or, do you apply a filter (criteria in a query) to pull just one record from that table down the long wire from the server and use that single record in your table? Very good.

It's all about letting SQL Server do the heavy lifting where possible with views, stored procedures and passthru queries and keeping only the necessary UI related tasks in Access. Some tasks, of course, do require processing on the client side (reports, calculations in reports, and so on), but anything that can be done server side should be done there, if possible, IMO.
Go to the top of the page
post Nov 29 2017, 09:02 AM

Posts: 1,832
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa

Thanks gentlemen for the tips!

Let me try out the suggestions.

Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2018 - 09:45 PM