UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Determine Where Fe Was Opened From, Access 2016    
 
   
FrankRuperto
post Mar 24 2020, 09:20 AM
Post#21



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


His immediate issue is the aacdb has a 2GB limit and his app is already hitting that max when certain queries run, so he's running C&R daily to try to keep the BE from blowing up. He needs to move quickly to identify whats causing bloat. Archive old data that's perhaps not needed, move images and docs to external windows folders, check text fields to see if imported data came with padded spaces, see if theres redundant data and tables not normalized, then plan for upsizing backend if all previous suggestions dont alleviate space problem.
This post has been edited by FrankRuperto: Mar 24 2020, 09:24 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
JAchord
post Mar 24 2020, 11:46 AM
Post#22



Posts: 227
Joined: 11-July 14



It is so big because of the record count. Item table is 361,778 records, jobs table is 452,115 records, Job material table is 1,362,871 records, jobroute is 1,125,406 records, and notes table is 5,330,590 records. So 8,632,760 records in this BE alone.

Item has item details original CSV is 277,629KB
Job has job details original csv is 203,225kb
Jobmaterial has materials used in jobs original csv is 619,970kb
Jobroute is information on job organization original csv is 399,218kb
notes are notes for items and jobs originally shorttxt field, now a memo field could cause some bloating.

None have images/attachments, all are normalized. All obsolete data has been deleted (who needs cost from 10 years ago). All logos/images in forms are in the front end so not an issue with the back end. No insertions or deletions. From my reading, the DB file size is based on field count even if the field is empty. All unnecessary fields have been deleted. The original ERP also allowed things like *,?, and NULL as values that caused all sorts of issues and have been deleted.

This is primarily a historical DB created from a data dump from an old ERP. We add records or modify records occasionally but not often. I will probably move the notes table to the other BE which should help.
Go to the top of the page
 
FrankRuperto
post Mar 24 2020, 12:12 PM
Post#23



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


Ok, that info helps. Was the ERP data you imported in a db that used fixed length fields? I'm assuming this data came from a Progress db? Have you checked the Access text/memo fields to see if there's spaces after the text values? If true, you can significantly reduce db size by trimming those padded spaces. Whether there's padded spaces, or not, because of the large record counts you should plan on upsizing the backend anyway to a db server like SQL-Server Express which can handle up to 10GB db size and 10 concurrent users. If the Access db is primarily used as a historical reference, then your users are mostly running queries to lookup stuff and their queries will/have caused the 2GB limit to be reached. Changes to the Access frontend will also be required to optimize the app for SQL-Server.
This post has been edited by FrankRuperto: Mar 24 2020, 12:17 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
JAchord
post Mar 24 2020, 03:03 PM
Post#24



Posts: 227
Joined: 11-July 14



The files were exported using the progress data administration app into a text file. I did check for padded spaces and deleted them all a while back. It may have freed up 1 meg but not much more. I will check to see if there is a fixed field length on any of the text fields.

I think one way to free up some space is to combine all of the notes from one key into one record. Progress limited the notes to 255 characters and then used a key with sequences. the key was linked to whatever the notes were for. Not sure if moving the data from all of the sequences to the top sequence would do anything to free up space. It would take 10 records and place the data in one memo field for one record instead of 10.
Go to the top of the page
 
FrankRuperto
post Mar 24 2020, 08:19 PM
Post#25



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


I dont think consolidating the notes will save much space, but storing them in txt files externally in a windows folder and referencing their path in access would save more space. However, the fact remains you have large record counts which justifies upsizing to a db server backend. Do users need to query and edit all those records, or can some be archived offline?

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
JAchord
post Mar 25 2020, 09:35 AM
Post#26



Posts: 227
Joined: 11-July 14



yes they have to be available for query at any time. Updates are not as critical as they do not happen often. New records are added as needed so average two to three times a month.

I already had issues with forms taking for ever to load since the subform was querying these records. Solved that so that the recordset for the subform is not populated till the tab for that subform is selected. Still not quick but at least the master form opens quickly without having to populate 5 subforms all at once.
Go to the top of the page
 
FrankRuperto
post Mar 25 2020, 10:56 AM
Post#27



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


QUOTE
I already had issues with forms taking for ever to load since the subform was querying these records.

Yes, that will definitely make your forms crawl. Since there's so many records in your tables, you should just load small blocks of records at a time when users are viewing them in the subforms, then load the next block as users scroll through X number of records, insteading of loading all the records when user makes a subform active.
This post has been edited by FrankRuperto: Mar 25 2020, 11:13 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
JAchord
post Mar 25 2020, 04:45 PM
Post#28



Posts: 227
Joined: 11-July 14



The one that gives me the most problems is when I show where an item is used. It has to query all of the job material records for a current item and an obsolete item if available. It may only show three records in the end but still takes about 30seconds to run. The next slowest is pulling the Bill of materials.
Go to the top of the page
 
FrankRuperto
post Mar 25 2020, 04:58 PM
Post#29



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


Is there an index on the item field of the job material table? As to pulling the BOM, again you might have to pull blocks of records as needed, and not the entire table. The idea is to fetch as few records and fields possible to complete the task at hand. If you only need some fields, dont SELECT * FROM BigTable, just SELECT SomeFields FROM BigTable. You can also use SELECT TOP 100 ... FROM BigTable to bring in 100 records at a time versus bringing in the entire 800,000 records from a BigTable.
This post has been edited by FrankRuperto: Mar 25 2020, 05:09 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
JAchord
post Mar 26 2020, 01:17 PM
Post#30



Posts: 227
Joined: 11-July 14



Most results are less than 50 records. I think part of it may be that I am pulling all the data from one query. It may be faster to pull just the keys in one query then use another query to pull the rest of the informaiton. I maygive this a try one day if I get some free time.
Go to the top of the page
 
FrankRuperto
post Mar 26 2020, 06:22 PM
Post#31



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


Subqueries have a high cost so try avoiding them when possible. Analyze each query to see if you can make them more efficient, e.g. only select the fields that are needed, indexes on fields that are not being used in "WHERE" conditions, (indexes consume space), remove fields from tables that are not really needed. Table scans and sorts (ORDER BY) also consume extra space when queries are building the result set.

Here's some additional things to look at:

http://www.fmsinc.com/tpapers/faster/

http://allenbrowne.com/QueryPerfIssue.html
This post has been edited by FrankRuperto: Mar 26 2020, 06:28 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
Jeff B.
post Mar 26 2020, 07:13 PM
Post#32


UtterAccess VIP
Posts: 10,480
Joined: 30-April 10
From: Pacific NorthWet


I'll chime in, too. 1.8 G is a lot of data. How many records are in your tables?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
FrankRuperto
post Mar 26 2020, 08:03 PM
Post#33



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


Hi JeffB, see post #22

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
FrankRuperto
post Mar 26 2020, 09:05 PM
Post#34



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


The notes table has 5.3M records so exporting all those notes to text files stored in a windows folder, and in the Access table replace the notes memo field with a text field that stores the reference paths to the text files will save lots of space.

This post has been edited by FrankRuperto: Mar 26 2020, 09:26 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
JAchord
post Mar 27 2020, 09:12 AM
Post#35



Posts: 227
Joined: 11-July 14



I may have to consider that. As it is I had to code a way to export them to a text file useing space as a delimiter, just so we could cut and past them into other files. Since each note line was a seperate record you couldn't just cut and past the notes without doing it for each line individualy.
Go to the top of the page
 
FrankRuperto
post Mar 27 2020, 09:29 AM
Post#36



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


Rethinking a better stop-gap solution, best to just move the notes table to a new accdb and relink to the notes table. This way you alleviate the 2gig limit problem and don't have to mod the existing vba. Next up is to use unbound forms and only pull in the data as needed.
This post has been edited by FrankRuperto: Mar 27 2020, 09:32 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
Jeff B.
post Mar 28 2020, 07:53 AM
Post#37


UtterAccess VIP
Posts: 10,480
Joined: 30-April 10
From: Pacific NorthWet


Thanks, <Frank>, must have missed or passed in the night...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    28th May 2020 - 02:43 AM