Re: poor performance on first query

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 01/21/05 

Date: Fri, 21 Jan 2005 07:43:46 -0500

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<BR>"
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"

Relevant Pages

  • Re: How to use access query computed fields in Word mailmerge
    ... Once you have gone through the connection process once, ... To make a DDE connection in code from Word 2000/2002 I think you will need ... My access databases have lots of these computed query ... >>> an access database with any data in it. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: How to stop source prompt in mail merge from access
    ... Are you calling OpenDataSource in your Access code? ... connection from the Word mail merge main document because Word will try to ... > The mail merge is being called from the Access database via a Visual Basic ...
    (microsoft.public.word.mailmerge.fields)
  • Re: error messages when connecting to an MS Access query
    ... I'm actually opening the Word document from VBA in the Access database, ... It's actually easy to run a Make Table query in Access, ... I got messages saying it couldn't make the connection. ... When you try to make a DDE connection, if the database is not already ...
    (microsoft.public.word.mailmerge.fields)
  • Re: JDBC Applets - MS Access
    ... > I am working on a Applet at the moment which must be able to access ... > I need to be able to setup a jdbc connection to an Access database. ... JDataConnect also uses a server side JDBC server. ...
    (comp.lang.java.databases)
  • Re: connecting to a database
    ... install at Beta sites, I would like to use an Access Database to store ... I have no idea how to set up a connection. ... connection to an Access Database that they would be willing to post. ... There are no real JDBC driver for MS Access. ...
    (comp.lang.java.programmer)