poor performance on first query

I've got an ASP site querying an Access database to get photo gallery
thumbnails. There are two types of queries, all on a single table with
15,000 records. For both types, performance for the first query is
poor, and improves on the 2nd and so on. The worst case is the free
search query against two fields, including a memo field - something
like this: SELECT field1, field 2, field 3 FROM myTable WHERE keywords
LIKE keyword OR memofield LIKE keyword ORDER BY field3 DESC

For the two-field query, we're talking a 30-second delay on the first
query, improving on the second try to 5 or 6 seconds. In this way, it
seems to relate to server sessions. That is, a visitor's first search
takes forever, but once that's over with (if they haven't left in
disgust) the speed is acceptable.

Any ideas? Thanks in advance.

Karl

Re: poor performance on first query

And are your indexes ok?

Re: poor performance on first query

satchinchico@yahoo.com wrote:
> I've got an ASP site querying an Access database to get photo gallery
> thumbnails. There are two types of queries, all on a single table with
> 15,000 records. For both types, performance for the first query is
> poor, and improves on the 2nd and so on. The worst case is the free
> search query against two fields, including a memo field - something
> like this: SELECT field1, field 2, field 3 FROM myTable WHERE
> keywords LIKE keyword

There's a problem right there. Unless keyword contains wildcards, you should
be using =, not LIKE.

> OR memofield LIKE keyword ORDER BY field3 DESC
>


> For the two-field query, we're talking a 30-second delay on the first
> query, improving on the second try to 5 or 6 seconds. In this way, it
> seems to relate to server sessions. That is, a visitor's first search
> takes forever, but once that's over with (if they haven't left in
> disgust) the speed is acceptable.
>
There is definitely some caching going on, but not webserver caching.

It sounds as if you are using dynamic sql to create these queries, instead
of using saved parameter queries which are pre-paresed and pre-compiled. By
using dynamic sql, you are forcing Jet to compile these queries at runtime.
This compilation process involves the creation of execution plans. For some
types of queries, especially those involving LIKE and OR, it can take some
time to come up with the best plan. In earlier versions of Jet, this plan
would need to be created every time the query was run, but from the sounds
of your symptoms, Jet may now be capable of caching query plans (this is a
guess on my part - it's been a few years since I used Access, so I'm not
reeally up-to-date with it).

The performance of queries using OR in the WHERE clause may be improved by
converting them to union queries, i.e.:
SELECT field1, field 2, field 3 FROM myTable WHERE keywords
LIKE keyword
UNION
SELECT field1, field 2, field 3 FROM myTable WHERE memofield
LIKE keyword
ORDER BY field3 DESC

Use "UNION ALL" if you don't want duplicates excluded from the results.


If the keywords field contains multiple keywords as the name implies, you
can improve performance even more by breaking them out into a separate table
in which one keyword is contained in each record, making sure you include
this field in the table's primary key. For example if you have this record
(assuming that field1,2 and 3 constitute the primary key):

field1 field2 field3 keywords
1 John Smith cat,Volvo,programmer


You would drop the keywords field from this table and create a new table
called Keywords:

field1 field2 field3 keyword
1 John Smith cat
1 John Smith Volvo
1 John Smith programmer


Then do an inner join between the two tables

SELECT DISTINCT m.field1, m.field 2, m.field 3 FROM myTable m
join Keywords k ON m.field1=k.field1 AND m.field2=k.field2 AND
m.field3=k.field3
WHERE k.keyword = 'cat'


HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: poor performance on first query

Bob Barrows [MVP] wrote:
> The performance of queries using OR in the WHERE clause may be
> improved by converting them to union queries, i.e.:
> SELECT field1, field 2, field 3 FROM myTable WHERE keywords
> LIKE keyword
> UNION
> SELECT field1, field 2, field 3 FROM myTable WHERE memofield
> LIKE keyword
> ORDER BY field3 DESC
>
I just want to expand on this a little: using UNION will only improve
performance it its use allows the query engine to utilize indexes in either
of the two queries being unioned. If the queries in the union still cannot
utilize indexes, then performance will be degraded instead of improved. The
only way to find out is to test the two versions.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: poor performance on first query

satchinchico@yahoo.com wrote:
> I've got an ASP site querying an Access database to get photo gallery
> thumbnails. There are two types of queries, all on a single table with
> 15,000 records. For both types, performance for the first query is
> poor, and improves on the 2nd and so on. The worst case is the free
> search query against two fields, including a memo field - something
> like this: SELECT field1, field 2, field 3 FROM myTable WHERE keywords
> LIKE keyword OR memofield LIKE keyword ORDER BY field3 DESC
>
> For the two-field query, we're talking a 30-second delay on the first
> query, improving on the second try to 5 or 6 seconds. In this way, it
> seems to relate to server sessions. That is, a visitor's first search
> takes forever, but once that's over with (if they haven't left in
> disgust) the speed is acceptable.
>
> Any ideas? Thanks in advance.
>
> Karl

Of course, I could be missing the boat entirely. Perhaps it's the initial
connection that's taking so long. Create a page that simply connects to the
database and writes the time it took to connect. Something like this:

<%
dim t, cn
t=now
response.write "Creating and opening connection
"
set cn=createobject ...
cn.open strConnect
t = datediff("S",t,now)
response.write "Connection open. Connection took " & t & " sec."
%>

Subsequent connections will use pooled connections so it will not take so
long to connect.

If it's the connection time that's the problem, you need to provide more
details. It may not be possible to resolve this - Jet isn't really designed
for server-based applications. For starters, make sure you are using OLE DB
instead of ODBC. See www.connectionstrings.com for details

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: poor performance on first query

Bob - many thanks for the quick and thorough response. I will try the
things you suggest in the next day or so and report results. Just
wanted to get a quick thanks in.

Karl

Re: poor performance on first query

wrote in message
news:1106284358.954566.220470@c13g2000cwb.googlegroups.com.. .
> I've got an ASP site querying an Access database to get photo gallery
> thumbnails. There are two types of queries, all on a single table with
> 15,000 records. For both types, performance for the first query is
> poor, and improves on the 2nd and so on. The worst case is the free
> search query against two fields, including a memo field - something
> like this: SELECT field1, field 2, field 3 FROM myTable WHERE keywords
> LIKE keyword OR memofield LIKE keyword ORDER BY field3 DESC
>
> For the two-field query, we're talking a 30-second delay on the first
> query, improving on the second try to 5 or 6 seconds. In this way, it
> seems to relate to server sessions. That is, a visitor's first search
> takes forever, but once that's over with (if they haven't left in
> disgust) the speed is acceptable.
>
> Any ideas? Thanks in advance.
>
> Karl
>

Are the thumbnails embedded in the database or does the database simple have
a path reference to the thumbnail file? If it's embedded you may want to
consider using path references instead. Here's an article explaining why:

http://aspfaq.com/show.asp?id=2149