Query Performance (1 Viewer)

[tmyers]

For the record, this is unrelated to the other project I have been working on.

I have a database that is used just for the purpose of warehousing data. It has a couple supporting tables, but the main table (tblTicketDetails) holds all of our sales data so I can easily access it for reports and such. I have created a second database that has that table as a linked table and so far every query I have designed to run against it is quite slow (can take over a minute). The table in question does have over 2 million records but I feel like that shouldn't cause performance problems quite yet, but could be wrong.

I think it may be due to being a linked table as the same exact queries finish in only a few seconds within the database that the table is located in. Is there anyway to increase performance for this?

 

[Minty]

Is the linked DB on the network and what sort of storage is employed?
If so how are you connected to it? Wireless, Slow LAN, Gigabit LAN?

Have you made the linked location a trusted location?

 

[tmyers]

It is on the same network stored on a file server and I am connected via LAN which while it doesn't have great speed, isn't terrible either.

I did not know about the trusted location. I will add the location and see if that helps.

 

[tmyers]

That did in fact help. It cut the time of the query down to about 30-40 seconds from being over a minute. It might improve more as I refine it with more filters so it doesn't have to go through all 2 million + records.

Edit:
If it matters, this is a totals query. It returns all customer names based on salesman number and totals their sales.

 

[CJ_London]

Check fields used for sorting/criteria are indexed, remove any domain functions, only bring across what you need

 

[CJ_London]

I notice you use the word filter - filtering brings everything across and then filters. Criteria only brings across the necessary records. So be clear about what you have

 

[Pat Hartman]

Sometimes you can coerce the database engine into choosing a different execution plan.

Try creating the totals query and save it. Then create the final query and join to the totals query. See f that impacts the time.

 

[tmyers]

I notice you use the word filter - filtering brings everything across and then filters. Criteria only brings across the necessary records. So be clear about what you have

Criteria is the word I am meant, not filter.

Sometimes you can coerce the database engine into choosing a different execution plan.

Try creating the totals query and save it. Then create the final query and join to the totals query. See f that impacts the time.

I was messing with doing that and there wasn't much change. I am now trying to see if I can do the main query in the original database and then link that instead of trying to run it locally.

 

[CJ_London]

Don’t see how you can since you won’t be able to apply criteria

 

[tmyers]

I am going to try a different approach all together on this.
Rather than trying to build a second database, I am just going to use the Excel sheet I was told to "improve" and am going to build all the queries and such I need in the original DB then utilize power query within Excel to pull only the data I actually need. Would save me the time of dealing with the various performance problems as well as not having to code and build a whole new database and interface when an Excel sheet is already built an existing and just needs live connections.

 

[Pat Hartman]

Joining tables from separate databases is always less efficient than joining tables within the same database.

Look at your indexes. When you define relationships between tables in the relationship window, Access always creates a hidden index on the foreign key. Since the other side of the relationship is always a PK, that is already indexed.

Since the two tables are in separate databases, you will have to manage the index on the PK yourself.

Access has a "feature" which most people know to turn off. It automatically creates indexes on fields with certain suffixes. If you have this "feature" turned on, turn it off and then go through every single table and remove the unneeded indexes. If you have a named index on the FK, it is a duplicate and should be deleted. You won't ever see the hidden index created by Access unless you loop through the indexes collection or look at the hidden MSys tables that defines relationships.