Any ideas for speed?

[EddyB]

Hi all,

Running Windows 2k Fmp6

I have 2 databases which are for testing students.

Database 1 is for registrations

Database 2 contains all the answers to the questions

When someone registers on the registrations database, depending on which subject they register for, it goes off to the questions database and pulls in all the answers from the questions database.

Now, the problem I have is some subjects have 18 sub-subjects, these subjects can contain up to 30 questions. Therefore, when someone registers it goes off on the relationship and gets data from 540 fields. Once this data is collected calculation fields are performed in the registrations database.

So when I'm opening the database it is taking up to 30 minutes to open, I'm only in the testing stages and so only contains 6 records!

So has anyone any ideas please on how to do this differently, maybe not have a relationship at all and set all the answers as global fields? I don't know, I'm stumped - Please help!

One point I am in the process of doing is changing all my calc fields from if statements to case statements to try and speed it up, but don't think this is going to help that much.

Thanks

Ed.

[Ugo DI LUCA]

So when I'm opening the database it is taking up to 30 minutes to open

Eddy,

I don't understand why it recalculates on opening. Did you set the calcs to unstored ? Why ? What are these calculations based on ?

[EddyB]

Hi Ugo,

Think I've really messed up on this one! and I'm going to have to start again from scratch, it was one of those where it started as a small database and it's grown and grown!

OK whats happening is it goes off and gets the answers to the questions, then it says if Correct Answer (from the questions database) = Users Answer, Then Score = 1. This happens for all 540 questions.

I don't think I have set the calcs to not calculate when reference fields are empty so I think everytime someone creates a new record it is doing 540 calculations when they haven't even entered any answers yet!

And yes, think I have set them to unstored, but the reason for this was they have the option to save their answers (over the web) or submit their answers.

If they save their answers, they don't get their results and can go back into their answers at a later stage and change what they originally answered, therefore the calculations would have to recalculate to set their score correctly.

Any ideas on this one please Ugo?

Ed.

[EddyB]

Sorry, also the registrations database contains about 3000 fields.

I had several layouts set up, one of these layouts contained all 3000 fields, thought this may have caused the problem so I deleted the layout but this did not help.

The majority of these fields are calculations e.g. Score of each answer, Text score i.e correct or incorrect, sub-section total scores, sub-section percentages, overall scores, overall percentages etc

Ed

[Ugo DI LUCA]

Hi Eddy,

In my opinion (really mine !!), you won't store the answers in the Answers.db as global fields, but you could set the User answers in the registration.db as global fields...

Besides, I would set 3 db for this.

Something like :

Registration

User Answers

Good Answers

1. The user registers and an ID is created in User Answers and Registration.db

2. The submit pulls the answers to the User Answers.db using the relationship, but calcs are made only from "User Answers" to "Good Answers".

3. The answered are stored using the Id until the user scripts the submit.

[EddyB]

Ok thanks Ugo, I think I understand that but it's late and I'm tired so will have another look in the morning!

Thanks for your help - AGAIN

If I get stuck tomorrow, I'll post again, hope you don't mind!

Thanks

Ed.

[EddyB]

Right, I think, through process of elimination, I have found the problem.

I created a new layout and put different fields on there to find out what was going slow and what was OK.

There are 2 lots of fields that are going slow.

The first are the fields that gets the status of the sections

The second are the fields that gets the URL address of the sections.

Both are case statements, as I've shown below

I'd better explain what happens first though.

The sections status depends on the previous sections status and what the user has submitted:

If the previous section is Waiting for submission

Then this section is locked

If the previous section is Saved

Then this section is locked

If the previous section is Submitted

AND the user has submitted answers

Then this section is Completed

If the previous section is Submitted

AND the user has saved answers

Then this section is Saved

If the previous section is Submitted

AND the user has not done anything

Then this section is Waiting For Submission

Then the address depends on the status:

If the section status is locked

The URL address locked.html

If the section status is submitted

The URL address submitted.html

If the section status is saved

The URL address is taken from the related database

If the section status is Waiting for Submission

The URL address is taken from the related database

So if there are 18 sections I guess this is happening:

Works out section 1 status

Works out section 1 address

Then it can

Work out section 2 status

Work out section 2 address

And so on until it reaches section 18

It cannot work out section 18 until it has worked out all previous 17 sections

Here are the case statements

Section 6 Status=

Case( S5 Status = "Waiting For Submission" , "Locked",

S5 Status = "Saved", "Locked",

S6 submit save = "Submit Answers", "Completed " & "- " & S6 percentage & "%",

S6 submit save = "Save Answers", "Saved",

S6 submit save = "", "Waiting For Submission",

"Error")

Section 6 address=

Case( S6 Status = "Waiting for submission" , Sections::S6 Address, S6 Status = "Saved" , Sections::S6 Address , S6 Status = "Locked", "Locked.htm", "Submitted.htm")

Hope someone can make sense of this and are able to see what my problem is! and how I can do this another way to try and speed the process up

Many Thanks

Ed.

[Ugo DI LUCA]

Hi Eddy,

At first, check if a new Case statement this way could speed things :

Case(S5 Status = "Waiting for Submission" or S5 Status = "Saved", "Locked", Case(S6 Submit Save = "Save Answers", "Saved", Case(S6 Submit Save = "Submit Answers", "Completed " & "- " & S6 percentage & "%", "Waiting for Submission")))

I cannot see why the "error" appears......

Now a second way could be to use a Choose calculation by changing the status fields from text to numbers .

Submit Answers = 2

Save Answers = 1

Waiting for submission = 0

Then :

Case(S5 Status <= 1, "Locked", Choose(Submit Save, "Waiting for Submission", "Saved", "Completed " & "- " & S6 percentage & "%", ))

Now, I'm quite sure relationship could speed this up in that case.

From your different case statements, it seems to me that the Current Session Status and adresses depends wether at least one session has a certain status :

If at least one is waiting -----> Session locked

If at least one is saved------> Session locked

If at least one is submitted ----> Session Waiting for submission

If all submitted ---------> Session Completed

In that case, what could speed up the calcs could be to involve relationships on a SelfjoinOnRegistration_ID, and use the Min(your relationhip).

Case( Min(selfjoin:: Status) <=1, "Locked", Choose(Submit Save, "Waiting for Submission", "Saved", "Completed " & "- " & S6 percentage & "%", ))

[EddyB]

That's great Ugo, didn't think of doing it this way so thanks for that.

I will give it a try and let you know how that affects things.

Many Thanks

Ed.

[EddyB]

Ugo,

A big big thank you!

I don't have to re-write my database - just make a lot of changes!

I have started changing the Status fields to numbers rather than text. Then the next status field is a case/choose function based on the previous.

I have only just started changing the first few sections and speed has improved greatly already.

Many thanks again

Ed.