I'm with you on that Bob. Life is good when the Yankees lose.....

Kenny Alligood

A sumary table is something of a table that is used for reporting purposes. You can create one and update it daily when performance is a big issue. It keeps you from running long queries that use a lot of joins. I have also used them once in SQL Server 2000 as a way to speed up my database performance.
_________________________________________________

Maybe Roni needs to first look closely at the design of the database front end. If he has an application that is query intensive just to displayt data and filter data, this alone will degrade any database application, even if the cack end is in SQL Server or Oracle. Before you remove any referential integrity, look at your front end.
Your database is small enough that you can make this work fast enough to make it bereable for your users, (I know, they are impatient, I have been on your shoes).
Make sure that you use ADO to populate your forms. When you open a form that is going to filter any data, make sure that you first get the value that you wish to filter by, then use that to get only the data you need. Remember that network speed and Jet problem.
Your database may be better off being on a server. Your PC where you have this is not stable enough or fast enough. Let me ask you, does it have a screen saver? if it does, change it for jus a blank screen.
On removing those relationships, remember that if you use joins, these relationships will speed up the queries. Look into re-indexing your database. Maybe you are using Autonumber fields as primary keys, but when it comes time to query you are using an address field or a person name for the query, and this address field is not indexed. I go against removing indexes and relationships simply becase your front end may not be as strong on this area. Consider this, do your users have access to viewing the tables? if they do one of them will eventually come with a great idea of cutting and pasting something from Excel into a table or just selecting a query and deliting every record they see just to make their report show what they want at that point in time. (I have had to clean this messes before)
Look into your indexing and primary keys, use them in the where clause of your queries, reduce the amount of joins, create sumary tables (do a create table query for your queries that have 10+ joins) and use them as the source for your filters, move the back-end to a server folder (because this folder is being backed up daily also), compact your databases if you haven't done so yet, you can bring your 4MB file down to 2MB.

I have been developing databases in Access and SQL Server for some time now and I know that even if your data resides in SQL Server and you are not using your indexes correctly or you are using too many joins, performance will degrade.

If these fails and you still want to remove your referential integrity, then consider the possibility, but I would never do that.

Question for Bob, Does ADO bring all of the table data to the client when Queried? I haven't found any documentation on that anywhere

By the way when I said MSDB I meant MSDE.



Sal

Unfortunately yes, if you are connecting to an .mdb backend, no if you are using a SQL Server backend and your app is designed correctly. Access is just a file-server application, not a true database server, so the backend file only acts as a data store, it can't perform any query processing. All query processing has to occur on the client, so there is no real advantage in re-writing the app to use unbound forms and ADO unless you are connecting to SQL Server. Even then, you have to be careful about using any clasues or expressions in your queries that SQL Server doesn't support. Access evaluates queries locally before sending them to SQL Server, and if it can't run the entire query on SQL Server, it still has to complete the job locally.

When I get a chance, I'll post a little info regarding Jet's ISAMStats function (which returns a list of query statistics) and Jet's ShowPlan function which lets you create and view a copy of the query plan used by the query engine optimizer. They can be moderately helpful aids in tuning query performance.

Regards,

Bob

In fact, and I'm nit-picking here, when working in a pure Jet environment (split front-end/backend-end .mdb), there is technically speaking no "client" involved, i.e. a box that requests services from a database server.

A split Access .mdb is not using client/server technology involving a client and a server, its using file-server technology involving a front-end (application file) and back-end (data file). Here the back-end database file contains only shared data, but can't perform any services (e.g. query processing). When a file-server app performs a query, all the records used as the source of the query are sent to the local workstation front-end. If the source of the query includes a table that contains 25,000 rows, all 25,000 rows are sent to the clent for processing, even if the query result is only asking for 2 rows out of that 25,000. It doesn't make any difference whether the data is retireved from linked tables (via DAO), or over an ADO connection to the back-end database file.

There is, however, one advantage in using ADO in a file-server setup. ADO alone won't let you create faster queries, but it will allow you to create much faster forms. Forms bound to a large recordset can be much slower than unbound forms that display only a single record. In fact, any discussion about multi-user app performance isn't complete without throwing the issue of form design into the mix as well.

Regards,

Bob

oops...I did it myself. Meant to say "all 25,000 rows are sent to the front-end application file for processing."

Wow…
That’s a lot to take in.

Sorry about the PK/FK somehow I just didn’t get the acronym. After looking at the DB for so long my brain must have melted. I was looking through the DB and I’m not sure what changes I can make although I’m sure that there are many that I just can’t see… yet.

When looking at the multitude of joins every form and report especially continuous forms can’t seem to function without a recordset. By limiting a form to a single record set you are forced to make a large number of joins to include all the information you will require for each task.

In almost every form there is customer information joined to order information joined to order line item information all joined to drug information which itself is 2 or perhaps 3 depending on the circumstance. In many cases there are addresses added to this mess and because people may have more than one address there is a many to many relationship “Lives At” pulling the combined mass of a typical form to 5 – 10 tables.

I have been knocking down table usage by adding fields here and there dashing the beautiful 3NF system to speed up the process and bring down the number of tables but still I normally require between 4 – 8 tables.

This also does not include some of the row sources of various list boxes. There are forms where the list box data will change on every keystroke. Allowing a much faster search for a person of a specific drug when the data is changed via the rowsource property which is probably requerying the backend every time.

Can I get a recordset once when dealing with a form as an ADODB.Recordset and have the form record source and the listbox rowsources reference one or two static recordsets?
I hope that makes sense.

-Roni


Roni Estein
[email protected]
https://www.e-drugsCanada.com

This also does not include some of the row sources of various list boxes. There are forms where the list box data will change on every keystroke. Allowing a much faster search for a person of a specific drug when the data is changed via the rowsource property which is probably requerying the backend every time.
-__________________________________________

This is exactly the thing that you should avoid. Yes bringing a recordset and then filtering it is faster, but if you are using a lot of many to many relationships, I doubt that the recordset solution will be a solution. Remember that you must go to 3NF and once your schema is done and is normalized, sometimes you must de-normalize. Meaning, you may be trying to keep too much history or trying to capture too much and now your database performance has degraded. You must give up one to get the other.
"Do not over-query your database"
You are putting too many tables in one form. Remember that the database brings all records from all tables in your database each time you query your database. It may look nice to have many subforms on a form, but your database and network will come to a hault.

You are going to have to re-design your front end app. Remember, forms are only to allow a user to edit, add, delete data and not as a way to show your data.

I worked on a project once where I had to use 5 many to many relationships on the database just to please the client. The backend was a strong SQL Server, but even then, performance was an issue. That was the time that I had to create tables (sumary tables) for their reports. It is not the nicest solution, but it pleased the customer.

Bob, about the front end/back end. That is exactly what I was refering to. With access as a back end, one really gets no client server technology anywhere.

Please make backups of your database.


Sal

Hi Roni,

If you have tables that store static/relatively static data that is just used used to populate form controls (e.g., combo boxes/list boxes), etc., by all means move them to you front-end application file. There is absolutely no reason to store them on your back-end. Also, there is no way to bind an ADO recordset to forms displayed in continuous or detail view, so that's not an option.

Bob

Does that mean that in my case SQL server would be processing, the the queries on the backend and serving only the "3 rows" out of the 25000 that I needed in the first place?

If you had both Access 2002 and SQL server 2000 at your disposal for a project like this which would you use?

Roni Estein
[email protected]
https://www.e-drugsCanada.com

Yup.

You could use a true Access client to execute stored procedures (precompiles SQL statements) on SQL Server and fetch a single record at a time, or just pull them over the network in batches of 25 or so if you wanted to provide a little record navigation functionality. All things being equal, a client/server arcitecture will vastly out-perform a file-server architecture.

Bob