UtterAccess.com
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
> Veeeery Slow Form Performance, Access 2010    
 
   
SemiAuto40
post Feb 12 2019, 10:30 AM
Post#1



Posts: 683
Joined: 3-April 12
From: L.A. (lower Alabama)


I have form frm_MethodsPending which has the RecordSource property as a SQL statement:
CODE
SELECT * FROM tbl_Methods WHERE tbl_Methods.Approved = FALSE ORDER BY MethodNumber

This is performing terribly slow and links to tbl_Methods which is on the company network share many miles away. I've checked my network speed and it seems to be adequate so I don't think that is it.
The table is not very large at all. Is there a way to make this form reasonably usable by using some other strategy for the record source?

BTW, I don't have graphics on the form.

Thanks.
This post has been edited by SemiAuto40: Feb 12 2019, 10:31 AM
Go to the top of the page
 
GroverParkGeorge
post Feb 12 2019, 11:45 AM
Post#2


UA Admin
Posts: 34,825
Joined: 20-June 02
From: Newcastle, WA


Is "Approved" a boolean field?

Do you have indexes on any fields? E.g. "MethodNumber" in particular?

Does the table have a Primary Key?
This post has been edited by GroverParkGeorge: Feb 12 2019, 11:46 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
SemiAuto40
post Feb 12 2019, 12:40 PM
Post#3



Posts: 683
Joined: 3-April 12
From: L.A. (lower Alabama)


Yes the checkbox Approved is a boolean. Index is on the Primary Key. Not more than 100 records.

Thanks for your suggestions.
Go to the top of the page
 
cheekybuddha
post Feb 12 2019, 12:57 PM
Post#4


UtterAccess VIP
Posts: 11,022
Joined: 6-December 03
From: Telegraph Hill


>> and links to tbl_Methods which is on the company network share many miles away <<

Even with the speeding up of our current internet, Albert's article here still applies somewhat.

If you are accessing a remote db over a WAN/internet, you will need it to be on a proper RDBMS like SQLServer/MySQL etc etc.

An Access db back-end won't cut it.

hth,

d

[Oops! forgot to add the link!!]

--------------------


Regards,

David Marten
Go to the top of the page
 
SemiAuto40
post Feb 12 2019, 02:31 PM
Post#5



Posts: 683
Joined: 3-April 12
From: L.A. (lower Alabama)


QUOTE
If you are accessing a remote db over a WAN/internet, you will need it to be on a proper RDBMS like SQLServer/MySQL etc etc.

An Access db back-end won't cut it.


I suppose changing my Access back end to a SQLserver backend requires some careful data typing and no small amount of time?

Thank you.
This post has been edited by SemiAuto40: Feb 12 2019, 02:31 PM
Go to the top of the page
 
GroverParkGeorge
post Feb 12 2019, 05:54 PM
Post#6


UA Admin
Posts: 34,825
Joined: 20-June 02
From: Newcastle, WA


Be careful, though.

Read this before upgrading to SQL Server.
Also refer to this.

You may or may not experience worse performance. Read those fully to understand why.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
SemiAuto40
post Feb 13 2019, 03:58 PM
Post#7



Posts: 683
Joined: 3-April 12
From: L.A. (lower Alabama)


Thank you for your contributions. hat_tip.gif I expect I will have to look very closely at the ACCESS SQL that I use throughout the application.
Go to the top of the page
 
Jeff B.
post Feb 21 2019, 08:37 AM
Post#8


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


Something I encountered (i.e., had to learn/implement) when I transitioned from an Access BE to a SQL-Server BE … It takes longer to bring all the records down the line/over the network than just a single record. I ended up modifying my forms to allow selection of the record I wanted to work with, rather than all possible records and then sifting through to find the one I wanted.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd April 2019 - 05:42 AM