Question regarding Compiling Queries

[Denburt]

According to our lovely friends at Microsoft they say that after compacting a database the queries should be recompiled by opening each one then closing it. So I compacted the database I was currently working on and it was about 100 megs down to a mere 30 megs. I wrote a routine to cycle through and run each of my queries, I then watched it run lol (crapped out twice Hmmm). Looks like i need an external log to see which one it hangs on. Well after running most of the queries (and crashed lol) it is 45 megs O.K. I can deal with this I guess if it is for performance size isn't as big an issue.

My question is though according to the following article:
http://support.microsoft.com/kb/209126

They mentioned several things about queries but I think they were vague. In the article they mentioned timing of a query.

There are two significant time measurements for a Select query: • Time to display the first screen of data
• Time to obtain the last record
If a query returns only one screen of data, these two time measurements are the same. If a query returns many records, these time measurements can be very different.

Then later this.

After you compact your database, run each query to compile the query so that each query will now have the updated table statistics.

So if we are opening the query to optimize it, we are doing so to update table statistics. Does this mean that we should open each query then scroll to the last record or is merely opening the query then closing it enough to fully compile it? Did I miss something in the article or are they just not very clear about this?

TIA

[NeoPa]

The statistics are updated whenever a query is compiled. A query is flagged for compiling when you save any changes to the query (or its underlying tables) and when the database is compacted. If a query is flagged for compiling, the compiling and the updating of statistics occurs the next time that the query is run. Compiling typically takes from one second to four seconds.

If you add a significant number of records to your database, you must open and then save your queries to recompile the queries. For example, if you design and then test a query by using a small set of sample data, you must re-compile the query after additional records are added to the database. When you do this, you want to make sure that optimal query performance is achieved when your application is in use.

From the article, I would say merely opening it and saving it would do the trick.
By the time it's shown any records it has already processed through the optimisation stage.
Interesting article. Some new info for me :)

[Denburt]

Thanks that was the same thinking I had but not sure. It also stated that the queries that need optimization were flagged somehow but didn't say how or where this "Flag" could be found or how to access it. i am thinking it may be in a system table but not sure. This could save a LOT of time and save on the bloating issue, most of my db's have a LOT of queries some of which may take a few minutes to run.

[Denburt]

I thought I would throw this out there in case anyone was interested.

Public Function ComileQueries()
Dim Q As Object
Dim db As Database
Set db = CurrentDb
For Each Q In db.QueryDefs
    If Not Q.Name Like "~*" Then
        'Debug.Print Q.Type & "   " & Q.Name
        Select Case Q.Type
            'Select Queries are 0 and Union queries are 128
            Case 0, 128
                On Error Resume Next
                DoCmd.OpenQuery Q.Name
                'If a table was removed and the query still refers to it a 3192 error will occur
                If Err.Number = 3192 Then
                    Debug.Print Q.Name
                Else
                    DoCmd.Close acQuery, Q.Name
                End If
        End Select
    End If
Next
Set db = Nothing
End Function
 

[NeoPa]

I don't think they wanted to publish where this extra object data is held (Essentially a dirty flag). It may be discoverable with some digging in the system tables, or even on the web maybe...
As for your routine, although you only run the safe ones (SELECT & UNION) remember that the other queries will be in the same state and will benefit from being run too (probably manually :().

[ADezii]

According to our lovely friends at Microsoft they say that after compacting a database the queries should be recompiled by opening each one then closing it. So I compacted the database I was currently working on and it was about 100 megs down to a mere 30 megs. I wrote a routine to cycle through and run each of my queries, I then watched it run lol (crapped out twice Hmmm). Looks like i need an external log to see which one it hangs on. Well after running most of the queries (and crashed lol) it is 45 megs O.K. I can deal with this I guess if it is for performance size isn't as big an issue.

My question is though according to the following article:
http://support.microsoft.com/kb/209126

They mentioned several things about queries but I think they were vague. In the article they mentioned timing of a query.


Then later this.


So if we are opening the query to optimize it, we are doing so to update table statistics. Does this mean that we should open each query then scroll to the last record or is merely opening the query then closing it enough to fully compile it? Did I miss something in the article or are they just not very clear about this?

TIA

I think you may have missed a step. In order to recompile a Query, you must:

1. Open the Query in Design Mode.
2. Save it.
3. Reexecute it.
4. Simply running Queries will not recompile them.

[Denburt]

Thanks for the input ADezii and Neopa looks like I have a little more work to do. I also found that I need to check the SQL statement for form links as well so this could get interesting. Once I get it together I will repost it and see what yall think.

[NeoPa]

I think you may have missed a step. In order to recompile a Query, you must:

1. Open the Query in Design Mode.
2. Save it.
3. Reexecute it.
4. Simply running Queries will not recompile them.

Re: Point 4 - Are you sure about that ADezii?
If you refer to the quoted part of post #2 it suggests otherwise.