Sudden speed issue with back end (1 Viewer)

RichO

Registered Yoozer
Local time
Today, 06:23
Joined
Jan 14, 2004
Messages
1,036
There was a phantom record in the main table that could not be deleted. It was a strange one because the primary key field was the same as another record somehow.

Anyways, I ended up making a temporary backup table with a make table query, then I deleted the original table, created a new table under that name, and then appended all data into the new table.

It works just fine but now it runs slow when reading the table. I tried deleting the link and relinking the table but that does not help.

Any ideas on why this is happening and how to fix it?


Thanks
 

isladogs

MVP / VIP
Local time
Today, 11:23
Joined
Jan 14, 2017
Messages
18,186
Sounds like corruption as it should be impossible to repeat a primary key field value

Try decompiling the FE to remove any corrupt compile code. Also compact the BE.
I would inspect the data in the problem table and look for any records with corrupt data. Start with the 'dodgy' record.
Finally its possible the MSysObjects system table has errors. There are various ways of fixing such errors. See http://www.mendipdatasystems.co.uk/remove-deleted-objects/4594424204

Hope that helps
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:23
Joined
Feb 19, 2013
Messages
16,553
one other possibility.

When you recreated the table, did you also recreate the indexing?
 

RichO

Registered Yoozer
Local time
Today, 06:23
Joined
Jan 14, 2004
Messages
1,036
I can't do much to try and diagnose the original problem because I deleted the table.

When I would open the table and search for the index number, it would find only one, not the duplicate, however, if I search for other data within the duplicate record and filter the form, the record would appear, but the record could not be deleted. Very strange.

Anyways, what exactly are you referring to with recreating the indexing? Do you mean the primary key?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:23
Joined
Feb 19, 2013
Messages
16,553
Anyways, what exactly are you referring to with recreating the indexing? Do you mean the primary key?
Not as such, just any indexing you had on the original table, required for any fields regularly joined, filtered/criteria, sorted on
 

RichO

Registered Yoozer
Local time
Today, 06:23
Joined
Jan 14, 2004
Messages
1,036
Not really sure how to do that. Is there a section in access where those relationships are defined?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:23
Joined
Feb 19, 2013
Messages
16,553
I wasn't talking about relationships - they are defined in the back end - and you need to define them. See the relationships window on the database tools ribbon

with regards indexes, they are also created in the back end - you need to look at your queries for joins, sorts and criteria, forms and reports for filtering and sorting to see if they are required.

Also I hope you table does not use lookups or multivalue fields - a sure killer for performance if you have a fairly large number of records.
 

RichO

Registered Yoozer
Local time
Today, 06:23
Joined
Jan 14, 2004
Messages
1,036
I checked the original back end and there are no relationships defined.

There are plenty of queries with joins using the primary key but the one most common operation that runs slow does not use any query objects. It's all done with VBA and recordsets.

The table does not use lookups or multi-value fields.

Tried compact/repair on the back end and did not make a difference.
 

RichO

Registered Yoozer
Local time
Today, 06:23
Joined
Jan 14, 2004
Messages
1,036
I think I got it fixed. I went in and compared the properties for each field in the table to the original one and the "Format" was blank for most fields that had a specified format.
 

Attachments

  • Untitled-1.jpg
    Untitled-1.jpg
    75.2 KB · Views: 152

Cronk

Registered User.
Local time
Today, 22:23
Joined
Jul 4, 2013
Messages
2,770
There has been previous reference in this thread to the need for indexing. Any field which is used in any sort or selection operation (query/recordset) should be indexed for improved performance.


I note in your screen shot, for example, there is no index on the Job_Date field - the Indexed property shows 'No'. I presume that records might be sorted on this field in a form or report and accordingly should have an index.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:23
Joined
Feb 19, 2013
Messages
16,553
the one most common operation that runs slow does not use any query objects. It's all done with VBA and recordsets.
to be clear - a recordset that is created by joining tables, using criteria and/or sorting is still a query. And filtering/sorting on a form or report is still effectively a criteria by another name.

Formatting should have no impact on the performance of a query since a format is simply a view of the data and does not impact the underlying value which is what is used by the query/recordset. Personally I believe it is better to not apply any formatting to a table since it hides the underlying value - dates may contain time elements, time may contain date elements, doubles may contain more than the formatted number of decimal places, etc.
 

Users who are viewing this thread

Top Bottom