Multiple Tables = Reduced Performance?

[systemx]

Hi all,

I have almost finalised the initial development of a fairly large split local F/E- network drive B/E access database. The database concept has grown from a 'Complaints Capture' database to a comprehensive 'Ticket of Work' database for a variety of different types of work.

Being my first database, the design has a number of weaknesses. Namely, due to the current design there are a large quantity of 'Empty' fields for each record.

I will be redesigning the structure as part of a future build. My question is simple.

I am using ADO to push and pull data across a network drive (no SQL server yet ). If I split up my data across additional tables, is this likely to negatively impact already borderline performance?

Cheers,

Rob

 

[Wiz47]

Hi all,

I have almost finalised the initial development of a fairly large split local F/E- network drive B/E access database. The database concept has grown from a 'Complaints Capture' database to a comprehensive 'Ticket of Work' database for a variety of different types of work.

Being my first database, the design has a number of weaknesses. Namely, due to the current design there are a large quantity of 'Empty' fields for each record.

I will be redesigning the structure as part of a future build. My question is simple.

I am using ADO to push and pull data across a network drive (no SQL server yet ). If I split up my data across additional tables, is this likely to negatively impact already borderline performance?

Cheers,

Rob

More than likely. The more tables you have on the be server, the longer it will take to load on the fe. One suggestion would be to move tables that are not likely to change, ie; lookup data for relatively constant comboboxes, to the fe with the forms, queries, and reports. That way, they will load locally and not tie up resources on the be.

 

[Mike Smart]

Hi all,

I have almost finalised the initial development of a fairly large split local F/E- network drive B/E access database. The database concept has grown from a 'Complaints Capture' database to a comprehensive 'Ticket of Work' database for a variety of different types of work.

Being my first database, the design has a number of weaknesses. Namely, due to the current design there are a large quantity of 'Empty' fields for each record.

I will be redesigning the structure as part of a future build. My question is simple.

I am using ADO to push and pull data across a network drive (no SQL server yet ). If I split up my data across additional tables, is this likely to negatively impact already borderline performance?

Cheers,

Rob

Hi Rob

My view is that if you don't normalise your database you'll end up doing lots of clumsy work-arounds that simply won't be efficient and you'll probably end up with a corrupt database. It's never worth it!

There's one technique I use that massively speeds up database clients. I use it with SQL Server databases when there are hundreds of users and network traffic is the issue but it would work just as well with fileserver. I create some ADO recordsets locally and load up all of the static data (not the whole table, just the primary keys and descriptions) when the user logs in. I never trouble the server for this information again but users are trained to know that they need to click a Refresh button if they know that new static data has been added but can't see it on their systems. It's massively inefficient to re-query the database every time you load a form just to populate the combo boxes!

Hope this helps

 

[The_Doc_Man]

Concur with Mike and Wiz. FE/BE split is important, but where there is static data to be had, take advantage of that fact. Download the static data to the FE as a "local" table. Also, if there is a way to do it, define things to be added to TEMP local tables. Build it all locally. When you commit the entry, be prepared for that to take a long time when you do the cross-network updates. Note also: Don't erase the local table until you can confirm the update is complete, because that way you will lose no (or at least less) data when network hiccups occur.