too many relationships?

[msar]

i inherited a database that i have been adding features to for quite some time.

i'm looking for insight as to what to look for that could be slowing things down.

recently, we have seen the server "freeze" and occasionally crash. typically it freezes only once a day, and will go for many days without freezing at all. this freezing started, perhaps coincidentally, when five users were added and 11 relationships to the task table were added.

we moved the database to a new machine and location to eliminate those variables. today, it froze again, took about 5 minutes to come back to life, but didn't kick any users.

we have jobs in the database and tasks associated with those jobs. sometimes many (40) tasks per job, but usually ~10. we have many relationships to the jobs and task for reports and portals.

these relationships are fairly simple, four at most:

user = userID

task type = static task global variable set in their home screen

etc.

i'm in the process of reviewing the database and its relationships, and i'm wondering if it is possible to have too many to one table.

my feeling is when users are creating tasks it is slowing down the system (each task takes 8 seconds to create [which is a long time]), and if multiple tasks are created around the same time the requests pile up and the "freeze" occurs.

the database has over 56,000 records on the job table...and nearly 300,000 on the task table.

the database is ~320MB.

i have played with removing more than half the records, and as you can guess it had no effect.

i have cloned the database and made a single job and task creation was still slow (8 seconds). so it is database structure creating the issue.

i have experimented with eliminating many of the fields in the task table with no positive effect.

i have done a "recover" and no errors appear.

i plan to do a recover with version 10 as there are more features and reporting on the process.

there are a few summary fields that are rarely seen, but those only get calculated when appearing on layouts, correct? so far as i can tell no one uses them.

there exists calculation fields that count tasks within portals...i'm experimenting with eliminating those.

we are going to do a user audit to see what users use which layouts etc so as to simplify the database further (i recently eliminated a large number of relationships and table instances from a project that has been retired).

any insight would be appreciated.

thanks.

Edited November 18, 2010 by Guest

[bruceR]

I doubt it is too many relationships. I've seen some incredibly complex graphs that still work much better than what you are experiencing. It will be hard to troubleshoot this without seeing the file. How about some basics?

How many tables? How many fields in each table? What do your external data source references look like?

They should NOT have IP addresses in them. They should be in the form:

file:yourfilename

[msar]

42 tables

job table: 342 fields

task table: 74 fields

the others average about 15 fields.

2 external database references, no IP address.

no custom functions (if that matters)

an empty copy of the database is 30MB and might be a lot to digest.

I doubt it is too many relationships. I've seen some incredibly complex graphs that still work much better than what you are experiencing. It will be hard to troubleshoot this without seeing the file. How about some basics?

How many tables? How many fields in each table? What do your external data source references look like?

They should NOT have IP addresses in them. They should be in the form:

file:yourfilename