charmedimsure
asked on
NETWORKIO on Access FE SQL Server Linked Tables slowing forms how to fix
We are getting repeated slowdown on an Access 2003 Database linked to SQL Server 2000 Tables. Last week our form opened quickly then all of a sudden it started to slowdown drastically in all environments, Production Certification and Development irregardless of record size. Our DBA keeps seeing NETWORKIO and wait times for no reason even if there is only a single user with very few records. The form in Access is not corrupt and when it goes against local Access Tables it opens instantly. All of the queries associated with the form in SQL open up quickly too.
How do we fix this? Thanks
How do we fix this? Thanks
NETWORKIO indicates that the connection si swaiting on the network. So there may be a network bottle neck somewhere.
is this during a full anti virus scan on the server?
does the FE code build temp tables? if so, it might have bloated the front end and you should compact/repair, and try to redesign not to use temp tables.
HTH
does the FE code build temp tables? if so, it might have bloated the front end and you should compact/repair, and try to redesign not to use temp tables.
HTH
ASKER
No there are no temp tables being built at all. When I open the queries directly they open lightning fast. And last week everything ran lightning fast even with the forms. All of a sudden we are getting these NETWORKIO wait times
I guess you are using a lot of filters and queries and things in your MS Access front end?
When you use a linked table in Access, it drags ALL the records over to the client then performs filtering and joins on the MS Access client. it DOES NOT do it in the database.
This is a common misconception. If you use MS Access functionliaty - querys, filters, functions etc., then the logic is performed at the client, even if it requires that all the records are dragged over.
That is why you are getting network I/O
To actually leverage your SQL Server, you need to develop using an MS Access Data Project (ADP)
When you use a linked table in Access, it drags ALL the records over to the client then performs filtering and joins on the MS Access client. it DOES NOT do it in the database.
This is a common misconception. If you use MS Access functionliaty - querys, filters, functions etc., then the logic is performed at the client, even if it requires that all the records are dragged over.
That is why you are getting network I/O
To actually leverage your SQL Server, you need to develop using an MS Access Data Project (ADP)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.