Paul Hartland
paul.hartland at googlemail.com
Wed Jan 5 23:15:51 CST 2011
John, I remember we had a similar problem at my old company many years back, especially with one table. Our simple solution (which surprised me that it worked) was to add two fields to the table a TimeStamp field and a UniqueIdentifier field (think those are the names of the datatypes in SQL). You could try that first. Paul On 5 January 2011 18:00, jwcolby <jwcolby at colbyconsulting.com> wrote: > I am having performance issues in a largish Access application, a > Disability Insurance Claim call center app. > > I have one particular table which is not huge in terms of field count but > it does have a lot of records and most of the fields are indexed, and it has > about 800K records in it. This table holds "contact" info, as in phone > calls that the users have. They document every "contact" with every one, > claimants, doctors, lawyers, etc. into a memo field and also date of call, > ClaimID FK, employee id FK, contact type id FK etc. Kind of a mini center > of the universe for this application. > > The result is that people are storing new records in this table constantly > throughout the day and we are getting a lot of "record locked..." issues > caused by (AFAICT) the time it takes Jet to store the records and update all > of the indexes, and probably the memo storage area of the mdb. > > Just to give a picture, this one table has been moved out to it's own mdb > and that mdb is about 700 megabytes after a compact. Most of the rest of > the database (150 tables) is in another mdb and after compact that database > is 800 megabytes, so this one table is close to as big as the rest of the > db. > > I do not have experience in a transactional database using SQL Server, but > I am thinking that SQL Server express 2005 will not have an issue keeping up > with this kind of usage - 25 users adding records to this table all day > without causing locking issues like I am seeing now. > > My issue at this point is that they use a network logon and force the users > to change their password every 30 days. Is SQL Server going to use that > same network username / password database or does it use a list of usernames > / passwords physically on the server itself? IOW will Windows > authentication work or will I need to go to SQL Server username / password? > > -- > John W. Colby > www.ColbyConsulting.com > _______________________________________________ > dba-VB mailing list > dba-VB at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-vb > http://www.databaseadvisors.com > > -- Paul Hartland paul.hartland at googlemail.com