 | |
Slow multi-user application, 1 user is fine...
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Slow multi-user application, 1 user is fine... -
11-05-2010
, 02:27 PM
|
But without the sec key the speed is fine... (PK is just an autonumber.) So now I have to make *sure* that records in the temptable to be appended are not yet in the backend-table... Well.. I have to be *sure*...! When I keep the secondary key and only append recs that don't exist, the append is still painfully slow... |
Remember, with row locking on, what was a 3 or 4 meg growth can turn into a
120 megs. With just 2, or 3 users, that translates into 350 megs vs less
then 10 megs of network bandwidth. It is rather an large amount. This issue
is "usually" more noticeable with code that updates data as opposed to code
that add records, but it still is a setting you want to check, and then
double check again.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com
#12
| |||
| |||
|
|
I just discovered: When I delete the secondary key (unique key on three fields) the speed is fine... Could there be a problem with indexes because of the conversion ?? ==> I am considering to recreate the relations and indexes again... But without the sec key the speed is fine... (PK is just an autonumber.) So now I have to make *sure* that records in the temptable to be appended are not yet in the backend-table... Well.. I have to be *sure*...! When I keep the secondary key and only append recs that don't exist, the append is still painfully slow... BTW: Using a temptable is very fast... No problem at all with that. "Marshall Barton" <marshbarton (AT) wowway (DOT) com> schreef Use an unmatched query in the append: INSERT INTO table SELECT T.f1, T.f2, ... FROM temp As T LEFT JOIN table ON T.id= table.id And T.sod = table.sid WHERE table.sid Is Null OTOH, using a temp table may account for part of the slowness. -- Marsh |
#13
| |||
| |||
|
|
Have you created an index on the secondary key in both tables? -- Marsh Secondary key consists of three fields . all of them indexed (Duplicates ok) |
no PK of sec key there...
Arno R
#14
| |||
| |||
|
#15
| |||
| |||
|
|
"Albert D. Kallal" <PleaseNOOOsPAMmkallal (AT) msn (DOT) com> schreef in bericht news:rMZAo.10157$z41.460 (AT) newsfe05 (DOT) iad... Have you disabled row locking? Yes I did, In none of the forms I use locking also: Application.SetOption "Use Row level Locking", False Application.SetOption "Default Record Locking", "No locks" Arno R |
However, you do really want to be sure you have this correct.
I was under the suspicion that the row level setting requires a application
re-start. I would simply check this setting in the options.
As noted, I seen this reduce bloat (and thus disk) activity by a factor of
50 times. However, based on your comments, I don't think the above is your
issue. However, when you run out of ideas, then you start to be real careful
in any option settings.
I suppose the other issue would be to disable any of the virus software,
since as of late a seen a good number of postings in this area that been a
problem (even in the case where mdb and accdb files have been excluded from
scans, their was a huge slow down).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com
#16
| |||
| |||
|
|
As I noted, the above suggestion is/was a long shot on my part. However, you do really want to be sure you have this correct. I was under the suspicion that the row level setting requires a application re-start. I would simply check this setting in the options. As noted, I seen this reduce bloat (and thus disk) activity by a factor of 50 times. However, based on your comments, I don't think the above is your issue. However, when you run out of ideas, then you start to be real careful in any option settings. I suppose the other issue would be to disable any of the virus software, since as of late a seen a good number of postings in this area that been a problem (even in the case where mdb and accdb files have been excluded from scans, their was a huge slow down). |
Hi Albert,
Thanks for your ideas. Appreciated!
I just opened another thread:
'Severe backend bloat while appending data'
Regards, Arno R
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.