slow queries, didn't used to be a problem

[tdw]

Hi all,

I have an "Address" field that, upon an After Update event, searches the database to see if the address I just entered already exists in the database. This search used to only take a little while on the first new record entry. From then on, as long as I kept the database open, this search was practically instantaneous.

I also have a button that I click when I am done entering a new record that, among other things, assigns a file number to the record. It does not use an autonumber field. What it does is search the database for the highest existing file number and adds 1. This search also used to be nearly instantaneous.

Suddenly one day, about two weeks ago, both of these searches started taking forever. I have to wait nearly 30-40 seconds for the address search, and over a minute or two for the file number search.

What might have caused this? I checked to make sure that the fields are indexed. They are.

[missinglinq]

Is this a unified database or a split (front end/back end) database? Is it a stand-alone or does it run on a network? I ask because it really sounds like a network problem, assuming nothing else had changed in you setup, Access-wise.

We probably also need to see the code you're using for the check.

Linq ;0)>

[tdw]

Is this a unified database or a split (front end/back end) database? Is it a stand-alone or does it run on a network? I ask because it really sounds like a network problem, assuming nothing else had changed in you setup, Access-wise.

We probably also need to see the code you're using for the check.

Linq ;0)>

That may be. It is a front end/back end, with the back end on a server. Other tasks are slightly slower, such as printing (networked printer), etc. But they don't seem nearly as slow (in proportion to their usual speed) as the searches listed above. It's acting as though it's not indexing.

If you think seeing the code might help, I can try to get it for you tomorrow.
Thanks for the help. I will look into the server question.

[tdw]

Here are the two bits of code that are running super slow. There doesn't seem to be any slowness to the server running any other programs.

Note: SC_NEW is a table for entering new orders, and for entering and storing quoted orders that have not yet been approved. When a new order is a go-ahead it gets moved to the open orders (SC_OPEN table). Once an order has been completed, it gets moved to the archived orders (SC_ARCH). So there are three tables that these two bits of code search.

This is the code that checks for duplicate addresses. It is an AfterUpdate even in the ADDRESS field on the SC_NEW table:

Private Sub ADDRESS_AfterUpdate()

'   Check to see if there is already a quoted order with this address
    Me.lblChecking1.Visible = True
    Me.ProgressBar.Visible = True
    Me.Form.Repaint
    If DCount("*", "SC_NEW", "[ADDRESS]='" & Me!ADDRESS & "'") <> 0 Then
        MsgBox "There is already a Pending or Quoted Order with this Address. Please search this NEW ORDERS form.", vbOKOnly
    End If
    
    Me.ProgressBar.Value = 33
    
'   Check to see if there is already an open order with this address
    Me.Form.Repaint
    If DCount("*", "SC_OPEN", "[ADDRESS]='" & Me!ADDRESS & "'") <> 0 Then
        MsgBox "There is already an OPEN ORDER with this Address. Please search the OPEN ORDERS form.", vbOKOnly
    End If
    
    Me.ProgressBar.Value = 66
    
'   Check to see if there is an archived order with this address
    Me.Form.Repaint
    If DCount("*", "SC_ARCH", "[ADDRESS]='" & Me!ADDRESS & "'") <> 0 Then
        MsgBox "This may be a Recert or a Duplicate order. An ARCHIVED ORDER with this address was found.", vbOKOnly
    End If

    Me.ProgressBar.Value = 100

    Me.lblChecking1.Visible = False
    Me.ProgressBar.Visible = False
    Me.Form.Repaint

End Sub

This is the code that creates a file number for a new order. It happens as part of a sequence of things on a button click on the New Orders Form that (uses SC_NEW table as it's control source):

    If DCount("*", "SC_OPEN", "Left([FILE_NO],4)=Format(Date(),'yyyy')") = 0 _
        And DCount("*", "SC_ARCH", "Left([FILE_NO],4)=Format(Date(),'yyyy')") = 0 Then

        Me.FILE_NO = Year(DATE) & "-0001"   '   Reset file number to 1
        DoCmd.RunCommand acCmdSaveRecord

    Else

'   Find the highest file number for the current year from both tables
        stHighOpen = Nz(DMax("Right([FILE_NO], 4)", "SC_OPEN", "Left([FILE_NO], 4) = Format(DATE(), 'yyyy')"))
            Me.ProgressBar.Value = 30
        
        stHighArch = Nz(DMax("Right([FILE_NO], 4)", "SC_ARCH", "Left([FILE_NO], 4) = Format(DATE(), 'yyyy')"))
            Me.ProgressBar.Value = 40

'   Pick the one that is highest
            If stHighOpen < stHighArch Then
                stHighest = stHighArch

            Else
                stHighest = stHighOpen

            End If
            
            Me.ProgressBar.Value = 50
        
        '   Create new file number
        If stHighest > 0 And stHighest < 9 Then
            Me.FILE_NO = Year(DATE) & "-000" & stHighest + 1
        End If
        
        If stHighest > 8 And stHighest < 99 Then
            Me.FILE_NO = Year(DATE) & "-00" & stHighest + 1
        End If
        
        If stHighest > 98 And stHighest < 999 Then
            Me.FILE_NO = Year(DATE) & "-0" & stHighest + 1
        End If
        
        If stHighest > 998 Then
            Me.FILE_NO = Year(DATE) & "-" & stHighest + 1
        End If

[tdw]

Latest update: for some unknown reason, it sped back up for one day then went back to being slow. The server has been rebooted since, but that didn't speed it up.

[tdw]

I have narrowed down this problem, I think.

It seems to run fast in the morning, UNTIL another user also opens up the database on their computer. It is a front end/back end, with the back end on a server.

I found several things by googling about similar issues, but can't quite seem to find an answer that fits the situation.

[ChipR]

It seems that the main operations in the first code are the DCount. Is there a primary key field that you can count, or another one that wouldn't contain nulls, instead of *? Also, I've been using DLookup to do the same thing, does anyone know whether one has any advantage over the other?

[tdw]

I just checked and I actually had not assigned a primary key.
So now I have made the FILE_NO field a primary key in the tables. The second bit of code contains DMax functions that use this field.
The DCount function is being used on an ADDRESS field, which is not a primary key. In fact, it can contain duplicates. We often go back and do a new survey on a property that we've been to before, but it is a new order each time.

[ChipR]

I noticed that the criteria for the DCount is the Address, but the field that is being counted is * (all). I have to assume that DCount filters out the records based on the Address first, then counts all the fields, so this shouldn't matter too much, but I'm thinking about trying it out and timing it.

[tdw]

Ah, I see what you mean. Because of my lack of knowledge, I do a lot of my code writing using help files, examples on help sites, copying code from one part of the database to another and making the changes that I think I need... that would be why I have "*" there as the expression. Would doing that differently speed it up?

For the ADDRESS, I don't actually need a "count" per se, I just need it to pop up the message if it finds a match. I don't care how MANY matches, one is enough.

[ChipR]

I know that DLookup only returns one value even if there are many matches, but I don't know if it actually stops looking when it finds one. If it does, it might be quicker. I do this: if not isnull(DLookup( ...)) then a record already exists.
Have you tried timing any chunks of your code? I had a really slow form with subforms on multiple tabs and timed functions to determine what was really slow and focused on improving those.

I'm just learning VBA myself, so I'm going from the MSDN and office websites mostly. This has DLookup and DCount and all the functions:

http://office.microsoft.com/en-us/access/HA012288171033.aspx

And this has been the most important reference (though not helpful here):
Access Object Model Reference

[tdw]

Thanks for the links. I'll check them out and see if they give me a new way to do it.
Part of what's throwing me for a loop is that this did not used to be a problem. Everything ran very fast, even when others were using the database at the same time. Splitting it into front-end/back-end did not slow it down, in fact it sped it up.
Then suddenly it goes very very slow when someone else has the database open, even though they have their own front end. It doesn't seem like the number of records in the tables could be the reason either because there are only a few hundred more records now than there were before. Considering that there are a few thousand records, it doesn't seem like the addition of a few hundred could have had that much effect.

[ChipR]

Very strange. When your users open the database, do you open and hide all your forms? I read that suggestion somewhere for improving the apparent speed of the application, but it seems like that would establish connections to every table from every user, so I don't do it. With regards to multiple users, I only know a little about record locking, but I think if you were waiting due to that, you would get a message.
Is it the same when anyone is connected or just from certain computers, and is it slow with just 2 connections or more? Is it even slower with more? A network problem may be really hard to track down but I've read about crazy network situations I would never have thought to investigate.
I still think I would stick some timing in. Like
dim startTime, endTime As Single
startTime = Timer
DCount...
endTime = Timer
msgbox "DCount took " & Format$(endTime - startTime, "0.0000") & " seconds."
Then you can see if it changes when another user connects and track down where it's getting slow.

[tdw]

Well, without adding the timer stuff I can tell you a few things for certain.
I have a progress bar, so I can see generally where it goes slow. I'm not sure adding the timer would help much because I already know the following:

1. It only takes one other person to be on in order to slow it down.
2. It slows down at the DCount, DMax, DLookup lines.
3. What used to take (and still does if I'm the only one on) 5 to 7 seconds, now takes approximately 2 full minutes!

[FishVal]

Well, without adding the timer stuff I can tell you a few things for certain.
I have a progress bar, so I can see generally where it goes slow. I'm not sure adding the timer would help much because I already know the following:

1. It only takes one other person to be on in order to slow it down.
2. It slows down at the DCount, DMax, DLookup lines.
3. What used to take (and still does if I'm the only one on) 5 to 7 seconds, now takes approximately 2 full minutes!

Hello, tdw.

I would suggest you to further localize source of the problem.

• Create blank databases on clients machines with a simple VBA sub benchmarking just a single domain aggregate function call in a way ChipR has suggested you.
  

  debug.print now()
  dummy=dlookup(....)
  debug.print now()

  Connect to existing backend on server and test execution speed.
• Create a blank database on server. Import table from an existing backend and run the same benchmark on existing frontends and on that new blank frontends.

Regards,
Fish