>Hi,
>
>I have created an ASP page that accesses a VFP database and queries it via SQL.
>
>The SQL used to be run on the web server with an IDC file and all was well. The exact syntax of the 'WHERE' statment is part of an index and I used to get an immediate response.
>
>Now that I use an ASP page, I still get the correct result but it no longer seems to use the index and takes several minutes to process (the 'main' table has approx 2.5 million records).
>
>syntax is shown below and I have hard coded a parameter into the query for now.
>
>Can anybody help ?
>
>'Create a DataBase Connection and store it to a variable named cnn
>set cnn =Server.CreateObject("ADODB.Connection")
>cnn.CommandTimeout = 0
>
>'Open a connection to the DataBase
>cnn.Open "Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=c:\inetpub\wwwroot\infonet\postcode\postcode.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
>
>'Create a Query
>SQL_query =
>"SELECT Postcode, Healthaut
>FROM main left outer join pcg on main.pcg=pcg.pcg_code
>WHERE (iif(at(' ',alltrim(postcode))=0,alltrim(upper(postcode)),alltrim(upper(substr(alltrim(postcode),1,at(' ',alltrim(postcode))-1)+alltrim(substr(postcode,5)))))) = 'PE100LD' "
>
>'Execute Query and store it to a variable called rst
>set rst = cnn.Execute (SQL_Query)

I'm not that familiar with ASP pages, but you can try using an recordset object to be able to manipulate the CacheSize property. Test different numbers and see which one gives you the best response. The default cachesize is 1. According to the docs, this setting causes the operation to retrieve 1 row at a time from the server.
The other thing you can try is spelling out all of the characteristics of your command and connection objects like the CommandType and CursorLocation, respectively. The way you have it ADO has to interpret it at runtime.