?
Solved

Performance decreased a lot after adding a single field

Posted on 2003-03-14
19
Medium Priority
?
266 Views
Last Modified: 2012-05-04
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?
0
Comment
Question by:Thor_DK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
19 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 8137385
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?
0
 
LVL 1

Author Comment

by:Thor_DK
ID: 8138175
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.
0
 
LVL 34

Expert Comment

by:arbert
ID: 8138320
Weird--can you post some examples of what is slow?

Brett
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 1

Author Comment

by:Thor_DK
ID: 8139110
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 :-)

0
 
LVL 34

Expert Comment

by:arbert
ID: 8139254
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
0
 

Expert Comment

by:Starkin
ID: 8140523
Have you tried the query analyzer wizard to see what it suggests?  I'm new at these DB's so be nice!
0
 
LVL 1

Author Comment

by:Thor_DK
ID: 8142000
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.
0
 
LVL 34

Expert Comment

by:arbert
ID: 8142895
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
0
 
LVL 1

Author Comment

by:Thor_DK
ID: 8150540
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
0
 
LVL 1

Accepted Solution

by:
Thor_DK earned 0 total points
ID: 8151560
Problem solved.

There was some kind of corruption in the query in question. I generated a new query with exactly the same definition as the one which had a bad performance and used that as recordsource for the form with slow responsetime.
It worked just fine :-)

Well...just another undocumented feature in Microsoft Access

Thanks to everyone for trying.
0
 
LVL 34

Expert Comment

by:arbert
ID: 8718102
Please close this question or award points...........
0
 
LVL 1

Author Comment

by:Thor_DK
ID: 8729472
Question closed.
I could not find any "Close this question" button???????
0
 
LVL 34

Expert Comment

by:arbert
ID: 8731839
You either "Accept as Answer" or post a question in "Community Support" to close it.

Brett
0
 

Expert Comment

by:CleanupPing
ID: 9275861
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.
0
 
LVL 34

Expert Comment

by:arbert
ID: 9634241
Why did you accept the ClanupPing as the answer (and with a grade of C as well).
0
 
LVL 1

Author Comment

by:Thor_DK
ID: 9641782
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
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question