I am trying to regain an adequate level of speed performance following converting my database from 97 to 2010 and splitting it into FE and BE.
The BE is on a server, FE on local machines, about a dozen users accessing. I have followed a wealth of advice (thanks everyone) regarding split databases etc. with some improvement but still some functions that need to happen within a second or so (when bar code scanning) are taking between 4 and 9 seconds. It is domain functions and some queries that are slow. I can't yet see a way around using DCount etc.
I think perhaps my main table (serial number records) is just too big now to perform operations on over a network and I need to take a different approach. It has 112,000 records with 36 fields (only text, number, date and yes/no fields).
I have heard that using temporary tables is the way to go but need some guidance regarding how this can help me. e.g. if I populate a temporary table with say just last years serial numbers I can see that this would work faster, but if the data is then manipulated, how can this be reflected in the original table? Am I misunderstanding something or am I barking up the wrong tree?
Any help appreciated. (I do have a persistent link, have tried it with no ant-virus scanning, have set the table's 'subdatasheet names' property to none, disabled the 'track name auto correct' option, have no multiple users in any one FE, have done 'compact and repair').
The BE is on a server, FE on local machines, about a dozen users accessing. I have followed a wealth of advice (thanks everyone) regarding split databases etc. with some improvement but still some functions that need to happen within a second or so (when bar code scanning) are taking between 4 and 9 seconds. It is domain functions and some queries that are slow. I can't yet see a way around using DCount etc.
I think perhaps my main table (serial number records) is just too big now to perform operations on over a network and I need to take a different approach. It has 112,000 records with 36 fields (only text, number, date and yes/no fields).
I have heard that using temporary tables is the way to go but need some guidance regarding how this can help me. e.g. if I populate a temporary table with say just last years serial numbers I can see that this would work faster, but if the data is then manipulated, how can this be reflected in the original table? Am I misunderstanding something or am I barking up the wrong tree?
Any help appreciated. (I do have a persistent link, have tried it with no ant-virus scanning, have set the table's 'subdatasheet names' property to none, disabled the 'track name auto correct' option, have no multiple users in any one FE, have done 'compact and repair').
