Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Performance decreased a lot after adding a single field

Posted on 2003-03-14
19
Medium Priority
?
267 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
16 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

581 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