473,320 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Split database performance question

8,435 Expert 8TB
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.
Oct 25 '06 #1
15 3146
MMcCarthy
14,534 Expert Mod 8TB
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.
Oct 25 '06 #2
NeoPa
32,556 Expert Mod 16PB
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.
Oct 25 '06 #3
Killer42
8,435 Expert 8TB
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?
Oct 25 '06 #4
MMcCarthy
14,534 Expert Mod 8TB
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 programmatically by defining the other database and opening the query there.


set db = dbengine(0).OpenDatabase("path to mdb")
set rs = db.OpenRecordset("QueryName")

This might make a difference.
Oct 25 '06 #5
Killer42
8,435 Expert 8TB
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 programmatically 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.
Oct 25 '06 #6
Killer42
8,435 Expert 8TB
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?
Nov 9 '06 #7
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

Expand|Select|Wrap|Line Numbers
  1.  
  2.  Select iif(isnull(table1.field1),table2.field1,table1.field1) as Field 1 from etc.
  3.  
  4.  
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,
Nov 9 '06 #8
Killer42
8,435 Expert 8TB
...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.
Nov 9 '06 #9
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
Nov 11 '06 #10
Killer42
8,435 Expert 8TB
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!
Nov 12 '06 #11
PEB
1,418 Expert 1GB
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!
Nov 12 '06 #12
Killer42
8,435 Expert 8TB
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...
Nov 12 '06 #13
PEB
1,418 Expert 1GB
Yeah, if there wasn't limits of the Access database!

Sure that should be different!
Nov 18 '06 #14
NeoPa
32,556 Expert Mod 16PB
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.
Nov 18 '06 #15
PEB
1,418 Expert 1GB
He he i'm also on those questions now() ;)
Nov 18 '06 #16

Sign in to post your reply or Sign up for a free account.

Similar topics

19
by: dchow | last post by:
Our database size is currently 4G and is incrementing at a rate of 45M/day. What is the max size of a SQL database? And what is the size beyond which the server performance will start to go down?
9
by: Steve Jorgensen | last post by:
Hi all, I'm working on the schema for a database that must represent data about stock & bond funds over time. My connundrum is that, for any of several dimension fields, including the fund name...
1
by: Dave Willock | last post by:
After splitting a database into the standard "Front end" " Back End" set-up the performance on the database has deteriorated to an unusable / unacceptable level - this occured as soon as the...
13
by: Larry L | last post by:
Access is noted for bloating a database when you add and delete records frequently. I have always had mine set to compact on close, and that works great. Now after everyone's advice I split my...
11
by: Daveo | last post by:
Hi there, Since splitting my database, one form in particular takes about 10 times as long to load and refresh, compared to the unsplit version on the server. The code behind it contains 36...
19
by: David Logan | last post by:
We need an additional function in the String class. We need the ability to suppress empty fields, so that we can more effectively parse. Right now, multiple whitespace characters create multiple...
7
by: Mark A | last post by:
If server 01 running HADR in the primary role crashes, and the DBA does a HADR takeover by force on the 02 server to switch roles, then the 02 server is now the primary. What happens when the...
9
by: =?Utf-8?B?TUNN?= | last post by:
I'm sure the answer to my question varies depending on the situation, but I am looking for a general "best practice". If I have an asp.net application and I load certain data from a database,...
21
by: =?ISO-8859-1?Q?Fad=A5?= | last post by:
Hello guys, I want to do kinda of an A/B split testing on a website I run. I just created a new version but I need to keep both version running and see which one will perform better. First, I'm...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.