Split database performance question

[Killer42]

Hi all.
I have seen mention in other messages of performance issues in a split Access database. Can someone point me to a good reference for info on this?

And just quickly, is it possible to overcome any of the performance problems? For instance, I have two fairly large tables (over 10 million records each) in separate databases, with identical layout. The field I'm interested in is indexed (non-unique) in both. I can get a Min or Max value for the field in either table, or in a query based on either table, in a second or two. But if I use a UNION query to connect the two, requesting a Min or Max on that query takes I-don't-know-how-long. I haven't sat around and waited it out yet, but I did wait a couple of minutes. The difference is just ridiculous.

[MMcCarthy]

Just a suggestion but could you get the min and max of both tables first and then compare them.

You could try a union query of the resulting queries. Should be a lot less records.

Hi all.
I have seen mention in other messages of performance issues in a split Access database. Can someone point me to a good reference for info on this?

And just quickly, is it possible to overcome any of the performance problems? For instance, I have two fairly large tables (over 10 million records each) in separate databases, with identical layout. The field I'm interested in is indexed (non-unique) in both. I can get a Min or Max value for the field in either table, or in a query based on either table, in a second or two. But if I use a UNION query to connect the two, requesting a Min or Max on that query takes I-don't-know-how-long. I haven't sat around and waited it out yet, but I did wait a couple of minutes. The difference is just ridiculous.

[NeoPa]

MMcCarthy's suggestion should help greatly here.

The reason that you're seeing these performance issues is that Access optimises queries heavily depending on information that it has about the data sources. I don't know exactly at what stage it gives up, but if it decides that it can't, 100% reliably, improve on the basic SQL it will just use it as is.

For instance, when it knows a recordset is a table with a primary key, it will usually process through the data via the primary key, thereby processing much quicker than running through the data randomly, or worse, deciding it needs to sort the dataset for its use first.

My guess is that after the UNION and the fact that they're in remote DBs, it 'forgets' what it knows about the source datasets.

[Killer42]

MMcCarthy's suggestion should help greatly here.
The reason that you're seeing these performance issues is that Access optimises queries heavily depending on information that it has about the data sources. I don't know exactly at what stage it gives up, but if it decides that it can't, 100% reliably, improve on the basic SQL it will just use it as is.
For instance, when it knows a recordset is a table with a primary key, it will usually process through the data via the primary key, thereby processing much quicker than running through the data randomly, or worse, deciding it needs to sort the dataset for its use first.
My guess is that after the UNION and the fact that they're in remote DBs, it 'forgets' what it knows about the source datasets.

That was certainly the impression I received - it seems to forget, for instance, that the indexes even exist. I mean, surely that's what the index is for. If I request somthing like the Min or Max or a of an indexed field, it should never have to go to the actual data at all, regardless of the sequence in which it does so. In fact, based on the time it took (between 5 and 10 minutes) and the amount of network traffic generated (a lot), I would say it probably did scan the entire table in this case.

As for "unioning" the two min/max queries, I appreciate the suggestion but it's not worth it in this case. I am loading the min/max values onto a form, so the user can then choose values within that range to scan. I have changed the VBA code to use a separate query for each. Lord knows how long the resulting searches are going to take, though.

Ah, I get it. The actual search may need to use such a technique - query each table and UNION the results. Yuck! There are about half a dozen tables, and more being added from time to time. The idea was to have one UNION query I could use for everything and not have to rewrite all the time.

Oh well...

The original question still stands, anyway - can someone point me in the direction of a good reference on this stuff?

[MMcCarthy]

I don't think indexes really work properly in Access when querying on linked tables.

One possiblity is to open as a recordset in the database that has the query programmaticall y by defining the other database and opening the query there.


set db = dbengine(0).Ope nDatabase("path to mdb")
set rs = db.OpenRecordse t("QueryName" )

This might make a difference.

[Killer42]

I don't think indexes really work properly in Access when querying on linked tables.

The indexes do seem to work fairly normally on the linked tables, just not when I use UNION.

One possiblity is to open as a recordset in the database that has the query programmaticall y by defining the other database and opening the query there.

I've acheived reasonable results (a couple of seconds to show the form) by just doing a separate query for each min/max. I'm just bemoaning the loss of flexibility. Of course I can get that back eventually by having the code scan for the tables rather than hard-coding them.
where it will get a little more complex is in building the queries. I'm allowing the user (which will mostly be myself anyway) to choose a particular table (year) to search, or "all". But at present the "all" option looks as though it will take all day to run. I'll have to rewrite the code to set up a UNION of the result queries rather than just doing the search on my stored UNION query.

Anyway, thanks for the pointers. This one is pretty low priority, so may go on the backburner for a while. However, I am about to post another question which relates. Your input will be most welcome.

[Killer42]

Hi again.

I'm just going to reiterate my original question here - can someone point out a good reference on splitting databases, specifcally in regard to performance issues?

[Andrew Thackray]

Killer

if your tables contain different record sets that you wish to join & search on the common indexed field try this approach. It may look long but in fact it should execute in seconds

1) Create an empty temp table with the only field the indexed field common to both tables. Make this the primary key.

2) Create a append query that extracts the indexed field on table 1 into a temp table.

3) create an append query that appends the same field from table 2 to the temp table. If the tables contain duplcate enties in the field being extracted use a left outer join between table2 & the temp table & only append records not already on the temp table.

3) create a query that joins the temp table to the first & second tables using left outer joins on the common field. The field selection SQL sould be in the form

 Select iif(isnull(table1.field1),table2.field1,table1.field1) as Field 1 from etc.

this will give you a union of the two tables effecively through a common index on the temp table and will overcome the performance issue.

To access the joined records clear the temp table, run the two append queries and then open the last query.

You will find it takes seconds to run the two append queries as essentially they are merely dumping an index into a table and the last query will be almost instant in is locating records.

I have used this technique quite often, especially if I want to rapidly run unions across totally separate datasbases such as Oracle & SQL server. It always seems to run many times faster that attempting a table join across two databases,

[Killer42]

...if your tables contain different record sets that you wish to join & search on the common indexed field try this approach. It may look long but in fact it should execute in seconds
...

Thanks Andrew, I'll file that technique away for consideration.

Don't know whether it will actualy help in my current situation, though. I actually have 9 separate databases so far, with identical tables, and more being added from time to time. They each hold anything up to around 15 million records, and the users need to be able to search on a wide combination of fields at any one time, including wildcard searching.

I have actually managed to get around the worst of the performance problem by using VBA code to build an SQL string which queries the individual tables and UNIONs the results.

All I'm after now is somewhere I can learn more about the whole area.

[Andrew Thackray]

Killer

I don't know of any references on optimising Access across multiple databases.

However I have come across the same issues as you when integrating multiple large systems and Access has been the best available tol for providing users with front end database acces across them.

I think given the number & sizes of the databases you are dealing with that you are coming up against the limitations of access & there is not a lot you can do in access itself to improve performance. This is because access will be using the users workstation to do most of its crunching & the workstations are not optimised for this kind of processing.

However there is another approach. If one of your databases is an SQL server database or you can set up an SQL server database on a server somewhere you can use this as your query cruncher while still being able to use access as the frnot end for the users. SQL server uses VBA as its native language so if you set up the SQL server workstation client on the workstations using Access and set the VBA reference to the SQL server objects in access you can directly build views & execute SQL server queries in VBA in access.

The technique here is to link your databases as linked servers on rthe main SQL server database. Then in VBA build the Transact SQL statements in access and execute them through the SQL server objects.

This will allow you to build queries on the fly according to the users requirements and pass the processing load back to the SQL server.

There are also other techniques for improvement this will open up for you. If most of the data you are dealing with is historical you could build SQL server DTS functions to create indexes on the SQL server automatically as often as you like. Using these will speed up queries onto the linked databases significantly. You could also run your union queries into a local table on the SQL server overnight usng a DTS and point your queries to this. You can also increase the grunt of the SQL server machine itself if all else fails.


There is a heap of references on the web on optimising SQL server databases and optimising queries to linked servers on SQL. The best place to start looking is in the Microsoft MSDN site.

Regards

Andrew

[Killer42]

Thanks for that, Andrew.

Unfortunately, SQL Server is not an option. I will, however, do some searching for references on the web inregard to splitting databases and related performance issues.

In this case I have managed to produce acceptable performance by tailoring (in VBA) my queries to the specific tables that are linked. I guess I just wanted to be lazy, and work with one big UNIONed query.

Ciao!

[PEB]

Hi Killer,
You've mentionned that you've 9 databases with simillar structure that you want to use like one table...

Also your users want to requery information from those 9 tables in 9 databases.

In this case your users can introduce the query criterias for the tables in a form..

Than using VBA run 9 Append queries in a temporary table with the respective condition and display the temporary table...

The filter should be less than 15 billions of records???
And shouuld run faster...

The Unions get a lot of ressources... I use them as little as possible!

[Killer42]

Hi Killer,
You've mentionned that you've 9 databases with simillar structure that you want to use like one table...
Also your users want to requery information from those 9 tables in 9 databases.
In this case your users can introduce the query criterias for the tables in a form..
Than using VBA run 9 Append queries in a temporary table with the respective condition and display the temporary table.
...

I'm doing something like that now. I'm not using a temporary table, unless this is something Access does "behind the scenes". What I do in VBA is build an SQL string which queries each of the tables, then UNIONs the results. The resulting speed is acceptable. I just hate having to play around with so many tables when, in theory, I should be able to treat them all as one big table.

In other words, I have a workaround and don't need a solution to the slow UNION problem, it simply annoys me as it seems so pointless. We might as well drop this thread, I just need to find the time to read up on split databases and so on.

By pointless, I mean that if Access can perform an action on each table in a couple of seconds, and they have appropriate indexes, it should not take half an hour to perform the same action once the two tables are stuck together by UNION. (And by "action" I mean something as simple as returning the Min or Max value of the indexed field.)

Oh well, I'll just grumble quietly in the corner...

[PEB]

Yeah, if there wasn't limits of the Access database!

Sure that should be different!

[NeoPa]

This is the thread I was thinking of when I posted this.
Thanks to PEB for bumping it for me :).

Also, a performance point for running (non-action) queries with large datasets (couldn't find other thread sry).
Look at the properties of the Access query saved. If it says use transactions or return a Dynaset, then these options will slow down performance.
Use Transactions is only required for an action query.
Returning data to view (not updatable) should always be with a snapshot.
Sorry if this is a little off this topic, but I thought you may be interested.