Link to home
Start Free TrialLog in
Avatar of Thor_DK
Thor_DK

asked on

Performance decreased a lot after adding a single field

Given a database Access 2000 front end (MDE type) connected using ODBC to tables on a MS SQL server 2000.
Performance has been very fine until today, where I added a single field (type real) to one table.

I have an older version of the front end (MDE type) connected to the same tables in the same SQL back-end database. This version has'nt had any decrease in performane.

In the MDB file I reconnected the tables to get the new field available.
After that operation, the performance of the MDB file (and the new compiled MDE) file has decreased a lot. In fact it is totally useless.
The old MDE file (which has'nt the new extra field) still works fine and fast.

I have tried to add an extra index with the new field, but that has not changed the performance.

Please: Can anyone explain why does the performance suddenly decrease so much and what should I do?
Avatar of arbert
arbert

So you're using Linked Tables or recordsets?

Adding an index to a field is pointless unless you use it in a where clause and it has good cardinality--you just increase your update times....


If you're using linked tables--did you actually relink them?
Avatar of Thor_DK

ASKER

Thank you.

In fact I use both. I use queries stored in the front end MDE/MDB file and in the code I use both recordsets and tables.

Yes, I did relink the all the tables.

The new field will contain a lot of equal entries, so I will delete the indexing of this field.
Weird--can you post some examples of what is slow?

Brett
Avatar of Thor_DK

ASKER

Here is the example requested.

Its a time/work accounting database.

The table (tblKomme) to which the extra field was added is also the table with the most records. It contains approx. 78000 records.
Each record contains the following fields
1 Identity field (int), 1 nchar field of length 20, 1 datetime, 10 smalldatetime fields, 4 fields of type real, 2 fields of type bit.
All the other tables (3 pc) have a small number of records (less than 100 except for one of approx. 1000 records) and only few fields.

When the user opens the database a form is shown with a subset of approx 3400 records from the table tblKomme.

Normally (i.e. before the extra field was added and still in the 'old' MDE file) the forms query completes within 1 second.

After the field was added the duration of the query is approx. 30 seconds. Much too long for the user to wait for each time the database is opened.

Hope this illustrates the large decrease in performance.

The database is located on a seperate SQL 2000 server with extremely light duty.

It also seems very weird to me :-)

Can you take the query that is being generated from the form an see what access plan SQL Server is using to return the rows?

Brett
Have you tried the query analyzer wizard to see what it suggests?  I'm new at these DB's so be nice!
Avatar of Thor_DK

ASKER

arbert: I'm sorry but I don't understand your comment. What do you mean by the word "plan"?
I don't have access to the database during the weekend, but I will return and state the exact SQL string on monday.

The query is not changed even if the extra field has been added. But it will be, as the information in the new field is to be added to the form. But frist I have to solve this strange performance decrease problem.

Starkin: Thank you. I will se if the analyzer gives me any clue to what might be wrong. The problem arised friday just before I had to leave for the weekend. I'll get back to you with the result monday.
Well, you can either use query analyzer or Profiler to see the "execution plan" that SQL Server is using to resolve the query.

You can pull up query analyzer (ships with the SQL Server client tools), paste your sql into it and hit ctrl-L to display the query plan.

This way, you can see exactly what your query is doing--table scan, index usage...blah blah blah

Sometimes Access (with the way it tries to get the data )doesn't cause the best plans to be generated.

Brett
Avatar of Thor_DK

ASKER

I'm sorry but the query analyzer can not be used as the query connected to the form uses parameters from another open form and is not stored on the server but in the front-end application.

I also have a small correction to my reply 03/14/2003 12:44PM PST. The time to open the form now exeeds 2 minutes!! It used to be within 1 second! Something is really strange here.

The query SQL for the form is (still unchanged)
SELECT tblKalender.*, tblKommeG.MedarbejderID, tblKommeG.IDTid, tblKommeG.Arbejdsdato, tblKommeG.Komme1, tblKommeG.G1, tblKommeG.Komme2, tblKommeG.G2, tblKommeG.Komme3, tblKommeG.G3, tblKommeG.Komme4, tblKommeG.G4, tblKommeG.Komme5, tblKommeG.G5, tblKommeG.SamletArbejde, tblKommeG.Difference, tblKommeG.Flexsaldo, tblKommeG.Lst, tblKommeG.SumPosteringer, tblKommeG.ndret, tblKommeG.ndretAf, tblKommeG.Afspadseringsdag, tblKommeG.Bemrkning
FROM tblKalender LEFT JOIN tblKommeG ON tblKalender.IDDato = tblKommeG.Arbejdsdato
WHERE (((tblKommeG.MedarbejderID)=[Forms]![frmMedarbejder]![txtIDMedarbejder]))
ORDER BY tblKalender.Datoen;

Regards
Thor
ASKER CERTIFIED SOLUTION
Avatar of Thor_DK
Thor_DK

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please close this question or award points...........
Avatar of Thor_DK

ASKER

Question closed.
I could not find any "Close this question" button???????
You either "Accept as Answer" or post a question in "Community Support" to close it.

Brett
Thor_DK:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
Why did you accept the ClanupPing as the answer (and with a grade of C as well).
Avatar of Thor_DK

ASKER

Because I could not find out how to end the question without accepting an answer. So I just picked one at random and accepted it. I'm sorry if I've offended you.

Just today I found out how to end a question that I've solved my self (posting the solution and posting another request in the community to get a refund etc.).
I will sure use that method in the future, if I get in the situation again.

Kind regards
Thor