All geek questions in one place

Access Requests Slower

I have an application with an interface in Access 2007 (Access 2000 file) with a backend on SQL Server 2008. The application has been running for two years, and I noticed that on some PCs queries run forever, while on others it works pretty fast. Each PC has its own Access file, so they do not share the file.

Simple queries with a single table are time consuming. The largest table has 578,000 records.

Sometimes it takes 10 seconds to load a simple table in just 6 records. We are in a gigabit network.

+3
source share
5 answers

I deleted the related tables in Access and added them again. This greatly improved the request time.

SQL Server 2008: SQL Server 10.0 v.2007.100.2531.00 SQL Server v. 2000.85.1132.00

ODBC, , - ODBC SQL Server Microsoft SQL Server 03.85.1132

0

, , , ?

ODBC .

+1

SET EXPLAIN ON..., , .

, , . , , SQL'08 , '05, .

+1
source

This may be caused by users joining a local table with a table on SQL Server.

Connecting a local table to a SQL Server table causes Access to retrieve the entire SQL Server table and connect to the local computer.

We had similar problems in the past.

0
source

Try updating the statistics on the SQl server.

0
source

Source: https://habr.com/ru/post/1770309/

More articles:


All Articles