Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-23-2019, 12:30 PM   #1
RichO
Registered Yoozer
 
RichO's Avatar
 
Join Date: Jan 2004
Location: Wisconsin, USA
Posts: 1,035
Thanks: 14
Thanked 2 Times in 2 Posts
RichO is on a distinguished road
Sudden speed issue with back end

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

__________________
Access 2010, Windows 7
RichO is offline   Reply With Quote
Old 08-23-2019, 12:44 PM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,913
Thanks: 114
Thanked 2,983 Times in 2,713 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Sudden speed issue with back end

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/r...cts/4594424204

Hope that helps
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-23-2019, 02:14 PM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,328
Thanks: 40
Thanked 3,667 Times in 3,536 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Sudden speed issue with back end

one other possibility.

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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 08-24-2019, 06:40 AM   #4
RichO
Registered Yoozer
 
RichO's Avatar
 
Join Date: Jan 2004
Location: Wisconsin, USA
Posts: 1,035
Thanks: 14
Thanked 2 Times in 2 Posts
RichO is on a distinguished road
Re: Sudden speed issue with back end

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?
__________________
Access 2010, Windows 7
RichO is offline   Reply With Quote
Old 08-24-2019, 11:29 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,328
Thanks: 40
Thanked 3,667 Times in 3,536 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Sudden speed issue with back end

Quote:
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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 08-25-2019, 07:43 AM   #6
RichO
Registered Yoozer
 
RichO's Avatar
 
Join Date: Jan 2004
Location: Wisconsin, USA
Posts: 1,035
Thanks: 14
Thanked 2 Times in 2 Posts
RichO is on a distinguished road
Re: Sudden speed issue with back end

Not really sure how to do that. Is there a section in access where those relationships are defined?
__________________
Access 2010, Windows 7
RichO is offline   Reply With Quote
Old 08-25-2019, 09:15 AM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,328
Thanks: 40
Thanked 3,667 Times in 3,536 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Sudden speed issue with back end

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.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 08-26-2019, 03:19 PM   #8
RichO
Registered Yoozer
 
RichO's Avatar
 
Join Date: Jan 2004
Location: Wisconsin, USA
Posts: 1,035
Thanks: 14
Thanked 2 Times in 2 Posts
RichO is on a distinguished road
Re: Sudden speed issue with back end

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.
__________________
Access 2010, Windows 7
RichO is offline   Reply With Quote
Old 08-26-2019, 04:21 PM   #9
RichO
Registered Yoozer
 
RichO's Avatar
 
Join Date: Jan 2004
Location: Wisconsin, USA
Posts: 1,035
Thanks: 14
Thanked 2 Times in 2 Posts
RichO is on a distinguished road
Re: Sudden speed issue with back end

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.
Attached Images
File Type: jpg Untitled-1.jpg (75.2 KB, 11 views)
__________________
Access 2010, Windows 7
RichO is offline   Reply With Quote
Old 08-28-2019, 11:09 AM   #10
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,149
Thanks: 3
Thanked 468 Times in 461 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Sudden speed issue with back end

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.
Cronk is offline   Reply With Quote
The Following User Says Thank You to Cronk For This Useful Post:
RichO (08-29-2019)
Old 08-28-2019, 11:23 AM   #11
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,328
Thanks: 40
Thanked 3,667 Times in 3,536 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Sudden speed issue with back end

Quote:
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.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Sudden Issue Exporting Reports to Excel EternalMyrtle Reports 29 08-07-2014 05:36 AM
database speed is getting an issue petko General 6 03-15-2014 11:52 PM
Speed Issue hey23 General 7 06-25-2009 01:33 AM
Front/back end speed-up trick - can anyone clarify this? BarryMK General 5 11-24-2006 07:09 AM
Speed Issue groupy Queries 3 01-14-2004 05:39 AM




All times are GMT -8. The time now is 04:07 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World