CODEWITHSUNDEEP
sqlsql-serverperformancems-access
user3790592
user3790592

Reputation: 21

How to speed up Access front-end / SQL Server back-end with large tables?

I'm re-designing a front-end for SQL Server in Access, to allow non-programmers in our company to query the database.

The problem is that some of the tables are very large. At the moment I'm using linked tables. One query that I'm trying to allow, accesses five tables including that large one. The table has millions of rows, as it has every transaction ever made in the company.

When I tried the query in Access it took minutes and would not finish, and Access just froze. So instead I decided to use a subquery to narrow down the large table before doing the joins. Every entry in the table has a date, so I made a subquery and filtered it to return only the current day just to test. In fact, because I was just testing, I even filtered it even further to only return the date column. This narrows it down to 80,000 entries or so. Eventually I did get results, but it took around three minutes, and that's just the subquery I'm testing. Once results DID return, Access would freeze every time I attempted to use the scroll bar.

Next I tried pass-through queries, thinking it'd be faster. It was faster, but still took around a minute and a half, and still had the freezing problems with the scroll bar. The issue is that this same query takes only 3 seconds on SQL server (the date query I mean.) I was hoping that I could get this query very fast and then use this for the join.

I could use views, but the problem is that I want the user to be able to specify the date range.

Is there anything I can do to speed up this performance or am I screwed?

Upvotes: 2

Views: 2412

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112682

It makes no sense to let the users scroll through 10th of thousands of records. They will be lost in the data flood. Instead, provide them means to analyze the data. First answer the question: "what kind of information do the users need? “ They might want to know how many transactions of a certain type have occurred during the day or within an hour. They might want to compare different days. Let the users group the data; this reduces the number of records that have to be transmitted and displayed. Show them counts, sums or averages. Let them filter the data or present them the grouped data in charts.

Upvotes: 1

Related Questions