Query Emulates Table, Slow Performance - Looking For Solutions

[bs0d]

I developed an Excel program that queries a database. Very recently the data source changed (new tables, new structure and everything). Now, I must link to these tables in Access.



In an attempt to keep things working seamlessly, I created a few "master queries" that are named the same as the tables I queried in the old database. The field names match exact as well. Each of these master queries consist of other smaller queries to pull the data together from the linked tables because it is stored different than before. For example, it takes about 8-10 sub-queries to get all the data for a property that was previously in a single table in the old database...

In the end, when I run my query, it see's the query name and thinks it is the table and can get the data I need. Although this looks great, it has slowed greatly as the record count grows, and will only continue to grow. I'm looking for ideas to help speed things up.

Here are a few untested thoughts:

• Store most all the historical data in a local table (not linked). Then use a union query that pulls history from local, and newer data from linked?

• Store the data in an MS SQL DB or SQL Lite -- faster?

• Create queries to update the old database with new data? --the old database didn't have the performance issues I'm seeing. I suspect it's due to the number of sub-queries required or that the tables are linked.

I have set relationships, joins, indexes... My largest table is about 70k rows, and will add about 225 new each day.

Thoughts?

[ssanfu]

the data source changed (new tables, new structure and everything)

So the 1 big table was normalized into many smaller related tables?

Why don't you have 1 query with all of the related tables added to create a "Virtual Table" that resembles the old database table? Why the sub-queries?


My thoughts:

Store most all the historical data in a local table (not linked). Then use a union query that pulls history from local, and newer data from linked?

Same speed as not or slower

Store the data in an MS SQL DB or SQL Lite -- faster?

Same speed - just different container. If current Access dB is not split, then SQL Server/ SQL Lite would be slower b/c of network

Create queries to update the old database with new data? --the old database didn't have the performance issues I'm seeing. I suspect it's due to the number of sub-queries required or that the tables are linked.

More sub-queries would be slower.

[NTC]

definitely moving to MS SQL DB or SQL Lite will not be faster because that misses the point; it is a combination of design and horse power - regardless of product.

first I would do a sanity check just to see what is the delay of calling in the core data without any processing/queried - - as that is essentially a network issue....and see if you have anything there.....

70k records is not a lot for today's PC. So your stack of queries is the cause and you want to know that they fire each time. The sorting function is one element that slows things down and should be eliminated if possible. You should fire them 1 at a time and see if there are some particularly slow ones that can be approached differently.

I have dealt with tables approaching 1M records where dealing with it becomes slow - so that beginning with a query that writes just the necessary records to a temp table - and then dealing with the temp table made sense. This could be your case - though in mine I was writing 70k records to avoid dealing with 1M.

[bs0d]

So the 1 big table was normalized into many smaller related tables?

Why don't you have 1 query with all of the related tables added to create a "Virtual Table" that resembles the old database table? Why the sub-queries?


My thoughts:

Same speed as not or slower


Same speed - just different container. If current Access dB is not split, then SQL Server/ SQL Lite would be slower b/c of network

More sub-queries would be slower.

Sorry if it wasn't that clear, but I ultimately have 2 virtual tables that resembles the old database tables. I have the sub queries to pull each of the fields in that I need. Some use domain aggregate functions, while others do not.

The point where it seems to slow down is when I create a query that performs a JOIN on two virtual tables. I can't really avoid this because all of the sub or virtual queries up to this point are just to mimic the previous DB table design. Also seemed to slow when a lot of the historical data was added to the DB.

I'll also double check each query to see if any particular one is slowing things down. But the join on the two virtual tables seems to be the culprit. To make matters worse, other queries I'll need to perform will include DSum which would further compound my issue.

[bs0d]

Toying around with this today. I was mistaken over the weekend about what seems to cause the delay.

I tested each individual query and they work as fast as a blink. The virtual query that combines the other field queries pulls ~80k rows in about 4-5 seconds.

I've narrowed things down to a query on the union query. (The union combines a local table with a linked table)

If the union query is UNION ALL -- and for a specific property, the results are reasonable, but Access freezes up on the newer records. If I scroll to the end, even if it's 50 - 100 records, Access hangs up and eventually shows the last few records.

If the union query is UNION -- the query results are much slower, but no hang-up in data sheet view.

Why would Access freeze up in this scenario (UNION ALL, Specific Property in Where clause, newest records only)? This is the type of query I need to run because it's much faster than UNION alone; there must be an issue in the union, maybe the connected table? The same query to the virtual table works fine. Thing is, I need the tables combined, so I need it to work the same on the union query.

[orange]

My guess is the UNION is reading all records and not passing any duplicates. AFAIK that's the difference between Union and Union ALL. So it is processing more info( behind the scene).

[bs0d]

It's crazy to me how I can query the table and get 80k rows in a few seconds. But if I need to limit to a specific entity, which has ~500 records, that takes significantly longer.

Sorry that this seems all over the board, but I'm debugging along the way. I may have come full circle a little bit in figuring out what is slowing things down.

It seems to me that If I'm doing any joins on the virtual queries --the queries that collect each of the smaller queries into one-- (union or not), then Access freezes on the last few records if viewing in datasheet view. I noticed in the left corner it doesn't show the max records (1 of XXX); So, although I'm seeing results, perhaps it's still running? And that's why if I jump to the last record or try to scroll down to the last record too early, it has to pause???

[bs0d]

Maybe there's a way to limit each of the field queries to a single id by design... somehow reference them to the id field of the virtual query; so when I query the virtual query (that combines each of the field queries) and provide an id value, it passes through to all the others, maybe resulting in a faster query?

There could be a circular reference issue in the way I described it, I'm not sure; But has anyone done this? I can't hard-code "WHERE ID = XXX" into each field query since ID may change. I'd like to specify it once and all the others recognize it.

I was testing it a bit and I get prompts to still key in the ID, even though I have an value specified in the criteria of the id column. Thoughts?

[NTC]

rather than Union query; can you write them into a common table? .....

[bs0d]

Thanks, I'm testing that now. I may have to do that- the downside is that it creates an extra step of update queries that will be manual, unless I create a macro to do it...

[ssanfu]

In an attempt to keep things working seamlessly, I created a few "master queries" that are named the same as the tables I queried in the old database. The field names match exact as well. Each of these master queries consist of other smaller queries to pull the data together from the linked tables because it is stored different than before. For example, it takes about 8-10 sub-queries to get all the data for a property that was previously in a single table in the old database...

What I don't understand is that there was one table, then the table was reorganized (normalized) into many smaller tables? Why all of the sub queries.
Have you tried creating one query with the required tables, then bring down the fields required to make the query look like the one original table? No sub queries.

[bs0d]

What I don't understand is that there was one table, then the table was reorganized (normalized) into many smaller tables? Why all of the sub queries.
Have you tried creating one query with the required tables, then bring down the fields required to make the query look like the one original table? No sub queries.

I may be mis-using the term sub-query because technically they are not sub queries. Sorry for that confusion. They are individual queries, most of which pull a single field.
Then, as you mentioned, I have a virtual query that pulls in each of those individual queries so I can replicate the the original table.

I got this to work fairly quick. But slows down when querying that virtual table for a specific property.