My database is slow suddenly?

[behedwin]

Hi
I have had a constant size of my database for almost a year.
Now suddenly my database have become very slow.
First i thought it was my network since i got the files on a server.
But running some test i get 50-70 Mbps. So it should not be a slow network.
However, if i put my database on my local computer it do get much faster again....
I dont understand that!!

I have the file split so i got a frontend and a backend.
It have been fast for over a year and i have done regular repair/compacts of the files.

Any ideas of what i can do to fix this. I dont even know where to search for an error....

 

[xenou]

Well, I'm stumped. I supposes there's a clue in that it works over VPN fast but not at work fast. But don't know what that clue means. You have to just keep trying different things to get more clues.

What kind of data is in your (I guess I'll say it, teeny tiny little) database? Where is it coming from? How often is it changing? Who is updating, deleting, inserting data into it?

 

[behedwin]

i guess you are right.
the database is a tool to track my staff and company items like computers etc.
each staff have a profile where i keep a log of things that happen, like sick, vacation etc.
each computer/item have a profile and then a log to track repairs and who are using it etc.
we are about 6 ppl that edit the database. but i am the one that built it.

i pretty much never delete data in the db.
instead when for example a staff quit his job, i mark that person with a date and filter the profile out. that way no data is lost if someone would come back i can just bring the old profile back.

i could try and delete all data that have this mark of being "deleted" so that less data is filtered... but is not that much.. specially since you say my "teeny tiny little database"

someone is using the db daily in some way, reading, updating data.

 

[xenou]

Its seems like a pretty complex database with so many forms reports queries (relative to the small amount of data you have). I might have suggested some alternative approaches but it isn't clear how much flexibility you have here.

If you care to, what are the 15 tables (basically, their "subject" or "purpose" ... which might be generally reflected in their names).

You could also just wait and see. If it slowed down suddenly it might speed up suddenly and get back to normal. Or as suggested, if anything new comes to mind (some variable that was not there before - a new person, a new table, a new form, a new workflow, a new IT event ... whatever might be a possible culprit).

 

[behedwin]

My tables are (all names ends with _table, but to lazy to write it here)
Acccess = hold login and user data for the ppl that use the database
BussA = hold log rows about students application of a buss-card.
BussElev = hold student names and data
cloth = hold data about work-cloth
clothA = hold data about who uses a specific garment
IT = hold data about IT products, computers, ipads etc.
ITA = hold data about who owns are are using an IT product.
ITLog = hold log rows about each IT product
key = hold data about keys and access-cards
keyA = hold data about who have a key signed to them
LonFor =holds data about specific ppl´s contracts that are split up between different bank accounts
paste errors = something access created a long time ago.... holds two rows of data in two columns
profile = hold data about staff-memembers names, contracts, start and end dates etc.
profilelog = hold log rows connected to each staff member.
undervisningtid = hold data about specific contracts and those peoples work-time


one process that are very slow is when i search for a person.
i have a form containing one textbox and one listbox.
listbox is populated for example with IT products or staff-members
when i type in the textbox i search in the listbox and filter down to what i am searching for.
this is the code, this was Instant before and is more or less instant when using VPN or on local machine. But at work it takes 5-10 seconds to search
this code is placed in an "*******" for a search-button.

Private Sub SOK_Click()
Application.Echo False


Me.txtSearch.SetFocus

  Dim strSource As String

  strSource = "SELECT KeyOwnerSelect_Query.Key_ID, KeyOwnerSelect_Query.Nyckel_Kort_Nummer, KeyOwnerSelect_Query.Nyckeltyp, KeyOwnerSelect_Query.LasSystem, KeyOwnerSelect_Query.Profile_ID_SK, KeyOwnerSelect_Query.Firstname, KeyOwnerSelect_Query.Lastname, KeyOwnerSelect_Query.KeyArchived " & _
    "FROM KeyOwnerSelect_Query " & _
    "Where (KeyOwnerSelect_Query.Key_ID Like '*" & Me.txtSearch.Text & "*' " _
    & "Or Nyckel_Kort_Nummer Like '*" & Me.txtSearch.Text & "*' " _
    & "Or Nyckeltyp Like '*" & Me.txtSearch.Text & "*' " _
    & "Or LasSystem Like '*" & Me.txtSearch.Text & "*' " _
    & "Or Profile_ID_SK Like '*" & Me.txtSearch.Text & "*' " _
    & "Or Firstname Like '*" & Me.txtSearch.Text & "*' " _
    & "Or Lastname Like '*" & Me.txtSearch.Text & "*') AND KeyArchived is null "

      Me.ListPicker.RowSource = strSource
      Me.ListPicker.Requery


Application.Echo True
End Sub

Since the code have worked so fast before i cant see why it now is slow.
My largest table contains 1500 rows and it is the profiles log table. Each profile/staff have a log where we keep track on vacation/sick or other things... maybe this log table have grown to big?
after that it is the log table about IT products where we log on each product what repairs and other things, it have about 900 rows and after that it is quite far for the next table with about 300 etc.

 

[xenou]

That is about as inefficient of a query as it is possible to write. I guess you could make it slower by adding some left joins. If this is a person search, why include fields like these:

Key_ID,
Nyckel_Kort_Nummer,
Nyckeltyp,
LasSystem,
Profile_ID_SK,
KeyArchived

Is KeyArchived often null?

 

[behedwin]

This example was the search for keys and access-cards to the buildings




The key archive value is set to a date when a user is pressing Delete.
That way i never delete a value i just set the date when someone wanted to delete a row.
This way i can go back and undo a delete.
so in my forms i only show rows where keyarchive is null



Key_ID, = yes this one i can remove it is not needed to search for.
Nyckel_Kort_Nummer, = this one is key-number or card-number it needs to be searched for
Nyckeltyp, this is the type of card or keys
LasSystem, this is the key system we have several
Profile_ID_SK, this is not needed, i can remove that.
KeyArchived = it needs to be set to only show Null values (se below)

 

[xenou]

What are the datatypes of these fields? If they overlap (for instance, two or more are both strings or integers, is there any pattern to the data?) Or just some sample data that you would search for in each field?

1. Nyckel_Kort_Nummer, = this one is key-number or card-number it needs to be searched for
2. Nyckeltyp, this is the type of card or keys
3. LasSystem, this is the key system we have several

I also try to encourage people to use Access built in search features but I think I am 100% unsucessful with that effort.

 

[behedwin]

Datatypes varies from field to field. most of them are string/text and some are numbers.
1. Nyckel_Kort_Nummber is text
2. Nyckeltyp is some predefined options in a dropdown list for the user so i guess text
3. LasSystem is also text

I did not know that access had a built in search feature?
How to use that one to filter/search a listbox... or do you mean i should instead use a sub-form and use the tiny search box at the button of a form?
Or can i use access search function and still use a textbox to search and look for values in a listbox or subform?

Im open to test to rebuild some parts if it is possible and not mess up things
Never done any other coding than with VBA access and excel. So im quite new to all this.

 

[xenou]

Not sure. Just thinking out loud. You are filtering a listbox so you can search in a listbox? The query isn't a search in the listbox, its a query on a table. So technically its a search in a table. And a fairly large search since its basically searching for every value in (almost) every column in (almost) every row. You lose all the advantages of relational databases at this point. It's not what datatabases are good for (plain text files would be equally useful at this point).

I would prefer to filter the list more directly based on what field you want to filter on.

For in built searches you can just use the right mouse button and do a search on any bound field to filter a table or query on that field. Listboxes begone. But nobody does this as far as I know. Seems like everyone prefers to roll their own search features using VBA.

 

[behedwin]

Not sure. Just thinking out loud. You are filtering a listbox so you can search in a listbox? The query isn't a search in the listbox, its a query on a table. So technically its a search in a table. And a fairly large search since its basically searching for every value in (almost) every column in (almost) every row. You lose all the advantages of relational databases at this point. It's not what datatabases are good for (plain text files would be equally useful at this point).

I would prefer to filter the list more directly based on what field you want to filter on.

For in built searches you can just use the right mouse button and do a search on any bound field to filter a table or query on that field. Listboxes begone. But nobody does this as far as I know. Seems like everyone prefers to roll their own search features using VBA.

Well isent that about being userfriendly and trying to build something that others understand.

For me that is very important.
So a rightclick to search is not fast and good enough i think.... Maybe i am wrong