Query very slow after compacting

[Giobibo]

I have an Access DB (Jet 3.5) with nearly 100.000 records (100 MB).

Last week I have compacted the database for the first time. After
compacting I have seen that a query is very slow, it takes nearly 4
seconds, while on the old database around 0.1 seconds (on a Pentium 4
PC, but on old Celeron it takes around 2 minutes)

The strange is that only this particular query is slower now, all other
queries take the same time as before.

The query is:

"SELECT * FROM List WHERE Date BETWEEN #" & sDate & " 00:00:00#" & " AND
#" & sDate & " 23:59:59# AND TPNr=Number"

it shows all records of a day (sDate) where the field "TPNr" has the
same value as the field "Number".

I have created the ShowPlan of the old database and of the compressed
database (see below) and noticed that they are different, in the
compacted database and index is not anymore considered (TestID).

Thus the compact action has altered the database in a way that a query
is not anymore so fast as before (I would say unusable slow)

I would appreciate any suggestion and any help to understand what
happened and how to speed up again my query.
Thank you very much.


==========================================================================
SHOWPLAN BEFORE COMPACTING
==========================================================================
- Inputs to Query -
Table 'Test'
Table 'Peak'
Using index 'TestID'
Having Indexes:
TestID 132225 entries, 446 pages, 107417 values
which has 1 column, fixed
Number 132225 entries, 612 pages, 9 values
which has 1 column, fixed
- End inputs to Query -

01) Restrict rows of table Test
using rushmore
for expression "(TDatum Between #7/19/2007# And #7/19/2007
23:59:59#) AND (TDatum Between #7/19/2007# And #7/19/2007 23:59:59#)"
02) Inner Join result of '01)' to table 'Peak'
using index 'Peak!TestID'
join expression "Test.TestID=Peak.TestID"
then test expression "TPNr=Number"
03) Sort result of '02)'
==========================================================================

==========================================================================
SHOWPLAN AFTER COMPACTING
==========================================================================
- Inputs to Query -
Table 'Test'
Table 'Peak'
Using index 'Number'
Having Indexes:
Number 132227 entries, 168 pages, 9 values
which has 1 column, fixed
- End inputs to Query -

01) Restrict rows of table Test
using rushmore
for expression "(TDatum Between #7/13/2007# And #7/13/2007
23:59:59#) AND (TDatum Between #7/13/2007# And #7/13/2007 23:59:59#)"
02) Inner Join result of '01)' to table 'Peak'
using index 'Peak!Number'
join expression "TPNr=Number"
then test expression "Test.TestID=Peak.TestID"
03) Sort result of '02)'
==========================================================================

 

[Allen Browne]

Compacting does cause Access to re-evaluate the query plan, but JET's
execution plan should handle this simple a query in its sleep. I am
convinced that something else is going on.

First thing to check would be that the index named TestID still exists. Open
the table itself in design view, and see if there is such an index. For
example, if this is a unique index, and JET discovers there is a duplicate
value, the compact process will delete the violated index. Creating the
index again should fix the problem.

If that doesn't solve it, the issue may be the use of reserved words:
- Date (field)
- Number (field and index)
Try adding square brackets and specifying the table name:

• .[Date]
  • .[Number]
    Ideally, you would rename the fields in your table (or at least alias them
    in the query.) For a list of names to avoid, see:
    http://allenbrowne.com/AppIssueBadWord.html
    
    You could also try including the table name in the SELECT clause from:
    SELECT List.* FROM List WHERE ...
    There's just a stray elephant's chance that may help JET pick up on the
    indexes available.
    
    The other factor is sDate. It's not clear to me how what appears to be a VBA
    string variable fits into the SQL statement of a saved query, but formatting
    the date correctly or declaring the parameter (if that's what it is) or
    something like that may be relevant.

 

[Giobibo]

Allen Browne ha scritto:

First thing to check would be that the index named TestID still exists.

Yes, the index still exists, I have checked that.
Moreover, if I change the query and set "Number=1" then index "TestID"
appears again in the showplan and the query is very fast (around 0.1
seconds, as in the old database)


I'll check the other point and will reply soon, but I wanted to show you
this odd fall.


=====================================================================
SHOWPLAN for query:
"SELECT * FROM List WHERE TDatum BETWEEN #" & sDate & " 00:00:00#" & "
AND #" & sDate & " 23:59:59# AND Number=1"
=====================================================================
- Inputs to Query -
Table 'Test'
Table 'Peak'
Using index 'TestID'
Having Indexes:
TestID 132227 entries, 446 pages, 107418 values
which has 1 column, fixed
Number 132227 entries, 168 pages, 9 values
which has 1 column, fixed
- End inputs to Query -

01) Restrict rows of table Test
using rushmore
for expression "(TDatum Between #7/13/2007# And #7/13/2007
23:59:59#) AND (TDatum Between #7/13/2007# And #7/13/2007 23:59:59#)"
02) Inner Join result of '01)' to table 'Peak'
using index 'Peak!TestID'
join expression "Test.TestID=Peak.TestID"
then test expression "Number=1"
03) Sort result of '02)'
=====================================================================

 

[Giobibo]

Allen Browne wrote:

first, thank you for your help, in my previous reply I forgot to write that.

If that doesn't solve it, the issue may be the use of reserved words:
- Date (field)
- Number (field and index)

The only reserved word I used is "Number", the field name for the date
is "TDatum".
Anyway as already remarked in my previous post, it seems that something
is wrong just with this index/field.
But changing the name is not anymore possible: there are so many
applications (written in 10 years) in vb and vba using that field name,
that I should edit a lot of code (around a dozen of applications).

Try adding square brackets and specifying the table name:

• .[Date]
  • .[Number]
    Ideally, you would rename the fields in your table (or at least alias
    them in the query.) For a list of names to avoid, see:

• • 
    
    Done, but no success
    
    

    You could also try including the table name in the SELECT clause from:
    SELECT List.* FROM List WHERE ...
    There's just a stray elephant's chance that may help JET pick up on the
    indexes available.

    
    Also this brought no improvements.
    
    

    The other factor is sDate. It's not clear to me how what appears to be a
    VBA string variable fits into the SQL statement of a saved query, but
    formatting the date correctly or declaring the parameter (if that's what
    it is) or something like that may be relevant.

    
    The mdb database is used by vb and vba applications.
    The SQL I have report was taken from a vb application, but anyway the
    query is slow both in vba or vb application.
    In short, the sDate is a string where I convert the date from the local
    format to a format supported by jet.
    
    Actually I am really in a loop and cannot find a way to speed-up the
    query..I would so much go back to my old not-compacted database....
    
    Thanks for any suggestion!

 

[Allen Browne]

So, none of the suggestions convinced JET to use the index.

I still don't understand how a VBA string that gets a literal embedded into
it can *be* a compiled query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen Browne wrote:

first, thank you for your help, in my previous reply I forgot to write
that.

If that doesn't solve it, the issue may be the use of reserved words:
- Date (field)
- Number (field and index)

The only reserved word I used is "Number", the field name for the date is
"TDatum".
Anyway as already remarked in my previous post, it seems that something is
wrong just with this index/field.
But changing the name is not anymore possible: there are so many
applications (written in 10 years) in vb and vba using that field name,
that I should edit a lot of code (around a dozen of applications).

Try adding square brackets and specifying the table name:

• .[Date]
  • .[Number]
    Ideally, you would rename the fields in your table (or at least alias
    them in the query.) For a list of names to avoid, see:

• • 
    
    Done, but no success
    
    

    You could also try including the table name in the SELECT clause from:
    SELECT List.* FROM List WHERE ...
    There's just a stray elephant's chance that may help JET pick up on the
    indexes available.

    
    Also this brought no improvements.
    
    

    The other factor is sDate. It's not clear to me how what appears to be a
    VBA string variable fits into the SQL statement of a saved query, but
    formatting the date correctly or declaring the parameter (if that's what
    it is) or something like that may be relevant.

    
    The mdb database is used by vb and vba applications.
    The SQL I have report was taken from a vb application, but anyway the
    query is slow both in vba or vb application.
    In short, the sDate is a string where I convert the date from the local
    format to a format supported by jet.
    
    Actually I am really in a loop and cannot find a way to speed-up the
    query..I would so much go back to my old not-compacted database....
    
    Thanks for any suggestion!

 

[Giobibo]

Allen Browne ha scritto:

So, none of the suggestions convinced JET to use the index.

I still don't understand how a VBA string that gets a literal embedded
into it can *be* a compiled query.

Well, I'll paste here a piece of code that I used in a vb5 program with
the quoted query:

=======================================================================
sSQL = "SELECT * FROM List WHERE Date BETWEEN #" & sDate & " 00:00:00#"
& " AND #" & sDate & " 23:59:59# AND TPNr=Number"

With datTest
.RecordSource = sSQL
.Refresh
End With
=======================================================================

datTest is a Data object.

I create the sSQL string and pass it to the property "RecordSource" of
the data object, finally I refresh the object.
This last refresh takes around 3 second on a dual core PC and more then
2 minutes on old PC.

 

[Giobibo]

Allen Browne ha scritto:

So, none of the suggestions convinced JET to use the index.

I still don't understand how a VBA string that gets a literal embedded
into it can *be* a compiled query.

I have hard-coded the date in order to remove any doubt:

"SELECT * FROM Liste WHERE Date BETWEEN #7/13/2007 00:00:00# AND
#7/13/2007 23:59:59# AND TPNr=Number"

It still takes very long time to be executed.

 

[Giobibo]

Giobibo ha scritto:

I have an Access DB (Jet 3.5) with nearly 100.000 records (100 MB).

Well, after one week I have found something like a solution to my problem:

Simply I have removed the index 'Peak!Number' from the table "Peak" and
now the query run as fast as the old one.
The new showplan explain what has happened: now Jet is forced to use the
old index 'Peak!TestID' for the join and the rushmore for the search.

Why the compression has modified the way of working of jet, I do not
know, but now all the query are very fast again.

=====================================================
THE NEW SHOWPLAN AFTER REMOVING index 'Peak!Number'
=====================================================
- Inputs to Query -
Table 'Test'
Table 'Peak'
Using index 'TestID'
Having Indexes:
TestID 132227 entries, 446 pages, 107422 values
which has 1 column, fixed
- End inputs to Query -

01) Restrict rows of table Test
using rushmore
for expression "(TDatum Between #8/3/2007# And #8/3/2007
23:59:59#) AND (TDatum Between #8/3/2007# And #8/3/2007 23:59:59#)"
02) Inner Join result of '01)' to table 'Peak'
using index 'Peak!TestID'
join expression "Test.TestID=Peak.TestID"
then test expression "TPNr=Number"
03) Sort result of '02)'
====================================================

Thanks to all for the help

 

[Allen Browne]

Looks like the problem was the field/index whose name is a reserved word.