massive data hierarchy advice (1 Viewer)

[accessuser1023]

Hi all,

I have a situation here where I'm going to be making a DB app that has a massive amount of data storage requirements, but ironically it fits perfectly into a 3-dimensional structure (e.g. - literally all 1-many relationships can be used for the entire set of tables). Here is an example of the hierarchy I might have:

=> table 1
   => table 1 sub 1
   => table 1 sub 2
=> table 2
   => table 2 sub 1
=> table 3
   => table 3 sub 1
      => table 3 sub 1 subsub 1
   => table 3 sub 2
      => table 3 sub 2 subsub 2
   => table 3 sub 3
   => table 3 sub 4
=> table 4
   => table 4 sub 1
=> table 5
   => table 5 sub 1
   => table 5 sub 2
=> table 6
   => table 6 sub 1
=> table 7
   => table 7 sub 1
   => table 7 sub 2
=> table 8
   => table 8 sub 1

So here's a question for someone who might be better at handling large apps than I am:

How close am I getting to Access not being able to handle this? I am looking to create the app such that Oracle creates theirs (e.g. - lots 'o forms!). So based on that hierarchy, I would assume that the number of forms would be 50 or so. There isn't anything wrong with this, is there? I mean from a concurrent user prospective? I will cover all the necessary issues such as write conflict, record locks / form locks, etc... I'm just wondering if the resource usage strain on Access itself would be something that needs to be looked into. The user for this initially would be 3, but it could get as high as 25. The other issue that might come into play is putting the datasets on Sharepoint in Sharepoint lists.

Any advice on this would be great guys. I'm actually looking to start a discussion regarding issues like this. It would certainly be useful for me, if not everyone else too.

thanks.

 

[MarkK]

How big is massive? I've had tables with hundreds of thousands of records and performance is fine.

But otherwise, are you asking if Access is an appropriate tool?

 

[accessuser1023]

How big is massive? I've had tables with hundreds of thousands of records and performance is fine.

But otherwise, are you asking if Access is an appropriate tool?

I'm not really asking about the *size* of tables, but rather all of the relationships that have to be formed between them, forms being used behind all of those relationships, and then many people then using those forms.

So yes, the question is about performance with this, and more than that, corruption issues. And also, yes I'm asking if Access is an appropriate tool for the scenario that I have outlined.

thanks.

 

[MarkK]

Relationships between tables are not dynamic things, like they don't require run-time resources. A field in one table matches a field in another, and that essentially what a *relationship* is.

Forms are only used by one user at a time, at least in a "split database," which is what you want. Each user has his own dedicated program file, which links to the tables on the server, and the forms for that user are in that program file, his or her dedicated copy. In this case there is no bottle-neck in respect to forms or their use.

Corruption is not a significant issue if your database is split as outlined in the previous paragraph. I've broken Access files in development, but I've never actually lost data to corruption. I don't believe that is an issue.

The tree structure you outline is not, at face value, complex. That could be represented in one table that is related to itself, with a structure like . . .

[B]tTree[/B]
TreeID (Primary Key)
ParentID (Foreign Key, link to PK in this table)
SomeDataHere

. . . so that is not going to put pressure on Access in any way.

But as I understand it, the only real thing that impacts performance in a database is the size of the tables, since every join operation is essentially a combination of a search and/or a sort--in which performance degrades as the set size increases--so the main thing you need to do is index your tables properly, on the fields you intend to use as joins or constraints, and that will have the greatest impact on performance. Given the case that your tables are properly indexed, then the only performance problem will be size, and in my experience, in Access, performance is acceptable in the range of hundreds of thousands of records.

Maybe network speed was an issue, but you can get Gigabit network hardware now, for cheap, so moving data from the server is not going to slow you down.

Does this address your concerns?

 

[accessuser1023]

But as I understand it, the only real thing that impacts performance in a database is the size of the tables, since every join operation is essentially a combination of a search and/or a sort--in which performance degrades as the set size increases--so the main thing you need to do is index your tables properly, on the fields you intend to use as joins or constraints, and that will have the greatest impact on performance.

YES. this is what I was getting at. So if indeed an application becomes such that hundreds of thousands of records are dwelling in the tables, would that be an indication (if you know things will go that far) that you should consider up front how many joins or complex joins should be involved in your table structure? It would seem to me that it would make sense in this way.

So if you have a choice initially to build tables with or without certain joins required, and you know that hundreds of thousands of records will eventually be in plenty of your tables, would it not be wise to choose lesser amounts of joins rather than more of them? Even if choosing 'more' would result in a better 3-dimensional and/or normalized setup?

This is exactly what I was getting at Lagbolt, I just didn't know what the issue was until someone mentioned it. thanks.

 

[MarkK]

Yeah, you bet . . .

. . . but, you don't perform joins as a function of what you think your data are going to scale to at some future date. You design your data structures, your tables, as models of the reality you are dealing with, and your model will be correct for your purpose or not. Joins in this sense aren't optional features that you include if the software can handle it. Joins will be necessary where the things you are modelling have a one-to-many relationship with each other. Period.

So if I write a CD database, I have artists and bands and tracks and CDs and genres, and the way these things are related to each other will dictate my tables and how they are joined, and these will be facts. A join will exist between the CD table and the Track table, because a CD has many tracks. Period. Not an option based on the speed of my database processing, a fact of the topology of my data.

Hope this helps,

 

[accessuser1023]

That is wonderful advice Lagbolt. Nothing else needed! thanks so much sir.

 

[Pat Hartman]

Keep in mind that your database would be defined exactly the same way whether you implement it with Oracle or with ACE or SQL Server or DB2, etc. Some RDBMS' have special features or data types that the others don't support but if you looked at the relationship diagram implemented in any RDBMS, it would be pretty close to identical.

The larger your row count, the more important it is to design the app with client/server techniques. Typical Access applications designed by novices, bind forms to tables or queries without selection criteria and then rely on filtering or scrolling to get to what the user needs to see. You need to move away from this from the beginning so that at any point in time, you can simply relink the tables and with a couple of changes to the DAO code, switch the BE to Oracle/SQL Server/DB2, et al.

Always make sure your forms/reports are bound to queries with selection criteria that selects the minimum amount of data necessary. Users can't realistically scroll through thousands of records in a form, so why bring them all down from the server? Better to bring one main record at a time. Let the user keep providing criteria for the next record. In some cases, you might want to create a form that brings a "keyset" and then by clicking on a record in the keyset form, open the main form with subforms.

Search for articles on optimizing Access for client/server for more ideas.