panthers1788
Registered User.
- Local time
- Today, 11:41
- Joined
- Jun 24, 2013
- Messages
- 11
I have a setup where I have a database front-end with another database which is the backend. All forms and VBA are stored in the front-end and the data is stored in the backend (pretty standard).
The client requested that I take the backend and "split" it into 2 seperate backends, as there was a straightforward distinction between 2 different sets of data, where 1 set should go in one backend and the other in another backend.
I didn't think much about it and simply went in and deleted some data inside certain tables by selecting, right-click delete (Not SQL delete statments). In other instances I right-click and delete on entire tables. (NOT an sql statmenet).
Once I completed this I noticed something that I found to be interesting. After deleting a bunch of data (and different data) from each backend (As to begin with I just made a complete copy and paste of the database, and then deleted the necessary data in each.) After doing those deletions the 2 seperate backends were exactly the same size as the databse was before doing any deltions on the previous "Master Backend". (around 22MB). I didn't think anything more about this until after I realized many users were having issues and I started looking into it. What I noticed was that running queries in both backend databases were running very much slower than before the deletions. The problems the users were experiencing all stemmed from the speed.
I did some research about the speed issue and came across doing a Repair/Compact. I did that and it did indeed make the backend database files much smaller. I thought this may have fixed it, but unfortunately it didn't.
I did some thinking of why the deleting of records would slow the database down and the only thing I can come up with is somethign to do with indexing. I never changed any default settings when creating these tables so I don't know if indexing was turned on or off, or if that would even be an issue. My thoughts would be that when running the queries it is not finding data that used to exist which is causing it to choke up, just my thoughts.
Any suggestions? Ideas?
The client requested that I take the backend and "split" it into 2 seperate backends, as there was a straightforward distinction between 2 different sets of data, where 1 set should go in one backend and the other in another backend.
I didn't think much about it and simply went in and deleted some data inside certain tables by selecting, right-click delete (Not SQL delete statments). In other instances I right-click and delete on entire tables. (NOT an sql statmenet).
Once I completed this I noticed something that I found to be interesting. After deleting a bunch of data (and different data) from each backend (As to begin with I just made a complete copy and paste of the database, and then deleted the necessary data in each.) After doing those deletions the 2 seperate backends were exactly the same size as the databse was before doing any deltions on the previous "Master Backend". (around 22MB). I didn't think anything more about this until after I realized many users were having issues and I started looking into it. What I noticed was that running queries in both backend databases were running very much slower than before the deletions. The problems the users were experiencing all stemmed from the speed.
I did some research about the speed issue and came across doing a Repair/Compact. I did that and it did indeed make the backend database files much smaller. I thought this may have fixed it, but unfortunately it didn't.
I did some thinking of why the deleting of records would slow the database down and the only thing I can come up with is somethign to do with indexing. I never changed any default settings when creating these tables so I don't know if indexing was turned on or off, or if that would even be an issue. My thoughts would be that when running the queries it is not finding data that used to exist which is causing it to choke up, just my thoughts.
Any suggestions? Ideas?