|
 |
asp_databases thread: Doesn't Access store Indexes in the mdb?
Message #1 by "Benny" <benny@t...> on Mon, 25 Sep 2000 22:41:40 +0200
|
|
Currently I am trying to optimize a huge MS Access 2000 database which is
queried via an ASP script.
I added some indexes and after that it got much faster but only as long the
application (and so the database) stays opened.
After I close it, reopen it and perform the query again it takes this first
time much more time then on any subsequent call.
I benchmarked this:
- first query takes about 8-9 seconds (which is unacceptable long)
- subsequent queries take about 160-180 milliseconds (which is great)
Can anybody explain what is going on here? Does this mean that the Jet
engine discards the index when the database is closed and rebuilds it when
needed the first time?
Doe you know how to make sure the index is available right from the first
call?
Thanks,
Benny
Message #2 by "Ken Schaefer" <ken@a...> on Wed, 27 Sep 2000 11:20:20 +1000
|
|
Is it possible that your subsequent connections are coming from the OLEDB
resource pool?
(not sure from your post what exactly you mean by "opening the database" and
"application")
Cheers
Ken
----- Original Message -----
From: "Benny" <benny@t...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, September 26, 2000 6:41 AM
Subject: [asp_databases] Doesn't Access store Indexes in the mdb?
> Currently I am trying to optimize a huge MS Access 2000 database which is
> queried via an ASP script.
> I added some indexes and after that it got much faster but only as long
the
> application (and so the database) stays opened.
> After I close it, reopen it and perform the query again it takes this
first
>
> time much more time then on any subsequent call.
>
> I benchmarked this:
> - first query takes about 8-9 seconds (which is unacceptable long)
> - subsequent queries take about 160-180 milliseconds (which is great)
>
> Can anybody explain what is going on here? Does this mean that the Jet
> engine discards the index when the database is closed and rebuilds it when
> needed the first time?
>
> Doe you know how to make sure the index is available right from the first
> call?
>
> Thanks,
>
> Benny
Message #3 by Hal Levy <Hal.Levy@M...> on Wed, 27 Sep 2000 12:59:46 -0400
|
|
It's not the index being re-built. It sounds like that the size of the DB
for the initial query is working in proper time and the second query is
hitting the cache. How large is this dB? Perhaps it is time to upsize to SQL
Server?
You can get around the delay by creating an APPLICATION_ONSTART routine in
the global.asa and "prime the pump".
Hal.Levy@M...
Senior Solutions Consultant
"Extending the Corporate Reach" (TM)
http://MediaServ.com
(xxx) xxx-xxxx Ext. 372
----- Original Message -----
From: "Benny" <benny@t...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, September 26, 2000 6:41 AM
Subject: [asp_databases] Doesn't Access store Indexes in the mdb?
> After I close it, reopen it and perform the query again it takes this
first
>
> time much more time then on any subsequent call.
>
> I benchmarked this:
> - first query takes about 8-9 seconds (which is unacceptable long)
> - subsequent queries take about 160-180 milliseconds (which is great)
|
|
|
View Cart
